3021 No current record

[presuming_ed]

Can anyone help? I'm running the following code:

' Find the email address appropriate to the current stock tracker record
strsql = "SELECT import_stock_data.store_number, import_stock_data.week, " _
& "import_stock_data.stock_data, lookup.email_address " _
& "FROM lookup INNER JOIN import_stock_data ON lookup.lstore_number = " _
& "import_stock_data.store_number " _
& "WHERE import_stock_data.store_number = '" & rst.Fields("store_number") & "';"

Set rst2 = db.OpenRecordset(strsql, dbOpenSnapshot)

If rst2!email_address = "" Then
MsgBox "null rst2"
Else
t_store_no = rst!store_number
t_email = rst2!email_address
t_stock_data = rst!stock_data
email_title = "Stock Tracker - Week " & rst!week & "Store " & rst!store_number

When I check whether rst!email_address is null i.e. whether there is an email address on my lookup table, I get a 3021 No current record message. How do I check whether a email address has been returned. I've tried using ISNULL against my recordset, and also tried NOMATCH but these don't work either.

Thanks.

 

[Fizzio]

if you are showing no current record, then the recordset is returning an empty recordset.

If rst2.eof then
MsgBox "null rst2"
Else
t_store_no = rst!store_number
t_email = rst2!email_address
t_stock_data = rst!stock_data
email_title = "Stock Tracker - Week " & rst!week & "Store " & rst!store_number
end if

will trap if there are no records in your recordset but can you not use a Dlookup to find out the e-mail address instead?

 

[presuming_ed]

Fizzio,

Unfortunately the recordset won't be empty as the store_number, week and stock data fields will always be populated. I've never come accross DLOOKUP, any pointers?

Thanks.

 

[Mile-O]

I've never come accross DLOOKUP, any pointers?

Starting with Help files might be a good suggestion.

DLookup folloes the structure

value = DLookup("FieldName", "Recordsetname", Criteria)

ie.


intNoOfLegs = DLookup("[NoOfLegs]", "tblAnimals", "[Animal] = 'Gorilla'")