Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple criteria find (2003)

    I'm sure there's an easy way to do this, but I'm just not thinking straight today.

    I have a table called tblProductionDetail where I capture daily production figures, by employee, each day. If they produce less than the total amount ordered, I create a new record with the same OrderID (a different unique ProdID key, of course), but with no qtyproduced. That way, the following day or on the next shift, another record can be captured with new production figures.
    The day after production, someone from Quality Control looks at the product and determines if any needs to be rejected. If so, I need to open up a new record to capture production, but ONLY if there isn't one there already. I'm working through this in VBA. How can I check to see if a "blank" record exists for this order? One will have been created already if the order hasn't been completely produced yet. If everything was produced, I have to create a new blank record because we have to replace the rejected product. I don't want to create two blank records because the reject parts will likely be produced as part of the remaining order on the next shift, so I really only need one blank record.

    Hope this makes sense. The database has gotten far too large to be able to strip it down and send it. I'm hoping you can give me some ideas.

    I thought of doing a FindFirst through the recordset, but I would need to look for the Order number AND a Qtyproduced of zero. Can you have multiple criteria with FindFirst?

    Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple criteria find (2003)

    FindFirst supports multiple conditions:

    rst.FindFirst "[OrderNo] = " & lngOrderNo & " AND [QtyProduced] = 0"
    If rst.NoMatch Then
    ' No record found, so create one
    rst.AddNew
    ...
    rst.Update
    End If

    Here, rst is a DAO recordset. I have assumed that the order number is, well, a number. If it is a text field, the first line becomes

    rst.FindFirst "[OrderNo] = " & Chr(34) & lngOrderNo & Chr(34) & " AND [QtyProduced] = 0"

    You must, of course, substitute the correct names.

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple criteria find (2003)

    That works perfectly. Thanks for setting me straight, as usual.

Posting Permissions

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