Unanswered: Building a Form out of one query and another query based on the previous query
once again its about the table I use to keep records off stuff bought by the company. We buy merchandise by the lot, so each item that I input in the table has got a serial number so that it can be tracked to the lot from where it came from. What I would like to do is to filter my table using the lot serial number, add the prices of every item on that lot and then display the results in a form. So here is what I have done so far:
1st - I used a form with a text box so that the user can input the serial number (it's working I checked). Let's call this form "OpenSerialLot"
2nd - I used a query (let's call it "SerialLotCriteria") to filter the table for items from the desired serial lot
in the serial number criteria I have: [Forms]![OpenSerialLot]![TxtSerial]
it's also working, I checked
3rd - I used a query (let's call it "SerialLotSum") recieving information from the previous query and adding the price value from all the items.
in the field I have: Total: Sum([Price]) - its also working I checked
NOW COMES THE PROBLEM:
4th - I want to make a form based on the two queries above. I want for it to have the follwing fields:
Serial - from the "SerialLotCriteria" query Supplier - - from the "SerialLotCriteria" query (if it has the same serial it has the same supplier obviously) Total - - from the "SerialLotSum" query
the problem is that when I use the "FormAssintant" to create this form it displays the following message:
"You have chosen record fields from origins that the assintant can not interconect. You may have chosen fields from a table or a query based on that table. If that’s the case, try selecting fields only from the table or only from the query" and does not allow me to create the form (bear in mind that this message is loosely translated 'cause my access 2007 is in Portuguese).
In the first Form, the "OpenSerialLot", I'm going to have a button with the following VBA code:
Private Sub Comand5_Click()
DoCmd.OpenQuery "SerialLotCriteria", acViewNormal, acEdit -->loads the filter
DoCmd.OpenQuery "SerialLotTotal", acViewNormal, acEdit --adds up the prices
Do.Cmd OpenForm "the name of the form I'm trying to create" -->opens the form with the information loaded onto it
DoCmd.Close acQuery, "SerialLotCriteria" -->closes the query
DoCmd.Close acQuery, "SerialLotTotal" -->closes the query