I am creating an unbound form that receives initials and uses them to determine what the next available key should be for given initials. When I run my test and select the add button I receive an "Invalid Qualifier" error on the "Set rst" line. VSBasic highlights the "dbs" right after the equal sign. The database I am using is an Access database I created. Am I using an incorrect way of selecting rows from the database?
Thanks for any help you may be able to give me.
Francie
Here is my VSBasic code. I have noted the line in error, below.
Public Sub GetNumber_Click()
Dim dbs As String
Dim rst As DAO.Recordset
Dim StrSQL As String
StrSQL = "SELECT Left(Max([Customer_Code]),2) AS ['Initials'], " & _
"Right(Max([Customer_Code]),6)+1 AS ['LastCodeUsed'] " & _
"FROM [ClientMaster] " & _
"WHERE (Left([Customer_Code],2)=Me![Initials_In]); "
Debug.Print "SQL="; StrSQLS
' Set dbs = CurrentDb
dbs = "ClientMaster"
Set rst = dbs.OpenRecordset(StrSQL) <-- THIS IS THE ERROR LINE
Debug.Print "Dbs="; dbs
Debug.Print "Rst="; rst
' rst.Open StrSQL
' DoCmd.OpenQuery "LastClientCodeUsed"
' NewClientCode = FrmName![Initials_In] & "00000" & rst.[LastCodeUsed]
NewClientCode = FrmName![Initials_In] & "00000"
' DoCmd.OpenForm ("NewClientEntry"
End Sub
Thanks for any help you may be able to give me.
Francie
Here is my VSBasic code. I have noted the line in error, below.
Public Sub GetNumber_Click()
Dim dbs As String
Dim rst As DAO.Recordset
Dim StrSQL As String
StrSQL = "SELECT Left(Max([Customer_Code]),2) AS ['Initials'], " & _
"Right(Max([Customer_Code]),6)+1 AS ['LastCodeUsed'] " & _
"FROM [ClientMaster] " & _
"WHERE (Left([Customer_Code],2)=Me![Initials_In]); "
Debug.Print "SQL="; StrSQLS
' Set dbs = CurrentDb
dbs = "ClientMaster"
Set rst = dbs.OpenRecordset(StrSQL) <-- THIS IS THE ERROR LINE
Debug.Print "Dbs="; dbs
Debug.Print "Rst="; rst
' rst.Open StrSQL
' DoCmd.OpenQuery "LastClientCodeUsed"
' NewClientCode = FrmName![Initials_In] & "00000" & rst.[LastCodeUsed]
NewClientCode = FrmName![Initials_In] & "00000"
' DoCmd.OpenForm ("NewClientEntry"
End Sub