Using a combo box to display multiple phone numbers

[BFish]

I thought I had my db fully functional until...

I'm using a phone number field (indexed, no duplicates) to qualify every
business in the db as unique. So upon a new entry added to the close to 6,
000 existing records I can eliminate duplicates due to mispellings of
business name.

Works fine until a business with multiply phone lines will give us a phone
number other than the primary (direct lines, 800 #'s ect.).

I am displaying business information in two different forms one in single
form and another in datasheet. I have split the [PhoneNumber] field off of
the main table (tblBusinessInformation) to its own table (tblPhoneInfo) with
foreign key of tblBusinessInformationID from the main table to store the
multiply phone numbers. In the datasheet view form (frmBusinessLookup) each
business with muptiple phone numbers will display more the one time I would
like to have only one instance displayed. I thought a combo box would be
the way to accomplish this but have struggled in both diplaying the the
business entry only one time and having the combo box using only that
businesses phone numbers to list in the box.

This is the combo box subquery:

SELECT tblPhoneNumber.BusinessInformationID, tblPhoneNumber.PHONE,
tblPhoneNumber.DateEntered FROM tblPhoneNumber WHERE (((tblPhoneNumber.
BusinessInformationID)=[Form]![frmBusinessLookup]![qselBusinessNameByPhone].
[BusinessInformationID])) ORDER BY tblPhoneNumber.DateEntered;

This unforunately is not working the way I intended:

First in having an incorrect reference I believe to the form control (when I
view the form I'm asked for the [Form]![frmBusinessLookup]!
[qselBusinessNameByPhone].[BusinessInformationID] value.

Second is still the muliply listing of each business, one time for each phone
number.

In what way can I accomplish my goal

Thanks for any help,

Bill Fischer

 

[Little]

Forgive me if i've got the wrong end of the stick, I'm new to trying to
help people

I believe that you will need ' ' around the form part in the WHERE
clause, since its a value. Also theres two sets of brackets around the
where clause? I'm not sure why you're using the dot operator for the
where clause value? that could be it. presuming that
qselBusinessNameByPhone is the control

SELECT tblPhoneNumber.BusinessInformationID, tblPhoneNumber.PHONE,
tblPhoneNumber.DateEntered
FROM tblPhoneNumber
WHERE
BusinessInformationID='[Form]![frmBusinessLookup]![qselBusinessNameByPhone]'
ORDER BY tblPhoneNumber.DateEntered;


Sorry for the lack of help, i tried!

 

[BFish via AccessMonster.com]

Thanks for the try.

I have been working on this since I posted and have been able to go a little
further down the road.

This subquery SQL has some additional formatting, but basically I correced
the reference to the forms control and is now partially working;

SELECT tblPhoneNumber.BusinessInformationID, Format(tblPhoneNumber.PHONE,"
(@@@) @@@-@@@@") AS Phone, tblPhoneNumber.DateEntered
FROM tblPhoneNumber
WHERE (((tblPhoneNumber.BusinessInformationID)=[Forms]![frmBusinessLookup]!
[BusinessInformationID]))
ORDER BY tblPhoneNumber.DateEntered;

It now will list only the related phone numbers for the business, but since
this form is datasheet view the query will only give me the phone numbers for
the first record in the recordset. All other phone fields are blank and when
using the dropdown for the combo box it is listing the first records phone
numbers for every other record.

Can anyone tell me how to apply the criteria for the subquery so that only
phone numbers for that record are available in the drop down for every record
in the recordset of this datasheet form.

Thanks,

Bill

Forgive me if i've got the wrong end of the stick, I'm new to trying to
help people

I believe that you will need ' ' around the form part in the WHERE
clause, since its a value. Also theres two sets of brackets around the
where clause? I'm not sure why you're using the dot operator for the
where clause value? that could be it. presuming that
qselBusinessNameByPhone is the control

SELECT tblPhoneNumber.BusinessInformationID, tblPhoneNumber.PHONE,
tblPhoneNumber.DateEntered
FROM tblPhoneNumber
WHERE
BusinessInformationID='[Form]![frmBusinessLookup]![qselBusinessNameByPhone]'
ORDER BY tblPhoneNumber.DateEntered;

Sorry for the lack of help, i tried!

 

[Little]

Was the dot operater then

You will need to add an event to the navigation buttons for re-querying
the drop down box.
On the event of the navigation buttons changing, add the VBA code
controlname.rowsource = controlname.rowsource
that will requery each time you change =)

As for the rest of the telephone numbers being blank, what do you mean?
They should be blank but are showing the first? Or they are blank but
should be showing something else?

 

[BFish via AccessMonster.com]

Again this is a datasheet view form so there are navigation buttons but
obviously users probably will not be using them.

As far as phone numbers being blank, each record has no data in the phone
field which is required and indexed with no duplicates. So they are blank
and should be showing the at least one unique phone number for that record
(business).

Thanks,

Bill

 

[BFish via AccessMonster.com]

Thanks Little.

I have all but the blank fields for phone number working now. When either
clicking the phone field or the record line the form will display the phone
number. I would like to be able to display the earliest entered phone,
specific to that record, for all records. Is this possible?

I am almost to the point of trying several phone fields for muliply numbers
in the table due to the many diferent possible numbers that could be for any
listed business, but really do not like the idea of over inflating my db
unnecessaryily.

Additionaly if the combo box is possible, I would like to highlight the phone
field with a conditional color format if there would be more than one phone
number in the drop down. I thought a DLookup >1 would be the answer but
either that is not true or I am incorrectly writing the syntax.

Any suggestions on either problem?

Thanks a bunch!!

Bill

Again this is a datasheet view form so there are navigation buttons but
obviously users probably will not be using them.

As far as phone numbers being blank, each record has no data in the phone
field which is required and indexed with no duplicates. So they are blank
and should be showing the at least one unique phone number for that record
(business).

Thanks,

Bill

Was the dot operater then

[quoted text clipped - 7 lines]

They should be blank but are showing the first? Or they are blank but
should be showing something else?