syntax error, missing operator

[mhaledot]

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:

    rs1.FindFirst ("[ReportType]= & Report_Type" & _
        "FROM Report_Status" & _
        " WHERE Report_Status.[PI] = '" & PI_Number & "' " & _
        "ORDER BY Report_Status.[PI]; ")
 

[patjones]

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

[Rabbit]

Here's what that string looks like once it's translated.

Given PI_Number = 'abc'

[ReportType]= & Report_TypeFROM Report_Status WHERE Report_Status.[PI] = 'abc' ORDER BY Report_Status.[PI];

[mhaledot]

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

 
Private Sub Save_Record_Click()
    If (PI_Number & vbNullString) = vbNullString Then Exit Sub
 
    Dim rs1 As DAO.Recordset
    Set rs = Me.RecordsetClone
    Set rs1 = Me.RecordsetClone
 
    rs1.FindFirst ("[ReportType]= & Report_Type" & _
        "FROM Report_Status" & _
        " WHERE Report_Status.[PI] = '" & PI_Number & "' " & _
        "ORDER BY Report_Status.[PI]; ")
 
    If rs1.NoMatch Then
        MsgBox "Sorry, no such record '" & Me.PI_Number & "' " & Me.Report_Type & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
 
End Sub
 
 

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

[mhaledot]

Could the error possibly be fixed by inserting "Select"? If so, I have *no idea* where to insert the word.

[patjones]

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:

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

[mhaledot]

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".

 
    If rs1.NoMatch Then
        Me.Recordset.Bookmark = rs1.Bookmark
    Else
        MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", _
               vbOKOnly + vbInformation
    End If
 
 

Any thoughts?

[mhaledot]

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?

[patjones]

Usually, how you save a record when working with DAO recordsets is to do something like this:

rs.AddNew
 
rs.[field1] = control1_value
rs.[field2] = control2_value
 
...
 
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.

[mhaledot]

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.

[patjones]

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.

[mhaledot]

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...

[patjones]

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.

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

[mhaledot]

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...

Private Sub Save_Record_Click()
    If (PI_Number & vbNullString) = vbNullString Then Exit Sub
    Dim rs1 As DAO.Recordset
    Set rs1 = Me.RecordsetClone
Debug.Print
    rs1.FindFirst ("[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'")
 
    If rs1.NoMatch Then
        'Me.Recordset.Bookmark = rs1.Bookmark
                MsgBox "Hello", _
               vbOKOnly + vbInformation
    Else
        MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", _
               vbOKOnly + vbInformation
    End If
    rs1.Close
 
End Sub
 

[patjones]

You need to find out exactly what is going into FindFirst:

Debug.Print "[ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"

[mhaledot]

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.

...
 
    If (PI_Number & vbNullString) = vbNullString Then Exit Sub
    Dim rs1 As DAO.Recordset
    Set rs1 = Me.RecordsetClone
Debug.Print "[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
    rs1.FindFirst ("[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'")
 
...
 

I think all this is really accomplishing is getting me frustrated beyond belief...

[patjones]

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.

[mhaledot]

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...

[patjones]

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.

[mhaledot]

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.

[mhaledot]

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?

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

Dim rs As DAO.Recordset
Dim strSQL As String
 
strSQL = "SELECT COUNT(*) AS num_dupes FROM tblProducts WHERE [ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
 
Set rs = CurrentDB.OpenRecordset(strSQL)
 
If rs!num_dupes = 0 Then
   'Code to save record here
Else
   MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", vbOKOnly + vbInformation
End If
 
...

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.

[NeoPa]

ZeppHead80:
but FindFirst doesn't require ( ) when passing arguments in.

See VBA Argument Lists for this side-issue.

[NeoPa]

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.

[mhaledot]

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.

[mhaledot]

@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.

Private Sub Save_Record_Click()
 
'Establish a RecordSet of any Records which meet the search criteria.
'Record count = 0 allows save
 
Dim rs As DAO.Recordset
Dim strSQL As String
 
strSQL = "SELECT COUNT(*) AS num_dupes FROM Report_Status WHERE [ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
 
Set rs = CurrentDb.OpenRecordset(strSQL)
 
If rs!num_dupes = 0 Then
    'If no duplicates, save the record
    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "Record Saved!", vbOKOnly + vbInformation
Else
    'If there are duplicates, give an alert box to inform user
    MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", vbOKOnly + vbInformation
End If
 
End Sub
 

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

[patjones]

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

[mhaledot]

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!

[patjones]

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.