Using a Combo Box in a Query

[howarthe]

I created a query to list all products with a null value in the action field, but I want to be able to update the action field, and I can't. The query results appear in a form. The action field is filled with a combo box. The drop down list appears, but when I click on a selection, nothing happens. It won't accept the data. Maybe I've structured this the wrong way. Any suggestions.

[puppydogbuddy]

I created a query to list all products with a null value in the action field, but I want to be able to update the action field, and I can't. The query results appear in a form. The action field is filled with a combo box. The drop down list appears, but when I click on a selection, nothing happens. It won't accept the data. Maybe I've structured this the wrong way. Any suggestions.

Do you have an AfterUpdate event for your combo box like this:

Private Sub YourCombo_AfterUpdate()
     Me!Action.Value = Me!YourCombo.Column(1)
     Me.YourCombo.Requery
End Sub
 

The above assumes your combo has 2 columns>>>>a hidden bound column, and the action column. Access starts column indexing at 0, so column 1 is the second column.

[howarthe]

I checked the properties of my combo box, and I do not have an AfterUpdate event. My combo box does have two columns, and one is hidden, but I will need more help understanding the event you wrote for me. I pasted it into the Code Builder and changed

YourCombo to
Combo33

But it still doesn't work. My guess is that I should probably be changing Me to something as well, but I can't guess what. Thanks for your help, I think we're getting closer.

[puppydogbuddy]

I checked the properties of my combo box, and I do not have an AfterUpdate event. My combo box does have two columns, and one is hidden, but I will need more help understanding the event you wrote for me. I pasted it into the Code Builder and changed

YourCombo to
Combo33

But it still doesn't work. My guess is that I should probably be changing Me to something as well, but I can't guess what. Thanks for your help, I think we're getting closer.

The after update event code for your combobox fires after you have made a selection from the combobox, and does whatever you code it to do....which in your case....is update the textbox with the selection made from the combo.

1. every combobox can have an after update event procedure if you choose to code it. Place your form in design view, highlight your combo, invoke the property sheet, and click the event tab...the after update event is the second event on the property sheet.
2. Me is correct as long as your combo, your textbox and after update procedure are firing from your main form, and not a subform.
3. combo33 sounds right if you are using the name generated by Access, but you should rename it in accordance with naming conventions to make it clearer to understand in code ....for example, cboAction.
4. if the name of your textbox that you want updated from your selection in the combobox is not Action, then you need to change to the actual name of your textbox.

[howarthe]

I rebuilt the combo box and named it as you suggested and it's working, but there's another problem.

The form is built on a query that lists all the products for which the action was quarantine. I want to use the combo box to update the action to recall or release or correction. I can do that now.

The odd thing is that although the correct products are appearing in the form, they appear to have a null value in the action field. They really do have quarantine in that field, but nothing is dispayed in that field.

Maybe its because I'm not really showing that field in my form. I'm actually showing the combo box. That would make some kind of sense. The database will still work properly the way it is, its just a little disconcerting for users who think they are opening a form with a list of products in quarantine and then the form appears to contain a list of products with no actions.

I really appriciate all your help, do you have a suggestion for this problem?

[puppydogbuddy]

I rebuilt the combo box and named it as you suggested and it's working, but there's another problem.

The form is built on a query that lists all the products for which the action was quarantine. I want to use the combo box to update the action to recall or release or correction. I can do that now.

The odd thing is that although the correct products are appearing in the form, they appear to have a null value in the action field. They really do have quarantine in that field, but nothing is dispayed in that field.

Maybe its because I'm not really showing that field in my form. I'm actually showing the combo box. That would make some kind of sense. The database will still work properly the way it is, its just a little disconcerting for users who think they are opening a form with a list of products in quarantine and then the form appears to contain a list of products with no actions.

I really appriciate all your help, do you have a suggestion for this problem?

I thought that the textbox on your form was where the Action selected in your combobox was being displayed as a result of the AfterUpdate event code I gave you. So I don't understand what you mean by this:
<<<<Maybe its because I'm not really showing that field in my form. I'm actually showing the combo box. >>>
1. Is the textbox visible?
2. Is the column width of the two columns in the combobox set properly. The column width of the action column of the combobox should be set wide enough so that it can be seen in the combobox.
3. Is the Action column of the combo box the second column from the left, which is column #1 (column numbers start with 0)
4. Why are you binding the form to a query that limits the results only to quarantined items...is that what you want? If you want to see all products, quarantined or not... your query record source for your would look something like this (keep in mind that I don't know your actual field names, so I am using illustrative names:

Select Product, Action From tblProduct

Please clarify. Thanks.