Combo Box will only allow first option

[ImLost]

Hello -

I have two combo boxes on a form, with the second set to pull up different lists depending on what is entered in the first box. To set this up, in Access 2003, I used one table to set up the Agencies with an ID# and then used another table to use that unique ID to indicate specific events, each with its own ID #. Then through a few queries and some VBA on the after update option on the first box (see below), it was working just fine. In theory...

Private Sub Type_AfterUpdate()
Me.Outcome = Null
Me.Outcome.Requery
End Sub

In practice, the second box will only allow me to select the first option. I can see that there are other options and click on them or type them in, but when I tab to the next field, it defaults back to the first option.

Please help if you can. You guys have been invaluable over the past few years and I only have 6 days left at this job to get this thing working. There's a good chance it will follow me to my new job, but it's important to get a few of these smaller bugs fixed before I go. Thanks for any input you can offer.

 

[vbaInet]

I'm guessing your first combo box is dependent on your second and vice versa? If that's the case it's going in a loop.

Let's see how you implemented this.

 

[ImLost]

Okay - hopefully I give you what you need.

There are two tables -

1. Contact - has two fields - a primary key set to autonumber for the ID# (Type ID) and a text field for the contact type.

2. Outcome - has three fields - a primary key set to autonumber for the ID# (Outcome ID), a number field for the Type ID (manually entered), and a text field for the outcome type.

The Outcome table is the one the second box pulls from and has various outcomes assigned to the Contact based on the Contact's ID #.

The queries get a bit trickier.

One only use the Outcome table and uses all three fields. The first column contains the Outcome list. Second has the Type ID with the criteria set to match the Type ID on the subform. Third has the Outcome ID. The bound column is #2.

On the form, the Type field pulls a two column query where the first column = Type and the second column = Type ID (bound column). The Outcome field pulls from the query listed above.

The Type field's after update property is set to the VBA in my original post so that the Outcome field resets accordingly.

 

[ImLost]

Thank you. In working through it, I realized what was wrong. My query order was a bit off and the bound column should have been 1 not 2. Thanks for helping with the brain storm and saving my monitor from going through my window. ^_^

 

[vbaInet]

Edit: Glad you got it working

 

[ImLost]

Edit: Glad you got it working

Thanks. I tipped your scales. ^_-

 

[vbaInet]

Thanks. I tipped your scales. ^_-

You're welcome!!!