Auto Fill Fields based on combo box selection

  • Thread starter In Desperate Need of Help
  • Start date
I

In Desperate Need of Help

I have a table called Attorney Information. On my form I have a field for
the Attorney Name, Atotrney Address, Attorney City, State, Zip Code, and
Contact Person. When I choose an Attorney Name, I need their address , city,
state, zip code, and contact person to automatically fill in with the
corresponding data from the table. How would I do that? I am in desperate
need of help. I am not that familiar with Access, and I don't know what to
do. I appreciate any help "for dummies" anyone may be able to offer.
 
E

Edward G

There are probably plenty of ways to do this and there is probably even a
wizard that could do the entire job for you. Here is what I would though.
Open the Database Window. Select the Form Tab. Click New. Select Design
View. A form will open in Design View.
Click the Toolbox icon on the Toolbar.(It has a hammer and wrench logo).
Select the subform tool (the name of the tool will show if you move the
pointer over it) and click on an area of your form where you would like to
display the attorney information. A wizard will open and ask where you want
the subform to get info from. Click tables. In the next page of the wizard
select the fields you want to include in your subform. You can either double
click the field name or single click and hit the arrow key, but ultimately
the fields you want should be included in the box called "selected fields".
On the next page of the wizard I would suggest you accept the default SQL
statement. Click Next. Then Finish.
Next insert a combo box on your form with the Combo Box tool in the Toolbox.
A wizard will open. Select the third choice which will select records on
your form based on your choice in the combo box. Click Next until you can
Finish.
Open your form in Form View. Should be working.

Ed

"In Desperate Need of Help" <In Desperate Need of
[email protected]> wrote in message
news:[email protected]...
 
B

Bruce

Use the combo box wizard to find a form based on a value you select in the
combo box. To use the wizard, open the toolbox, and click the magic wand so
that it is highlighted (if it isn't already). Click the combo box icon
(hover the mouse to see what each icon means) in the toolbox, and click a
blank spot in the form. Follow the prompts. There are plenty of details and
options, but this should accomplish the basics of what you need.
 
R

Rick Brandt

"In Desperate Need of Help" <In Desperate Need of
[email protected]> wrote in message
I have a table called Attorney Information. On my form I have a field for
the Attorney Name, Atotrney Address, Attorney City, State, Zip Code, and
Contact Person. When I choose an Attorney Name, I need their address , city,
state, zip code, and contact person to automatically fill in with the
corresponding data from the table. How would I do that? I am in desperate
need of help. I am not that familiar with Access, and I don't know what to
do. I appreciate any help "for dummies" anyone may be able to offer.
Click to expand...

You should not be copying data from one table to another in this fashion.
You should only store the Primary Key value from the Attorney Information
table in the record you are creating and then use lookup methods to
"retrieve and display" additional fields you want to see in the form or
report that you are building. Having multiple copies of this data is
redundant and violates proper database design.

There are many ways to do this, but a fairly easy and flexible one is to
have your ComboBox for Attorney Name include other hidden columns in its
RowSource for all of the other data from the Attorney Information table
that you want to show. Then you can display those hidden columns in other
controls on your form by using expressions like...

=AttorneyName.Column(1)
=AttorneyName.Column(2)
etc..

Again, the above will not "copy" the data from the Attorney Information
table into your form. It will simply display the data that corresponds to
the selection in the Attorney Name ComboBox. It will change automatically
when you make different selections and as you navigate from one record to
the next so to the user it "feels" the same. The important difference is
if you change a piece of data for an attorney (new address for example),
you won't have to go back and update all of the previously created records.
If you copied the data then you would have to do this or older records
would simply show incorrect data.
 
C

Chris

Hi. The following code should work for you. Anything between [] is the
cell name in your table. For [attorneyId], you'll have to enter your
primary key. cboAttorneyName is the name of your combo box. On your form
the txt boxes should be unbound for your address, zip etc. Attorney
Information is a bad name for a table and should be one word with the prefix
"tbl" i.e. tblAttorneyInformation.

Put this code in the after update event of the combobox

Dim strAdd As String
Dim db As Dao.Database
Dim rst As Dao.Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("Attorney Information", dbOpenSnapshot,
dbReadOnly)
rst.FindFirst "[AttorneyID] = " & Me.cboAttorneyName.Value


Me.TxtAddress.Value = rst![address1]
Me.txtAddress2.Value = rst![address2]
Me.txtCity.Value = rst![city]
Me.txtState.Value = rst![State]
Me.txtZip.Value = rst![ZipCode]
Me.txtContact.Value = rst![Contact]

"In Desperate Need of Help" <In Desperate Need of
[email protected]> wrote in message
news:[email protected]...
 
B

Bruce

I took the question to mean he wants to select an attorney from a list and
have that attorney's information show up on the screen, and that "copying"
the information represents a misunderstanding of what actually happens when a
form is displayed. Of course, I did not verify that.
 
Top