Autopopulating fields in a form, MS Access 2000

[lvroye]

I have a table of about 6000 clients which have unique client numbers.
I am creating a data entry form to log contacts with the clients. I
want the client information (Name, Sales Rep, phone number, etc) to
autopopulate fields on the form when the client number is entered. I
have seen this done on another database I have access to, but have
been unseccessfull in recreating it. Can anyone point me in the right
direction?

[Scott]

you want to use Dlookup in an event procedure that runs after you update the
client number

"lvroye" <rp*********@co x.netwrote in message
news:11******** **************@ y80g2000hsf.goo glegroups.com.. .

>I have a table of about 6000 clients which have unique client numbers.
I am creating a data entry form to log contacts with the clients. I
want the client information (Name, Sales Rep, phone number, etc) to
autopopulate fields on the form when the client number is entered. I
have seen this done on another database I have access to, but have
been unseccessfull in recreating it. Can anyone point me in the right
direction?

[Accessed]

Hi Ivroye

I would not be keen to do a dlookup after the even. For a start, you
only get 1 value back at a time, which may make for slow processing
when you have to get a lot of data back.

Try something like this
Client Number <after update>

dim db as dao.database
set db = currentdb()
dim rst as dao.recordset

set rst = db.openrecordse t("select * from tblClientInfo where
clientnum = " & cstr(me!tboClie ntNum, dbopensnapshot)

if rst.recordcount 0 then
me!tboclientnam e = rst!clientfirst name & " " & rst!clientfamil yname
me!tboaddress= rst!clientstree t & " " & rst!clientcity
me!tbophone= rst!clientareac ode & " " & rst!clientmainp hone
endif

On Apr 16, 8:56 am, "lvroye" <rpelling...@co x.netwrote:

I have a table of about 6000 clients which have unique client numbers.
I am creating a data entry form to log contacts with the clients. I
want the client information (Name, Sales Rep, phone number, etc) to
autopopulate fields on the form when the client number is entered. I
have seen this done on another database I have access to, but have
been unseccessfull in recreating it. Can anyone point me in the right
direction?

[Arno R]

"lvroye" <rp*********@co x.netschreef in bericht news:11******** **************@ y80g2000hsf.goo glegroups.com.. .

>I have a table of about 6000 clients which have unique client numbers.
I am creating a data entry form to log contacts with the clients. I
want the client information (Name, Sales Rep, phone number, etc) to
autopopulate fields on the form when the client number is entered. I
have seen this done on another database I have access to, but have
been unseccessfull in recreating it. Can anyone point me in the right
direction?

Use a query that auto-magically poputlates your fiellds from the Client table.
Check 'AutoLookup-query' in Help

Arno R

[Larry Linson]

"lvroye" <rp*********@co x.netwrote

I have a table of about 6000 clients which have unique client numbers.
I am creating a data entry form to log contacts with the clients. I
want the client information (Name, Sales Rep, phone number, etc) to
autopopulate fields on the form when the client number is entered. I
have seen this done on another database I have access to, but have
been unseccessfull in recreating it. Can anyone point me in the right
direction?

Consider using a Subform Control linked to the Client ID in the main Form's
Record Source (or if the response isn't satisfactory, with code to create a
RecordSource for the Form embedded in the Subform Control that just returns
the one record). Then it will automatically populate when you move to or
select a new Client.

You can design/format the forms so that it is not obvious that it is a
Subform, if you wish.

Larry Linson
Microsoft Access MVP

[chris.nebinger]

I'm going to go a different route, and you tell me if this is what you
need.

Often, when a client requests this functionality, the only thing that
they really want to see is the data. They do not want the data saved,
and they do not want the ability to modify it. Rather, they want to
type in a customer number, and have the first/last names, phone
numbers, etc. populate to ensure that it is the correct customer.

If this is what you are looking for, then you could have a drop down
list box (combo box) that has something along the following for the
row source:

Select ClientID, CLientName, SalesRep, PhoneNumber, (etc.) from
Clients

Now, you can set the ColumnCount in the properties to the correct
number of fields returned. In the ColumnWidth property, set the
fields to be 0 for those that are not displayed. Something like: 1";
0;0;0;0;0;0

Now, you can create a text box, and for it's ControlSource, set it to:

Forms!FormName! ComboBoxName.Co lumn(1) (keep in mind it's 0 based, so
Column(1) is the second column)

Whenever you change the combo box, it will change the name as well.
6000 records shouldn't be too many, if you are pulling from a local
database. If it's coming from SQL Server, then network traffic will
impact your combo box.

Chris Nebinger

On Apr 16, 8:03 am, "Larry Linson" <boun...@localh ost.notwrote:

"lvroye" <rpelling...@co x.netwrote

I have a table of about 6000 clients which have unique client numbers.
I am creating a data entry form to log contacts with the clients. I
want the client information (Name, Sales Rep, phone number, etc) to
autopopulate fields on the form when the client number is entered. I
have seen this done on another database I have access to, but have
been unseccessfull in recreating it. Can anyone point me in the right
direction?

Consider using a Subform Control linked to the Client ID in the main Form's
Record Source (or if the response isn't satisfactory, with code to create a
RecordSource for the Form embedded in the Subform Control that just returns
the one record). Then it will automatically populate when you move to or
select a new Client.

You can design/format the forms so that it is not obvious that it is a
Subform, if you wish.

Larry Linson
Microsoft Access MVP