Form Recordset

[clauses]

I am trying to create a recordset for a form using a query when the record set is opened. Using hte following code I keep getting an error about a parameter missing a value. If i remove the query and just put in the table name it seems to work ok but I only want to access specific records on the table. The code I am using is structured as follows:

SrchCrit = "Select txtYear, txtSemester, txtClassdate, txtWeekNo, txtDOW"
SrchCrit = SrchCrit & " FROM tblClassDates Where "
SrchCrit = SrchCrit & "(((tblClassDates.Year)=[SrchYear]) AND ((tblClassDates.Semester)='[SrchSemester]'))"


cnnClassDates.Open CurrentProject.Connection
rstClassDates.Open SrchCrit, cnnClassDates, adOpenDynamic, adLockOptimistic, adCmdText

 

[charityg]

Don't know if this will help, but at first glance it looks like your missing the "endsem"

SrchCrit = "Select txtYear, txtSemester, txtClassdate, txtWeekNo, txtDOW"
SrchCrit = SrchCrit & " FROM tblClassDates Where "
SrchCrit = SrchCrit & "(((tblClassDates.Year)=[SrchYear]) AND ((tblClassDates.Semester)='[SrchSemester]'));"

 

[Pat Hartman]

Jet is interpreting [SrchYear] and [SrchSemester] as parameters since it has no knowledge of VBA variables. The proper syntax (assuming that both fields are text) is:
SrchCrit = SrchCrit & "(((tblClassDates.Year)= '" & [SrchYear] * "') AND ((tblClassDates.Semester)='" & [SrchSemester] & "'))"

Essentially, what I have done is separated the variables so that they are outside of the string so that VBA can substute their value rather than their name as it builds the SQL string.

 

[clauses]

Using your suggestion I was able to get the query to at least pass a syntax check but the correct data is still not being displayed on my form. Instead of using the recordset the form is displaying the entire file. The table that is used to create the record set is the record source for the form and I was wondering if that might make a difference.

 

[Pat Hartman]

You must not be setting the report's recordsource in the correct place.

An easier way to do this is to apply a filter when using the OpenReport Method. You would just pass the following part of the SQL as the filter:

"(((tblClassDates.Year)= '" & [SrchYear] * "') AND ((tblClassDates.Semester)='" & [SrchSemester] & "'))"