Filter...Am I missing something?

[Kelsey]

Hi All,
I have a form, say frm1, with a subform which we'll call frmsub. frm1 has a JobID text box. frmsub has InventoryID, IssueQuantity, and OperationNumberID(combo box). Each JobID can have multiple OperationNumberID's with multiple InventoryID's. I want to be able to only show OperationNumberID's in my combo box that are associated wtih the JobID in frm1.

[TheShabz]

create a new query in the builder. Throw in the table that holds your OperationNumberIDs as well as the foreign key to the table with JobID. Select the OperationNumberID and the JobID. uncheck the JobID display box so it doesn't show. In the criteria for JobID enter the location of the textbox in frm1, Forms!frm1!JobID. Save this query as something descriptive, say, qryOpNumByJobID.

Now, on your subform create a new combobox that references the query you just made as its rowsource.

Now go back to the textbox and in the AfterUpdate event, in the VBA window, enter:
Me.frmsub.comboboxName.Requery

That should do it.

[Kelsey]

Works great except for my OperationNumberID in my subform isn't changing once i go to a new record. It works fine when I click the first record I want to go to. I was running into this problem before too.

[rpeare]

can you provide a sample of your database with fake data in it?

[TheShabz]

Add in a Me.frmsub.Form.Requery as well and give that a go.

[Kelsey]

The form in question is frmIssue with subform frmIssueSub. Thanks

[rpeare]

Is this what you want?

[Kelsey]

No. It needs to be a subform where you are able to add new issues. You can Issue an InventoryID to a specific OperationNumberID in the specific Job. Many InventoryID's can be added to Operation Numbers in this form. Many Operation Numbers can be added to the specific job in the form frmTruckOperationNumbers.
I just want to have the OperationNumberID field in this subform to show only operationNumberID's being used in that Job.
Hope that makes sense

[rpeare]

I don't understand what the problem is then. I'm attaching two thumbnails, one is what I see when I open the form to JOBID 13. The second thumbnail is the contents of the tables tblJOB and tblIssueDetail. From what I'm seeing on this table and what's reflected on the form your subform is already limiting the data to the operation ID's you have listed on your detail table.

Are you saying that when you open the form frmIssue, that the subform is not correctly displaying the contents of tblIssueDetail that relate to that JobID as you scroll through records? In other words what you're seeing when you look at JobID 13 is different than what I'm seeing or am I just not understanding (again) what you want?

[Kelsey]

Your first thumbnail first of all.... is on JobID 13. This is the only one that seems to be working right. If you go to JobID 14, and pull the drop down list for OperationNumberID, it gives you 62 and 1, which are the operationNumberID's for JobID 13m instead of the operationNumberID's that are really in JobID 14, which in this case I believe should be 19.
The operationNumberID drop down in the subform should be limited to ONLY OperationNumberID's that are connected to JobID (shown in main form).
Hope that makes it a little more clear..

[rpeare]

in the ON ENTER property of the OPERATIONNUMBERID combo box put this:

Forms!frmissue.frmIssueSub!OperationNumberID.Reque ry

[Kelsey]

Thank you!