How can I do a "Find" from a Subform?

[WaltW]

I have two linked tables, and a data-entry form that accesses these two tables.&nbsp;&nbsp;In this form, Table A fields are in the main form, and Table B fields are in a subform.&nbsp;&nbsp;There is a one-to-many relationship between Table A (one) and Table B (many).&nbsp;&nbsp;What I'd like to be able to do is do a &quot;Find&quot; on one of the fields in the subform (Table B), and have it find both the record in Table B and the linked record in Table A, and display the correct data on the form, for data entry, view, update, etc.&nbsp;&nbsp;But right now, it only searches the records in Table B that match the current record in Table A - it can't find anything that's for a different record in Table A.<br><br>Example:&nbsp;&nbsp;Table A and Table B are linked by a field called Order#, and Table B can contain several Part# records for each Order#.&nbsp;&nbsp;Order# 1 contains records in Table B for Part # 100, 200, and 300.&nbsp;&nbsp;Order# 2 contains records in Table B for Part # 200, 300, 400, and 500.&nbsp;&nbsp;The problem arises when I have Order# 1 showing, and want to find an order containing Part # 400 or 500.&nbsp;&nbsp;The way it's set up now, if I search for part # 400 while on order# 1, nothing is found (since this part# is found in order# 2 and I have order# 1 showing), but if I search for part # 400 while on order# 2, it will find it.<br><br>How do I correct this problem?&nbsp;&nbsp;I'm using Access 97.<br><br>Thanks for any help you can provide!<br><br>Walt Wright<br>

 

[DougP]

The reason it does not work is because of your relationship.<br>Get rid of it and your problems will go away.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[WaltW]

Thanks for the suggestion, but eliminating the relationships didn't change anything.&nbsp;&nbsp;The Record Source for the subform contains a &quot;SELECT DISTINCTROW&quot; line to limit the records that appear in the subform.&nbsp;&nbsp;Since I want to do a Find on one of the fields in the subform, doesn't that limit what I can search to what's been selected in the SELECT DISTINCTROW line?&nbsp;&nbsp;How do I get around this?&nbsp;&nbsp;Or is that not where the problem lies?&nbsp;&nbsp;Thanks.<br><br>Walt Wright