Searching a Subform

[Curtis Stevens]

You have a main form, you want to search for data in a sub form. Main form
ties to main table. Sub form ties to another table, that has a relationship
to main table. So this subform is basically more info on the main record in
main form. Like main form is customer's info, sub form is owners info, so
you can have more than one owner, etc.

Now, I have merchants name in this sub table / form, split up into two
fields, first & last name. If I wanted to search all the records by
merchant's name, only way I could find was put that table's fields in the
query, so customers table & merchantinfo table both had values listed in the
select query.

BUT the records are locked, you can update them, etc.

Any suggestions?

Thanks
Curtis

 

[ManningFan]

The query needs to be updateable, which means the underlying tables
need to have 1-to-1 relationships with indexed fields.

I think there's a way to do this by making a cross-table, which has
records from both tables in it. For instance, it would have an indexed
field (AutoNumber), CustomerID and MerchantID. Then put this table
between the 2 tables you have now and it creates a 1-to-1 relationship.
Then your query will be updateable.

 

[Curtis Stevens]

The query needs to be updateable, which means the underlying tables

need to have 1-to-1 relationships with indexed fields.

Would this still be true if I just want to use it like normal, pull up
customer's record, view information, edit it like I normally do with a select
query, as Update queries function differently to what I know.

I think there's a way to do this by making a cross-table, which has
records from both tables in it. For instance, it would have an indexed
field (AutoNumber), CustomerID and MerchantID. Then put this table
between the 2 tables you have now and it creates a 1-to-1 relationship.
Then your query will be updateable.

I'll try this.

Curtis