Creating a lookup (1 Viewer)

Good afternoon,

I want to create a text box within a form that automatically populates a contact number based on a selection from a combo box, also in the same form.

For example, I have a Bidders Table (tblTenders), this form includes information regarding the Tendor like the company name and a main contact within that company and a phone number for that contact.

I've created a separate table for all the contacts called tblContacts. This table holds all the contact information for each contact. I have a simple form called frmTenders that asks the user to input the Customer (which is the company who are bidding) and the Main Contact, which is a combo box to select the main contact from tblContacts. Below that combo box is a text box called 'Contact Number' - I want this box to display the contact number for the main contact automatically when a main contact is selected from the combo box.

The contact number text box isn't storing that information in any tables etc. It's just for viewing purposes when we need to make a call to that specific tender.

Could someone offer some advice?

Many thanks,

Here is an Example, of course change to suit your needs.


HTH

No need for a DLookup.

Put the contact number in the query of your main contact combo. Set the column count to include this, and then hide the column with the contact number.

In your textbox, set it's ControlSource to the hidden column of the combo

ie.

if you bring through the telephone number in your combo rowsource - something like

set the following
number of columns: 3
bound column:1
column widths: 0;2;0


then in the unbound textbox controlsource put

=combo.column(2)

note columns start from 0, whereas bound column starts from the '1st column (perverse but true)

Hi,

Thank you for your responses, the solution worked for me.

____________________________________________________________

I now have a separate issue which is related to the same combo box as mentioned in my original thread.

The text box which I created to show the phone number automatically works fine, so I created another text box to do the same with the email address. Again, both work fine.

What I'm trying to do now, is to create a way to edit the details, if they are incorrect or are blank and need adding. So, I've created a command button called "cmdEditContact" that when clicked opens the "frmAddContact" in edit mode to allow changes or additions. Due to the large number of contacts, I needed a way to filter out the specific contact that is currently shown in my "frmTendersSubform". I've done this using a macro on click...

I've added a text box to my form called 'txtContactID' which uses the same control source as above...


The macro then uses the WhereCondition expression to locate the relevant contact. The expression I've used is...


When the button is clicked, everything works fine and shows the correct information and allows additions and edit. All fine so far! However this only works if I have the form open itself. As the form is used as a sub-form, its used only via the 'frmProjectDetails' and when the command button is clicked, it gives me a Parameter box which says:

"Forms!frmTendersSubform!txtContactID"

I'm puzzled as to why this will work if I open the form itself and click the command button, however when clicked through another form, as is intended, it gives me the 'Enter Parameter Value' - If I enter the value, it works, however I don't want to have to enter the value everytime. I want it to work automatically, as it does in the form.

Any ideas? :banghead:

Many thanks,
Chris

Hi,

I'm really annoyed that Ijust typed my last post then solved it (sod's law)

I was missing the frmProjectDetails from the start of the string, so it was working when I used it in the form but not finding the contact when I used it through frmProjectDetails.

the expression now reads...



This now works as intended. Apologies for boring you with the long-winded post.

Thanks for your support.

Chris.