Combo Box in a Form based on Parameter Query

[JoeyB_99]

I am using Access 2007 and have created a parameter query, and then a Form based on this query.

I do not want a parameter prompt for the user, but instead a combo box drop-down list of all valid values!

How do I accomplish this? I have been researching the 'Net and am getting confused.

Is it necessary to create a separate form that has the Combo box? If so, why?

Why can't the combo box be on the same form, as the form I created from the query?

 

[John Big Booty]

If you are firing the query from an already open form you can use the following, in the criteria row of your query, to get the criteria from a combo on that form;

Forms!YourFormName!YourComboName

Be aware that this will return the value held in the first column of your Combo.

You might also want to put some code in the event that is firing your query to ensure that your user has made a selection from the combo, the following would do the trick;

If IsNull(Me.YourCombo) Then
     MsgBox "Please make a selection from the list"
     Me.YourCombo.SetFocus
     Me.YourCombo.DropDown
     Exit Sub
End If

 

[JoeyB_99]

Thanks JBB!

I do not want to fire up the query separately. I want a split form based on a parameter query to work from a combo box selection.

Yes, I am aware that the results should be based on the first column of the Combo Box. that is my intention.

I inserted your statement into the criteria row of the record source for the form, and the results come up blank.

What am I doing wrong?

 

[pr2-eugin]

Hello Joe,

I am not sure if your question is clear here.. But I am just picking up what I think should have been..

You wish to have a Form based on the Selection, which calls for a new form design that will collect the information first..

Is it necessary to create a separate form that has the Combo box?

Yes, it is necessary to create a Form first, to get the desired result, which then can be assigned for the Form; in which you will see data pretaining the Query..

If so, why?

The answer lies within your question,

I do not want a parameter prompt for the user, but instead a combo box drop-down list of all valid values!

If you wish to prompt user to enter data other than TEXT (in this case a ComboBox), you do need a Form that will open a Query using the code JBB gave you..

in the criteria row of your query, to get the criteria from a combo on that form;

Forms!YourFormName!YourComboName

Why can't the combo box be on the same form, as the form I created from the query?

It can do, but at this point it is a bit unclear..

Hope that makes sense..

 

[JoeyB_99]

Thank you 'pr', I appreciate the thorough response but I'm still confused on the structure here. Please bear with me.

Are you saying that I must set up a "front" Form that contains the Combo Box, where the user makes a selection?

If so, how does this "front" form connects to the Form I have created?

 

[pr2-eugin]

I know it is quiet confusing.. Sorry about that.. Okay lets take it from the start.. Imagine the DB is empty.. Now you want to;

STEP - 1: Ask the user to select a company name (eg. "Company 3", where there are several "Company 1", "Company 2", "Company 3", "Company 4"). This is the first Form where you will have the Combo Box.. in the Demo called the Main From...

STEP - 2: Create the Query (eg. FilteredDate), that will be used to create the Form that will display the list of all selected company.. The Query being, something like..

SELECT [Adhoc - BL].[COMPANY CODE], [Adhoc - BL].[COMPANY NAME], [Adhoc - BL].Amount, [Adhoc - BL].[DATE PAYABLE]
FROM [Adhoc - BL]
WHERE ((([Adhoc - BL].[COMPANY NAME])=[COLOR=Blue][B][Forms]![MainForm]![Combo0][/B][/COLOR]));

STEP - 3: Base that query to build the actual Form..

Look into the attachment for further details..

 

[JoeyB_99]

Thank you 'pr', very impressive. I appreciate the sample database.

This helps me out a lot, but I still would like to understand some things here with your suggested structure.

The user clicks on the "front" form to select the company number, which 'drives' the opening of the "second" form.

But, if the user now wants to see a different company they must close the "second" form and go back to the "front" form to make a new selection. Correct?

I would like to design a single form. It has a combo box which allows the user to see different results based on the selection made from the combo box.

Is that possible, and if so, how can I do that?

 

[pr2-eugin]

Yes you can do that.. Since now I can see what you actually want.. I could understand it.. There is something called as the FindFirst.. Where you will find a Record based on the selection.. See the new attachment...

 

[JoeyB_99]

Perfect 'pr', that is exactly what I want.

Thank you very much for the sample db! But I am weak with VBA code so I may need some help in utilizing 'FindFirst'.

I assume that I must copy the code from the After Update propert of your company combo box. Correct? Should I simply copy from the comment statement forward? If not, please clarify what I need to copy.

I'm also assuming I just have to do some simple 'swapping' of data fields/references to make it fit my database.

Is there any other changes I need to implement or do?

 

[pr2-eugin]

If you are not great with VBA just follow the ComboBox wizard.. there would be an option at the last to find record..

Follow that simple step..

 

[JoeyB_99]

Okay 'pr' that is big help now....but one last request, please.

I have a Split Form that is based on a table of transactions where I want to implement this combo box pull-dwon selection, as we have discussed.

When I add the combo button through the wizard it works fine, but it gives me the records I selected and those immediately following it.

I would like the form to simply show the records based on the combo-box selection, in the datasheet view, and no others.

That way, the user only sees and works with the records from the combo-box selection.

How do I achieve this effect?

 

[fleurette]

If you are not great with VBA just follow the ComboBox wizard.. there would be an option at the last to find record..



Follow that simple step..

Hi, this is very helpful as I am wanting to create a combo list based on a selection of particular record like what is described here.

I am on Access 2007 but I am unable to find the last option in the picture you posted above.

I only have the 2 options to select.

Am I missing something?
Thanks