Combo box not displaying data (2000(SP3))

Topic

New Reply

I have a pair of cascading combo boxes. The selection in combo 1 filters the choices available in box two. The choices are stored in lookup tables that have a parent-child relationship. The data is then stored as a number field in the record. All of this works fine for entering data, but when you come back to a record after closing the database or scroll back up after entering several rows of data, combo box 2 shows up blanks. If you check the info in the tables it is there, it just isn’t showing up on the subform.

I am assuming this is some sort of requerying/repainting problem for the subform but am not sure where to put code and what type of code to put in there so that this data is always visible when the record is visible.

Reply | Quote

Replies

Is your subform continuous (or a datasheet)? There is only one instance of a combo box on a continuous/datasheet form, so if the row source of the combo box changes when you move from record to record, it changes for all records. As a consequence, the value of the bound field may not be in the row source of the combo box for other records than the active record. A workaround is to put a transparent text box bound to the same field on top of the combo box.

If this does not match your situation, please post back.

Reply | Quote

This started off as a problem with a continuous subform so I changed it to a datasheet and thought the problem was fixed. I have subsequently found out, obviously that it wasn’t. There is no problem changing it back to continuous since clearly the datasheet isn’t the answer!

I have seen other posts with the transparent textbox. Is that the best solution? No nice little line of code I can enter somewhere?

Reply | Quote

The problem, as I indicated, is that although a continuous form / datasheet form display multiple records, there is actually only one set of controls. If you change the Row Source of a combo box, the change affects ALL displayed records, not just the current record. Let’s take a simple example:
The list part of the combo box contains 1, 2, 3 and 4. The user selects 4, so the text box part of the combo box displays 4.
The user moves to the next record. The combo box is requeried and the list now contains 5, 6, 7 and 38. This list applies both to the new current record AND to the previous record. The value 4 is no longer part of the list, so the text box part of the combo box is blanked out in that record – it can only display values from the list.
So no, there is no code solution. Either place a transparent text box bound to the same field on top of the combo box, or make the subform a single form instead of a continuous/datasheet form.

Reply | Quote

I have got most of the way there with the transparent text box over the combo box. Since the value that gets stored when the item is selected from the combo box is a number, I am storing that in a hidden box, Text10 then using DLookup in another textbox (the transparent one over the combo box) to return a text value for display. The DLookup is below.
=DLookUp(“[ServiceAreaDetails]”,”tblLookupISAPServiceAreaDetails”,”[ServiceAreaDetailsID] = ” & [Forms]![frmISAPSubMeetingsDetails]![Text10])

I am a little stumped on the syntax. When I use the above, it displays the correct information if I open the subform frmISAPSubMeetingsDetails on its own, but I get the #Name? error when I open the main form. In addition, if I change the last bit to just Me!Text10 then I get the #Name? error even on the subform. Any suggestions? This is a bit of a rats nest of forms: frmISAPSubMeetingsDetails is on frmISAPSubMeetings which is in turn on frmISAPSub

Reply | Quote

I seem to have solved it. I changed the syntax to the following and it seems to be working now. Is there a reason why? Perhaps the “Me” language is reserved for VBA?

=DLookUp(“[ServiceAreaDetails]”,”tblLookupISAPServiceAreaDetails”,”[ServiceAreaDetailsID] = ” & [Text10])

Reply | Quote

[Forms]![frmISAPSubMeetingsDetails]![Text10] didn’t work in the subform because a subform is not a member of the Forms collection, only main forms are.
Me is, as you suspected, only valid in VBA.

Reply | Quote

What you have seems to work, but Dlookup is slow.

Alternatively you can just join tblLookupI to the query behind the form, and get ServiceAreaDetails directly from the query.

Reply | Quote

I tried that first and ended up with a recordset that was not updatable. For my purposes, DLookup should be sufficient.

Reply | Quote