Too few parameters expected 4

[sammylou]

I know this has been posted many times and I have searched through this site for answers but nothing resolves my problem , especially as "yes, I am providing the parameters from my form!!

This is my code: (note the SELECT statement is all on one line in my DB)
Dim mysql3 As String
Dim myrset3 As Recordset
Set mydb = DBEngine.Workspaces(0).Databases(0)
mysql3 = "SELECT [Report Details].ReportRefPrefix, [Report Details].ReportRefNo, [Report Details].ReportFrequency, [Report Details].Duplicate_Record FROM [Report Details] WHERE
((([Report Details].ReportRefPrefix)=[Forms]![Change_Prefix_and_Number_for_existing_service]![RepPrefix])
AND (([Report Details].ReportRefNo)=[Forms]![Change_Prefix_and_Number_for_existing_service]![RepNo])
AND (([Report Details].ReportFrequency)=[Forms]![Change_Prefix_and_Number_for_existing_service]![Frequency])
AND (([Report Details].Duplicate_Record)=[Forms]![Change_Prefix_and_Number_for_existing_service]![Duplicate]));"
Set myrset3 = mydb.OpenRecordset(mysql3)
If myrset3.RecordCount = 0 Then
MsgBox "Report " & Form.[RepPrefix] & Form.[RepNo] & " does not exist on the system as a " & Form.[Frequency] & "report", vbOKOnly + vbInformation, "DIS Report Logging Database"
Me!RepPrefix.SetFocus
RepPrefix = Null
DoCmd.Hourglass False
Exit Sub
End If

The problem is that when the recordset is empty the code stops on the red line above and says "Too few parameters expected 4". I know for the criteria I am keying into the fields on the form in this instance that the recordset will be empty but I want to display a msg when empty. I am though providing the parameters from the open form??? I did try using DLOOKUP instead of SQL as follows but couldn't get the syntax (double and single quotes) right as there are 4 different lookup criteria:

DLookup("[ReportRefPrefix]", "Report Details", "[ReportRefPrefix] = '" & Form.[RepPrefix] & "' & " And [ReportRefNo] = " & Form.[RepNo] & " And [ReportFrequency] = " & Form.[Frequency]& " And [Duplicate_report] = " & Form.[Duplicate] & ")

 

[KenHigg]

Try this:

DLookup("[ReportRefPrefix]", "Report Details", "[ReportRefPrefix] = '" & Form.[RepPrefix] & "' And [ReportRefNo] = '" & Form.[RepNo] & "' And[ReportFrequency] = '" & Form.[Frequency] & "' And [Duplicate_report] = '" & Form.[Duplicate] & "'")

(I'm not sure the form control references will work. You may have th use something like 'forms.frmMain.mycontrol')

???

 

[Mile-O]

Use a QueryDef object and build the parameters. This has been explained plenty of times, as you say, when you search on Too few parameters.

 

[Pat Hartman]

Here's one:
http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=176367#post176367

 

[sammylou]

Thanks for that. I did manage to get the DLookup working in the end. Admittedly, I don't normally use query defs, more comfortable working with SQL or DLookup. I just wanted to get the SQL to work rather than abandon it and do something more long winded!

 

[Mile-O]

Admittedly, I don't normally use query defs, more comfortable working with SQL or DLookup.

The DLookup domain aggregate function is extremely slow and you should only use it as a last resort.

Building SQL in VBA contributes to database bloat (as you are continually creating and destroying the query) which means it runs slower than a compiled QueryDef object.

In this instance, you should use the QueryDef routine and not the DLookup.