|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
November 4th, 2009, 11:42 AM
|
|||
|
|||
Forms - Unbound
I am trying to do something very similar to the forum I just read with the unbound forms writing to a table.
I have unbound combo boxes that are updating depending on what they have chosen from the first combo box. That is all working great but when they have selected everything from the combo boxes and typed their comments, I can't get the information to write back to the nonconformance table for reporting later. All combo boxes are using the tblktables to get the data. My question is how do I get an unbound combo box to write to the table since using the bound boxes is not an option?
|
|
#2
November 4th, 2009, 01:00 PM
|
||||
|
||||
|
You can write an Event Procedure that updates your table when a command button ('Save') is clicked. There's a wealth of information on how to do this, such as: http://www.techonthenet.com/access/...update_date.php
__________________
Experience is the thing you have left when everything else is gone.
|
|
#3
November 4th, 2009, 01:30 PM
|
|||
|
|||
|
Thanks. I've been trying to write the event and do have the command button to save. That is where I am getting a little lost as I don't have a lot of VB skills.
|
|
#4
November 4th, 2009, 03:03 PM
|
||||
|
||||
|
Quote:
If you'd like some help with that, you'll have to show us what you have written and explain how it behaves (do you get an error message? what happens?). When you post code here in the forum, please use the [code] tags to enclose it (you can use the # icon on the toolbar of the Message entry box).
|
|
#5
November 4th, 2009, 03:13 PM
|
|||
|
|||
|
Quote:
Code:
Dim db As Database Code:
Dim LUpdate As String Code:
Set db = CurrentDb(tblnonconformance) Code:
LUpdate = "update[strproduct]" Code:
LUpdate = "update[strassembly]" Code:
End Sub I have more of the combo fields to be updated but didn't think you needed them all listed.
|
|
#6
November 4th, 2009, 03:40 PM
|
||||
|
||||
|
Just put one [Code ] tag at the beginning of your code and a [/code ] tag at the end of your code (don't include the space!).
What you posted appears to lack any updating action whatsoever. It appears that you are trying to use the example in the tutorial I suggested for you, but the important lines are missing. Nothing will happen unless you call the db.execute line, and that line must have the "set ... = ..." syntax. If all of your post didn't come through properly, follow my instructions at the beginning of this post, and try again.
|
|
#7
November 4th, 2009, 03:44 PM
|
|||
|
|||
|
Quote:
ok sorry I didn't realize I could just do it once and paste in the body! I am using a couple other samples as well right now and have found one that actually shows the adding of fields.. I know how to use access but have never gotten this deep into the code portion before. I am willing to learn and am currently teaching myself how to use this. Currently, I've added some more lines but am no longer getting an error. the save button is going through but the data is not being placed on the table. I will continue to look at the samples to figure out what I am missing.
|
|
#8
November 4th, 2009, 04:05 PM
|
||||
|
||||
|
Again, if you want help, you will have to show us the code you are having trouble with. We are all willing to help a newcomer, but we can't do it without knowing just what you're doing.
Access VBA has multiple ways of doing most things, so be careful of picking from several different examples and trying to mix them. You need to clearly understand what it is that you are trying to do. You must open a database and send an SQL command to the database, telling it to update a particular record, setting one or more fields' value(s) to the desired contents. So you must understand the SQL syntax, as well as the VBA syntax that sends the SQL command to the database. Often the examples will also include error trapping routines, which is good practice, but can confuse a beginner.
|
|
#9
November 4th, 2009, 04:16 PM
|
|||
|
|||
|
Quote:
Well sure am confused because I keep finding posts out there that are similar to what I want to do and they just aren't working for me. Basically, I have several lookup tables and they are being used for the dropdown combo boxes. The user picks something from the first box which then reruns the query to only allow certain options from the second box. all of that is working fine. they will then add comments in the comment box and who repaired the item again a drop down or text box. then click save for the data to be dumped into the nonconformance table which has all of the fields in there. I'll attach all of the code I have but this time one only in a group. Code:
Option Compare Database
Private Sub btnAdd_New_Click()
End Sub
Private Sub Combo10_AfterUpdate()
DoCmd.Requery
End Sub
Private Sub Save_Machine_comments_Click()
Dim db As Database
Dim LUpdate As String
Dim tbl As TableDef
Dim fld As DAO.Fields
Set db = CurrentDb()
Set tbl = db.TableDefs("tblnonconformance")
Set fld = tbl.CreateField("strproduct", dbText, 50)
LUpdate = "update[strproduct]"
LUpdate = "update[strassembly]"
LUpdate = "update[strcomponent]"
LUpdate = "update[strnonconformance]"
LUpdate = "update[strnonconformanceadj]"
End Sub
Private Sub Save_Nonconformance_Click()
End Sub
Private Sub strProduct_AfterUpdate()
DoCmd.Requery
End Sub
Private Sub tblNonConformance_strComments_AfterUpdate()
DoCmd.save
DoCmd.Requery
End Sub
Private Sub XAssembly_AfterUpdate()
DoCmd.Requery
End Sub
Private Sub XComponent_AfterUpdate()
DoCmd.Requery
End Sub
Private Sub Xnonconfadj_AfterUpdate()
DoCmd.Requery
End Sub
Private Sub XNonConformance_AfterUpdate()
DoCmd.Requery
End Sub
Private Sub add_new_nonconformance_Click()
On Error GoTo Err_add_new_nonconformance_Click
DoCmd.GoToRecord , , acNewRec
Exit_add_new_nonconformance_Click:
Exit Sub
Err_add_new_nonconformance_Click:
MsgBox Err.Description
Resume Exit_add_new_nonconformance_Click
End Sub
Private Sub add_new_record_Click()
On Error GoTo Err_add_new_record_Click
DoCmd.GoToRecord , , acNewRec
Exit_add_new_record_Click:
Exit Sub
Err_add_new_record_Click:
MsgBox Err.Description
Resume Exit_add_new_record_Click
End Sub
I actually have the main form and at the bottom, they are clicking on a link to add a new nonconformance which opens up the form I am trying to get to write to the table. The first form is bascially using a query to write and it is working fine. The problem is someone else was creating this and they are no longer with the company. They had parts of this in access, parts in vb.net etc... I decided it needed to be all in one format and easy for anyone to pickup and run with. If that all makes sense. Thanks for your help.
|
|
#10
November 4th, 2009, 07:55 PM
|
||||
|
||||
|
I'll have to spend a lot more time than I have this evening, to figure out what you're trying to do. There's no way the code you are showing will work. That's the problem with just copying code that you don't understand. Nearly all of what you showed does absolutely nothing. You can't just assign different strings to a variable, one after the other. Only the last one you assign will be contained in the variable. Most of your code is empty. For your information, each block of code that begins with:
Private Sub ... and ends with: End Sub is an independent Event Procedure, so all those you have where there is nothing between the beginning and the end are totally useless and should be deleted. I'll try to find time tomorrow to see if I can untangle that and suggest how you can perhaps get something working.
|
|
#11
November 5th, 2009, 08:11 AM
|
|||
|
|||
|
Quote:
Thank You I've cleaned up the code. Most of that was there from my many attempts to get a button to work etc. I am really not trying to do anything hard. I just don't know how to set the code to make an unbound field write to a table. There is a main form. The user will select a product, serial number etc and that will fill in the remainder of the information for them. They can then add inspection comments or click at the bottom and click on add new non conformance. That button is opening a new form for them to write the non conformance information to. That is where I am having a problem getting that data to write to a table. The nonconformance form is needing to get the auto key number and the new data placed on the form once they click save or exit the form. I can't make the fields bound to a column in the table because the combo box drop downs do not work and won't refresh each other. If there is a much better way of doing combo boxes with the bound options, I am open to hearing them. I am only doing this in a way I've gotten it to work so far. Code:
Option Compare Database Private Sub Combo10_AfterUpdate() DoCmd.Requery End Sub Private Sub strProduct_AfterUpdate() DoCmd.Requery End Sub Private Sub tblNonConformance_strComments_AfterUpdate() DoCmd.save DoCmd.Requery End Sub Private Sub XAssembly_AfterUpdate() DoCmd.Requery End Sub Private Sub XComponent_AfterUpdate() DoCmd.Requery End Sub Private Sub Xnonconfadj_AfterUpdate() DoCmd.Requery End Sub Private Sub XNonConformance_AfterUpdate() DoCmd.Requery End Sub I can send a copy of the screen shot with my form if that would help you see what I am doing.
|
|
#12
November 5th, 2009, 02:06 PM
|
||||
|
||||
|
Quote:
Yes, that would be a big help. Also please show your table structure, like: Code:
tblnonconformance: id Autonumber (PK) strproduct Text strassembly Text strcomponent Text ... ... I probably won't have time to look at this until tomorrow, but it's really 1000% simpler than you are going about it.
|
|
#13
November 5th, 2009, 02:26 PM
|
|||
|
|||
|
Quote:
oh I am sure it is much simpler than what I am trying to do.. I didn't really want a second form but was told the combo boxes needed to be in a header.. this is just the nonconformance table I want to write to. I have all of the lookup tables being used for the combo boxes if you would like them. I would be glad to send you the entire database if that would help. I just want to learn what I am doing wrong and apply all of this to the next two I need to work on yet. Code:
tblnonconformance intPrimaryKey autonumber intinspectionid number strassembly text strcomponent text strnonconformance text strnonconformanceadj text strlocation text strcomments memo struser text dtedate date/time strrepairedby memo strproduct text strsalesorder number I'll get the screen shot over as soon as I figure out how to attach it. thanks
|
|
#14
November 7th, 2009, 12:59 PM
|
||||
|
||||
|
OK, I have a little time this morning to think about this. Going back to your original post, I understand that you have a form with cascading combo boxes and that you want to be able to update the records in the main form, is that correct? Please clarify for me:
The issue would be, I think, how to expand the list to values NOT currently in the table. That would take a little programming. To show you how to do that, here is a simple demo database. It works fine for updating, although now I see that it doesn't properly populate the 2nd combo box for a new record. It's all I have time for this morning, I'm afraid. If this doesn't get you on the right track, post back here and when I have time, I'll give it another look. [Edit:] Oops, now I see that my demo doesn't work properly. It doesn't populate the 2nd combo box properly. I'll have to give that some more thought. I'll leave the example posted here, as it might prove useful in thinking through the task. Last edited by don94403 : November 7th, 2009 at 03:40 PM.
|
|
#15
November 9th, 2009, 09:02 AM
|
|||
|
|||
|
Quote:
Thanks. I think I've got something that will work. It isn't the prettiest way I am sure but it is writing to the table. I am using the following Code:
Private Sub Save_Machine_comments_Click()
Dim A As String
Dim C As String
Dim P As String
Dim N As String
P = xproduct
A = xAssembly
C = xComponent
N = XNonConformance
Dim dbs As Database
Set dbs = OpenDatabase("C:\Documents and Settings\ajblack\Desktop\testonlyfinalinspectionwi thsub.mdb")
DoCmd.RunSQL "insert into tblnonconformance" _
& "(strproduct , strassembly , strcomponent , strnonconformance) " _
& "values('" & P & "','" _
& A & "','" _
& C & "','" _
& N & "');"
dbs.Close
DoCmd.Requery
End Sub
|
|
| Viewing: ASP Free Forums > Database > Microsoft Access Help > Forms - Unbound |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
