Querying in a subform with multiple linked tables

[Anthony]

I am having a problem doing a query on a subform. It is quite complex so
here goes: I have 2 forms, I'll call them FormA and FormB. These forms have
separate tables associated with them. In fact, Form B is a subform of Form
A. When I open Form A, Form B also opens as a subform on Form A in Datasheet
view. I enter data into FormA then tab into Form B to enter more data. Form
B has 5 visible columns (I hide the Form A linked column). That is the set
up. Now here is the problem.
I would like to automatically fill in several columns of Form B(subform)
using data obtained from a third table - Table C. Table C was created
specifically for this convenience so I don't have to manually enter data into
ALL the columns. I would rather NOT use a lookup combo box because Table C
has about 100 records that I would have to scroll through, I would rather the
data just "pop" into those columns when I enter the field. In order to
identify the correct row of data in table C, I need to do a query on Table
C. There are TWO fields in Table C that must be queried in order to uniquely
identify the correct row in Table C. (Both fields in Table C allow duplicate
data, therefore two fields need to be queried in order to uniquely identify
the correct row in table C.) Field 1 matches data I just entered into
COLUMN 1 of Form B and Field TWO matches data I previously enter into a field
on Form A.
I have discovered I cannot create a query of the FormB table and Table C to
use as the basis of the subform because, like I said, it requires a
relationship with 2 fields in table C but only ONE of those fields exists in
TableB - the other field is in Table A(Form A's table). Because of this,
Access cannot create a "One-to-Many" relationship required for subforms, it
becomes an "indeterminate" relationship which "locks" the Dynaset if you try
to update it (I have tried "Dynaset Inconsistent Updates") but Access won't
update the tables as I enter the data. I have tried the Macro wizard (I
don't know how to program VB macros) to "Setvalue" the columns I want BUT the
Macro wizard doesn't allow "Where" SQL statements which I think I need to
query Table C.
Any help would be appreciated. I use OFFICE XP which has Access 2002.

 

[Tom Lake]

I am having a problem doing a query on a subform. It is quite complex so
here goes: I have 2 forms, I'll call them FormA and FormB. These forms
have
separate tables associated with them. In fact, Form B is a subform of
Form
A. When I open Form A, Form B also opens as a subform on Form A in
Datasheet
view. I enter data into FormA then tab into Form B to enter more data.
Form
B has 5 visible columns (I hide the Form A linked column). That is the
set
up. Now here is the problem.
I would like to automatically fill in several columns of Form B(subform)
using data obtained from a third table - Table C. Table C was created
specifically for this convenience so I don't have to manually enter data
into
ALL the columns. I would rather NOT use a lookup combo box because Table
C
has about 100 records that I would have to scroll through, I would rather
the
data just "pop" into those columns when I enter the field.

In the On Enter event of the first column of the subform, use some Dlookups
to populate those fields.

Tom Lake

 

[Anthony]

I am guessing you mean use the "expression builder" on the "On Enter" event
of the field I am trying to fill in - not the first column. The First column
I manually enter data, then I want to "pop" data into the second and Fourth
columns of the subform datasheet. I haven't done it yet since I don't know
the Dlookup command that well but, I problem I think I will have doing that
is one of the fields I need to use to query Table C is the data I just
entered into column 1 of the subform. I think I need to say something like:
"Where form![Form B].field1 = TableC.Field1". However, since that data was
just entered it has not been "Committed" into the table yet so field
"form![Form B].field1" is currently "NULL".