How to link ListBox with CommandButton 1

[470]

Hi Everybody,

I hope someone can help me with this (probably) simple question.

I have a table with several fields (i.e. country, subject, scale, date, etc.)
I have a form with different list boxes (i.e. one to select the country, one to select the subject, etc.) based on my table.
On the form I put a RunQuerry Button that should querry for the parameters selected in the list boxes and show the results in a report.
At the moment if I press my RunQuerry Button I get a "Enter Parameter Value" Box for each field defined in the querry (i.e. country, subject, scale, etc.). After typing all the parameters I get a report with the results.
The problem is, that I don't want this "Enter Parameter Value" Boxes, but I want to select my parameters with the ListBoxes and then the RunQuerry Button should run the querry and open the report with the results based on the selections made in the different ListBoxes.
SUMMARY:
I WANT TO SELECT MY PARAMETERS IN THE LIST BOXES AND DEPENDING ON THESE SELECTIONS I WANT A REPORT TO POP UP SHOWING ALL THE RECORDS WITH THESE PARAMETERS
MY QUESTION IS:
HOW DO I LINK (THE PARAMETERS SELECTED IN) THE LISTBOXES WITH THE COMMANDBUTTON THAT RUNS THE QUERRY AND OPENS THE REPORT WITH THE RESULTS?

Thank's a lot for any answer

Ciao Andreas

 

[jebry]

Hi!

I am assuming that you are using a query which has the list boxes in its criteria. You do have a couple of options:

1. Change the list boxes to combo boxes and reference them.

2. Write code to build the query or to store the selected items in hidden text boxes which you can reference from the query.

If you want to use the list boxes and are not sure about the code you will need just repost here and I will help with some sample code.

hth
Jeff Bridgham

 

[470]

Hi Jeff,

thank's for your quick answer.
I wanted to use listboxes because you can't add new data to the underlying table (is this right?)
I'm actually quite a beginner in this subject, so what do you mean by "I am assuming that you are using a query which has the list boxes in its criteria"?
It would be really great, if you could help me with some code examples!
Thank's a lot again

Ciao Andreas

 

[jebry]

Hi!

The only time you will make a change in an underlying table is when you set the control source of the list box, or any other control, to a field in a table or query. The table or query needs to be set as the record source of the form. If you open the property window of the controls and form and click on the data tab, you will see the properties I am talking about. So, in this case, it doesn't matter which control you use as long as it is 'unbound', meaning that it has no control source. It is highly recommended that any control, which will be used to search for records, be unbound.

What I meant by the query and the criteria is, in the QBE(Query By Example) interface, which is the standard query interface that most developers use, there are several rows in the grid at the bottom of the interface. The first is for the field name and then in order are: table name, sort, show and finally criteria. When a developer wants to use a form to limit the records a query returns, they often use this criteria line to link the query to the form as follows:

Forms!YourFormName!YourControlName

Of course, you need to put the actual names of the form and control after the Forms!. If Access doesn't recognize what you put in the criteria box, then it will pop up a form asking the user to enter the parameter. I am not sure that Access will accept input from a list box, though I can't say why it wouldn't. I am sure that it will accept input from a combo box using the format above. Then, when the query is run from the command button, the query will automatically take the information off the form when selecting records.

Let me know if this information is what you need or if you still want to see some code to build the SQL using VBA.

hth
Jeff Bridgham

 

[tlbroadbent]

You can reference listboxes in a query just as you can a textbox or combo box. Special code is required for multi-select list boxes but for a single selection, you just reference the list box.

Example: Select Employees in the department selected in the listbox.

Select *
From tblEmployees
Where Dept=[forms].[frmSelectDept].[DeptList] Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[470]

Hi again,

and thanks again for the quick response.
But I still get this "Enter Parameter Value" Box popping up, so I guess there must be still something wrong in my criterias.
The following is the SQL statement I have in my query:

SELECT main.country, main.subject, main.[sub-subject], main.[original scale], main.[creation date], main.title, main.[short description], main.[last revision]
FROM main RIGHT JOIN country ON main.country = country.cntname
WHERE (((main.country)=[Forms]![frmForm2]![countrycbo] Or [Forms]![frmForm2]![countrycbo] Is Null) AND ((main.subject)=[Forms]![frmForm2]![subjectcbo] Or [Forms]![frmForm2]![subjectcbo] Is Null) AND ((main.[original scale])=[Forms]![frmForm2]![scalecbo] Or [Forms]![frmForm2]![scalecbo] Is Null) AND ((main.[creation date])=[Forms]![frmForm2]![datecbo] Or [Forms]![frmForm2]![datecbo] Is Null));

"main" is the table with the fields "country, subject, etc."
"Form2" is the form where I put the comboboxes and the Run Query Button.
Could someone please tell me what's wrong?

Thanks a lot again.
Ciao Andreas

 

[jebry]

Hi!

Try this format for your criteria:

(main.country) like IIf(IsNull([Forms]![frmForm2]![countrycbo]), "*", [Forms]![frmForm2]![countrycbo])

Now it should look for anything in the field if it is left blank.

hth
Jeff Bridgham

 

[470]

It's me again,

I'm sorry but it's still not working. I'm really desperate!
The form has as row source the query and the comboboxes, as example, the following: SELECT DISTINCTROW [main].[ID], [main].[country] FROM [main]
I really don't know what's wrong in my criteria

Ciao Andreas

 

[jebry]

Hi!

What is the bound column of your combo boxes? I should have asked before how many columns you had. I the case at hand you need to make sure that the bound column matches the field you want to pass to the query.

hth
Jeff Bridgham

 

[470]

Hi,

my comboboxes have two fields, where the first field is a hidden ID field and the second field is the one showing i.e the selectable countries.
In the properties the bound column is set (by default) to 1.
May be I have to define this Id's in my criterias?

Ciao Andreas

 

[jebry]

Hi!

That won't be necessary, just change the bound column to 2 and it should pick up the country, subject, etc.

hth
Jeff Bridgham