Combo List column not populating when chosen (XP SP2)

Topic

New Reply

I have a table (tbl_target) with two columns, Target & Product, both primary required keys. (Products are on another table and are validated in tbl_target) I have a form based on the main table (FAQ) which has both Target and Product. When the user moves to the Target field I want one of two things to happen. First choice = since the form already knows the product, have the combo box show only the appropriate targets for the product chosen based on the combinations in tbl_target. I couldn’t figure out how to do that as I’m quite the newbie. Second choice = change the default combo box to display both fields from tbl_target so the user can choose the correct one. This works as long as Target is the first column, but I want Product to be so they can quickly find just the appropriate targets for the product. No matter what “Bound Column” property value I choose (0,1,2) it returns the first column from the query which populates the combo box.

So, two questions…
1) What am I doing wrong?
2) How can I make what I consider the better choice, choice 1, work?

Reply | Quote

Replies

You’ll have to write a bit of code for choice 1.
Open the form in design view.
Select View/Code (or click the Code button on the toolbar).
Enter the following code; I have used cboTarget as the name of the combo box bound to Target and txtProduct as the name of the text box bound to Product; substitute the names you have used on your form. I have assumed that Product is a text field.

Private Sub UpdateCombo()
cboTarget.RowSource = “SELECT * FROM tbl_Target WHERE Product = ” & Chr(34) & Me.txtProduct & Chr(34)
End Sub

If Product is numeric, you can omit & Chr(34) twice (they represent quotes around the value of txtProduct; they are not needed for a numeric value).

For the second choice, you would have to create a query based on tbl_Target that selects Product as first field and Target as second field. and order it by Product. Use this query as row source of the combo box.

Switch back to Access.
Click in the form window, but outside the form itself (you may have to scroll or enlarge the window to do that); this selects the Form object.
Activate the Properties window (View/Properties).
Click the Events tab.
Click in the On Current event.
Select Event Procedure from the dropdown list.
Click the Build button (the button with … to the right of the event.
Type UpdateCombo, so that you see

Private Sub Form_Current()
UpdateCombo
End Sub

Switch back to Access.
Select the text box bound to the Product field.
Click in the AfterUpdate event of this text box (in the Properties window)
Select Event Procedure from the dropdown list.
Click the Build button (the button with … to the right of the event.
Type UpdateCombo, so that you see

Private Sub txtProduct_AfterUpdate()
UpdateCombo
End Sub

Switch back to Access.
Close and save the form.

Reply | Quote

Thanks very much! This works exactly as I’d hoped. The detailed instructions really helped since I know nothing

Is Me. the value in memory for a column? What does the Private Sub Form_Current() do?

Reply | Quote

You can refer to controls on the form (or report) that is running the code in several ways:

ControlName
Me!ControlName
Me.ControlName

Me returns a form object that represents the form running the code. The advantage of using Me. is that when you type the period, Access will display a list of all properties and methods of the form, including all controls.

The OnCurrent event occurs each time another record gets the focus, that is, if you move to another existing record or create a new record. The Form_Current() procedure is the event handler for this form – it gets executed whenever another record gets the focus. This ensures that the row source of the combo box is updated when you navigate through records.

Reply | Quote

Cool. Thanks. This is fun!

Bryan

Reply | Quote

Bryan,

You can do the first by adding one line of codes. As you said, the form knows the product, so create a module for OnCurrent and write the following:

Private Sub Form_Current()

comboTarget.RowSource = “SELECT Target, Product FROM tbl_target WHERE [Product] = ‘” & Me.Product & “‘”

End Sub

Be careful about WHERE clause. I am assuming Product is a string, so the value, Me.Product, should be wrapped by single quotation marks (which may not be clear on this page). If Product is a number, the line should be

comboTarget.RowSource = “SELECT Target, Product FROM tbl_target WHERE [Product] = ” & Me.Product

About your 2nd question, if you just want to show Product in the combo box, go to Column Widths and set it to

0″; 2″

so it will show Product instead of Target, but the value of the combo is still Target (first column).

Shlomo

Reply | Quote

Thanks for the help. It worked.

Reply | Quote