Short Code for extracting a field from a recordset?

[Alexander S.]

Hi Guys,

Is there a possibility to use a shorter code for extracting one field from one record, than opening a recordset, getting the field, closing it and setting the recset to "Nothing" again? I'm doing this multiple times in my code and it seems a bit too much for what it's supposed to do.

sql = "SELECT tblAdFlgDaten.* FROM tblAdFlgDaten WHERE (((tblAdFlgDaten.AuftrID)=" & ABAuftrID & _
        ") And ((tblAdFlgDaten.Schritt)=2))"
    Set RSnap = dbase.OpenRecordset(sql, dbOpenSnapshot)
    If IsNull(RSnap.Fields(2)) Then
...
...
    End If
    RSnap.Close
    Set RSnap = Nothing

Thank You

 

[spikepl]

You shoould never ask for more than what you need:
tblAdFlgDaten.* You really need the entire record?

Thre is always Dlookup. Are you looking in the same data setr or different data sets?

 

[gemma-the-husky]

this effectively

myvar = nz(dlookup("field2","tblAdFlgDaten","AuftrID = " & ABAuftrID & " and Schritt=2"),"")

 

[jdraw]

Can you tell us in plain English WHAT you are trying to accomplish---let's understand the requirement before checking/revising your sql/vba.

 

[Alexander S.]

Oh yeah, you're right. I don't need the entire record.
And DLookup() is exactly what I was looking for. Thank you!

DLookup("AbBrAdID", "tblAdFlgDaten", "[Schritt] = 2 And [AuftrID] = " & ABAuftrID)

Sweet!