Invalid Qualifier Error

[FrancieFL]

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 &quot;Dbs=&quot;; dbs
Debug.Print &quot;Rst=&quot;; rst

' rst.Open StrSQL

' DoCmd.OpenQuery &quot;LastClientCodeUsed&quot;

' NewClientCode = FrmName![Initials_In] & &quot;00000&quot; & rst.[LastCodeUsed]
NewClientCode = FrmName![Initials_In] & &quot;00000&quot;

' DoCmd.OpenForm (&quot;NewClientEntry&quot

End Sub

 

[WildHare]

Dim dbs As String

Try setting your DBS as a DATABASE object, then you won't have such a problem setting a RECORDSET in it...Access is just having trouble setting a recordset object in a string...

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[PaulBricker]

Dim dbs as String
should be
Dim dbs as DAO.Database

That should fix it.

Paul

 

[FrancieFL]

Thank you both so very much. That got me past that problem.

Francie