Error 3061 on Set Recordset

[Juan Schwartz]

I am trying to make a function that will check for dupes based on
certain dynamic criteria. I get an error when running my query with
brackets inserted around what could be table/field names with spaces.
It's treating it like a parameter instead of a table/field name. Any
assistance would be appreciated.

Also, in my function, is there a way to let "dupestring" in the
function slip through as a null value as I have a check for that in
the function...

Function checkdupestring(dupestring As String, dupetable As String,
dupefield As String, fieldname As String)
If Len(dupestring) > 0 Then
Dim dupe As Recordset

dupetable = "[" & dupetable & "]" 'When this line and the line below are commented out, it works... unless the field/table/whatever has a space in it which would require the brackets.
dupefield = "[" & dupefield & "]"
Set dupe = CurrentDb.OpenRecordset("SELECT * FROM " & dupetable & " WHERE " & dupefield & " = '" & dupestring & "'") 'This is the actual error line

Debug.Print

If dupe.RecordCount > 0 Then
MsgBox ("Duplicate record found in database with " &
fieldname & ": " & dupestring & ".")
End
End If
End If
End Function

 

[Allen Browne]

Build the SQL statement as a string.
You can then Debug.Print it.
When it fails, you can copy it from the Immediate Window (Ctrl+G), and see
what's wrong.


Function checkdupestring(dupestring As String, dupetable As String, _
dupefield As String, fieldname As String)
Dim db As DAO.Database
Dim dupe As DAO.Recordset
Dim strSql As String

strSql = "SELECT * FROM [" & dupetable & "] WHERE [" & dupefield & "] "
& _
IIf(dupestring <> vbNullString, "= """ & dupestring & "";", "Is
Null;")

Debug.Print strSql

Set db = CurrentDb()
set rs = db.OpenRecordset(strSql)
If dupe.RecordCount > 0 Then
MsgBox ("Duplicate record found in database with " & fieldname & ":
" & dupestring & ".")
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to make a function that will check for dupes based on
certain dynamic criteria. I get an error when running my query with
brackets inserted around what could be table/field names with spaces.
It's treating it like a parameter instead of a table/field name. Any
assistance would be appreciated.

Also, in my function, is there a way to let "dupestring" in the
function slip through as a null value as I have a check for that in
the function...

Function checkdupestring(dupestring As String, dupetable As String,
dupefield As String, fieldname As String)
If Len(dupestring) > 0 Then
Dim dupe As Recordset

dupetable = "[" & dupetable & "]" 'When this line and the line
below are commented out, it works... unless the field/table/whatever
has a space in it which would require the brackets.
dupefield = "[" & dupefield & "]"
Set dupe = CurrentDb.OpenRecordset("SELECT * FROM " & dupetable
& " WHERE " & dupefield & " = '" & dupestring & "'") 'This is the
actual error line

Debug.Print

If dupe.RecordCount > 0 Then
MsgBox ("Duplicate record found in database with " &
fieldname & ": " & dupestring & ".")
End
End If
End If
End Function