Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box not displaying data (2000(SP3))

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo box not displaying data (2000(SP3))

    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.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box not displaying data (2000(SP3))

    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 <img src=/S/drop.gif border=0 alt=drop width=23 height=23> 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? <img src=/S/please.gif border=0 alt=please width=31 height=23>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo box not displaying data (2000(SP3))

    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.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box not displaying data (2000(SP3))

    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

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box not displaying data (2000(SP3))

    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])

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo box not displaying data (2000(SP3))

    [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.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combo box not displaying data (2000(SP3))

    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.
    Regards
    John



  9. #9
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box not displaying data (2000(SP3))

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •