Updating on screen text fields with multiple table data
I have posted this query on other newsgroups with no response yet so
I'm guessing this is a toughyu but here goes...
VB6, ADO 2.1, Access97 database
I have a Form with many text boxes set to fields in an ADO recordset.
The recordset holds data from 2 tables. All the data from the 1st table
is stored in the text boxes as it is except for one field which is
stored in a DataComboBox and it holds the primary key to a record in
the 2nd table. The SQL query returns a field from that second table
based on the foreign key in table one matching the primary key in table
SELECT T1.Field1, T1.Field2, T2.Fieldn From Table1 T1 Join Table2 T2
Where T1.ForeignKeyField = T2.PrimaryKeyField
A DataComboBox (DCB) control holds Table1's foreign key to Table2's
Primary key. The row source of the DCB is SELECT Field1, Field2, FIELDn
FROM Table. When I change the value of the DCB, the new value is stored
in the recordset but 2 other fields on the form which hold values from
Table2 are not updated. This situation is augmented more by the face
that there is a 3rd table which is linked to Table2 that is also
displayed on this form.
To better explain... Lets say Table1 holds customer Names. Table2 holds
their Category. And Table 3 holds the regions that each category
belongs to. On the form the DCB displays the Name of the category but
is bound to the recordset via Table1's ForeignKey field which points to
Table2's PrimaryKey. One text box then holds the Catgeory description
and another text box holds the Regions description. This all display
properly except when I change the value of the DCb the other 2 text
boxes are not updated until I close the form and re-load it which opens
the recordset backup.
I know this is a lot. The Requery method has provoked only errors.