List box in a parameter query?

[rschmidt32]

I have a customer master report that includes a parameter query to better define the criteria for the report. The parameter query works fine, but I want the parameter query to provide a drop down list of the customer names that are currently in the table. How do I add this list box to the parameter query?

Thanks!

 

[John Big Booty]

First up welcome to the forum.

In the criteria for Customer put;

Forms![COLOR="Magenta"]FRM_FormName[/COLOR]![COLOR="Magenta"]ComboName[/COLOR]

Replace FRM_FormName with the name of the form that is calling the report, and ComboName with the name of the combo box on that form. The combo must return in it's first column (ie. column 0 {zero}), not necessarily the visible column, the same data that will allow the query to select the correct Client.

 

[rschmidt32]

John, thanks for the reply! I am not sure that I understand what you are suggesting. I have a customer master table that I have populated without the use of an input form. For that table, I have created the report that I described. I am hoping to be able to use a combo box/list box to select the customer name prior to printing the form. Does this explanation change your recommendation? Thanks!

 

[Brianwarnock]

I shoudn't think so. To use selection list your parameter query will have to get the data from a combo on a form.
What you have is a selection form which contains the combo populated , presumably, from the table, which is referenced in the query as John described, and a command button to open the report.

Brian

 

[rschmidt32]

Thank yoiu for the additional clarification! That will work?

 

[rschmidt32]

Brian & John, I apologize for being a newbie in Access, but I have a second issue that I need help with. I created a form that allowed me to capture the respective customer name via the list selection and included within that form is a control button to activate the report. I used the syntax that John suggested to pass the customer name from the form to the report query to generate the report.

After selecting the respective customer name, I activate the report button which generates the report; however, the customer name is not passed, and I receive no data from my query.

The report control button works fine, so I know that the issue is with how I am passing/receiving the selection.

Thoughts?

 

[rschmidt32]

In addition, in my new Form that allows me to capture the desired customer name, how do I setup that Form to where Access will not try to update the existing customer master data? I am simply trying to retrieve the customer name. Does this question make sense?

 

[John Big Booty]

Any chance you could post a copy of your DB?

 

[rschmidt32]

John, thanks for offering to take a closer look. I have attached a copy of the database. The 'Select Customer Name For Report' form is used to select the customer name to be passed to the report. The control button accesses the 'Customer Master Report'.

The second issue that I described in my follow-on note can be simulated by selecting a name other than Bernie Swader and trying to get out of the form. You will receive the error from Access.

Thoughts?

 

[John Big Booty]

Any chance you can save it as a Access 03 version, as I don't have Access 07 at my current location

 

[rschmidt32]

John, here is the 2003 version. Let me know if you can open this. Thanks!

 

[Brianwarnock]

The selection form should not be a bound form.
The combo takes its data from the table.

See attached, new form and changes to the query.

Brian

 

[John Big Booty]

Looks like Brianwarnock beat me to it

 

[John Big Booty]

In Response to a PM from the OP here is my mod to the DB.

Firstly avoid using spaces and other special characters in field and control names, they will always cause you grief, in this case your criteria was enclosed in Double Quotes (" ") where it should have looked like

[Forms]![Select Customer Name For Report]![combo4]

you also need to be careful to use the actual Control name In this case Combo4 rather than the control source.

Whilst we are talking about naming, consider implementing a naming convention for your tables, forms, etc. Something like FRM_FormName, TBL_TableName, QRY_QueryName and so on is good, as it lets you easily identify what the object is that you are referencing in your code.

I would also echo

The selection form should not be a bound form.
The combo takes its data from the table.

See attached, new form and changes to the query.

Brian

Unless of course you want this button to operate as part of your data entry rather than as a search and report as Brain has envisaged.

 

[Brianwarnock]

In Response to a PM from the OP here is my mod to the DB.

Firstly avoid using spaces and other special characters in field and control names, they will always cause you grief, in this case your criteria was enclosed in Double Quotes (" ") where it should have looked like

[Forms]![Select Customer Name For Report]![combo4]

you also need to be careful to use the actual Control name In this case Combo4 rather than the control source.

.

I think the Double Quotes arose because he had Form! instead of Forms! but I agree about spaces in names, also I should have called my Combo cboCustomername not just customername, I like to give them meaningful names but identify them as Combos.

Brian

 

[rschmidt32]

Gentlemen,

Thanks for the tips and all of your work. I am traveling on business for a couple of days and will implement the recommendations when I get back.

Thanks again!

 

[Stephen_p]

I want to pass a list box (which is called Pract_List) to a paramter query called testquery but for some reason it wont work. The line qdf.SQL = strSQL is comming up in the debugger could anyone help?


Private Sub Command_4_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("TESTQUERY")
For Each varItem In Me!Pract_List.ItemsSelected
strCriteria = strCriteria & "," & Me!Pract_List.ItemData(varItem) & ""
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything." _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, [Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], [Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], [Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], [Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov]" & _
"WHERE((([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE])=[Forms]![Test_Form]![Pract_List]));IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "TESTQUERY"
Set db = Nothing
Set qdf = Nothing
End Sub

 

[Brianwarnock]

Stephen I suggest that you copy your post into a new thread in the VB sub forum, and delete it from here to avoid double posting

Brian

 

[Brianwarnock]

I've just come across Stephen's identical post in the Forms forum.

Stephen do not double post it pisses people off and they may not help you

Brian

 

[Stephen_p]

Sorry brian I just checked the date and thought it no one would see this