Open subform to specific records based of off input from combo boxes (1 Viewer)

[mayestom]

Is this a case of looking for something upside down or back to front.

I'm not sure what this means?

In any case, it sounded like in Uncle Gizmo video that he was not sure what to do.
Okay, forget the way it's set up in the database. How can I have two combo boxes set the search criteria that will open up a form with the matching criteria in the subform? The header of the second form would only display the criteria. The subform to be able to add records to them. User can pick one or both combo boxes to filter from.

 

[vbaInet]

It's not that Uncle Gizmo wasn't sure what to do, he was just saying that it's odd to have such a setup but if that's how you like then it's fine.

You have two options:
1. Change the Source Object of the subform to a form who's Record Source is based on query of the table. If I remember correctly, it was bound to a table.
2. In the Open or Load event of the subform construct the Filter property
3. And set the Filter On property of the subform to True.

OR

1. Change the Source Object of the subform to a query that's based on the table.
2. In the query, set the criteria of each field to each of the relevant controls on the switchboard. An example of the criteria would be:

Forms![COLOR="blue"]FormName[/COLOR]![COLOR="blue"]ControlName [/COLOR]Or Forms![COLOR="blue"]FormName[/COLOR]![COLOR="Blue"]ControlName [/COLOR]Is Null

...amend the bits in blue.

The second method implies that the record source will be reliant on the switchboard's values so any filters you apply from within the form won't have any effect.

 

[Uncle Gizmo]

I haven't got access to a PC at the moment I'm doing this from my mobile. The way your search works (I think) is based on a table in the main form, which restricts the records shown in the subform . The parent child subform links specify 3 fields each, and that, along with selections in combo boxes on the main form controls the search.

At least that's what I think is happening. I remember experimenting with something similar to this when I first started learning MS Access.

From the options mentioned earlier, I personally like the SQL method. I never had much time for in form filtering however a lot of people use that successfully. I don't know if there are any advantages or disadvantages between the two.

I have recently completed two YouTube video playlists on building search criteria with SQL statements. They might be worth having a look at if you decide to go with the SQL method. Unfortunately they don't cover using combo boxes yet, that will be the next playlist which I haven't done.

This is the last video to give you some idea of where it's going:-
Building Advanced Search Criteria 7 http://youtu.be/mAjnip9kbvg


Playlist 1
MS Access - Building Search Criteria https://m.youtube.com/playlist?list=PLhf4YcS5Ajdq0MFtLAHAN0Jg9soJpBVvw

Playlist 2
MS Access - Building Advanced Search Criteria
https://m.youtube.com/playlist?list=PLhf4YcS5AjdrTi5ztaQpiwC7zTuAZur6m

 

[Uncle Gizmo]

A good tip for anyone watching YouTube videos:- Many browsers now support playing YouTube videos at twice the recorded speed.

This means you can watch a 10 minute video in 5 minutes!

From my point of view as someone that is producing the videos, videos over 5 minutes long are not good, too long for most people to watch. However now they can speed them up and watch a 10 minute video in 5 minutes I am now making my videos 10 minutes or even 15 minutes long.

It's less stressful for me because I can talk at a lower speed, it's more relaxed, allowing me more time for thinking about what I am saying.

The other thing is translation of the speech into text. YouTube does this automatically for you, however it does make quite a few mistakes if you speak fast. The mistakes are vastly reduced by speaking slowly making the conversion into text (to give each video subtitles) is much easier.

 

[mayestom]

vbaInet and Uncle Gizmo -
Thank you both for your input and guidance. I will be starting to work on this again in the next couple hours. I'm sure once I do I will have many questions as to how to implement certain things that have been mentioned by both of you.

 

[RainLover]

Quote:
Is this a case of looking for something upside down or back to front.
I'm not sure what this means?

You are looking for something in the Sub Form which is not right.

Normally you may look at the Main Form, say a street name e.g. "Long Street"
But you are looking for the Smiths who line in Long street.

If this is what you are doing then it is all wrong.

But then it could be me who's upside down. If I am right and someone writes some code for you then it will all come unstuck somewhere down the track.

 

[mayestom]

vbaInet:
I tried the second option in #42 post. But that didn't work for me. I obviously don't think I did it correctly. But when I tried it, it give me five parameter when I try opening the form after making a selection from the switchboard. And when the form does open it doesn't display any records. Another problem is that one of the fields that is being used to filter the subform is not in the same table as the other.

 

[mayestom]

I believe I am going about this the wrong way, as you guys have mentioned before. So...

Is there a way to filter a form that's in continuous form view? I have attached a screen shot of the present form. I would like to be able to filter on Program, Customer and/or PKG Engineer. What I was envisioning was to have combo boxes above each of the respective fields and a command button to filter. The second screen shot is a mockup of what I am envisioning. I can also post the db again if you guys would like.

 

[vbaInet]

We just said that it's an odd arrangement, but you have your reasons for doing it that way that we can't understand.

It doesn't matter what view a form is, you can filter it any way. What difficulty were you having from the suggestion in post #42 and which one did you try?

 

[mayestom]

I don't know how to do either option. Also, there is no subform.

 

[vbaInet]

What have you tried so far? Show us what you've tried and we'll advice.

 

[mayestom]

Ok, so far:
-There is no Source Object on the form, there's a Record Source. Do I change that to the query I created?
-Query sql code:

SELECT [Packaging information charts - Copy Of].OEM, [Packaging information charts - Copy Of].Program, [Program Customer ModelYear].PKGEngineer
FROM [Packaging information charts - Copy Of], [Program Customer ModelYear]
WHERE ((([Packaging information charts - Copy Of].OEM)=[Forms]![Program Customer ModelYear].[Form]![Customer]) AND (([Packaging information charts - Copy Of].Program)=[Forms]![Program Customer ModelYear].[Form]![Program]) AND (([Program Customer ModelYear].PKGEngineer)=[Forms]![Program Customer ModelYear].[Form]![PKGEngineer])) OR ((([Packaging information charts - Copy Of].Program)=[Forms]![Program Customer ModelYear].[Form]![Program]) AND (([Program Customer ModelYear].PKGEngineer)=[Forms]![Program Customer ModelYear].[Form]![PKGEngineer]) AND (([Forms]![Program Customer ModelYear].[Form]![Customer]) Is Null)) OR ((([Packaging information charts - Copy Of].OEM)=[Forms]![Program Customer ModelYear].[Form]![Customer]) AND (([Program Customer ModelYear].PKGEngineer)=[Forms]![Program Customer ModelYear].[Form]![PKGEngineer]) AND (([Forms]![Program Customer ModelYear].[Form]![Program]) Is Null)) OR ((([Program Customer ModelYear].PKGEngineer)=[Forms]![Program Customer ModelYear].[Form]![PKGEngineer]) AND (([Forms]![Program Customer ModelYear].[Form]![Customer]) Is Null) AND (([Forms]![Program Customer ModelYear].[Form]![Program]) Is Null)) OR ((([Packaging information charts - Copy Of].OEM)=[Forms]![Program Customer ModelYear].[Form]![Customer]) AND (([Packaging information charts - Copy Of].Program)=[Forms]![Program Customer ModelYear].[Form]![Program]) AND (([Forms]![Program Customer ModelYear].[Form]![PKGEngineer]) Is Null)) OR ((([Packaging information charts - Copy Of].Program)=[Forms]![Program Customer ModelYear].[Form]![Program]) AND (([Forms]![Program Customer ModelYear].[Form]![Customer]) Is Null) AND (([Forms]![Program Customer ModelYear].[Form]![PKGEngineer]) Is Null)) OR ((([Packaging information charts - Copy Of].OEM)=[Forms]![Program Customer ModelYear].[Form]![Customer]) AND (([Forms]![Program Customer ModelYear].[Form]![Program]) Is Null) AND (([Forms]![Program Customer ModelYear].[Form]![PKGEngineer]) Is Null)) OR ((([Forms]![Program Customer ModelYear].[Form]![Customer]) Is Null) AND (([Forms]![Program Customer ModelYear].[Form]![Program]) Is Null) AND (([Forms]![Program Customer ModelYear].[Form]![PKGEngineer]) Is Null));

 

[vbaInet]

Before you go plugging in all that criteria, have you actually tried one criteria to get it working first? I.e.:

WHERE [Packaging information charts - Copy Of].OEM=[Forms]![Program Customer ModelYear].[Form]![Customer]

 

[mayestom]

Ok so I tried one criteria for OEM
code:

SELECT [Packaging information charts - Copy Of].OEM, [Packaging information charts - Copy Of].Program, [Program Customer ModelYear].PKGEngineer
FROM [Packaging information charts - Copy Of], [Program Customer ModelYear]
WHERE ((([Packaging information charts - Copy Of].OEM)=[Forms]![Program Customer ModelYear].[Form]![Customer] Or [Forms]![Program Customer ModelYear].[Form]![Customer] Is Null));

parameter box comes up and I enter a value that I know is there. Should of only returned 298 but returned 1192 records.

 

[vbaInet]

The parameter box is coming because it can't find the subform.

1. Is the subform open before you attempt to run the other form?
2. Is this being run on a navigation form?

 

[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.