criteria lookup

[awake2424]

I am using the code below to pull the inuse reagents into a form. The code works great (Thanks David R for all your help). Is it possible to only pull the inuse reagent If [QC] has a date associated with it? In other words,

tblExtraction
Lot Kit QC Inuse Exp Reactions
1229788 Purelink gDNA 22-Jan-13 FALSE 22-Jan-14 50
1388047 Purelink gDNA 19-Nov-13 TRUE 19-Nov-15 50

Since 1388047 is Inuse it transfers to the form. If the QC was blank is it possible to prompt the user with an error saying "not QC'd".

 If Me.Text45 = "Test1" Or Me.Text45 = "Test2" Then
      Me.Text61 = DLookup("[Kit]", "[tblExtraction]", "[Inuse] = -1")
      Me.Text63 = DLookup("[Lot]", "[tblExtraction]", "[Inuse] = -1")
      Me.Text65 = DLookup("[QC]", "[tblExtraction]", "[Inuse] = -1")
      Me.Text67 = DLookup("[Exp]", "[tblExtraction]", "[Inuse] = -1")

Thank you.

 

[nathansav]

You are not using the Lot ID in the Dlookups though? Look on the net at DLOOKUP multiple criteria, then you can modify what you already have

 

[pr2-eugin]

Why not use Recordset? Using DLookup for getting more than one columns is not optimal nor efficient. A Domain function is a Simplified Query, the above code you have will Query the table Four times, instead, use a Recordset to query once and get the required information?

I look back to this link for (re)learning the use and manipulation of recordsets - http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners

You code could be as simple as..

Dim rsObj As DAO.Recordset

If Me.Text45 = "Test1" Or Me.Text45 = "Test2" Then
    Set rsObj = CurrentDB.OpenRecordset("SELECT Kit, Lot, QC, Exp FROM tblExtraction WHERE Inuse = -1")
    If rsObj.RecordCount <> 0 Then
        Me.Text61 = rsObj.Fields("Kit")
        Me.Text63 = rsObj.Fields("Lot")
        Me.Text65 = rsObj.Fields("QC")
        Me.Text67 = rsObj.Fields("Exp")
    End If
End If