Updating a DLookup on a form

[gweasel]

Not sure what a good subject would have been for this.

I've got a database that was set up some time ago by a former
employee. In it is a logbook form that pulls from a query (which
combines fields from 3 tables).

The first control on the form that is editable by the user is a combo
box for them to select a company name. Once they select a company
name, fields specific to that company fill in (contact name, delivery
address, phone number, email, fax) automatically by way of a macro and
invisible DLookup controls on the form.

The change that has been requested is to make it automatically fill in
multiple options for some of those controls. Specifically the contact
name and phone number. There are many companies we work with who have
multiple departments that we work with, and therefore, multiple
contacts to deal with. Rather than having the same company entered in
multiple times, it is currently entered once with options for
"Secondary Contact" - but that is not called on at all when the
company name is selected.

I know DLookup will only return a single value (or that is my
understanding). Is there a way to make the contact info automatically
fill in as a combo box where multiple users are selectable?
Alternative ideas, explanations and / or samples would be
appreciated.

Thanks,
RJB

 

[Al Campagna]

G,
Sounds like you need to "drill down" a bit further than just Company. I'd try a combo
(cboCompany), for CompanyName, and any "Company" associated data (CompanyAddress,
CompanyMainPhone, CompanyZip, etc...). Then, using that cboCompany value, "filter" a
second combo box (cboContact) to display all Contacts associated with just that one
Company. Then we'll update your form fields with just the selected Company and Contact
combos. (no Dlookups needed)

Now, in your table behind the form, it is not necessary to "capture" all the Company
info (and all the Contact info)... just the CompanyID and just the ContactID. All the
other ancillary associated data can be "displayed" on the form. If you have the CompanyID
saved, and the ContactID saved, you can always re-associate the ancillary data via those
ID values from the combos.
Example... why "capture" the CompanyAddress? If I have the Company ID, I can always
relate the correct address for that Company in any subsequent forms, queries, or reports.

OK, try this example using just 3 columns...
cboCompany combo columns...
CompanyID CompanyName CompanyAddress
123 ZYR Co Inc. 101 Main St. <<(this selected)
412 ABC Industries 75 North Ave
741 HHH Products 12 Davis Ave
... etc ...
cboCompany setup...
ControlSource = CompanyID
NoOfCols = 3
ColumnWidths = 0" ; 1.5" ; 2"
ListWidth = 3.5"
LimitToList = Yes
What this does is allow the user to sleect a Company by Name, and displays that Name,
BUT... stores the Company ID (Hidden) in the CompanyID field of your form table.
Now, an unbound calculated text control named CompanyAddress with a ControlSource of...
= cboCompany.Column(2) (combo cols numbered 0,1,2,3,etc)
would "display"... "101 Main St"
If cboCompany had a fourth column for City then a calculated control called City with...
=cboCompany.Column(3)
would "display"... "Boston"
Use this technique for all your Company fields you want "display."

Using that same concept for cboContact (all CompanyID = 123 Contacts)
ContactID ContactName ContactPhone ContactFax
413 Jim Smith 1-666-666-6666 1-666-666-1234 (<selected)
715 Mary Jones 1-666-666-6667 1-666-666-6668
= cboContact.Column(3) displays "1-666-666-6666"
=cboContact.Column(4) displays "1-666-666-1234
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."