Can't Open A RecordSet - Please help!

[rhunt]

I am not a developer, and non of my VB developers know VBA...yet, I am creating an Access database that will require VBA. I have been able to create the following code, but I am getting a "Type Mismatch" error. When I step through the code, it fails on the open recordset. I simply can't find the problem...any help is greatly appreciated. Thanks. Ryan Hunt

Function GetDupCode() As Boolean

Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim strRST As String
Dim strDupCode As String
Dim nRefNum As Integer

'Set variables to form values

strDupCode = Me!DupCode

nRefNum = Me!RefNum


'Open connection to current Access database
Set db = CurrentDb()

'Create SQL statement to retrieve value from GST table
strSQL = "SELECT * FROM tblArtFair WHERE DupCode = '" & strDupCode & "' AND REFNUM <> " & nRefNum
'Debug.Print strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Retrieve value if data is found
If Not rst.EOF Then
MsgBox "DupCode '" & Me.DupCode & "' has already been entered into the database."
GetDupCode = True
Else
GetDupCode = False

End If

rst.Close
Set rst = Nothing

' GetDupCode = strRST

End Function

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click
Dim sCheck As String
sCheck = "This DupCode already exists, are you sure you want to save?"
If GetDupCode = True Then
If MsgBox(sCheck, vbOKCancel, "Duplicate Record Warning") = vbOK Then
'MsgBox "OK"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else
DupCode.SetFocus
End If
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If

 

[namliam]

You are probably using A2K or above...

You must disambiguate your recordset... as well as database

Read up on DAO and ADO

I am not 'hot' on ado but to me it looks like you are using DAO so ...

Dim db As DAO.Database
Dim rst As DAO.Recordset

GL & Regards

 

[cogent1]

I think your code would fail much sooner if it was a reference problem. I believe it is the SQL string that is causing the error. What does your debug statement return?

 

[adam_fleck]

Reference Problem

The code you're using is for DAO recordsets. I think it's pretty likely the problem is that your project has a reference to ActiveX Data Objects 2.X Library as well as a reference to Microsoft DAO 3.6 Object Library.
These are not compatible.

Try this: In the VB project window, Tools, References... - the references popup will appear.

If ActiveX Data Objects 2.X is ticked, remove the tick.

If Microsoft DAO 3.6 Object Library isn't ticked, locate it and tick it - it's a long way down the list (type M to get to the M's).

With the settings shown in good_ref.jpg, your code works fine. With the settings shown in bad_ref.jpg it breaks just as you descirbed.