Autofill fields keyed off of one field 11

[BLutgen]

I have a form where many of the fields are distributor information. I also have a table that is [Distributor Name] [Address] [Phone] [Fax] and [Contact]. These same fields are on my form. On the form I have a pull down combo box for the distributor name and would like the rest of the information to automatically fill in when I select the Distributor name. Thanks a ton

Brad

 

[scking]

Let me first say that I question why you would have two tables containing the same information. For instance, let's say that the address or phone of a distributor changed. With your current design you would have to change your source table and each and every occurence where that source information changes. It would be much better to only place the key of the value from the source table in the table. Then, when values change and you use a query as the source of a form everything will be current when the source table is changed.

To answer your original question. The ControlSource property for the combo box has a builder. When you click on the builder it will show the query design which is where you would select every field you want to use on the other form. Back in the properties you need to indicate the number of columns (columns), and the column widths (ColumnWidth). If you don't want the values displayed when the combo box is pressed then the column width is ;0". Finally, in the AfterUpdate event for the combo box merely assign the contents of the combo box columns to the controls on the form. Me.Address = Me.cboSource.Column(1)

Steve King Growth follows a healthy professional curiosity

 

[BLutgen]

I'm sorry, I guess I wasn't clear enough when writing my question. I have one table that is just distributor information such as [Distributor Name] [Address] [Phone] [Fax] and [Contact]. I then have a main table that is all the information that is on our orders. The form I am designing is a data-entry form that will enter a quote into that main table. All of the fields on the form are from that main table. The main table gets its distributor combo box from the distributor table. The rest of the information such as [phone] etc. are also on that table. I want to get my data entry form to automatically fill in the rest of the data like you said before.

I tried doing(at least I think) what you told me to do but I am not clear on a couple of things. Be a little patient with me since I really just started working with Access and am learning as I go. I tried using the builder on the combo box from the form design view. However, I could not get it to work. Where should I be taking the fields from? What should the expression look like? I went back and used the lookup wizard at the table level, but that just lumps all of the information together in what looks like one long field. I also need a little clarification on assigning the controls. I do not understand the code that you gave me at all. Sorry, but like I said, I am just starting at this. Thank you so much for any more help you can give.

 

[scking]

In design view click on the combo box and look at the properties. Click in the 'Row Source' property and two buttons will be displayed on the right side. Select the button with the three dots ... and it will open a query builder. Select the table you will be using as the source for the information and pull down the fields you want to user. Click to close and it will build the query statement for you in the 'Row Source' property.

If you selected four fields you want to use in the combo box then the 'Column Count' is 4, the 'Column Widths' would be something like 1";0";0";0" if you only wanted to show one of the columns. You would reference each of these four values as follows:

Me.cboCombo.Column(0)
Me.cboCombo.Column(1)
Me.cboCombo.Column(2)
Me.cboCombo.Column(3)

I frequently set constants to represent the names of the columns like:

Const NAME As Integer = 0
Then I can reference Me.cboCombo.Column(NAME)

So, after you have selected a value for the combo box it will have the information you can reference or assign. If you have a text box name txtName on the form you could then assign the name from column(0) as follows:

Me.txtName = Me.cboCombo.Column(NAME)
or
Me.txtName = Me.cboCombo.Column(0)

Hope this helps.

Steve King
Growth follows a healthy professional curiosity

 

[BLutgen]

Thanks again for all of the help but I am still having some problems. I got the first part of the query building no problem. The first time around I was using "Control Source" instead of "Row Source" Anyway, so I got that set up but I am still having problems with the Me.cboCombo.Column(0)

You say to "reference" the values with those codes. Where do I go to put those in? I have the combo box and then 5 text boxes I need to fill off of the combo box. Does the code go into the text box properties somewhere? Do I need to set up a Macro? When I tried putting one into the AfterUpdate event it just said it didn't recognize the Macro. Do they all go in the same place? Once I have them in wherever they go, how do I connect the text box to it? I believe that is what is at the end of your last instructions but I can't get that far to try it. Thanks again for the help...it is really appreciated.

Brad

 

[scking]

Let's say you have a form with one combo box named cboCombo and 4 text boxes name box1, box2, etc on a single form. You have already used the row source to get four columns of information into the combo box. Do NOT use macros to implement this.

In the properties for the cboCombo go to events 'After Update' and build the following. Don't try to make it too difficult as it is a simple concept. When you assign a value to the text box like Me.box1 it uses the default property value so it doesn't need to be shown normally.

Sub cboCombo_AfterUpdate

Me.box1 = Me.cboCombo.Column(0)
Me.box2 = Me.cboCombo.Column(1)
Me.box3 = Me.cboCombo.Column(2)
Me.box4 = Me.cboCombo.Column(3)

End Sub

This will assign values from the columns of the combo box to the text boxes. Access does all the rest. When to move off the record or save it it applies those values to the table if, and only if, the text boxes are bound to a field in a table.

Steve King Growth follows a healthy professional curiosity

 

[BLutgen]

Thanks so much for sticking with me but I still cannot get it working. I am going to give you everything that I am doing and hopefully you can spot the problem.
First I am opening my form in design view and clicking on my combo box which is named [Distributor]. I am then going into properties, clicking on row source, and then clicking on the builder. A query builder comes up with my distributor table. Currently the only field in the Query is the [distributor]. I then proceed to click down all of the other fields which are [Distributor Address][Distributor Phone][Distributor Fax][Distributor Contact]and [Regional Manager]. I exit the query builder and set my column couont to 6 since I have 6 fields. I then also set the column widths to 1";0";0";0";0";0"
I then click on the 'event' tab in the [Distributor](combobox) field, and click on 'After Update.' I then click on the builder and it asks me whether I want an Expression, Macro, or Code. I select Expression and the Expression builder box appears. In the white box I type exactly the following:

Sub Distributor_AfterUpdate
Me.Distributor Address = Me.Distributor.Column(0)
Me.Distributor Phone = Me.Distributor.Column(1)
Me.Distributor Fax = Me.Distributor.Column(2)
Me.Distributor Contact = Me.Distributor.Column(3)
Me.Regional Manager = Me.Distributor.Column(4)
End Sub

I then click OK and exit out of the properties. The questions I also have are am I not supposed to include the [Distributor] field since it is the name of the combo box and I am not using it in a text box. Then I would have 5 fields instead of 6 and the above code would seem to balance out. Right now I am still a field short. Right now my query builder looks like this:
[Distributor][Distributor Address][Distributor Phone][Distributor Fax][Distributor Contact][Regional Manager]

Should I get Distributor out of there and only have 5 fields? Will I still get the distributors for my pull down combo box? Does everything else look right? Thank you so much for you time helping me.

Brad

 

[scking]

In the expression builder you select CODE because this is all vba code. It will build a shell that looks exactly like this:

Sub Distributor_AfterUpdate
End Sub

Then paste the following lines between the sub and end sub:


Me.[Distributor Address] = Me.Distributor.Column(0)
Me.[Distributor Phone] = Me.Distributor.Column(1)
Me.[Distributor Fax] = Me.Distributor.Column(2)
Me.[Distributor Contact] = Me.Distributor.Column(3)
Me.[Regional Manager] = Me.Distributor.Column(4)

When you are in the code window and type 'Me.' it should pop up a list of all the properties, methods, and controls for the form although this is not necessary. You can not refer to a control with a space in the name unless you replace the space with an underscore. Your Me.Distributor Address would have to be Me.[Distributor Address]. Make sure that your columns are matched with your controls; Address in column(0), Phone in column(1), etc.

Steve King Growth follows a healthy professional curiosity

 

[BLutgen]

I finally got it to work!!!! Thank you so much. You have no idea how happy I was to finally see that information pop up on that form. I really think that I am getting into this thing a little over my head, but thanks to your help there is still hope! Thanks Again!

Brad

 

[Watkin]

awesome stuff scking, I've been playing around trying to figure this all out myself for a few hours, trying different tactics, and you've finally provided a most useful post!! Thanks heaps!!

 

[deweb]

This helped me a lot, but I have a slight variation on this problem.

I have a contacts database with three primary tables (tblContacts, tblOrganizations, tblAddresses). Some of the contacts are affiliated with organizations and some aren't.

I have a combo box on my form listing all of the organizations. If the contact is affiliated with an organization I want the organization's address information to populate the address fields, HOWEVER, if the contact is not affiliated with an organization I want to enter the address manually, so the address fields need to be bound to the tblAddresses table. I don't want duplicate information in the tblAddresses table.

How do I do this??

 

[Noshy]

scking...just to say that it is nice to come across someone with so much patience. Thanks for the thread Blutgen.Ive been trying to figure this one out for ages and had one day left before the presentation.

 

[LaoWai]

Thanks scking! Great patience and info here. Anyone care to make it a FAQ?