Fill in a field based on another field

[Sean]

I have a Table that has the following fields:
Account # Account Name Address City St Zip

In a form I want to select an account number from a list
(combo Box) and have the account name field, address
field, City field, st field, and zip field, to fill in
automatically based on the account number entered in the
account number field.
Questions:
1. What type of Fields would the Acct Name, address,
city, st and zip be? (ie. Combo Box, List box, text box)
2. How do you link the Acct Name, address, city, st and
zip, to display the info based on what account is listed.
This info would come from the Table (Customer
Information).
3. Please answer in easily understandable text and
functions.

Thanks,
Sean

 

[Poh]

Hi
Try this
Assume that your question is referring to what type of
control to be added.
If your combo box for account number consists of all
fields, you may carry out the following:
Insert other fields as text boxes and define the control
source '=[account number].Column(#)', depending on the
position of the field in the combo box.

 

[Wayne Morgan]

1. I assume you mean Controls not Fields. They would probably be text boxes.

2. There are several ways depending on exactly what you're trying to do.

a) If the account number is in the same table as the other data, you could
simply make a multicolumn combo box and set the width of the other columns
to 0. In the textboxes, set their Control Source to point to these hidden
columns in the combo box. The column number is zero based, so 0 is the first
column, 1 the second, and so on.

Example Control Source:
=cboMyCombobox.Column(1)

b) In the After Update event of the combo box, find the values you need,
possibly using the DLookup Function, and assign the values to the textboxes.

Example:
Me.txtAddress = DLookup("[AddressFieldName]", "[Customer Information]",
"[AcctNumberFieldName]=" & Me.cboMyCombobox)

c) If the form is based on [Customer Information] and the textboxes are
bound to the fields, you could move to the record indicated by the combo
box.

Example:
Me.Recordset.FindFirst "[AcctNumberFieldName]=" & Me.cboMyCombobox

 

[VOLK21]

1. I assume you mean Controls not Fields. They would probably be text boxes.

2. There are several ways depending on exactly what you're trying to do.

a) If the account number is in the same table as the other data, you could
simply make a multicolumn combo box and set the width of the other columns
to 0. In the textboxes, set their Control Source to point to these hidden
columns in the combo box. The column number is zero based, so 0 is the first
column, 1 the second, and so on.

Example Control Source:
=cboMyCombobox.Column(1)

b) In the After Update event of the combo box, find the values you need,
possibly using the DLookup Function, and assign the values to the textboxes.

Example:
Me.txtAddress = DLookup("[AddressFieldName]", "[Customer Information]",
"[AcctNumberFieldName]=" & Me.cboMyCombobox)

c) If the form is based on [Customer Information] and the textboxes are
bound to the fields, you could move to the record indicated by the combo
box.

Example:
Me.Recordset.FindFirst "[AcctNumberFieldName]=" & Me.cboMyCombobox

--
Wayne Morgan
MS Access MVP

I have a Table that has the following fields:
Account # Account Name Address City St Zip

In a form I want to select an account number from a list
(combo Box) and have the account name field, address
field, City field, st field, and zip field, to fill in
automatically based on the account number entered in the
account number field.
Questions:
1. What type of Fields would the Acct Name, address,
city, st and zip be? (ie. Combo Box, List box, text box)
2. How do you link the Acct Name, address, city, st and
zip, to display the info based on what account is listed.
This info would come from the Table (Customer
Information).
3. Please answer in easily understandable text and
functions.

Thanks,
Sean