ACCESS

[Guest]

TABLE1 HAS SEVERAL FIELDS, ONE OF WHICH CURRENTLY HAS A COMBO BOX DISPLAYING
INFORMATION BASED FROM A FIELD IN TABLE 2.

UPON SELECTING AN ENTRY FROM THE COMBO BOX, I WOULD LIKE TO AUTO POPULATE
THREE ADDITION FIELDS IN TABLE1, TO MATCH THEIR CORROSPONDING FIELDS IN
TABLE2.

I HOPE THIS MAKES SENSE, AND THAT SOMEONE CAN ASSIST/DIRECT ME
THANKS

 

[Rick B]

NO NEED TO SHOUT!

Are you sure you want to put the data in the table? That is a little
redundant. Proper database design would dictate that you create a
relationship between the two tables and have ONE common field to create your
link, but that you do not actually move data into the second table.

For example, if you enter a ZIP code in a table, and you have another table
with all the ZIP, State, City, and County entries. When you select the ZIP
in your main table, you would not want the other four items to be copied to
your table.

Post back if you need help with the relationship, or if you have a valid
reason to grab the data and repeat it in your main table. A vlid reason
might include if the data was time sensative. Such as the closing price of
a stock. You might want to apply it to your main table because it will
change tomorrow.



Rick B

 

[Ken Snell [MVP]]

THERE IS NO NEED TO SHOUT....

You cannot do this in a table, but you can do it on a form that has a
subform(s) based on the other tables. But you'll need to give us more info
about your specific setup.

 

[Guest]

tblOrder;
Hauler Name, Contact, Phone #, Project, Job #, PO#, Freight Rate, Dump Fee,
Prevailing Wage

TblHauler;
Hauler Name, Hauler #, Contact, Phone #,

frmOrder;
Hauler Name, Contact, Phone #, Project, Job #, PO#, Freight Rate, Dump Fee,
Prevailing Wage

I use frmOrder to input my data to tblOrder. In tblHauler I have several
Hauler names, but only 1 contact name and number assigned to each Hauler.
When using frmOrder I would like to select a Hauler name from my combo box,
and would like contact & phone # to fill in the corresponding cells on my
form, however I want the ability to type over the contact & phone # fields in
the event that there may be a temporary change in that information for a
particular order.

 

[Ken Snell [MVP]]

OK - no need for a subform after all, now that I see your setup.

On your frmOrder, you want to use a combo box for the Hauler Name values.
Use a query as the Row Source for this combo box. The query should obtain
the Hauler Name, Hauler #, Contact, and Phone # fields.

Then see The ACCESS Web for an article on how to have textboxes autofill in
after you make a selection in a combo box; you'll want to use the one that
uses code to put the values into the boxes, because you'll want to bind the
textboxes to the fields in the tblOrder table and you want the ability to
modify those values:
http://www.mvps.org/access/forms/frm0058.htm

Post back if you need more info.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

OK - if you haven't figured it out yet I am new to Access, VB and this forum.
I reviewed the article you referenced and created the hypothetical
situation. Works great this is exactly what I am after.
However I can't seem to figure out how to add the additional steps to the
code so that each textbox receives a value from the appropriate column number
of the combo box. (Bound textbox example)

 

[Ken Snell [MVP]]

Give me a bit more to go on... what code and form controls have you put
together so far, and what (in words) do you want to do yet?

 

[Guest]

I would like to select an ID number from cboPersonID and populate the
FirstName, MiddleName & LastName fields on the form with data from
tblPersons. Currently I can only populate one field. (FirstName or MiddleName
or LastName).

I know this is a bad example. It’s just a quick DB to learn how to perform
this function, it has no other purpose.

FrmNewPersons (for data input to tblNewPersons)

cboPersonID
Bound Column…1
Column Count…4
Column Widths…1â€;0â€
Row Source Type…Table/Query
Row Source…SELECT [tblPersons].[ID], [tblPersons].[FirstName],
[tblPersons].[MiddleName], [tblPersons].[LastName] FROM tblPersons;

After Update…
Private Sub cboPersonID_AfterUpdate()
Me.FirstName.Value = Me.cboPersonID.Column(1)
End Sub

TextBox1… FirstName
TextBox2… MiddleName
TextBox3… LastName

TblPersons; ID, FirstName, MiddleName, LastName

TblNewPersons; ID, FirstName, MiddleName, LastName

 

[Guest]

I would like to select an ID number from cboPersonID and populate the
FirstName, MiddleName & LastName fields on the form with data from
tblPersons. Currently I can only populate one field. (FirstName or MiddleName
or LastName).

I know this is a bad example. It’s just a quick DB to learn how to perform
this function, it has no other purpose.

FrmNewPersons (for data input to tblNewPersons)

cboPersonID
Bound Column…1
Column Count…4
Column Widths…1â€;0â€
Row Source Type…Table/Query
Row Source…SELECT [tblPersons].[ID], [tblPersons].[FirstName],
[tblPersons].[MiddleName], [tblPersons].[LastName] FROM tblPersons;

After Update…
Private Sub cboPersonID_AfterUpdate()
Me.FirstName.Value = Me.cboPersonID.Column(1)
End Sub

TextBox1… FirstName
TextBox2… MiddleName
TextBox3…LastName

TblPersons; ID, FirstName, MiddleName, LastName

TblNewPersons; ID, FirstName, MiddleName, LastName

 

[Ken Snell [MVP]]

You just need to add additional code steps for the other textboxes:

Private Sub cboPersonID_AfterUpdate()
Me.FirstName.Value = Me.cboPersonID.Column(1)
Me.MiddleName.Value = Me.cboPersonID.Column(2)
Me.LastName.Value = Me.cboPersonID.Column(3)
End Sub

 

[Guest]

Thank you.