Combo Box Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Telobamipada - 25 Jul 2007 16:16 GMT

I have a Database (Access 2003) with a form containing 2 combo boxes one
dependent on the other. The 1st (DME) using Table (DMEBusiness) and the other
(Phone) using Table (DMEPhone). When a Business is selected in my (DME) combo
box I would like for the (Phone) combo box to display phone numbers for that
business. Presently, it will display only one phone number for each business
with no others available in the combo box. I am using the code below which is
not working for me. Could someone help me with this please?

Private Sub DME_AfterUpdate()
Me.Phone.RowSource = "SELECT DMEPhone.[Phone] FROM" & _
" DMEPhone WHERE BusinessID = " & Me.DME & _
" ORDER BY Phone"
Me.Phone = Me.Phone.ItemData(0)
End Sub

The 2 Tables are set up as such:

DMEBusiness
BusinessID = AutoNumber
    Business = Text

DMEPhone
    PhoneID = AutoNumber
    Phone = Text
    BusinessID = Number (Matches Business ID field in DMEBusiness)
    Lookup settings: Display Control = Combo Box
            Row Source Type = Table/Query
Row Source = SELECT DISTINCTROW [BusinessID], [Business] FROM
DMEBusiness ORDER BY [Business];

Signature

If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

Reply to this Message

George Nicholson - 25 Jul 2007 16:48 GMT

As far as I can tell, the code you provide should populate the Combo box
with all phone numbers associated with a given BusinessID. If the controls
in question are bound, you'd probably need to have similar code in the
Form_Current event (or call DME_AfterUpdate from there) as well. That way if
you are simply scrolling through records, the combo will update when you
haven't touched DME.

I'm not sure the following is necessary, but something about your wording
makes me want to add the following for clarity: a combo box at rest will
*never* show more than one value (no matter how tall you make it). Only in
its "drop down" state will it show multiple values from which the user can
make a single selection. If you need multiple values to display at all
times, you need a list box.

HTH,

>I have a Database (Access 2003) with a form containing 2 combo boxes one
> dependent on the other. The 1st (DME) using Table (DMEBusiness) and the
[quoted text clipped - 30 lines]
> Row Source = SELECT DISTINCTROW [BusinessID], [Business] FROM
> DMEBusiness ORDER BY [Business];

Reply to this Message

Telobamipada - 25 Jul 2007 17:02 GMT

Thank you for the quick reply George!

My comboxes are unbound and yes I'm referring to the drop down state. The
funny thing is (Maybe this could provide a clue), I have a single business
setup with 2 phone numbers for testing purposes and all others with one, when
I use the drop down I see 2 spaces in the drop down state for that business
and all others show a single space when in the drop down state... I'm just at
a loss!!

Signature

If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

> As far as I can tell, the code you provide should populate the Combo box
> with all phone numbers associated with a given BusinessID. If the controls
[quoted text clipped - 46 lines]
> > Row Source = SELECT DISTINCTROW [BusinessID], [Business] FROM
> > DMEBusiness ORDER BY [Business];

Reply to this Message

Telobamipada - 25 Jul 2007 18:52 GMT

Thanks for the help George, I figured it out... I had a field setting wrong
on the combox box properties.

Signature

If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

> Thank you for the quick reply George!
>
[quoted text clipped - 55 lines]
> > > Row Source = SELECT DISTINCTROW [BusinessID], [Business] FROM
> > > DMEBusiness ORDER BY [Business];

Reply to this Message