combo box completing multiple fields help

[jeffm3434]

Ok... big time newbie/hack here. I have a tiny bit of programming experience from waaay back with UNIX and C. Have basic database experience and understanding but no VB experience. So I appologize ahead of time if this seems really simple. I'm working on "fixing" a small access database/program for my wife's work. Very simple design.

I have a company table and a customer table. I have customer form and a company form. They may have several customers at the same company. So my design has them starting on the Customer form. They begin by filling in the Customer's Name and then they get to the Company Name combo box. They should select the Company Name from this box. If it is a new company and is not in the list they should click on the "New Company" button beside the Company Name field. Then the Company Form opens and they can add a new Company along with Company address and phone numbers etc. Then they close the Add Company Form and then can select the newly added Company form the Company Name combo box (by the way, the only way I could get this box to update with the new company name was to use a macro that only runs the Requery statement for the On Enter event... it works... I hope this is ok).

Anyway, this is where my problem arises. I need the selection of the Company Name to also pull over the address, phone number, fax number, etc. info from the Company table and complete it on the Customer form/table now. How do I do that? I think I need to do this with the After Update event with some VB code... but I have no idea how to actually pass this data. The property for my combo box is as follows:
Control Source........CompanyName
Row/Source Type.....Table/Query
Row Source......SELECT zMASCompanies.CompanyName FROM zMASCompanies;

The field doesn't have to be a combo box (if this is not the right way to do it)... I just need them to select a company name and have it fill in about 20 or 25 fields from the company table/form into the customer table/form.

I did try searching the forum and found a few things but nothing I found was very clear (especially since I am not sure I am even heading down the right path). Any assistance would be greatly appreciated!!!

Any help please????
Thanks,
Jeff

 

[statsman]

Ensure that the combo box you are using has all of the fields from Customer you want to place onto your form. Create a new combo box if you have to. You can adjust the fields in the existing combo box but its easier to just delete the old combo box and create a new one.

In the form’s design view, click on the combo box. Access will have assigned the combo box a number (for this demonstration we’ll say it’s Combo7 )

The columns in the combo box are numbered. They start at the left hand column with number 0 (zero) and go up one number at a time as you go to the right. The third column from the left would therefore be column 2.

Right click on the combo box and select “Build Event”, then select “Code Builder”. In the top right corner of the new screen select “AfterUpdate”. On the screen, the cursor should now be in the coding area for the “AfterUpdate” event. It will look like this:

-------------------------------------------------------------------------------------------
Private Sub Combo7_AfterUpdate()

End Sub
---------------------------------------------------------------------------------------------

In the line between
Private Sub Combo7_AfterUpdatek()
And
End Sub
enter:

Me![fieldname] = Me![Combo7].column(#)

The coding area should now look like this:

-------------------------------------------------------------------------------------
Private Sub Combo7_AfterUpdate()

Me![fieldname] = Me![Combo7].column(#)

End Sub
-----------------------------------------------------------------------------------------

You can add new blank lines just by pressing enter on your keyboard.

The [fieldname] is the name of the field on the form, the column(#) is the number of the column in the combo box that has the data you want to place into that field.
Place as many lines of code in the “AfterUpdate” area as you have fields on the form you want to fill automatically when you select the customer form the Combo box. Each field requires a separate line. If you selected a field to be entered on the form when you created your combo box, that field will not require a line of code.

Save your changes by clicking on the floppy icon or by using File -> Save, then click on the “X’ in the top right corner. You should be returned to the form design screen.

If it doesn’t work properly you’ve probably misspelled something or you got the bracketing wrong.

 

[jeffm3434]

thanks for the help!!!

I think I am on the right track with the info you gave me, but the problem is that the data is not filling in right.

I think I have everything set up as you described but when I do the drop down it only correctly completes one other field (other than the value for the combo box itself). The other fields get blanked out. For example:

In my Company table I have the following values:
CompanyName = abc corp
CompanyK_ID = 2
CompanyPhone = (123) 456-7890

In my Customer table I have the following values (before doing the lookup):
CompanyName = testname
CompanyK_ID = 4
CompanyPhone = (098) 765-4321

Then on my Customer form (pretending to be an end user that needs to change a customer's company) I go to the CompanyName combo box and select the company "abc corp" and it changes the values for CompanyName and CompanyK_ID properly but it just blanks out the CompanyPhone field. I have tried several fields and the only one I can get to work right is CompanyK_ID. The rest all just blanked out. I'm not getting any errors like: "can't find field" (which I got when I made a typo).

Here is my VB code:
Private Sub CompanyName_AfterUpdate()
Me![CompanyK_ID] = Me![CompanyName].Column(1)
Me![PhoneNumber] = Me![CompanyName].Column(2)

End Sub

Here is my combo box properties:
Row Source Type = Table/Query
Row Source = SELECT zMASCompanies.CompanyName, zMASCompanies.CompanyK_ID, zMASCompanies.PhoneNumber FROM zMASCompanies ORDER BY zMASCompanies.CompanyName;

My fieldnames on my form are:
CompanyName
CompanyK_ID
PhoneNumber

Any ideas what is wrong?

 

[jeffm3434]

Finally figured it out!!!!

The trick was that my "Column Count" had to have the actual number of data columns specified correctly. In that I have 20 data fields I had to set the Column Count property to 20. Then I used the "Column Widths" property and set the Company Name field to 15" and the rest to 0". So now the end user will only see the Company Names in the combo box but the data will be accessible to the form so that the other fields can be auto completed. Works like a champ. Thanks.

 

[ScottGem]

I have to disagree with Statsman's advice here. There are three methods of DISPLAYING data from another table on a form. Those three are subforms, the Column property and DLookups. In your case, I would use a subform. The only time I would not use a subform is when the number of vlaues to be displayed is 4 or less. Since you have 20 fields from the company table to be displayed, you would be much better off with a subform. Link the subform on CompanyID and no other coding is necessary. As soon as the Company is selected from the combo, the data appears inthe Subform. If a New Company is being added, then just enter the data in the subform.

Notice the emphasis on DISPLAY. There is no way you should have any data from the Company table other than the company ID (Name is NOT a good choice for CompanyID). The CompanyID is the Foreign Key in the Customer table.

 

[jeffm3434]

I understand that "good" database design does not usually allow for "repeated" info in several tables but in this case it is really the only good choice for what I need to accomplish. See the company table is really just serving as a "template" for information. While a customer may work for a company, their address may or may not correspond to the actual address for the customer. This is a situation where there are few companies and many customers. So when a new customer is added, the end user will select the company the person works for, if the auto completed info is correct then they have nothing further to do, but if the address, email, phone number, etc. info is not right, they can correct if for this person without changing the "template". If the template is wrong then they can use my company form to update the template. So a sub form would not accomplish what I need. I did use sub forms for the projects for each customer and then another sub form that links product samples back to the projects (essentially a nested sub form).

In this case, the combo box accomplishes exactly what I need. I'm sure there might be other ways to do this and maybe even better designs... but in this case it provides me with the final result I need.

As far as your other options ("Column property and DLookups"), I don't know what these refer to so I can't comment on them... remember I am a newbie/hack at VB and Access.

"Name is NOT a good choice for CompanyID"--- actually I have another field that is not accessible to the end user that is called CompanyK_ID that is the unique key for each company (autonumber field). The "Company Name" is just the text field for the end user's benefit. All reports, queries, and other behind the scenes things are done with the CompanyK_Id field.

Thanks for your input... if you see any flaws in my logic please let me know. I do not profess any great knowledge of any of this. I'm just doing this for my wife's work in my spare time (no compensation)... it's just a system they have had for several years and it has some bad design problems and she has explained the problems and I am trying to fix it as best I can without starting completely over. If that were an option... I would not be using access or VB since I am so unfamiliar with it. Plus I gotta salvage the old data too into the new tables so there is some restrictions there too.

 

[ScottGem]

I understand that "good" database design does not usually allow for "repeated" info in several tables but in this case it is really the only good choice for what I need to accomplish. See the company table is really just serving as a "template" for information. While a customer may work for a company, their address may or may not correspond to the actual address for the customer. This is a situation where there are few companies and many customers. So when a new customer is added, the end user will select the company the person works for, if the auto completed info is correct then they have nothing further to do, but if the address, email, phone number, etc. info is not right, they can correct if for this person without changing the "template". If the template is wrong then they can use my company form to update the template. So a sub form would not accomplish what I need. I did use sub forms for the projects for each customer and then another sub form that links product samples back to the projects (essentially a nested sub form).

In this case, the combo box accomplishes exactly what I need. I'm sure there might be other ways to do this and maybe even better designs... but in this case it provides me with the final result I need.

As far as your other options ("Column property and DLookups"), I don't know what these refer to so I can't comment on them... remember I am a newbie/hack at VB and Access.

"Name is NOT a good choice for CompanyID"--- actually I have another field that is not accessible to the end user that is called CompanyK_ID that is the unique key for each company (autonumber field). The "Company Name" is just the text field for the end user's benefit. All reports, queries, and other behind the scenes things are done with the CompanyK_Id field.

Thanks for your input... if you see any flaws in my logic please let me know. I do not profess any great knowledge of any of this. I'm just doing this for my wife's work in my spare time (no compensation)... it's just a system they have had for several years and it has some bad design problems and she has explained the problems and I am trying to fix it as best I can without starting completely over. If that were an option... I would not be using access or VB since I am so unfamiliar with it. Plus I gotta salvage the old data too into the new tables so there is some restrictions there too.

Actually there are several flaws. The ONLY time when it is valid to repeat info (other than the foreign key) inb a related table is when you are trying to freeze time sensitive data like the current price of a product. There is NO way that that your situation is "the only good choice". Its actually a bad choice.

As I understand your situation, you have companies and customers. A customer is associated with a single company. But a customer may or may not use the same address as the company. In that case what you have a separate address table;
AddressID (PK Autonumber)
StreetAddress
City
PostalCode
etc.

In your Company and Customer tables you add a Foreign key for AddressID. On your form, you have a combo or list box to select an AddressID. You then have a subform bound to the Address table where you can add a new address if necessary.

Again, CompanyName is NOT a good idea for a foreign key. In your original note you stated that the Company combo was bound to the companyname field. That's not a good idea. The relevant properties of that combo should be:

Row Source = SELECT zMASCompanies.CompanyK_ID, zMASCompanies.CompanyName, zMASCompanies.PhoneNumber FROM zMASCompanies ORDER BY zMASCompanies.CompanyName;
Bound Column: 1
Column Count: 3
Column Widths: 0";2";.75"

What this does is allow the user to select from and display the CompanyName but stores the CompanyID as the Foreign Key so you can use it for joins.

Normalization is an absolutely key issue in designing databases. I suspect that a large part of the problems you've encountered are due to normalization issues. If you get the underlying table design correct then a lot of things will fall into place.

 

[Sharkiness]

Ensure that the combo box you are using has all of the fields from Customer you want to place onto your form. Create a new combo box if you have to. You can adjust the fields in the existing combo box but its easier to just delete the old combo box and create a new one.

In the form’s design view, click on the combo box. Access will have assigned the combo box a number (for this demonstration we’ll say it’s Combo7 )

The columns in the combo box are numbered. They start at the left hand column with number 0 (zero) and go up one number at a time as you go to the right. The third column from the left would therefore be column 2.

Right click on the combo box and select “Build Event”, then select “Code Builder”. In the top right corner of the new screen select “AfterUpdate”. On the screen, the cursor should now be in the coding area for the “AfterUpdate” event. It will look like this:

-------------------------------------------------------------------------------------------
Private Sub Combo7_AfterUpdate()

End Sub
---------------------------------------------------------------------------------------------

In the line between
Private Sub Combo7_AfterUpdatek()
And
End Sub
enter:

Me![fieldname] = Me![Combo7].column(#)

The coding area should now look like this:

-------------------------------------------------------------------------------------
Private Sub Combo7_AfterUpdate()

Me![fieldname] = Me![Combo7].column(#)

End Sub
-----------------------------------------------------------------------------------------

You can add new blank lines just by pressing enter on your keyboard.

The [fieldname] is the name of the field on the form, the column(#) is the number of the column in the combo box that has the data you want to place into that field.
Place as many lines of code in the “AfterUpdate” area as you have fields on the form you want to fill automatically when you select the customer form the Combo box. Each field requires a separate line. If you selected a field to be entered on the form when you created your combo box, that field will not require a line of code.

Save your changes by clicking on the floppy icon or by using File -> Save, then click on the “X’ in the top right corner. You should be returned to the form design screen.

If it doesn’t work properly you’ve probably misspelled something or you got the bracketing wrong.

Hey Statsman,

This has worked perfectly for me so thank you. I was just wondering. If I select an option for the combobox all my field updated which is perfect.

What I need to know is if I make a selection and was meant to leave it blank. I can manually delete what is in the combobox but cant delete anything in the other fields. Do you have any suggestion for If make the combobox blank that the other fields will also go blank.

Again thanks for this and hope you can help.

Sharkiness