Autofill Data Into Form

[GMan]

With Access 2000, is there a way to autofill text into a form by linking to a
field after establishing the client's ID, name, address etc.? For example, I
would like to type "100" into the Client ID field and then have that client's
name, address, city, state and zip automatically be entered into the form for
subsequent records.

Then, type "101" into the Client ID field in the next record and autofill
using the same process for that client. Any ideas?

Thanks in advance --

Gino

 

[tina]

are you saving the client data in each of multiple records in the table? if
so, you shouldn't be. you should have a Clients table containing the
ClientID (primary key), name, address, etc *only once for each client* -
which apparently you do have. to link a specific client to a record in
another table, just save the ClientID value into the record as a foreign
key.

if, on the other hand, you simply want to *display* the client name,
address, etc in the form - without saving it into a table multiple times -
then post back and i can give you suggestions on how to do that.

hth

 

[GMan]

Tina - Thanks for the reply. Your explanation is right on target. To
clarify, I just want the client data to be displayed in the form without
saiving it into a table multiple times. Please advise on how best to do
that. Much appreciated, Gino.

 

[tina]

there are several ways, all involving displaying the client data in
*unbound* textbox controls on the form, on control for each data element.

1. you can run a DLookup() function in each unbound control to return the
specific element, as

=DLookUp("ClientNameField", "ClientsTableName", "ClientIDField = " &
[ClientID])

the above goes all on one line in the unbound control's ControlSource
property, and the expression assumes that the ClientID field in the Clients
table is a Number data type, not Text data type

2. you can use VBA to open a recordset on a query or SQL statement that
returns the specific client record from the Clients table, and assign the
returned values to the unbound controls. that's easy enough to do if you're
familiar with coding, but i won't go into detail unless you really want to
explore it.

3. in many situations, probably the easiest way is to use a combo box
control to enter the ClientID. if you are saving the ClientID value in the
table underlying the form, then bind the combo box to the appropriate field
in the form's Fields list. set the following properties in the Properties
box, as

RowSourceType: Table/Query
RowSource: SELECT ClientIDField, ClientNameField,
ClientStreetAddressField, ClientCityField FROM ClientsTableName ORDER BY
ClientIDField;
ColumnCount: 4
ColumnWidths: 1"; 0"; 0"; 0"
ListWidth: 1.25"
LimitToList: Yes

in the RowSource property, substitute the correct names of the Clients
table, and *all the fields you want to display values for in the form*, in
addition to the ClientID field, of course.
count up all the fields you listed in the RowSource between SELECT and FROM.
enter that number in the ColumnCount property.
in the ColumnWidths property, leave the first column at 1" (or any width
that will accomodate the length of the "widest" ClientID), and include a 0"
width for each additional field listed in the RowSource between SELECT and
FROM.

the above setup means that the ClientID combo box will only display the
ClientIDs in the droplist - but all the other client data that you need will
be "available" for further use in the form. suggest you read up on combo box
properties in Access Help, so you'll understand better how they work.

now, in each unbound control, you can set the ControlSource to pull a value
from a specific combo box column, as

=[ComboBoxControlName].[Column](n)

note that combo box columns are zero-based in Access, so the first column
(reading left-to-right) is (0), the second column is (1), the third column
is (2), etc.

btw, suggest you also add the following code to the combo box control's
NotInList event procedure, as

Response = acDataErrContinue
Screen.ActiveControl.Undo
Screen.ActiveControl.Dropdown

if you don't know how to add an event procedure to a form or control event
property, see the following link for illustrated instructions, and scroll
down to "Create a VBA event procedure".
http://home.att.net/~california.db/downloads.html

hth

 

[GMan]

That's great! Thanks again, Tina.

there are several ways, all involving displaying the client data in
*unbound* textbox controls on the form, on control for each data element.

1. you can run a DLookup() function in each unbound control to return the
specific element, as

=DLookUp("ClientNameField", "ClientsTableName", "ClientIDField = " &
[ClientID])

the above goes all on one line in the unbound control's ControlSource
property, and the expression assumes that the ClientID field in the Clients
table is a Number data type, not Text data type

2. you can use VBA to open a recordset on a query or SQL statement that
returns the specific client record from the Clients table, and assign the
returned values to the unbound controls. that's easy enough to do if you're
familiar with coding, but i won't go into detail unless you really want to
explore it.

3. in many situations, probably the easiest way is to use a combo box
control to enter the ClientID. if you are saving the ClientID value in the
table underlying the form, then bind the combo box to the appropriate field
in the form's Fields list. set the following properties in the Properties
box, as

RowSourceType: Table/Query
RowSource: SELECT ClientIDField, ClientNameField,
ClientStreetAddressField, ClientCityField FROM ClientsTableName ORDER BY
ClientIDField;
ColumnCount: 4
ColumnWidths: 1"; 0"; 0"; 0"
ListWidth: 1.25"
LimitToList: Yes

in the RowSource property, substitute the correct names of the Clients
table, and *all the fields you want to display values for in the form*, in
addition to the ClientID field, of course.
count up all the fields you listed in the RowSource between SELECT and FROM.
enter that number in the ColumnCount property.
in the ColumnWidths property, leave the first column at 1" (or any width
that will accomodate the length of the "widest" ClientID), and include a 0"
width for each additional field listed in the RowSource between SELECT and
FROM.

the above setup means that the ClientID combo box will only display the
ClientIDs in the droplist - but all the other client data that you need will
be "available" for further use in the form. suggest you read up on combo box
properties in Access Help, so you'll understand better how they work.

now, in each unbound control, you can set the ControlSource to pull a value
from a specific combo box column, as

=[ComboBoxControlName].[Column](n)

note that combo box columns are zero-based in Access, so the first column
(reading left-to-right) is (0), the second column is (1), the third column
is (2), etc.

btw, suggest you also add the following code to the combo box control's
NotInList event procedure, as

Response = acDataErrContinue
Screen.ActiveControl.Undo
Screen.ActiveControl.Dropdown

if you don't know how to add an event procedure to a form or control event
property, see the following link for illustrated instructions, and scroll
down to "Create a VBA event procedure".
http://home.att.net/~california.db/downloads.html

hth

Tina - Thanks for the reply. Your explanation is right on target. To
clarify, I just want the client data to be displayed in the form without
saiving it into a table multiple times. Please advise on how best to do
that. Much appreciated, Gino.

 

[tina]

you're welcome

That's great! Thanks again, Tina.

there are several ways, all involving displaying the client data in
*unbound* textbox controls on the form, on control for each data element.

1. you can run a DLookup() function in each unbound control to return the
specific element, as

=DLookUp("ClientNameField", "ClientsTableName", "ClientIDField = " &
[ClientID])

the above goes all on one line in the unbound control's ControlSource
property, and the expression assumes that the ClientID field in the Clients
table is a Number data type, not Text data type

2. you can use VBA to open a recordset on a query or SQL statement that
returns the specific client record from the Clients table, and assign the
returned values to the unbound controls. that's easy enough to do if you're
familiar with coding, but i won't go into detail unless you really want to
explore it.

3. in many situations, probably the easiest way is to use a combo box
control to enter the ClientID. if you are saving the ClientID value in the
table underlying the form, then bind the combo box to the appropriate field
in the form's Fields list. set the following properties in the Properties
box, as

RowSourceType: Table/Query
RowSource: SELECT ClientIDField, ClientNameField,
ClientStreetAddressField, ClientCityField FROM ClientsTableName ORDER BY
ClientIDField;
ColumnCount: 4
ColumnWidths: 1"; 0"; 0"; 0"
ListWidth: 1.25"
LimitToList: Yes

in the RowSource property, substitute the correct names of the Clients
table, and *all the fields you want to display values for in the form*, in
addition to the ClientID field, of course.
count up all the fields you listed in the RowSource between SELECT and FROM.
enter that number in the ColumnCount property.
in the ColumnWidths property, leave the first column at 1" (or any width
that will accomodate the length of the "widest" ClientID), and include a 0"
width for each additional field listed in the RowSource between SELECT and
FROM.

the above setup means that the ClientID combo box will only display the
ClientIDs in the droplist - but all the other client data that you need will
be "available" for further use in the form. suggest you read up on combo box
properties in Access Help, so you'll understand better how they work.

now, in each unbound control, you can set the ControlSource to pull a value
from a specific combo box column, as

=[ComboBoxControlName].[Column](n)

note that combo box columns are zero-based in Access, so the first column
(reading left-to-right) is (0), the second column is (1), the third column
is (2), etc.

btw, suggest you also add the following code to the combo box control's
NotInList event procedure, as

Response = acDataErrContinue
Screen.ActiveControl.Undo
Screen.ActiveControl.Dropdown

if you don't know how to add an event procedure to a form or control event
property, see the following link for illustrated instructions, and scroll
down to "Create a VBA event procedure".
http://home.att.net/~california.db/downloads.html

hth

Tina - Thanks for the reply. Your explanation is right on target. To
clarify, I just want the client data to be displayed in the form without
saiving it into a table multiple times. Please advise on how best to do
that. Much appreciated, Gino.

:

are you saving the client data in each of multiple records in the

table?
if

so, you shouldn't be. you should have a Clients table containing the
ClientID (primary key), name, address, etc *only once for each client* -
which apparently you do have. to link a specific client to a record in
another table, just save the ClientID value into the record as a foreign
key.

if, on the other hand, you simply want to *display* the client name,
address, etc in the form - without saving it into a table multiple times -
then post back and i can give you suggestions on how to do that.

hth


With Access 2000, is there a way to autofill text into a form by linking
to a
field after establishing the client's ID, name, address etc.? For
example, I
would like to type "100" into the Client ID field and then have that
client's
name, address, city, state and zip automatically be entered into

the
form

for
subsequent records.

Then, type "101" into the Client ID field in the next record and autofill
using the same process for that client. Any ideas?

Thanks in advance --

Gino