Autopopulating Values

[heiligm]

I relatively new to Access but I have a basic understand and have designed many simple databases and forms from scratch; however, I've been given a task at work that is requiring me to do something that is altogether new to me.

In a form I created, I need two text fields to auto populate their values based on a selection from a dropdown list. The dropdown list has a series of names that are drawn from a different table. I'll call that table the "Name" table for this post. The "Name" table has three fields in it, the name of the rep (these names are the list in the dropdown field), their ID number and who their manager is.

OK, back to my form. When I select a rep name in the dropdown list, I want the ID text box and the Manager text box to fill based on the name I select. In other words, if I select "Mike", I want Mike's ID and Manager to auto populate on the form. There are other fields to complete but these I need to fill in automatically. I've read about the "On Change" procedure but I don't know how to do it.

I hope I explained that right. Please post the “For Dummies” version is possible. I really need to get this working.

Mike

 

[AccessMSSQL]

This will require a few steps:
First, your combo box needs to be set up with the following properties set up like this:
Rowsource = "select ID, Name, Manager from Reps"
ColumnCount=3
ColumnWidths="0;1;0

Use the AfterUpdate event of the combo box (not the OnChange)

Put this code in the AfterUpdate Event of the combo box

Private Sub cboRep_AfterUpdate()
Me.txtID = cboRep.Column(0)
Me.txtManager = cboRep.Column(2)
End Sub

http://www.accessmssql.com/

 

[bob fitz]

Try this:
Set the Control Source property of the ID text box to:
= [NameOfComboBox].Column(0)

Set the Control Source property of the Manager text box to:
= [NameOfComboBox].Column(2)

 

[Isskint]

Mike,

First off welcome to Access World.

OK this is a fairly easy task to achieve.

I would include all the fields of your 'Name' table in your combobox, just hide the ID and Manager (set the column widths to 0;3;0 (the 3 is a guess but just figure out what width you need to see all of the reps name). With this done add some code to the combobox OnChange event, as follows:

textboxID = comboboxRep.column(0)
textboxManager = comboboxRep.column(2)

You may also want to set the focus to the next control on your form this way the user will be able to just select the rep and be able to start typing data without having to reposition the cursor.
For example, if the first thing a user will type after selecting the reps name is an appointment date then

textboxAppointmentDate.setfocus

 

[heiligm]

Thank you everyone! All of your advice was extremely helpful. @Isskint, Hiding the column width was genius. Worked like a charm.

I have a follow up:

I have another section in this same form that will require a similar but not exact solution. I have a drop down list with three choices hard coded into it, I'll call it "A, B and C". Just under this field, on the form, are four other drop down lists that will contain identical data (a list of statements). These statements are all listed in individual tables that contain all the statements associated with A, B or C. Should you select "A", I want the subsequent fields to look up their values in the corresponding "A" table. Should you select "B", those values would change to the corresponding statements in the "B" table...and so on.

Suggestions?

 

[AccessMSSQL]

In your after update event of combo box 1 first check that there is a value selected and then programatically change the rowsource properties of the other drop down lists:

if me.combobox1 <> "" then
    select case me.combobox1
        case "A"
            me.combobox2.Rowsource = "SELECT * from tableA"
        case "B"
             me.combobox2.rowsource = "SELECT * FROM tableB"
end select
me.combobx2.requery
end if

 

[heiligm]

I can see where you're going with this and it makes sense; however, I'm new to the code side of Access and I'm afraid I'm taking what you wrote quite litterally. Let me make sure I understand.

Where "ComboboxX" is listed, I'll enter the exact name of my filed.
Where "TableA" is listed, I'll enter the exact name of the table I want the data to be drawn from.

When you say "SELECT * from tableA", do I only change the table name or do I do something with "*" too?

Mike

 

[heiligm]

@AccessMSSQL

I think I got it. Working great! Thanks!

 

[Isskint]

Thanks Pat, yes i did not think about that:banghead:. Time for me to JOIN the JOIN club