Auto Populate Fields

[AMC31]

I have a table - tblBroker and I also have a table - tblClient.

From the Broker Form I have a drop down box. The options in the drop down include "office name, contact first name, contact last name, Adress, etc.".

I want the office name, first name, last name, and address to automatically populate to the correct text box, according to what is selected from the Office Name drop down. I have completed this task in another database (years ago!) but can not seem to get it to work this time.

Any help you can provide is greatly appreciated. Thanks!!
Alicia

 

[dcx693]

Not sure I quite understand. Can you give me an example of what you'd choose in the first drop-down box and what you would want to populate the other fields?

 

[AMC31]

I have a client table with the following fields:
Office Name, First name, Last Name, Address, City, State, Zip

I have a broker table/form
I want to pull the contact information from the client table into the broker table/form without having to retype the information each time a new project is started for that client.

From the Broker Form:
I have a drop down box that has four columns (office name, contact first and last name and address. This is to identify the contact and select it.

After selecting one...I want all of the fields to automatically populate. If I choose Company A, I want the first name, last name, address, city, state and zip to automatically fill in on the form.

The drop down row source is: SELECT DISTINCTROW tblClient.OfficeName, tblClient.ContactFName, tblClient.ContactLName, tblClient.Address, tblClient.City, tblClient.State, tblClient.Zip FROM tblClient;

And in the Contact First Name field the control source is: tblClient.ContactFName

Hope this helps...

 

[AMC31]

I have attached the database to help explain what I am trying to do. From frmBroker I want to use the drop down next to Office Name and I want the other contact information fields to automatically complete.

Also...when the "File Started" box is checked can the current date automatically appear in the date field?

Thank you so much for any help anyone can provide!!

 

[dcx693]

OK, I took a look at the database. Looks like you're just placing the client information at the top for display purposes, so that after the user makes a selection from the OfficeName combo box you want to have the client name and address pushed into the boxes below. The only field that gets saved to the database is the OfficeName field.

The easiest way to do this is to place this type of expression into the control source for the fields: =cboOfficeName.Column(x) where x is the column number, starting at 0. So for example, place this into the Client's First Name field: =cboOfficeName.Column(1), etc... just changing the x to the appropriate column number.

Also, since you don't want the user to attempt to change any of the values in these combo boxes, you should disable and lock them.

 

[AMC31]

I do want the First Name, Last Name, Address, etc. saved in the Broker Table

How to I make it populate and save as a field in the table?

 

[dcx693]

OK, then. It get a bit more complicated, but not crazy.

You need to change the control source of the text boxes to fields within the Broker table. (I'm not sure why you'd want to replicate this information, since you have the OfficeNumber being saved to the broker table, and you can join that table to the client table to get this information.)

Now, to get the information into the fields, use a variation of the technique from my last post. You'll need to attach VBA code to the AfterUpdate event of the OfficeName combo box. I would rename the text boxes on your Broker form, since the names you used were confusing. I would use names like txtClientContactFName, etc... The code to attach to the AfterUpdate event will look like this:
Me.txtClientContactFName=Me.cboOfficeName.Column(1).

Just change that column number for the other fields.

 

[jfgambit]

I believe that this is what you want...but you are duplicating data in your database and that is really a "no-no" in a normalized database. You should just be storing the Client ID in the Broker Table and using lookup functions to populate the unbound textboxes on your form to view the Name, Address, etc.

 

[AMC31]

Thanks jfgambit! The copy you sent me duplicates the contact information. How do I show the information, but only record the client number? I know how to pull it out of both tables with a querry, just don't know how to make it "all" display, but not record.

This site is great for obtaining helpful information!! Thanks!

 

[jfgambit]

You need to change your table structure so that only the ClientID is stored in the Broker table. Then you can utilize DLookUp functions to populate unbound textboxes on your form. I will try to put together an example or update the db you posted later today to show you an example. I may not get to it til tomorrow.

 

[dcx693]

jfgambit, I'm not generally a fan of dlookup function as they're usually quite flow. AMC31's sample database uses a multi-column combo box that already has the information that he needs for the fields in question. I proposed that he push the data into the fields using VBA.

 

[jfgambit]

To each his own...

If you look at the example I posted you will see that the information is being pushed to the fields via VB. The problem stems from the fact that he is duplicating data in a database and that is bad form (excuse the pun). There is no need to store all the Client information a second time when it already exists in a Client table. The only field necessary in the Broker table is the Client ID.

 

[dcx693]

To each his own...

I agree. Whatever works is the solution.

I, too, admonished AMC31 against duplicating information in the database. Having information in a single location is one of the best reasons for using a relational database manager like Access.

 

[Pat Hartman]

So far you've been given two code solutions for displaying data that is not stored in the table a form is bound to - DLookup() and copying a value from the current row of a combo.

There is a third option and this is the option that relational databases are all about. The JOIN. Simply base the form on a query that joins the two tables. You will be able to see (and even modify if you must) all the fields from both tables. This method requires NO CODING and is preferred over both of the other proposed solutions.

The DLookup() solution is a very poor performer. Domain functions should be avoided unless there is absolutely no other way to solve the problem. In the example given, you would need to run the same query (that's what DLookup() does) SEVEN times. Once to retrieve each desired field.

 

[dcx693]

Pat Hartman is speaking of the Autolookup feature in Access, which I agree is the best solution of all. Do a search in the Access help on this feature.

 

[nebon]

Hallo, first of all, I dont see why you dont try to make things as simple as possible! why dont why try to create a relationship between the 2 tables, and speaking of which , the tblbroker has to many fields , why don't you try to split it into multiple tables ? furthermore, try to not to have much on the rowsource of the forms, this is going to slow down the process also , do not use DLOOKUP, as it is to slow, and I dont see why you should do it, after making relationship betweem the tables, do something like this, have the IDno as the bound column for the combobox and then, make its width to 0, thereafter by defining the relation betwen the 2 tables in the forms rowsource, the name, address etc, will automatically change!!! hope thing works out for you !



--------------------
Read- & understand- & Code- The problem!

 

[Vaimpir]

I believe that this is what you want...but you are duplicating data in your database and that is really a "no-no" in a normalized database. You should just be storing the Client ID in the Broker Table and using lookup functions to populate the unbound textboxes on your form to view the Name, Address, etc.

This is sort of what I am looking for in my DB. One question. Is there a way to have the "Office Name" combobox only show the first column in the drop down?

 

[Pat Hartman]

Use the ColumnWidth property to control the viewable widths of the columns in the combo's rowsource. Set the width to 0 for any column you don't want to show. 2",0",1" for example, will hide the middle column but show the first and third.