Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-19-2017, 09:46 AM   #1
cigarprofiler
Newly Registered User
 
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
cigarprofiler is on a distinguished road
"db engine doesn't recognize expression" - but it's right there!?

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)?

Code:
    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

cigarprofiler is offline   Reply With Quote
Old 06-19-2017, 09:50 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,580
Thanks: 8
Thanked 3,602 Times in 3,546 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "db engine doesn't recognize expression" - but it's right there!?

Try this:

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

And test for EOF instead of NoMatch.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-19-2017, 09:55 AM   #3
moke123
Newly Registered User
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 430
Thanks: 0
Thanked 133 Times in 126 Posts
moke123 is on a distinguished road
Re: "db engine doesn't recognize expression" - but it's right there!?

if pending_review is numeric try
Code:
rs.FindFirst "[reviewid] = " & pending_review

moke123 is offline   Reply With Quote
The Following User Says Thank You to moke123 For This Useful Post:
cigarprofiler (06-19-2017)
Old 06-19-2017, 10:06 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,580
Thanks: 8
Thanked 3,602 Times in 3,546 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "db engine doesn't recognize expression" - but it's right there!?

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
cigarprofiler (06-19-2017)
Old 06-19-2017, 11:05 AM   #5
cigarprofiler
Newly Registered User
 
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
cigarprofiler is on a distinguished road
Re: "db engine doesn't recognize expression" - but it's right there!?

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 is offline   Reply With Quote
Old 06-19-2017, 11:23 AM   #6
cigarprofiler
Newly Registered User
 
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
cigarprofiler is on a distinguished road
Re: "db engine doesn't recognize expression" - but it's right there!?

Quote:
Originally Posted by pbaldy View Post
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?
cigarprofiler is offline   Reply With Quote
Old 06-19-2017, 11:49 AM   #7
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 10,716
Thanks: 36
Thanked 850 Times in 763 Posts
The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all
Re: "db engine doesn't recognize expression" - but it's right there!?

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over six months and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
cigarprofiler (06-19-2017)
Old 06-19-2017, 12:53 PM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,580
Thanks: 8
Thanked 3,602 Times in 3,546 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: "db engine doesn't recognize expression" - but it's right there!?

Quote:
Originally Posted by cigarprofiler View Post
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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 06-19-2017, 01:12 PM   #9
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 940
Thanks: 206
Thanked 114 Times in 105 Posts
NauticalGent will become famous soon enough
Re: "db engine doesn't recognize expression" - but it's right there!?

Quote:
Originally Posted by cigarprofiler View Post
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"

NauticalGent is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access Database engine does not recognize " as a valid field name or expression? diofree Reports 1 12-17-2015 11:34 AM
Database engine does not recognize 'payment' as a valid field name or expression EdK Reports 2 03-01-2015 02:37 AM
The feature "list item edit form" "access 2007" Doesn't Work After I Convert The DB t vanleurth Forms 0 03-04-2013 11:01 AM
Microsoft database engine could not find the object ""...." when running mde JoanneJames General 0 04-14-2009 08:09 AM
How do I get "Transfer Text" to recognize my query name wmdavidwynn Modules & VBA 8 08-12-2005 05:26 AM




All times are GMT -8. The time now is 01:45 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World