Form/Subform Navigation Buttons Goes to Subform Not Next Main Form Record

[beeowens]

Have two simple Forms w/subform (essentially clones of each other but criteria is different so I can display records coded as Open or only records coded as Closed based on info in a field in MATTERS table named Status). Status is a lookup field to a small table with 5 records. The form's record sources are queries built on the two tables (listed below) that have an established parent/child relationship.
The Closed form is working fine. The Open form is not working. The Open form has a simple OnOpen macro that says GoToRecord:New. The criteria for both forms is based on the "Status" field in a query. Closed is: <>1 And <>5. Open is: Like 1 Or 5.

Main table: CLIENTS (ClientID autonumber primary key)
Secondary table: MATTERS (ClientID number linking key); Status lookup field in this table
Lookup table: lkStatus
Main form is based on CLIENTS query. Subform is based on a MATTERS query that has the criteria above.

Two issues:
1 - In order to force the parent/child relationship to work in the Open form, I have to add the foreign key to the main query. If I don't, I see all records. I don't have to do this for the Closed form.
2 - The other issue is when I try to go to the Next Record using the Navigation buttons on the form. The navigation cycles through each subform record first, then it will move to the next main form record. So, if I have 3 items in the subform, I have to click the navigation arrow 3 times to move to the next record on the main form.

I've found one other person online who posted a similar problem but no one ever answered his question. I hope someone can answer mine. How do I fix this?
Nothing I've tried solves. Here's what I've tried: *decompile *compact/repair *create form over from scratch *create forms from Tables skipping the queries *add buttons in form footer using VBA to move to next record *cross-check record counts *etc, etc.



Reminder, I'm a novice especially when it comes to VBA. But I don't think I'm trying to do anything extraordinary. Just want it to work.

[ranman256]

If you start with NEW Record, you MUST save the master record 1st in order to get the KEY. (autonumber I presume)
NOW you can use the sub forms.

If you try to goto next record from a NEW record , you cant. You should be at the end. You would need to refresh, or use the rec# navigation at the bottom.

Or am I way off the problem?

[beeowens]

I need to clarify. I'm not actually creating a new record yet. That's just eventually for the user.
I'm testing and when I gotofirst and then try to navigate to next record or just click previous button and try to navigate backward, if I run into a record that has multiple subform records, I have to cycle thru those records before can proceed either forward or backward.

[beeowens]

Addendum: I also tried removing the OnOpen macro from form and still had issue. I've tried to attach a small example file but can't get file size small enough.

[beeowens]

Guess I've stumped the experts ... again (like my one and only other post). Anyway, I've talked the user into letting me make one form with no filters which solves the immediate issue. However, I would really like to know "why" the troubled form won't filter when the near identical form and all my reports work fine. If anyone is interested in helping me understand the "why" I have a smaller sample file that I could attempt to upload.