How to link a table for searching and data entry? Please help!

[Guest]

Dear All,

I have two tables A and B (ID is the primary key), and a query C. Query C
catches ID from table A. I have created a form; this form can select ID from
query C. What I need to do is:

If the selected ID is in table B, I will show all the items in table B in
this form. If the selected ID is not in table B, I can enter data through
this form, and all my data entry will go to table B.

My problem is: I can select ID from Query C into this form, but all the item
in table B can not change with the selected ID. I also can not test if I can
do data entry, because only the first record in table B show up in my form.

Thanks,

Blinda

 

[John W. Vinson]

Dear All,

I have two tables A and B (ID is the primary key), and a query C. Query C
catches ID from table A. I have created a form; this form can select ID from
query C. What I need to do is:

If the selected ID is in table B, I will show all the items in table B in
this form. If the selected ID is not in table B, I can enter data through
this form, and all my data entry will go to table B.

My problem is: I can select ID from Query C into this form, but all the item
in table B can not change with the selected ID. I also can not test if I can
do data entry, because only the first record in table B show up in my form.

Thanks,

Blinda

Normally one would use a Form for table A, with a Subform for TableB.
The master/child linking feature of the subform will do just what you
describe - display all the records in TableB matching the ID in table
A, and automatically filling in that ID for any new records.

If you're trying to create a Form based on query C, joining the two
tables together, you'll have a more difficult time! Try the Subform
instead.

John W. Vinson [MVP]

 

[Guest]

Ok, will try that. Thanks.

Normally one would use a Form for table A, with a Subform for TableB.
The master/child linking feature of the subform will do just what you
describe - display all the records in TableB matching the ID in table
A, and automatically filling in that ID for any new records.

If you're trying to create a Form based on query C, joining the two
tables together, you'll have a more difficult time! Try the Subform
instead.

John W. Vinson [MVP]

 

[Guest]

Hi John,

Thanks. I made a table D which include all the records in query C. then I
used your way for searching and data entry. It works very well.

Thanks and have a good day!

Blinda

 

[John W. Vinson]

Thanks. I made a table D which include all the records in query C. then I
used your way for searching and data entry. It works very well.

Of course that's NOT what I suggested... :-{(

if it's working for you, fine.

John W. Vinson [MVP]

 

[Guest]

Thanks. I also tried using my query C for the main form, and table B as the
subform. and link them by the ID in query C. It also works fine.