Using List Boxes and Multiple Feilds.

[Bombay]

I have a Form that is used for an invoice.

There is a Field labeled Customer ID that is a List Box.

I have these other fields on the Form that I wish to autofill with the
Customer's Info based on what Customer is selected in the Listbox Customer ID.

State
City
Zip
Address

All of those are fields I want to auto fill with that customers info, how
would I do this?

 

[Mark A. Sam]

Bombay,

The RowSource property of the listbox needs to include all of the fields. I
am assumning [Customer ID] would be in the first positon, which when
referencing will be Column(0).

Set the ColumnCount property of the listbox to 5.

On the AfterUpdate event of the listbox put this code:

[State] = [Customer ID].Column(1) '[Customer ID] is Column(0)
[City] = [Customer ID].Column(2)
[StateZip] = [Customer ID].Column(3)
[Address] = [Customer ID].Column(4)


And this assumes that the fields or in the order listed below and the field
names match the Texbox names..

God Bless,

Mark A. Sam

 

[Bombay]

Mark I get this problem

"The Expression you entered contains invalid syntax"

Code used

[StateOrProvince] = [Customer ID].Column(1)
[City] = [Customer ID].Column(2)
[PostalCode] = [Customer ID].Column(3)
[BillAdd1] = [Customer ID].Column(4)

 

[Bombay]

Is a list box sufficient enough to do this?

Is there something I need to do in the other fields(City etc) to make sure
it updates?

Brian

 

[tkelley via AccessMonster.com]

If I understand properly, It's absolutely sufficient. Listboxes rule. The
syntax is incomplete. From Access help:
=====================
Column Property
You can use the Column property to refer to a specific column, or column and
row combination, in a multiple-column combo box or list box. Read-only
Variant.

expression.Column(Index, Row)
expression Required. An expression that returns one of the objects in the
Applies To list.

Index Required Long. A long integer that can range from 0 to the setting of
the ColumnCount property minus one.

Row Optional Variant. An integer that can range from 0 to the setting of
the ListCount property minus 1.

This property setting is only available by using a macro or Visual Basic.
This property setting isn't available in Design view and is read-only in
other views.

Remarks
Use 0 to refer to the first column, 1 to refer to the second column, and so
on. Use 0 to refer to the first row, 1 to refer to the second row, and so on.
For example, in a list box containing a column of customer IDs and a column
of customer names, you could refer to the customer name in the second column
and fifth row as:

Forms!Contacts!Customers.Column(1, 4)
=====================

You refer to the selected row for the row portion. If the field
[StateOrProvince] needs to be filled by the 2nd value in the listbox's
rowsource for the selected column:

[StateOrProvince] = me.Listbox.Column(1,me.listbox.listindex)

If you are using column headers in your listbox:
[StateOrProvince] = me.Listbox.Column(1,me.listbox.listindex+1)

 

[tkelley via AccessMonster.com]

Just to be thorough, that goes in the after update event of the listbox.

If I understand properly, It's absolutely sufficient. Listboxes rule. The
syntax is incomplete. From Access help:
=====================
Column Property
You can use the Column property to refer to a specific column, or column and
row combination, in a multiple-column combo box or list box. Read-only
Variant.

expression.Column(Index, Row)
expression Required. An expression that returns one of the objects in the
Applies To list.

Index Required Long. A long integer that can range from 0 to the setting of
the ColumnCount property minus one.

Row Optional Variant. An integer that can range from 0 to the setting of
the ListCount property minus 1.

This property setting is only available by using a macro or Visual Basic.
This property setting isn't available in Design view and is read-only in
other views.

Remarks
Use 0 to refer to the first column, 1 to refer to the second column, and so
on. Use 0 to refer to the first row, 1 to refer to the second row, and so on.
For example, in a list box containing a column of customer IDs and a column
of customer names, you could refer to the customer name in the second column
and fifth row as:

Forms!Contacts!Customers.Column(1, 4)
=====================

You refer to the selected row for the row portion. If the field
[StateOrProvince] needs to be filled by the 2nd value in the listbox's
rowsource for the selected column:

[StateOrProvince] = me.Listbox.Column(1,me.listbox.listindex)

If you are using column headers in your listbox:
[StateOrProvince] = me.Listbox.Column(1,me.listbox.listindex+1)

Is a list box sufficient enough to do this?

Is there something I need to do in the other fields(City etc) to make sure
it updates?

Brian

 

[Mark A. Sam]

You are referencing [StateOrProvince] . On your original post the textbox
name was [State]. That could be the problem.