Updating a table using ADO !

[rdrunner]

Hi there

I have 12 Comboboxs on a form and i am trying to use a for next loop to copy the contents from each combo box to a variable and then copy to an underlying table.

Comboqty is a Variant and contains the number of ComboBox's visible on the form !!!

The Comboboxs are called comboName1, comboName2 and so on up to comboName12 on a form called Form_FirstandLast and the table is called alldetails and the fields are called comboname1,Comboname2and so on to comboName12

'*********
For I = 1 To ComboQty
Form_FirstandLast("Comboname" & I).SetFocus

alldetails!("ComboName" & I) = Form_FirstandLast("ComboName" & I).Text

Next I

Any Help in this problem would be greatly appreciated !!!!

I am new to ADO !

Regards
Murray

 

[boblarson]

To use ADO, first you have to declare a couple of variables.

Example:
Dim cnnCurrent As ADODB.Connection, rstMyRecordset As ADODB.Recordset

Then you have to set the variables:

Set cnnCurrent = CurrentProject.Connection
Set rstMyRecordset = New ADODB.Recordset

Then you have to open your recordset

rstMyRecordset.Open "tblMyTableToOpen", cnnCurrent, adOpenDynamic, adLockOptimistic

Then you can do your stuff in the table:

If you are wanting to add the values of your combo boxes to each corresponding field in a table, then you would do something like this:

rstMyRecordset.AddNew "mycomboNameFieldName", comboname1.Value

Do the AddNew for each combobox if it goes into a different field. Remember to replace my generic terms with your actual table name, field names, combobox names, etc.

Then when all of those are added, be sure to add:

rstMyRecordset.Close
cnnCurrent.Close

Set rstMyRecordset = Nothing
Set cnnCurrent = Nothing

And you should be good to go.

 

[rdrunner]

That will work fine .... I want it to be dynamic so that if i only have 8 out of the 12 comboboxs containing data then i can use the index value of the combobox and then i want to use the index value of the combobox to loop round and just update the table name inside a loop or in a function....

Is this possible ????

Murray

 

[boblarson]

You can do a loop, however, I'm not sure how you'd do it.

Another quick way to do it would be to just put an IF statement where

IF cmbMyComboBox1.Value <> "" then
Do the update portion here
End if

Then do the same for #2-12. So if there is something in the Combo box it will fire and update your table.

BL
hth