I am receiving the User Deined Type error on the "Dim dbs" statement. What I am trying to do is select the next value of "Last Code Used" so I can generate the next key.
Am I working with DAO incorrectly?
Thanks,
Francie
Here's the code:
Public Sub GetNumber_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim StrSQL As String
Dim FrmName As String
Set FrmName = Forms![GetInitials]
StrSQL = "SELECT Left(Max([Customer_Code]),2) AS ['Initials'], " & _
"Right(Max([Customer_Code]),6)+1 AS ['LastCodeUsed'] " & _
"FROM [Clientr Master] " & _
"WHERE (Left([Customer_Code],2)=FrmName![Initials_In]); "
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
rst.Open StrSQL
' DoCmd.OpenQuery "LastClientCodeUsed"
NewClientCode = FrmName![Initials_In] & "00000" & rst.[LastCodeUsed]
End Sub
Am I working with DAO incorrectly?
Thanks,
Francie
Here's the code:
Public Sub GetNumber_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim StrSQL As String
Dim FrmName As String
Set FrmName = Forms![GetInitials]
StrSQL = "SELECT Left(Max([Customer_Code]),2) AS ['Initials'], " & _
"Right(Max([Customer_Code]),6)+1 AS ['LastCodeUsed'] " & _
"FROM [Clientr Master] " & _
"WHERE (Left([Customer_Code],2)=FrmName![Initials_In]); "
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
rst.Open StrSQL
' DoCmd.OpenQuery "LastClientCodeUsed"
NewClientCode = FrmName![Initials_In] & "00000" & rst.[LastCodeUsed]
End Sub