Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2009

    Unanswered: Building a Form out of one query and another query based on the previous query

    Hi guys,

    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


    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

    End Sub

    Any help would be apreciated
    Last edited by franchico; 08-06-09 at 13:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts