[SOLVED] "db engine doesn't recognize expression" - but it's right there!?

[cigarprofiler]

Like my wife, Access doesn't understand me. In case of my wife, it usually means I'm doing something wrong, but with Access too?

Here's what I'm trying to do:

1. Retrieve an ID from table1 (pending_review)
2. Use that ID to retrieve a corresponding value from table2 (pending_model)

Part one works fine, part 2 not so much. What am I doing wrong? I get a 3070 runtime error at the bolded line, even though at the underlined portion, I can see (by hovering the mouse over it) that the correct ID has been retrieved. In my mind, that means pending_review is a valid expression but I guess that's not how Access's mind works...

I found what looks like the same question here but applying this syntax gives me a 3464 data type mismatch, even if I Dim pending_review as String:

rs.FindFirst "[reviewid] = '" & pending_review & "'"

What am I doing wrong (apart from dabbling in VBA with no solid knowledge of the language)?

    If txt_frm_name = "frm_start" Then                      'Interrupted flow

        Dim pending_review As Long
        Dim pending_model As Long
        Dim rs As Recordset
                                
        'retrieve pending reviewid from local review table
        
        pending_review = DLast("review_id_l", "local_tblreview_items")
        Forms!frm_review.txt_reviewid.SetFocus
        Forms!frm_review.txt_reviewid.Text = pending_review

        'retrieve corresponding modelid from public review table
        
        Set rs = CurrentDb.OpenRecordset("public_tblreviews", dbOpenSnapshot, dbReadOnly)
        rs.FindFirst "[reviewid] = 'pending_review'"
        
        If rs.NoMatch Then
        
            MsgBox "There is no pending review"      'Nog een uitgang zoeken, lokale tabel alsnog legen.
            
        Else
        
            pending_model = rs!reviewmodelid         'gets the modelid from tbl_reviews
            Me.txt_reviewmodelid = pending_model     'puts the modelid in the review form

        End If

[pbaldy]

Try this:

Set rs = CurrentDb.OpenRecordset("SELECT reviewmodelid FROM public_tblreviews WHERE [reviewid] = 'pending_review'", dbOpenSnapshot, dbReadOnly)

And test for EOF instead of NoMatch.

[moke123]

if pending_review is numeric try

rs.FindFirst "[reviewid] = " & pending_review

[pbaldy]

Oh, I think I was goofed up. I thought you were looking for the literal text. Now I think moke has it, though I would still incorporate it into the OpenRecordset:

Set rs = CurrentDb.OpenRecordset("SELECT reviewmodelid FROM public_tblreviews WHERE [reviewid] = " & pending_review, dbOpenSnapshot, dbReadOnly)

It's more efficient than opening a recordset on the entire table and then trying to find a single record.

[cigarprofiler]

Thank you both. I first tried Moke's solution, and it worked. But then I was also sensitive to pbaldy's argument (my only programming experience was in the dark ages with Pascal and allocating memory blocks on 4 MB of RAM, so that left a scar...).

Again, thank you both. Being able to incorporate simple SQL into VBA will probably make my life easier down the road as well (and my code uglier).

[cigarprofiler]

Originally Posted by pbaldy

And test for EOF instead of NoMatch.

I put in the NoMatch for completeness sake, but it's basically impossible for a user to call this part of the code without a match. If there were no match, they would be calling a previous IF-statement.

The way this works is that in the regular flow of my application, reviewid (PK) would be the first thing that gets created in public_tblreviews. If that were to fail, local_tblreview_items would remain empty, and the interrupted flow would never be called.

That said, I'm not one to throw good advice in the wind. Is it always better (as a matter of principle) to test for EOF rather than NoMatch?

[The_Doc_Man]

If you are a cautious person, an extra check doesn't usually hurt, particularly if the odds are against the check firing an exit or skip action.

PBaldy's specific response, however, is why you should use .EOF rather than .NoMatch - you see, when you open up an SQL statement for a recordset AND it contains the WHERE clause, that takes the place of the .FindFirst action

If you had left the query in place and used .FindFirst, you WOULD choose .NoMatch to decide it failed. BUT, because Paul suggested a recordset on a QUERY with the WHERE clause already in place, all you need is .EOF testing because the recordset will either find something or it will find nothing. And if it finds nothing, it sets .EOF to TRUE for you.

[pbaldy]

Originally Posted by cigarprofiler

That said, I'm not one to throw good advice in the wind. Is it always better (as a matter of principle) to test for EOF rather than NoMatch?

I would state it differently. I'd say it's better to use an SQL statement that only returns the desired record(s) rather than opening the recordset on the whole table and then searching for the desired record. The appropriate test depends on how you opened the recordset.

It may not matter now, but with a large table you'd notice a speed difference in the two methods. Your method requires pulling the whole table "over the wire", mine only pulls the single record.

[NauticalGent]

Originally Posted by cigarprofiler

What am I doing wrong (apart from dabbling in VBA with no solid knowledge of the language)?

Reminds me of a Line in a movie called The Indian in the Cupboard: "You should not use magic you do not understand!"

I hear those words just about EVERY time I get the dreaded "Your code ain't working for whatever reason, End or Debug"