updating to a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that has 7 combo boxes, I would like to be able to make a
selection in each box (if no selection made, then default or null), and then
click on a button and have the selected items in each combo box to update/add
7 separate fields on a new line of a separate table. If anyone can help I
would appreciate it. I already have the combos setup and can select a value
in each combo. I am just not sure how to have a button cause all of the info
to update a table.
 
hi,
you can do that with a record set where you open your
table as a recordset and update it with new data.
put this code in the button on click event. change the
field1 to the names of your table fields and the combobox1
to the names of your combo boxes.
if you want to qualify the data you could add
if isnull(me.combobox1) then
mgsbox(" make a selection in combobox1")
exit sub
end if
this way, the recordset will not run untill someone make a
selction in combo box 1.

Dim Dbs As Database
Dim rst As Recordset
Set Dbs = CodeDb()
Set rst = Dbs.OpenRecordset("yourtable", dbOpenDynaset)
With rst
.AddNew
!Field1 = combobox1
!Field2 = combobox2
!Field3 = combobox3
!Field4 = combobox4
!Field5 = combobox5
!Field6 = combobox6
!Field7 = combobox7
.Update
End With
rst.Close
Dbs.Close

hope this helped.
 
Jimenda said:
I have a form that has 7 combo boxes, I would like to be able to make a
selection in each box (if no selection made, then default or null), and then
click on a button and have the selected items in each combo box to update/add
7 separate fields on a new line of a separate table. If anyone can help I
would appreciate it. I already have the combos setup and can select a value
in each combo. I am just not sure how to have a button cause all of the info
to update a table.
Click to expand...

The basic method is...

dim SQL as string

SQL = "INSERT INTO TableName " & _
"(Field1, Field2, Field3, etc...) " & _
"VALUES(" & Me.NumericComboBox & ", '" & _
"Me.TextComboBox & "', #" & _
"Me.DateComboBox & "#, etc...)"

Debug.Print SQL
'CurrentDB.Execute SQL, dbFailOnError

The challenge is in getting a string built up that results in a valid SQL
statement with all the proper delimiters, spaces, and If-then handling to
account for the Null values you want if a ComboBox is left blank.

That is why I include the Debug.Print line and have the execute statement
commented out. What you should do is tweak your code, try the code and then
examine the SQL string that is sent to the immediate window. This can be
examined to see if it is structured as you expected and can even be pasted into
a query to see if it has any errors and works as expected.

Once you have the code creating a correct statement you can comment out the
Debug.Print line and remove the comment from the execute line.
 
Back
Top