Updating multiple recordsets ???

[Dillinger4]

Hi all!
If someone wanted to add data to multiple fields
in diffrent tables how would that be done?
Can multiple recordsets be open at once?

'Say if i have
Set db = OpenDatabase("C:\whateverpath")
Set rs = db.OpenRecordset("Account Information")

' And then i want to add information to the recordset
' base on what the user entered into the control.

rs.Fields("Account Name") = frmAccountSetup.ctrlAccountName.Text
rs.Fields("Account Address") = frmAccountSetup.ctrlAccountAddress.Text
rs.Fields("Phone Number") = frmAccountSetup.ctrlPhoneNumber.Text
rs.Fields("Account ID") = frmAccountSetup.ctrlAccountID.Text

Thanks all....
'all of the information entered into the controls gets saved into the coresponding fields but Driver id in in another table so i end up getting a run time error item
not found in this collection 3265. So i guess what i getting at is. Is there a way to add data to fields with
the same name in diffrent tables. So i can just open one recordset and close one recordset.


rs.Fields("Driver ID") = frmAccountSetup.ctrlDriverID.Text

[gwdash]

make a query to merge all the data into the query, or use SQL:
NOTE:If you use SQL, make sure you add the appropriate inner Joins

Dim strSQL as String
strSQL="SELECT [Account Name], [Account Address]" & _
", [Phone Number],[Account ID],[Other Table].[Driver ID]," & _
" FROM [Account Information] INNER JOIN [Other Table] ON " & _
"[Other Table].[Account ID] = [Account ID]"
Set DB = DBEngine.Workspaces(0).OpenDatabase("C:\whateverpath\whateverdb.mdb")
Set RS = DB.OpenRecordset(strSQL)
RS.AddNew
RS![Account Name] = frmAccountSetup.ctrlAccountName.Text
RS![Account Address] = frmAccountSetup.ctrlAccountAddress.Text
RS![Account ID] = frmAccountSetup.ctrlAccountID.Text
RS![Phone Number] = frmAccountSetup.ctrlPhoneNumber.Text
RS![Driver ID] = frmAccountSetup.ctrlDriverID.Text
RS.Update

Set RS = Nothing
Set DB = Nothing

This post should probably be in the Database Development Board, not games and graphic development

[Edited by gwdash on 09-09-2000 at 02:39 PM]

[Dillinger4]

Hey gwdash thaks for your help. Yeah i just noticed
that i posted in the wrong forum. I guess that's
what happens when you stare at you monitor to long.
{{{laughing}}}