06-19-2017, 09:46 AM
|
#1
|
Newly Registered User
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
|
"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
|
|
|
06-19-2017, 09:50 AM
|
#2
|
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,250
Thanks: 8
Thanked 3,720 Times in 3,663 Posts
|
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.
|
|
|
06-19-2017, 09:55 AM
|
#3
|
Newly Registered User
Join Date: Jan 2013
Location: Massachusetts
Posts: 590
Thanks: 0
Thanked 186 Times in 175 Posts
|
Re: "db engine doesn't recognize expression" - but it's right there!?
if pending_review is numeric try
Code:
rs.FindFirst "[reviewid] = " & pending_review
|
|
|
The Following User Says Thank You to moke123 For This Useful Post:
|
|
06-19-2017, 10:06 AM
|
#4
|
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,250
Thanks: 8
Thanked 3,720 Times in 3,663 Posts
|
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.
|
|
|
The Following User Says Thank You to pbaldy For This Useful Post:
|
|
06-19-2017, 11:05 AM
|
#5
|
Newly Registered User
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
|
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).
|
|
|
06-19-2017, 11:23 AM
|
#6
|
Newly Registered User
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
|
Re: "db engine doesn't recognize expression" - but it's right there!?
Quote:
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?
|
|
|
06-19-2017, 11:49 AM
|
#7
|
AWF VIP
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,460
Thanks: 41
Thanked 998 Times in 909 Posts
|
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 one year and survived being home all day with the wife. She must really love me.
|
|
|
The Following User Says Thank You to The_Doc_Man For This Useful Post:
|
|
06-19-2017, 12:53 PM
|
#8
|
Wino Moderator
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,250
Thanks: 8
Thanked 3,720 Times in 3,663 Posts
|
Re: "db engine doesn't recognize expression" - but it's right there!?
Quote:
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.
__________________
Paul
Microsoft Access MVP
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
06-19-2017, 01:12 PM
|
#9
|
Pristine Curmudgeon
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,229
Thanks: 282
Thanked 160 Times in 140 Posts
|
Re: "db engine doesn't recognize expression" - but it's right there!?
Quote:
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"
|
|
|
Thread Tools |
|
Display Modes |
Rate This Thread |
Linear Mode
|
|
All times are GMT -8. The time now is 04:59 PM.
|
|