Combo Box Problem

[Guest]

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

 

[George Nicholson]

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,

 

[Guest]

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!!

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

 

[Guest]

Thanks for the help George, I figured it out... I had a field setting wrong
on the combox box properties.
--
If you can read this, thank a Teacher...
If your reading it in english, thank a Veteran!

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!!

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