Opening query from code

A

Amy Blankenship

Following this example: http://www.mvps.org/access/queries/qry0003.htm

I've written the following code:
Dim pwd As String, tmpUname As String, db As database, rst As Recordset,
qry As QueryDef
Dim SSN As String, OldUName As String, Uname As String, UNum As Integer,
i As Integer, iFound As Integer
'set user name
Debug.Print CStr(Me.UserName)
If IsNull(Me.UserName) Then
tmpUname = Left(Me.FirstName & Me.LastName, 18)
Set db = CurrentDb()
Set qry = db.QueryDefs("CheckExistingUserName")
qry![Enter a UserName] = tmpUname
Set rst = qry.OpenRecordSet()
If Not rst.EOF Then
OldUName = rst(1)
For i = 0 To 9
If InStr(1, OldUName, i) < iFound Or IsEmpty(iFound) Then
iFound = InStr(1, OldUName, i)
Next
End If
If iFound > 0 Then
UNum = CInt(Right(OldUName, Len(OldUName) - iFound)) + 1
End If
Uname = tmpUname + String(2 - Len(UNum), "0") + UNum
Me.UserName.Value = Uname
End If

It errors at Set rst = qry.OpenRecordSet() Runtime error 13 type mismatch.
I'm pulling my hair out.

Any ideas?

TIA;

Amy
 
K

Ken Snell [MVP]

You need to disambiguate the Dim step for the Recordset variable, because
ACCESS thinks you want an ADO recordset, but you really want a DAO
recordset:

Dim pwd As String, tmpUname As String, db As DAO.database, rst As
DAO.Recordset,
qry As DAO.QueryDef
 
A

Amy Blankenship

I tried that, and it didn't work. However, changing it to Variant (ugh) did
work.

This is not something I can spend a lot of time on right now, so it'll have
to do.

Thanks;

Amy

Ken Snell said:
You need to disambiguate the Dim step for the Recordset variable, because
ACCESS thinks you want an ADO recordset, but you really want a DAO
recordset:

Dim pwd As String, tmpUname As String, db As DAO.database, rst As
DAO.Recordset,
qry As DAO.QueryDef

--

Ken Snell
<MS ACCESS MVP>

Amy Blankenship said:
Following this example: http://www.mvps.org/access/queries/qry0003.htm

I've written the following code:
Dim pwd As String, tmpUname As String, db As database, rst As
Recordset, qry As QueryDef
Dim SSN As String, OldUName As String, Uname As String, UNum As
Integer, i As Integer, iFound As Integer
'set user name
Debug.Print CStr(Me.UserName)
If IsNull(Me.UserName) Then
tmpUname = Left(Me.FirstName & Me.LastName, 18)
Set db = CurrentDb()
Set qry = db.QueryDefs("CheckExistingUserName")
qry![Enter a UserName] = tmpUname
Set rst = qry.OpenRecordSet()
If Not rst.EOF Then
OldUName = rst(1)
For i = 0 To 9
If InStr(1, OldUName, i) < iFound Or IsEmpty(iFound) Then
iFound = InStr(1, OldUName, i)
Next
End If
If iFound > 0 Then
UNum = CInt(Right(OldUName, Len(OldUName) - iFound)) + 1
End If
Uname = tmpUname + String(2 - Len(UNum), "0") + UNum
Me.UserName.Value = Uname
End If

It errors at Set rst = qry.OpenRecordSet() Runtime error 13 type
mismatch. I'm pulling my hair out.

Any ideas?

TIA;

Amy
Click to expand...
Click to expand...
 
Top