SubForm will not open from Main Form

[dvgef2]

Good Morning, I am really pulling what few strands of hair I have left out! I would not ever believe just opening forms could be so difficult. I have 2 tables that are linked together by a control labeled intWONumber. I created a query encompassing both tables and a form/subform based off the query. When I click on a checkbox from the main form, it should open the subform to the same record as the main form. Even when I get the form to open, it will not open to the right record. Currently I get a run time error 2465 stating that access cannot find the field '|1' referred to in my expression. This is the code where it gets stuck at. DoCmd.OpenForm "frmRoofSub", , , "intWONumber = " & Me.[intWONumber] The form will open up if I take the 'Where As' criteria out of it and so it gets stuck at the 'intWONumber' portion. The fields are on both forms and they are bound to the query. If anyone has experience in this area I would appreciate any assistance you can give. Sorry for the appearance of this message but I cannot seem to make paragraphs or blank lines for some reason. Thanks for your time! Dave

[June7]

This is not a form/subform, this is two independent forms.

A form/subform arrangement is when one form sits on another form.

Why a query joining tables as form RecordSource? Data entry/edit can only be done in one table.

These two tables each have a field named intWONumber? Do you pull both fields into the query field grid? If so, Access doesn't know which one is referenced in the 'Where As' expression.

You might consider a split form instead of two forms.

[dvgef2]

Thanks June7 for your reply, You are very correct...I meant to call it a pop-up form. I hadn't had my coffee when I wrote this. The form is kind of working as a subform to my system at work and that carried over to my description. Sorry for the confusion!! I actually have several tables that will work in the same manner with this one main table but I figured if I got one to work, I could get the other 4 to work as well. I did not know that data entry edit could only work with one table. That may be a problem for me.

I put these in separate tables because when I had them in one table it was confusing me...the table was too big, trying to find things was ridiculous and I needed to have fields with the same name for each category of work ...since my naming conventions were so horrible I started over. I do pull both fields into the query field grid but I also tried putting the table name in front of the form name like ...DoCmd.OpenForm "frmRoofSub", , , "tblRoof.intWONumber = " & Me.[tblWorkOrder.intWONumber] and that did not work either resulting with the same error. I have no reason to use a query vs the table other than I thought I read somewhere to use queries for the forms. I am very capable of misunderstanding what I read though.

I have the field intWONumber in each table because in my primitive mind, I thought I could link the tables together with that field and they would need to have the same number in that field if the work applies to that work order. Is there any hope?

[rpeare]

can you provide a sample of your database, if it has sensitive information just create a copy of your database dump any privacy information but keep enough data in there to work with as an example. What you're asking for would likely be easy with a form/subform but can also be done the way you're talking about if things are set up correctly.