Solved Too few parameters - need workaround for invoking form control in recordset

[mike7352]

Hello. I have created a recordset that uses the following WHERE clause

WHERE (((paylines.date)>Now()-Weekday(Now()-1)-14 And (paylines.date)<Now()-Weekday(Now()-1)))

I would like to change the WHERE clause to be based on a form control. I know I can't use the following:

WHERE (((paylines.Date)>=[forms]![payroll_summary]![from] And (paylines.Date)<=[forms]![payroll_summary]![to]));

I have tried numerous workarounds, including putting the control inside Eval() and putting single quotes around the control ("'" & control & "'") and I'm still getting too few parameters message.

Before I look at rewriting this, any other suggestions?

 

[Gasman]

You concatenate the sql with the form controls?
Set tempvars and use those in sql?

 

[mike7352]

@Gasman, if you are proposing that I define a variable with the single quotes around the form control and call that in the sql statement, I have tried that. I found this function online. A) Does this work? B) I have never created a custom function and am not sure how to use it in a sub procedure. I am lower intermediate with VBA syntax.

Function fDAOGenericRst(strSQL As String, _
Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
Optional intOptions As DAO.RecordsetOptionEnum, _
Optional intLock As DAO.LockTypeEnum, _
Optional pdb As DAO.Database) As DAO.Recordset

Dim db As Database
Dim qdf As QueryDef
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter

If Not pdb Is Nothing Then
Set db = pdb
Else
Set db = CurrentDb
End If

On Error Resume Next
Set qdf = db.QueryDefs(strSQL)
If Err = 3265 Then
Set qdf = db.CreateQueryDef("", strSQL)
End If
On Error GoTo 0

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

If intOptions = 0 And intLock = 0 Then
Set rst = qdf.OpenRecordset(intType)
ElseIf intOptions > 0 And intLock = 0 Then
Set rst = qdf.OpenRecordset(intType, intOptions)
ElseIf intOptions = 0 And intLock > 0 Then
Set rst = qdf.OpenRecordset(intType, intLock)
ElseIf intOptions > 0 And intLock > 0 Then
Set rst = qdf.OpenRecordset(intType, intOptions, intLock)
End If
Set fDAOGenericRst = rst

Set prm = Nothing
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

End Function

 

[Gasman]

Here is a function from theDBguy as well.

Code Snippets

If you concatenate the control values, then all you have is a simple string?

Put it all into a string variable, and you can debug.print it, to see if it is correct

Also search here for too few parameters, as it gets asked a lot

Edit: Your code looks to be that from @theDBguy? He will be able to advise better on that, as I have never used it.

 

[mike7352]

@Gasman, here is the full string. I think this is simple?

SELECT paylines.name, lastname([name]) AS last_name from paylines WHERE [between two form controls, what I'm struggling with] ORDER BY lastname([name])

I will look through other threads but some of the answers are too advanced for me.

 

[mike7352]

Are there any prerequsites to getting debug.print to work? I tried to do that and nothing is coming up in the Immediate panel after I click run.

 

[Gasman]

Are there any prerequsites to getting debug.print to work? I tried to do that and nothing is coming up in the Immediate panel after I click run.

I tend to set a breakpoint and then walk through the code line by line, or at least between various breakpoints.
I have never heard of debug.print not working, as long as that line gets executed?

I am trying to find some code that I might have used in the past to do the same thing.

Another way is to set the parameters within a query and use that like below?

If IsNull(pdtStartDate) Then
    Set qdf = CurrentDb.QueryDefs("qryBonusCommissionNull")
Else
    Set qdf = CurrentDb.QueryDefs("qryBonusCommissionProcessed")
End If
With qdf
    .Parameters("[tmpEmployeeID]") = plngEmployeeID
    .Parameters("[tmpBonusPeriod]") = piBonusPeriod
    If Not IsNull(pdtStartDate) Then
        .Parameters("[tmpStartDate]") = pdtStartDate
        .Parameters("[tmpEndDate]") = pdtEndDate
    End If
End With
Set rst = qdf.OpenRecordset

 

[The_Doc_Man]

Let's look at this a different way. In this form that you are trying to use, is the form bound to a recordset based on a table or named query?

Because if so, then you could JOIN with it to get your Payroll_Summary.From and .To (perhaps) and not rely on the form controls at all. It would probably be a lot cleaner. And maybe faster.

 

[Gasman]

Here is another using Tempvars.

' SQL for employee recordset
strSQL = "SELECT tblEmployee.EmployeeID, tblEmployee.EndDate, tblEmployee.Forename, tblEmployee.Surname, tblEmployee.Initials, tblEmployee.WeekHours, tblEmployee.BasicRate, tblEmployee.IsCommPaid, tblEmployee.CommMultiLevel  FROM tblEmployee"
strSQL = strSQL & " WHERE (((tblEmployee.EndDate) Is Null) AND ((tblEmployee.Payroll)=True)) OR (((tblEmployee.EndDate) > " & [TempVars]![StartDate] & "))"

strSQL = strSQL & " ORDER BY tblEmployee.EmployeeID"

' Set the week end date
dtWE = Me.cboWeekEnd.Column(1)

Set rst = db.OpenRecordset(strSQL)

 

[mike7352]

@Gasman it's hard for me to see how the tempvars work without seeing how they are defined, could you add that code?

@The_Doc_Man, the form is bound to a table called paylines which is the same table that my SQL statement is based on. I'm really curious what you're suggesting. I've never manually written a JOIN statement, so I'd need some training wheels.

 

[Gasman]

@Gasman it's hard for me to see how the tempvars work without seeing how they are defined, could you add that code?

@The_Doc_Man, the form is bound to a table called paylines which is the same table that my SQL statement is based on. I'm really curious what you're suggesting. I've never manually written a JOIN statement, so I'd need some training wheels.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

' Set some tempvars for using in sub routines
TempVars("StartDate") = Format(Me.txtDateBW, strcJetDate)
TempVars("EndDate") = Format(Me.txtDateWE, strcJetDate)

 

[Gasman]

I'm off to bed now, so I'll let more qualified people offer their advice.
Good luck with it anyway. I expect to see it solved by the time I get up tomorrow.

 

[mike7352]

Thank you @Gasman

 

[theDBguy]

I found this function online. A) Does this work? B) I have never created a custom function and am not sure how to use it in a sub procedure. I am lower intermediate with VBA syntax.

Hi. Copy the function into a Standard Module and use it like so.

Set rs = fDaoGenericRst(strSQL, dbOpenDynaset)

Hope that helps...

 

[mike7352]

Hi @theDBguy, so strSQL is going to be the query WITH the form control, correct?

Set rs = fDaoGenericRst(strSQL, dbOpenDynaset) yields the following error:

Expected variable or procedure, not module.

 

[mike7352]

Actually @The_Doc_Man, the from and to fields are just text boxes on a form, they are called in other recordsets that are based on table paylines. paylines itself doesn't have a from and to field. Not sure if that matters.

I'm going to propose a workaround. I can have an InputBox for from date and to date and have the user manually enter mm/dd/yyyy. I tried to call that in my SQL statement (made sure to put a "#" on either side) and I still got too few parameters.

 

[theDBguy]

Hi @theDBguy, so strSQL is going to be the query WITH the form control, correct?

Set rs = fDaoGenericRst(strSQL, dbOpenDynaset) yields the following error:

Expected variable or procedure, not module.

Did you copy the function into a standard module and made sure the name of the module is not fDaoGenericRst?

 

[bastanu]

@mike7352: You never posted the entire code related to your original post. Where do you use the recordset? Is the Payroll_summmary form open?
The dates need to be wrapped in # like so:
WHERE paylines.Date>=#" & [forms]![payroll_summary]![from] & "# And paylines.Date<=#" & [forms]![payroll_summary]![to] & "#;"
Cheers,

 

[arnelgp]

is your Module Name same as the Function Name?
you need to rename Either one.

 

[mike7352]

Thank you all. I fixed it by putting the form controls inside Eval(). I also didn't understand that variables need to be outside of the sql string and concatenated and fixed that as well. I would like to explore the function from @theDBguy at some point but for now the issue is solved. I also probably could have done it @bastanu's way. Thanks everyone!