Opening another form based off another forms combo box value

[Neobeowulf]

Team,

I'm trying to get a combo box & button to work together and i'm failing miserably. On the main form of the attached database, I just want the button to go to the associated record based off my selection (or entry) in the combo box.

I've tried the following:
- Looked through the Northwind sample database for a close example. Tried my best to replicate the functions but it didn't work
- Looked through other examples posted and tried to manipulate the code to no avail
- Tried using [Forms]![Main Form]![cmbLastName] in both the filter & criteria section of the macro

 

[James Dudden]

Your main problem is the query 'AA Table Query' that has a join to 'Item list Query' which is limiting your Recordset. Remove this table as I cant see that it's needed.

Then in design view of your Main Form for your 'Find..' button change the Macro to an [Event Procedure] on the OnClick and put the following line in the VBA window:

DoCmd.OpenForm "InputForm", , , "[Customer ID] = " & Me!cmbLastName

 

[Neobeowulf]

The item list query is there because items are limited to 25 items per week per customer. If I put all of this info on the main table, I won't be able to break the information into a weekly basis.

I made some adjustments based on your suggestion for the button. A new problem has emerged though, when I go to create a new user, the subform shows all records out there when it should show no inputs.

However, if you go into the table itself after adding a new user and put some values into say...clothing items, everything functions normally again.

Give it a try and let me know your thoughts. Updated database attached.

 

[James Dudden]

To be honest I really can't see why you need the 'Item list query' in your main form recordsource so I would strip it out.

The issue with your subform is down to a property of the subform on the data tab called 'Filter on Empty Master' which is set to No - set this to Yes.

 

[Neobeowulf]

I think I understand what you're saying. Strip out the item list query and put those items on the AA Table Query?

Thanks for the assist on this, much appreciated.