469,366 Members | 2,286 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

syntax error, missing operator

I hope it is not bad form to join and immediately ask a question (especially one that appears to be common). I have looked at my code so long today, that I cannot figure out what MS Access/VBA wants. I am apparently missing an operator. Any help is greatly appreciated.


The snippet flagged when I debug is:
Expand|Select|Wrap|Line Numbers
  1.     rs1.FindFirst ("[ReportType]= & Report_Type" & _
  2.         "FROM Report_Status" & _
  3.         " WHERE Report_Status.[PI] = '" & PI_Number & "' " & _
  4.         "ORDER BY Report_Status.[PI]; ")
  5.  
Oct 4 '11 #1

✓ answered by patjones

OK, what I'm going to do then is suggest a slightly different course of action. Let's say that your columns are in a table called tblProducts. Instead of using RecordsetClone, you can do this:

Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim strSQL As String
  3.  
  4. strSQL = "SELECT COUNT(*) AS num_dupes FROM tblProducts WHERE [ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
  5.  
  6. Set rs = CurrentDB.OpenRecordset(strSQL)
  7.  
  8. If rs!num_dupes = 0 Then
  9.    'Code to save record here
  10. Else
  11.    MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", vbOKOnly + vbInformation
  12. End If
  13.  
  14. ...

What this does is establishes a recordset of any records that happen to meet your criteria. If no records meet your criteria, then its record count is zero and you can proceed with the save. Otherwise, it contains one or more records and you need to inform the user.

28 3263
patjones
931 Expert 512MB
The typical usage for FindFirst assumes that you previously populated the recordset (using say CurrentDB.Execute or OpenRecordset). Once the recordset is populated, you use FindFirst to grab the first record in the recordset that meets the criteria that you specify in the argument.

The argument that you pass to FindFirst appears to have such criteria ("[ReportType] = & Report_Type"), but then you follow it with a fragment of a SQL string (a SQL statement without a SELECT clause isn't even possible, but that's a little beside the point here).

Can you 1) post more code so that we can see what you're doing prior to the FindFirst call, and 2) explain what you're trying to accomplish with the SQL fragment?

Pat
Oct 4 '11 #2
Rabbit
12,516 Expert Mod 8TB
Here's what that string looks like once it's translated.

Given PI_Number = 'abc'
Expand|Select|Wrap|Line Numbers
  1. [ReportType]= & Report_TypeFROM Report_Status WHERE Report_Status.[PI] = 'abc' ORDER BY Report_Status.[PI];
Oct 4 '11 #3
Rabbit, thanks - I am new to VBA so I was still muddling through what that would come out as.

zepphead80, Funny thing is that when I saved & closed my code, then closed the form, then reopened the form and clicked the button that's supposed to run this, it (sortof) worked. Gave the opposite response than I wanted...

So I have this database with Project ID Numbers (PI_Number) and I want to allow users to use a form to enter data. Problem is my users are the typical person who doesn't understand to (manually) search for previous entries in the database before creating a new entry.

1 problem with not allowing duplicate PI numbers is that we have duplicates based on what report type they are viewing. So no two PI + Report Type can duplicate. I want to allow user to enter a PI Number and Report Type and have code to run when s/he hits "save" (not the default - but a button on the form) that looks for the PI NUmber + Report Type combination in the database. If the combo already exists, give them a pop up message that states "exists - going to record" and they click ok and go. If the combo doesn't exist, the user is none the wiser, and a new entry is created.

To me this sounds very convoluted...

Anyhow... my code follows for the find record if no record give a mesage box (testing before just allowing a save).

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Save_Record_Click()
  3.     If (PI_Number & vbNullString) = vbNullString Then Exit Sub
  4.  
  5.     Dim rs1 As DAO.Recordset
  6.     Set rs = Me.RecordsetClone
  7.     Set rs1 = Me.RecordsetClone
  8.  
  9.     rs1.FindFirst ("[ReportType]= & Report_Type" & _
  10.         "FROM Report_Status" & _
  11.         " WHERE Report_Status.[PI] = '" & PI_Number & "' " & _
  12.         "ORDER BY Report_Status.[PI]; ")
  13.  
  14.     If rs1.NoMatch Then
  15.         MsgBox "Sorry, no such record '" & Me.PI_Number & "' " & Me.Report_Type & "' was found.", _
  16.                vbOKOnly + vbInformation
  17.     Else
  18.         Me.Recordset.Bookmark = rs.Bookmark
  19.     End If
  20.     rs.Close
  21.  
  22. End Sub
  23.  
  24.  
Honestly, I have no idea if I'm doing any of it correctly, but I sometimes get the error message (yea!) but sometimes the error message is, well, erroneous (boo!)
Oct 4 '11 #4
Could the error possibly be fixed by inserting "Select"? If so, I have *no idea* where to insert the word.
Oct 4 '11 #5
patjones
931 Expert 512MB
This does not seem convoluted to me at all. What you are implementing is something called a compound primary key...it relies upon more than one column in order to establish uniqueness. A compound key is not ideal, but it does have its place in table design. It's possible that you might be able to go further in normalizing your tables to eliminate this situation, but that's a little beside the point of what you're asking.

I would try and write the FindFirst call as this:

Expand|Select|Wrap|Line Numbers
  1. rs1.FindFirst ("[ReportType]= " & Report_Type & " AND [PI] = '" & PI_Number & "'")


I am assuming here that PI_Number is a text string, and so enclosing it in ' '. Note that concatenation is important here; for example, if you include Report_Type inside the string then, as Rabbit points out, VBA interprets the whole thing as [ReportType] = Report_Type. In order for VBA to recognize Report_Type as a control on your form, it needs to be outside the string.

If rs.NoMatch is true, then you can proceed with the save. Otherwise you need to raise a message and exit gracefully.

Pat
Oct 4 '11 #6
Thank you. I could not for the life of me figure out how to properly concatenate that - hence why I was (poorly) using the SQL.

I am still getting my message box when I know rs1.NoMatch should be false. That is, say I have a report in the database that is entitled '0000000' (seven zeros, as text) and has a report type of 'CR'. When I type that into my form, I should not get the message box. But I do...

When I switch the clauses (making it so that I get a message should the report already exist & saving the entry from the form if record does not exist) then I get a run time error "No Current Record".

Expand|Select|Wrap|Line Numbers
  1.  
  2.     If rs1.NoMatch Then
  3.         Me.Recordset.Bookmark = rs1.Bookmark
  4.     Else
  5.         MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", _
  6.                vbOKOnly + vbInformation
  7.     End If
  8.  
  9.  
Any thoughts?
Oct 4 '11 #7
Sorry to continuously "double post" (triple post?) but I just noticed it is not correctly going through the if Statment. Should I create a new topic for this?
Oct 4 '11 #8
patjones
931 Expert 512MB
Usually, how you save a record when working with DAO recordsets is to do something like this:

Expand|Select|Wrap|Line Numbers
  1. rs.AddNew
  2.  
  3. rs.[field1] = control1_value
  4. rs.[field2] = control2_value
  5.  
  6. ...
  7.  
  8. rs.Update

What you're trying to do as the code currently stands is take the current record and populate the form with it. This is the opposite of your goal.
Oct 4 '11 #9
Thank you again! I will try this and see what I can break from there. :)

However, the NoMatch condition is giving me true everytime. So I get "Hello" even when the if statement *should* be false. So if I correct the "add new" portion, I believe I'll be back at square one of adding a record which already exists... And that is also the opposite of what I'm trying to accomplish.

So the new problem is the if statement I guess.
Oct 4 '11 #10
patjones
931 Expert 512MB
This means that the FindFirst call is not working properly. Have you tried to determine why that might be? One way is to Debug.Print the argument that you're passing into FindFirst (right before the call), and view the result in the Immediate window. This will tell you for sure if you're really passing in what you think you're passing in.
Oct 4 '11 #11
WOW... It's taking the text from my Report Type and saying the first letter (becuase the test type I'm using right now is L&D) and saying Does not recognize 'L' as a valid field name or expression. Yikes. I need it to raed the entire entry in the field as the text string...
Oct 4 '11 #12
patjones
931 Expert 512MB
Looking at your previous post, I see that your report type is a text value ('CR')...which means that you need to enclose that in ' ' in the criteria string.

Expand|Select|Wrap|Line Numbers
  1. rs1.FindFirst ("[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'")

Data types and string concatenation can be tricky. Generally, numbers don't need to be enclosed in anything, but text strings need ' ' while dates need # #. It's also important to watch out for things that look like numbers but aren't (for example '052986')
Oct 4 '11 #13
That fixed the "L" problem... I believe the FindFirst is still "broken" though as I am still getting message "Hello" when the if should be false (and therefore giving me "Sorry, a review for 0000409 CR already exists." - without quotes).

I should probably repost the code so that it's not too jumbled...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Record_Click()
  2.     If (PI_Number & vbNullString) = vbNullString Then Exit Sub
  3.     Dim rs1 As DAO.Recordset
  4.     Set rs1 = Me.RecordsetClone
  5. Debug.Print
  6.     rs1.FindFirst ("[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'")
  7.  
  8.     If rs1.NoMatch Then
  9.         'Me.Recordset.Bookmark = rs1.Bookmark
  10.                 MsgBox "Hello", _
  11.                vbOKOnly + vbInformation
  12.     Else
  13.         MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", _
  14.                vbOKOnly + vbInformation
  15.     End If
  16.     rs1.Close
  17.  
  18. End Sub
  19.  
Oct 4 '11 #14
patjones
931 Expert 512MB
You need to find out exactly what is going into FindFirst:

Expand|Select|Wrap|Line Numbers
  1. Debug.Print "[ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
Oct 4 '11 #15
I feel really dumb now. I added that line, ran the form (clicked the button after entering some data), but it won't produce anything.

Expand|Select|Wrap|Line Numbers
  1. ...
  2.  
  3.     If (PI_Number & vbNullString) = vbNullString Then Exit Sub
  4.     Dim rs1 As DAO.Recordset
  5.     Set rs1 = Me.RecordsetClone
  6. Debug.Print "[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
  7.     rs1.FindFirst ("[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'")
  8.  
  9. ...
  10.  
I think all this is really accomplishing is getting me frustrated beyond belief...
Oct 4 '11 #16
patjones
931 Expert 512MB
It has to produce something. You're looking in the Immediate window, correct? Go View > Immediate and it will appear along the bottom of your VBA screen. Then re-run the code and the result of the Debug.Print should be shown there.
Oct 4 '11 #17
Thank you. Previously I got a pop-up with the error...

Anyhow. It produced:
[ReportType]= 'CR' AND [PI] = '0000409'

and
[ReportType]= 'Rev CR + L&D' AND [PI] = '1234567'

and
[ReportType]= 'ICTM' AND [PI] = '1234567'

(I had tried a few times). I'm pretty sure this is what I want it to produce. I cannot tell if this is going through the FindFirst though (as I get the same result no matter what I enter)

For instance:

[ReportType]= 'CR' AND [PI] = '0000409' is an existing report.

Should FindFirst be able to take the previous input, look through the recordset (or clone in this case) and then return something? If so, does whatever it returns get assigned to rs1 then?

If my guess above is correct, no wonder my if statement is always true...
Oct 4 '11 #18
patjones
931 Expert 512MB
Can you remove the parenthesis from around the FindFirst argument and see what happens? It's a detail that I overlooked, but FindFirst doesn't require ( ) when passing arguments in.
Oct 4 '11 #19
Removing the parenthensis does not appear to affect the outcome.


That is, the If statement still answers as though true - even when I feed information which should be false.
Oct 4 '11 #20
I know I posted it previously, but what I want to have happen is when a user enters a duplicate basicall "catch" the error (number 2950 apparently) and give the user the option to go to the the record they tried to duplicate or change their attempted entry to something not duplicated for appending to the table. If there is no duplication, then just save the entry in the table.

Am I just going about this the entirely wrong way?
Oct 4 '11 #21
patjones
931 Expert 512MB
OK, what I'm going to do then is suggest a slightly different course of action. Let's say that your columns are in a table called tblProducts. Instead of using RecordsetClone, you can do this:

Expand|Select|Wrap|Line Numbers
  1. Dim rs As DAO.Recordset
  2. Dim strSQL As String
  3.  
  4. strSQL = "SELECT COUNT(*) AS num_dupes FROM tblProducts WHERE [ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
  5.  
  6. Set rs = CurrentDB.OpenRecordset(strSQL)
  7.  
  8. If rs!num_dupes = 0 Then
  9.    'Code to save record here
  10. Else
  11.    MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", vbOKOnly + vbInformation
  12. End If
  13.  
  14. ...

What this does is establishes a recordset of any records that happen to meet your criteria. If no records meet your criteria, then its record count is zero and you can proceed with the save. Otherwise, it contains one or more records and you need to inform the user.
Oct 4 '11 #22
NeoPa
32,185 Expert Mod 16PB
ZeppHead80:
but FindFirst doesn't require ( ) when passing arguments in.
See VBA Argument Lists for this side-issue.
Oct 5 '11 #23
NeoPa
32,185 Expert Mod 16PB
Having gone back to the original question, and because you (MHaledot) sound like you might appreciate the tips, I thought I'd post a link to How to Debug SQL String. You may find it helpful going forward.
Oct 5 '11 #24
Thank you. It may, in fact, help a lot. I know it probably obvious by now, but I'm not really very familiar with VBA... I guess I'm "learning by accident" as my coworkers prove to need more locks than I ever thought my "simple" record keeper database would need.
Oct 5 '11 #25
@zepphead

Thank you very much! I implemented the changes (as shown below) and now the code is functioning as I expect! And it has less "meat" which I always like.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Record_Click()
  2.  
  3. 'Establish a RecordSet of any Records which meet the search criteria.
  4. 'Record count = 0 allows save
  5.  
  6. Dim rs As DAO.Recordset
  7. Dim strSQL As String
  8.  
  9. strSQL = "SELECT COUNT(*) AS num_dupes FROM Report_Status WHERE [ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
  10.  
  11. Set rs = CurrentDb.OpenRecordset(strSQL)
  12.  
  13. If rs!num_dupes = 0 Then
  14.     'If no duplicates, save the record
  15.     DoCmd.RunCommand acCmdSaveRecord
  16.     MsgBox "Record Saved!", vbOKOnly + vbInformation
  17. Else
  18.     'If there are duplicates, give an alert box to inform user
  19.     MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", vbOKOnly + vbInformation
  20. End If
  21.  
  22. End Sub
  23.  
Sorry about the //documentation. That is from my java days in school where the teacher wanted us to document everything. I think it helps here (for me at least).
Oct 5 '11 #26
patjones
931 Expert 512MB
Excellent. I wish I could say why the FindFirst call didn't work properly, and perhaps NeoPa or someone else can yield some insight in that regard.

I personally find myself using this method quite a bit as opposed to the form's RecordsetClone property. As you work your way through Access and VBA, you'll find that there are numerous methods for populating a recordset, and in fact that there are different types of recordsets (DAO and ADO for instance).

Documentation is a good thing. I know that I appreciate picking up someone else's well-documented code.

Pat
Oct 5 '11 #27
Not that it really matters (to the specific topic of this thread), but thanks to both you and NeoPa I have a code that searches for the record and yells at the user if they duplicate it (not literally of course - but that may one day be necessary) and takes them to the existing record if it does exist (that last part I actually figured out on my own - I can be taught!).

Seriously, this will make the database that much more efficient. Thanks!
Oct 5 '11 #28
patjones
931 Expert 512MB
Just so that you're aware of it, there are lots of ways to do duplicate checking in a database.

In your situation, another possibility is to go in to table design view and actually specify the compound key. In the code, you can do an INSERT of whatever the user is trying to save. If the project ID/report type combination already exists in the table, then Access will raise an error and the save will not succeed. From a coding standpoint this is even leaner than what we've done here.
Oct 5 '11 #29

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Alan Murrell | last post: by
3 posts views Thread by access baby | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.