Search function for continuous form keeps asking me to "Enter Parameter Value" (1 Viewer)

kvnd

Registered User.
Local time
Today, 16:49
Joined
Oct 9, 2018
Messages
16
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

Registered User.
Local time
Today, 16:49
Joined
Apr 9, 2015
Messages
3,765
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

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

Immoderate Moderator
Staff member
Local time
Today, 15:49
Joined
Feb 28, 2001
Messages
19,027
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

Registered User.
Local time
Today, 16:49
Joined
Oct 9, 2018
Messages
16
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

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.
 
Last edited:

Users who are viewing this thread

Top Bottom