Query Object To Recordset

[TallMan]

Good Afternoon,

I am in the middle of creating a function that populates two unbound text boxes on a form and then uses those unbound textboxes in a query (Total of 3 unbound text boxes - 2 are populated from this function). The saved query object is working fine when I manually execute it (after the unbound text boxes have been populated). However, when I go to set the same query to a recordset I am getting the "Too Few Parameters. Expected 3." error message.

In the saved query I used the build function to use the unbound text boxes as part of the where clause.

Below is the code I am trying to execute:

Public Function Test()
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Dim DtBegin As String
''FInd the Begin dt
stBegin = DateAdd("q", -1, DateSerial(Year(Date), (DatePart("q", Date) - 1) * 3 + 1, 1))
Dim DtEnd As String
''find the end date
DtEnd = DateAdd("Q", DateDiff("Q", 0, Date) - 1, 0)
''populate the unbound text with begin dt
Forms!frmConsultingMain!txtTrailBegin = Format(stBegin, "YYYYMMDD")
''populate the unbound text with end dt
Forms!frmConsultingMain!TxtTrailEnd = Format(DtEnd, "YYYYMMDD")
 
Set rst = db.OpenRecordset("qryTest")  ''The saved query that works when executing directly.
If rst.RecordCount = 0 Then
MsgBox "no records"
Exit Function
End If
While Not rst.EOF
MsgBox rst.Fields(0)
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
End FUnction

Any ideas on this? The unbound text boxes are populated before the query is set to the recordset.

Thank you in advance for your help.
Tallman

 

[pbaldy]

Sure:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

You may be able to wrap the parameters in the Eval() function as well.

 

[TallMan]

Thank you for the quick reply. I did come accross the link you sent when I was conducting my research a little earlier, but it was a little over my head. In addition, I did not see anything about the Eval() function within the link. I just tried placing the eval() around each of the criteria in the saved query and received the same message. Where would you place the eval()?

One of the parameters in the where clause is a between statement. WOuld you wrap it around each unbound textbox in the where clause or just one large one around the whole between statement?

 

[pbaldy]

Eval() isn't mentioned in the link, I mentioned it as a possible alternative. It would go around each form reference:

Eval('Forms!FormName.TextboxName')

 

[TallMan]

Nice. That worked! I added the apostrophies. Thank you, pbaldy. You da man!

 

[pbaldy]

Happy to help!