Search function for continuous form keeps asking me to "Enter Parameter Value"

[kvnd]

Search function for continuous form keeps asking me to "Enter Parameter Value"

Ok, there is some background info necessary to understand the problem. Sorry for it's length:

Division, Center, and Program. You can think of these as being like Country, State, and Town respectively. (imagine for a second that zip codes and towns have a 1:1 correspondence for the rest of this question)

The actual data stored in the database is only the Program Code ("Zip Code").

I have a query that pulls information from an SQL table (dbo_TabFacilityView) that also lists the Division Name, Center, Center Code, and Program Name for each Program Code. This query is called "ProgramList".

I have a continuous form which lists all records. The division combo box on this form is locked and disabled, with 2 columns, 1 being the Program Code (which is hidden by having a width of zero) and the other column being the Division Name, which is visible to the user as the sole information in the combo box. This combo box is called "Division List". This combo box has the Program Code as the control source. Here's the row source for this box:

SELECT dbo_TabFacilityView.ProgramCode, dbo_TabFacilityView.[Division Name] FROM dbo_TabFacilityView ORDER BY dbo_TabFacilityView.[Division Name];

I have a button "Open Search Form" in the header of my continuous form which opens up another form "Search Form", where there is a combo box named "cboDivision". It lists all the unique division names. This is the row source for this combo box:

SELECT DISTINCT ProgramList.[Division Name]
FROM ProgramList
ORDER BY ProgramList.[Division Name];

You can select a Division in this combo box, then hit a "Search Records" button which is supposed to apply a filter to the continuous form so only records where the Division selected in cboDivision on the search form is equal to the Division Name listed in the Division List combo box on the continuous form.

This is the VBA code for this search button on "Search Records":

Private Sub Search_Click()
Dim req As String
req = ""
'-----------------------------------------------
If Not IsNull(Me.cboDivision) Then
req = set_req(req, "[Division Name] = '" & Me.cboDivision & "'")
End If

TempVars!req = req
TempVars![old pgm] = Me.Form.Name
DoCmd.Close
End Sub
'------------------------------------------------------------
' set up req
'------------------------------------------------------------
Public Function set_req(req As String, reqcon As String) As String
If req <> "" Then
set_req = req & " and " & reqcon
Else
set_req = reqcon
End If

End Function


My problem is, when I pick a Division Name on the search form, and press "Search Records" it gives me the popup "Enter Parameter Value" for "Division Name". Anyone know why this is, and how to resolve this?

Thanks. Please move this if this is not the appropriate subforum.

 

[Ranman256]

Re: Search function for continuous form keeps asking me to "Enter Parameter Value"

the query may have a param in it ,either:
a misspelled field name, or one that does not exist.

if you are giving params via code, then you must add them to the code

dim qdf as querydef
dim rst as recordset

Set qdf= currentdb.QueryDefs!qrySalesReportDaily
qdf.Parameters![Formula] = 'Formula2'
qdf.Parameters![Plant] = 'Plant1'
Set rst= qdf.OpenRecordset

 

[The_Doc_Man]

Re: Search function for continuous form keeps asking me to "Enter Parameter Value"

Darn, Ranman256 beat me to it. The FIRST question to ask when this kind of dialog box opens up is, what is it asking for and when you defined that something, did you spell it correctly?

The thing it requests is how you actually spelled it in the place where Access wanted to use it. If that doesn't EXACTLY match the way it was defined, there is your error.

 

[kvnd]

Re: Search function for continuous form keeps asking me to "Enter Parameter Value"

the query may have a param in it ,either:
a misspelled field name, or one that does not exist.

if you are giving params via code, then you must add them to the code

dim qdf as querydef
dim rst as recordset

Set qdf= currentdb.QueryDefs!qrySalesReportDaily
qdf.Parameters![Formula] = 'Formula2'
qdf.Parameters![Plant] = 'Plant1'
Set rst= qdf.OpenRecordset

Sorry but I'm not specfically seeing how to apply this to my problem. I am fairly new to access so forgive me. This code is mostly taken from someone else so I don't understand this stuff as well as you might think.