VBA Updating two tables from the same form

  • Thread starter Thread starter Graham
  • Start date Start date
G

Graham

I have a database with two tables. I am using a form with two unbound
text boxes and combo boxes to capture user input. The problem is I
want to be able to update two tables from the same form.

The form has three fields first, surname and gender.

txtFirst
txtSurn
cboGender (values: M; F; U)

The two tables have the following fields

tblName
fields:
Person (autonumber)
First (text)
Surn (text)

tblGender
fields:
Person (autonumber)
Gender (text)

Here is my code to update the first table:

Dim rstIndi As ADODB.Recordset
Dim txtINDI As Integer
Set rstIndi = New ADODB.Recordset

rstIndi.Open "NAME", CurrentProject.Connection, adOpenStatic,
adLockOptimistic
If rstIndi.Supports(adAddNew) Then
With rstIndi
.AddNew
.Fields("First") = txtFirst
.Fields("Surn") = txtSurn
.Update
cmdReset_Click
End With
End If

rstIndi.Close

Set rstIndi = Nothing

Now the problem is when the First and Surn fields are added to tblName
it works fine, incrementing the autonumber field as required. How do
I copy the autonumber field from tblName to tblGender and add the
value selected in cboGender on the form to the field Gender, all of
course from the same submit function in the form?

Thanks

Graham
 
It seems to me that you're making life much too hard for yourself. Why not
simply put all your fields into a single table, tblPeople, and bind your
form to that table. Each of the fields you have in both tables refer to a
single attribute of a particular person; normal database design principles
would have all this data in a single table.

If you must have two tables, then the Person field in tblGender CANNOT be an
autonumber if it is to be used as the foreign key for a person in tblName
where the primary key is (unless you've done something really strange) the
autonumbered Person field - it must be a long integer. But again, there
seems to be no reason to do it that way.

HTH,

Rob
 
OK understood.

So I have changed the person field to a long integer in tblGender.
Now how do I update both tables from the one form?

Thanks

Graham
 
The simplest way is to set up your data structure correctly, and combine the
two tables, as I suggested. Use a form bound to the (single) table, with
the name textboxes and the gender combo-box bound to the appropriate fields
in the table, and it all just happens.

Do you have any compelling reason for keeping two tables (apart from making
life harder for yourself)? Do you have any compelling reason for using an
unbound form/fields?

Rob
 
Back
Top