Why do I get "No value given" error if qry has filter criteria

[KerryL]

I'm trying to limit (by filtering) the list of records users can make a selection from by allowing them to filter the list by name and/or ssn. But when I try and create a cbo or listbox based on my query (qrySelPartic) I receive this informational error:

"No value given for one or more required parameters."


Let me try and summarize what I'm trying to do:
1. User clicks "Add Participant" cmd button.

2. frmAddPartic opens and prompts the user to enter a Last Name and/or SSN

3. frmAddPartic.lstSelPartic is populated with only those records the query finds that match the name or ssn entered by the user. (the source for lstSelPartic = qrySelPartic, which prompts the user for 'Last Name' or 'SSN'. This filters 185,000 records down to only those that match the user's input.)

4. User double-clicks the record in lstSelPartic that he wants to add, and the record is added to frmAddPartic and stored in tblParticipant.


Hope that makes sense. As I said, the problem comes from the fact that qry SelPartic contains [Enter Last Name:] criteria for the LName field. If I remove it, I do not receive the error above. But I need to perform that filtering step in order to minimize the selection list for the users.

What am I doing wrong? Why can't I base my list box on a filtered (by user input) query?

Thanks,
kerry

 

[KyleS]

Hi Kerry,

Put a text box on your form with the "add Participant" button, let's call this text box txtCriteria. Have the user enter either the Last Name OR the SSN in this box before they press the "Add Participant" button.

Change the WHERE statement of your query to this:

WHERE LName = [Forms]![b][Your Form Name Here][/b]![txtCriteria] OR SSN = [Forms]![b][Your Form Name Here][/b]![txtCriteria]

This should solve your problem. Though you'll get no results if nothing is entered in the text box (which I assume is what you want)

Kyle

 

[iamthebestuk]

Hi Kyle,

I'm having a similar problem as described above but my criteria for the query is coming from a form. I tried your suggestion above but it did not solve the problem.

Here's my query:

SELECT [Global Business Warehouse data].[Country key], [Global Business Warehouse data].[Employee], [Global Business Warehouse data].[Last Name], [Global Business Warehouse data].[First name], [Global Business Warehouse data].[Position]
FROM [Global Business Warehouse data]
WHERE ((([Global Business Warehouse data].[Last Name]) Like [Forms]![frmSuc_Plan_JobSearch_Results]![Last_Name] & "*") And (([Global Business Warehouse data].[First name]) Like [Forms]![frmSuc_Plan_JobSearch_Results]![First_Name] & "*"));



Could you recommend a solution for using the results of this query and putting them into a listbox ready for user selection?


best regards,
Bhavesh



Power is Knowledge
Knowledge is Power

 

[iamthebestuk]

Got it.

The solution:

Try creating the listbox on the select query without the the parameters then readd them after the listbox is in place.



Power is Knowledge
Knowledge is Power