How to pass a controls value as a filter for another form?

[bozrdang]

I have a form (frmOrders) and a subform (sfrmOrders) that I use to enter customer orders. I also have another form (frmProduction) that I use to enter each shifts production numbers and subtract them from the original order quantity. I am trying to set it up so that I can double click on the part number control in sfrmOrders and open frmProduction (which is based on a query) and have it filtered by the value of the OrderDetailsID control in the same subform. I can't seem to get this working right.

I have attached the db. If you open frmOrders and double click on a partnumber, you will see what I mean.

 

[Pat Hartman]

I fixed the immediate problem by changing the OpenForm method to use the where argument and removing the code from the Load event of the Production form. I also removed most of the DLookup() from the Production form. I can't figure out why they were there to begin with.

I also think that there is a logic problem with the Production form. I don't understand the recordset returned by its query. What is the point of showing individual records for a shift when you are summarizing the count data?

 

[bozrdang]

I don't want to pass the PartID as a filter, I want to pass the corresponding OrderDetailsID. The problem is that filtering by PartID includes all entries for a particular part across ALL po's. I basically want to filter by part and po number. If I'm correct, filtering by OrderDetailsID will accomplish this. I want to add each shifts production numbers until that particular order is complete.

I used all the Dlookups in the Production form because the first four text boxes are fields that would be common to each record. The part number, description, po number, and order qty would all be the same so it is basically only for display. I wanted it it as a double check so a user again sees which part they are adjusting.

Ultimately, this form will only allow new records, and I want the Dlookup text boxes to display info about the order. Also, the odd looking OrderDetailsID text box on the Production form is only there as a link for the filter and would normally be hidden.

 

[bozrdang]

Any ideas?

 

[Pat Hartman]

I showed you how to make it work. Change it so that it links on the key field that you want it to link on.

 

[bozrdang]

Sorry for being dense. The first time I tried changing it, it didn't work. I tried again and it works fine now. Thanks!

If you don't mind, I have two more questions.

1. Why do my Dlookups on frmProduction keep returning the same info regardless of the filter. I want it to show info related to the records I select on sfrmOrders. EDIT: I figured this one out.

2. When I go to enter a new record in frmProduction, my TotalRemaining text box displays "#Error". I assume that this is because there is no value entered in QtyProduced yet. Is there a way to get it to show the balance instead of the error BEFORE I add a new record?

Thanks again.

 

[Pat Hartman]

1. I removed the DLookup()s and replaced them with the bound columns because they were getting information from the bound record anyway so they were redundant.
2. You should be able to get rid of the error by using the Nz() function. I don't remember the calculation but something like the following should work:
=Nz(somefield,0) - Nz(Someotherfield,0)

 

[bozrdang]

Thanks Pat! I finally realized I was taking the wrong approach and recreated the form from scratch as a form with a subform. This solved all the Dlookup problems.

I still had the error problem and your NZ() suggestion fixed it. Thanks again!