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.Execu te 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 & "'"