Populating a text or Combo Box

[Fuego]

I'm currently working on a db that will allow a user to track a project with 3 different contractors. These contractors and their personal info (ie First name,Last name, Company etc..) are located in a table called Contacts.

The Form I'm working with is a projects for and stores data into the projects table.

My users want to be able to goto a specific project and be able to look at/ and enter , the 3 contacts without having to goto a separate form. My solution to this has been to create unbound combo boxes run sql statements on the first, Last, and Company names. I have done this so that the user can locate a person based on name without having to remember a specific contact id number which is my linking field.

So when the user enters the first name field they can type or use a pulldown to identify the users first name. Next they tab into the last name field which runs a query based on the first name field to narrow the search. Next they tab to the company name field which runs a query based on both first and last name to identify the appropriate people and their associated companies.

My problems start now!!! I need to, from the fields provided, identify the contacts id and store it in the Contact Id field. I also need to code so that when opening a particular record, it shows the appropriate Contacts.

Can anyone help. Also does anyone know how to set the value of a combo box so that the first field in the list or in my case SQL request is shown as a default value--instead of a blank field?

 

[FoxProProgrammer]

I'm not sure what kind of help you are looking for on the Contact ID field. Please post a more specific question and I'll try to help out.

To set the value of a combo box to the first value, do this:

cmboname.Value = cmbo.ItemData(0)

One note of caution: There's no guarantee that users will tab into fields in the order that you want, or perceive to be the most logical. You will need to allow for this is your code.

dz

 

[FoxProProgrammer]

I'm not sure what kind of help you are looking for on the Contact ID field. Please post a more specific question and I'll try to help out.

To set the value of a combo box to the first value, do this:

cmboname.Value = cmboname.ItemData(0)

One note of caution: There's no guarantee that users will tab into fields in the order that you want, or perceive to be the most logical. You will need to allow for this is your code.

dz

 

[Fuego]

Let see how to be specific---hmm

What I'm trying to do is to avoid duplicating information. My users need to be able to identify specific contacts associated with with a project. For now let's just suppose one contact. The users dont want to have to remember a Id number, but do want to be able to start typing a contacts name and have it autofilled by the db. So as I have it now.

My user has 3 unbound combo boxes and the bound contact's id field. I have it so that when someone starts to enter the firstname of the contact it will autofill for them, or they can use the pull down list. Then next field runs a query which filters the contacts table based on the first name that was entered and will autofill when the person starts to type the last name of the user or they can once again use the pulldown list. The company field is just another step on this "staircase" effect.

Once the user has entered this info I need to : 1. identify the contact id and store it in the bound field (which I think i can now do with your prior response) 2. when a specific project record is called up to be looked at I will need it to automatically look at the contact Id number and fill the unbound fields with the appropriate data.

So let me see how sounds to you. in the Afterupdate section of [company_name] I specify

contactid.Value = contactid.ItemData(0)
me.refresh

{this sets my bound field to the contact_id number that i need}

Now I need to on opening the Form, have the form look at the Contact ID and populate the unbound fields (FIRST,LAST, and Company) with the info Associated to the Specified Contact Id number.

Would I do this in the form load event?

 

[FoxProProgrammer]

Ok, I have a better idea what you want to do. Here's a description of how I see it. Please correct me where I'm wrong.

Your table has the following fields.
contactid
firstname
lastname
company
several other fields that you didn't specify.

Your form has three unbound combo boxes.

cbofirstname
cbolastname
cbocompany

Your form has one bound text box
contactid

Your form has several other unbound text boxes

The user selects a first name from cbofirstname, either by typing the first few characters until they find it, or by using the scroll bars on the combo box.

The Rowsource of cbolastname contains a Where clause that limits its contents to those records where the first name matches what was selected in cbofirstname. The user selects a last name from cbolastname similar to how they selected the first name from cbofirstname.

The Rowsource of cbocompany contains a Where clause that limits its contents to those records where the first name and last name match what was selected in the previous two combo boxes. The user selects a company name from cbocompany.

Now, what you want to do is find the contactid in the table that matches the firstname, lastname, and company selected. Is this correct?

Here's a potential problem: Two or more people that work for the same company could have the same name. How would you differentiate them the way that you've designed this? There is probably a better way to design this, but I don't understand what is driving the requirement for three combo boxes.

I'm also not clear how the project record relates to the contact information.

Back to initializing the combo boxes. From your first post in this thread, I thought that you wanted to initialize the combo boxes to the first record instead of blank lines. If that's the case, you need to set the value of cbofirstname in the Form Open event, and requery the other two combo boxes.

You wrote:

<So let me see how sounds to you. in the Afterupdate section of [company_name] I specify

contactid.Value = contactid.ItemData(0)>

No, this won't work. The code example that I gave you set the initial value of a combo box, not a text box. Isn't contactid a text box? If so, it doesn't have an ItemData property.


Let's start here for now.



dz

 

[kelfuego]

Yes you have a good idea of what i'm trying to do. actually though, the form is bound to the projects table.

the projects table has the Foreign Key of ContactID in it.

I simply want to be able to show on the projects form specific contact information.

I had originally intended to &quot;pull&quot; the contact info across in a subform based on the ContactID. This organization has had issues with data integrity in the past and I wanted to &quot;force&quot; the users to physically goto a contacts form to update information and work with the contacts(including indentify the ContactId to place in projects form). Though this is a ticky tac way to handle things, my partner and I spent hours cleaning up their data.

The idea of having to know the ContactID number was too much for them to fathom though.

So now, I need to display the contact info of 3 separate contacts on the projects form and set it up so that they can start 'typing' and have it pull up a name.

My goal has been to create the unbound fields and allow them their entry/search ability through them, and then tie them to the contacts table via the contactid field. I changed this field to a combobox in attempts to do this through code---and though I found success (meaning it populated the way I wanted it to)I was still getting a macro group error.

Your suggestion worked well for me via a save button:
contactID.value = cmboItemData(0)
me.refresh

Now the problem I have is making it so that when a record is opened, it displays the proper information in the unbound fields based on the store contact Id.

2nd I need to figure out a way to update the contact table If a user tries to enter an new contact through this form or update one.

I prefer not to have this option, but hey the customer is always right.

By the way I appreciate all your help.