Combo Box & On Event Requery's (1 Viewer)

mab9 · Nov 8, 2007

In my form, I have a combo box tied to a field in my table. The combo box is set to allow certain values based on the results of a query. The values allow for each record are different depending on another field in the table (let's call this the List Key). In order for this to work properly, I've set the form's On Current event to cb_dbkey.requery. What's happening now is the combo box shows up as blank unless I'm on a row with a similiar list key. For example:

List Key = A, cb_dbkey = 1
List Key = A, cb_dbkey = 2
List Key = B, cb_dbkey = 3
List Key = B, cb_dbkey = 4

If my form had these four rows in it, when a record with a key of A is selected, the 1 and 2 become visible, but the 3 & 4 in the bottom records goes blank. Then vice versa when a "B" row is selected. Any clues whats causing this? This used to be working properly but I had to change what field the combo box was actually capturing & now this is driving me up the wall.

KenHigg · Nov 8, 2007

Can you just set the combobox 'row source type' to 'value list' and reset it with the 'row source' property in the on current event?

ken

mab9 · Nov 8, 2007

KenHigg said:
Can you just set the combobox 'row source type' to 'value list' and reset it with the 'row source' property in the on current event?

Unfortunately I don't think so. The list for the combo box is bouncing off a table of 400+ records. And the list changes often so I want to keep as it a live query so its always right.

KenHigg · Nov 8, 2007

So your 'row source type' is a query. Where is the parameter in the query that limits the rows?

???
ken

mab9 · Nov 8, 2007

The parameter in the query essentially says:

ListKey = Form(List Key)

So that when On Current hits & refreshes the query, the combo box selection is always right. I'm just stumped on why the value is vanishing when a record with a different list key is selected.

The one change I did make that might be doing it is I had to change the bound column from 1 (the value I want displayed on the form) to 3 (the PK in the other table).

KenHigg · Nov 8, 2007

I guess you could reset the entire row source every time the on current fires.

me.myCombo.RowSource = "myQueryName"

???
ken

mab9 · Nov 8, 2007

By tinking, I've found the problem & I had a hunch this was it. The query being to populate the combo box is made up of four fields:

- POG_ID
- POG_Name
- DBKey (PK)
- List Key

The old way I was doing it bound the table back to column 1 (POG_ID). Due to some design changes, it needs to be bound against column 3 (DBKey), but I only want to display the ID in the combo box after selection is made.

I've found that if I make it the DBKey the first column, the data shows up in every row properly. But with it in column 3, the ID only shows up with based on the list key thing.

Any clue on how to make the name "stick" like it does is the DBKey is the first column?

KenHigg · Nov 8, 2007

The only two thing relevant here are the bound column and the width of the columns. By changing these you should be able to get whatever need...

???
ken

mab9 · Nov 8, 2007

That's what I'm thinking, but here's the curious part. The query has

- DBKey (PK)
- POG_ID
- POG_Name
- List Key (not shown)

When I do:
- bound column = 1
- width = 1";1";4"
I see the DBKey repeating properly on every row on the row.

When I do (and this how I want it to be)
- bound column = 1
- width = 0";1";4"
I see the POG_ID in rows that match the List Key on the currently selected record. I can't figure out why it won't repeat like the DBKey method does.

The problem here is the users of this form only work off the POG_ID. If they see the DBKey at all, they'll just get really confused.

KenHigg · Nov 9, 2007

You kind of lost me - Any chance you could post a sample db?

mab9 · Nov 9, 2007

Attached is a very stripped down version of the file. You'll notice there are two forms in there:

frm_wrong: this form replicates the problem I'm trying to overcome. In the third column is a ListKey. As you click on records with different keys, the fourth column will update & only show those names.

frm_almost_right: this form replicates it as almost right. The actual field being captured by the combo box is not the name, but the "dbkey." You'll notice that when it's told to show the dbkey instead of the name, it properly shows up in every row, no matter which record is currently selected. I'd like to have this working, but displaying the name instead of the dbkey.

KenHigg · Nov 9, 2007

What the heck are you trying model. I can see you have buildings. A building can have multiple locations (I assume locations within the building?). What I don't understand is how a fixture (sounds like a physical thing, like a light fixture) can be located in multiple locations. Unless when you say 'fixture' you're speaking of a 'type' of fixture, like 'small red light fixture'. So you could have 'small red fixtures' in multiple build locations.

???
ken

mab9 · Nov 9, 2007

It's more of the 2nd, the type of fixtures within a building. It's giving a place to select what goes into each of the fixtures but restricted that list due to its type.

KenHigg · Nov 9, 2007

Ah... So we have fixture type a always uses a light bulb type 1, etc.?

mab9 · Nov 9, 2007

Well sort of. In the sample you can see the two fixture types:
- E1
- G1

Of the 73 selectable items (from qry_maxkey2), it breaks out as:
- E1 = 24 possible choices
- G1 = 49 possible choices

Depending on the type of each record, the combo box list changes to the appropriate group. Each choice is defined (primary key) by the field MaxofDBKey (from the combo box qry's) and is sourced back into tbl_locations.dbkey.

When the combo box is set to display the dbkey (as is done in the form frm_almost_right), you'll see the information displaying properly in each record.

When the combo box is set to display the name (which is what i want & is done in the form frm_wrong), you'll see it only will show the names that match the key type of the selected you are selected on.

I'm stumped on how to make the name repeat like the dbkey does.

KenHigg · Nov 9, 2007

You've lost me again. I personally think you have data model issues. If you want to pursue it from that angle let me know. Otherwise maybe someone else can jump in...

ken

mab9 · Nov 9, 2007

I don't believe that it's the model. I think it really just has to do with the visible field displayed in the combo box vs the field captured and the fact that the combo box's row source is potentially different on each record.

Combo Box & On Event Requery's (1 Viewer)

mab9

Registered User.

KenHigg

Registered User

mab9

Registered User.

KenHigg

Registered User

mab9

Registered User.

KenHigg

Registered User

mab9

Registered User.

KenHigg

Registered User

mab9

Registered User.

KenHigg

Registered User

mab9

Registered User.

Attachments

KenHigg

Registered User

mab9

Registered User.

KenHigg

Registered User

mab9

Registered User.

KenHigg

Registered User

mab9

Registered User.

Similar threads

Users who are viewing this thread