Open subform to specific records based of off input from combo boxes

[mayestom]

There is no subform. Not sure what is meant by “being run on a navigation form”? This is the query you told me to create.
When I took out the second table the query worked and returned the proper number of records.

 

[vbaInet]

Upload a test db.

 

[mayestom]

Ok attached is the db. Query name is qryProgramCustomerPKGEngineer. Form is called Program Customer ModelYear.

 

[vbaInet]

There are supposed to be two forms right? What are their names?

 

[mayestom]

Program Customer ModelYear is the form that I would like to filter the records on. That in turn opens up Program Customer Model Year form (which has a subform). Originally, I was thinking of a way to filter the second form from the first. But now I would just like to filter the records in the first form (Program Customer ModelYear) only.

 

[vbaInet]

Program Customer ModelYear is the form that I would like to filter the records on. That in turn opens up Program Customer Model Year form (which has a subform).

You know you're just going to confuse yourself with those names. And besides that it's not good practice to include spaces in your names.

 

[mayestom]

I know. I'm sorry. It's not my idea. I'm working on this db with someone else. As you can see some of the naming convention is ok, some not so much.

 

[vbaInet]

You don't need to be sorry, I was only highlighting it I'll have a look later.

 

[vbaInet]

Ok, which fields do you want to filter on? Program, Customer and ModelYear?

If this is the case then I can see that these fields are the link fields between the parent form and the subform so all we need to do is filter the parent form and the subform will follow suit.

 

[mayestom]

Yes correct, to filter on Program, Customer and ModelYear. Just to make sure, this is in the form named "Program Customer ModelYear". And that is considered the parent form and "Program Customer Model Year form" is considered the child (with a subform)?

 

[vbaInet]

Yes I'm talking about the form with the subform (whatever the name is)

 

[mayestom]

Okay understood. But we’re going to add the filters to the parent form, “Program Customer ModelYear”. I’m sorry if I’m repeating myself, I just want to make sure I’m on the same page.

 

[mayestom]

Ok I have attempted creating a filter on the form. It is not filtering on the selected field that you choose. Whenever you choose a value and click filter all the records go away. The form name is 'Program Customer ModelYear'. Would someone please take a look at it and tell me what I'm doing wrong?

 

[vbaInet]

Ok I have attempted creating a filter on the form. It is not filtering on the selected field that you choose. Whenever you choose a value and click filter all the records go away. The form name is 'Program Customer ModelYear'. Would someone please take a look at it and tell me what I'm doing wrong?

What you're trying to build is not a search form and the code you've used in your db is from Allen Browne's website but you've not left the credits.

See attached a working db.

I've not had a thorough look at your db but from the tables/queries that are related to the forms I was looking at, you have some fundamental issues:
1. Use of lookup fields in field level. Some of your queries and tables are a mish mash of the ID number or the related text. Lookups in fields (i.e. having a combo box in the field) should not be used and here's why:
http://access.mvps.org/access/lookupfields.htm
2. One or more of your tables is/are not related to anything when it actually should. Look into setting up relationships and referential integrity in the Relationships Designer.
3. Based on the points above I think your tables could be better normalised.

You'll benefit from creating a new thread asking for assistance on your table structure and setup.

 

[mayestom]

vbaInet
I’m sorry I don’t know how it happened that Allen Browne’s credit was left out of the code. Please know that I would never do something like that intentionally. I was working on the code, copying and pasting and changing this and that trying to get the code to work and somehow it got left out. As an example of how much I appreciate others helping me out, please look at this post from another form. Someone was helping me with another piece of code and I wanted to give them credit in the code. I honestly would never pass off someone else’s work as my own. I shall be more careful in the future.

As for the db:

1. Use of lookup fields in field level. Some of your queries and tables are a mish mash of the ID number or the related text. Lookups in fields (i.e. having a combo box in the field) should not be used and here's why:

I understand you shouldn’t use Lookup’s in the tables but where can you use Lookup fields? On the forms? If you shouldn’t use Lookups in the tables why is the option there?

2. One or more of your tables is/are not related to anything when it actually should. Look into setting up relationships and referential integrity in the Relationships Designer.

The db that I uploaded is only a mockup of the original. So some of the tables/forms/macros aren’t working/don’t make sense/or still being worked on. But I do realize that I still have much to learn and I appreciate all the guidance/constructive criticism/tips/pointers that I can get from you and other helpful users on this form and others have given me.

I looked at the attached db that you posted. I’m not sure what you want me to look at? It looks like the same db I posted earlier.

 

[vbaInet]

You didn't need the long explanation, I was only reminding you

Run the forms.

 

[vbaInet]

I understand you shouldn’t use Lookup’s in the tables but where can you use Lookup fields? On the forms? If you shouldn’t use Lookups in the tables why is the option there?

As for Lookups, they just should never be used simply because they mask the true nature of the data that should be saved in that field.

In place of lookups, you create relationships in the Relationships Designer and when building the queries join the parent and child tables via the ID to pull in the respective text.

 

[mayestom]

Okay, understood. Thank you vbaInet.

 

[vbaInet]

No problem! Have you tested the forms yet?

 

[mayestom]

I'm really sorry vbaInet, I'm sure I'm missing something, but I can't see any difference in the forms.
I've attached an updated version of the db. The form name is Program Customer ModelYear. Customer and engineer filters seem to be working but not all Programs are being filtered on. For example if you select JK, A1XC or KL it doesn't filter on them. If you wouldn't mind taking a look at it to see what you think, I'd appreciate it.