CODEWITHSUNDEEP
ms-accessvbams-access-2010
CodeMed
CodeMed

Reputation: 9191

compile error: expected end of statement

A Microsoft Access 2010 database is giving me the following error message: 

Compile Error: Expected End Of Statement

Here is the method that is throwing the error message: 

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Provide the user with the option to save/undo
    'changes made to the record in the form
    If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.Save
        Else
            DoCmd.RunCommand acCmdUndo
    End If

    Dim sSQL As String
    sSQL = "SELECT max(Clients.ClientNumber) AS maxClientNumber FROM Clients"
    Dim rs As DAO Recordset
    Set rs = CurrentDb.OpenRecordset(sSQL)
    MsgBox ("Max client number is: " & rs.Fields(1))
End Sub

The line of code that is throwing the error message is: 

 Dim rs As DAO Recordset

I am not sure if the problem has to do with the syntax of what is on the line preceding it. Can anyone show how to fix this problem? And explain what is going on?

Upvotes: 0

Views: 34788

Answers (2)

Cayucodies
Cayucodies

Reputation: 1

You're mising the semicolon at the end of your Sql statement

Upvotes: 0

Chris Rolliston
Chris Rolliston

Reputation: 4808

You are missing a full stop (period) between the DAO and the Recordset - it should be

Dim rs As DAO.Recordset

Beyond that, you will also have a runtime error on reading the field value, since a DAO Fields collection is indexed from 0, not 1. Hence, change the penultimate line to this:

MsgBox ("Max client number is: " & rs.Fields(0))

Alternatively, reference the field by its name:

MsgBox ("Max client number is: " & rs!maxClientNumber)

Upvotes: 3

Related Questions