f



Autopopulating fields in a form, MS Access 2000

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?

0
4/15/2007 10:56:19 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

5 Replies
793 Views

Similar Articles

[PageSpeed] 2

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

"lvroye" <rpellington@cox.net> wrote in message 
news:1176677779.920120.301110@y80g2000hsf.googlegroups.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?
> 


0
4/15/2007 11:34:12 PM
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.openrecordset("select * from tblClientInfo where
clientnum = " & cstr(me!tboClientNum, dbopensnapshot)

if rst.recordcount > 0 then
   me!tboclientname = rst!clientfirstname & " " & rst!clientfamilyname
   me!tboaddress= rst!clientstreet & " " & rst!clientcity
   me!tbophone= rst!clientareacode & " " & rst!clientmainphone


endif

On Apr 16, 8:56 am, "lvroye" <rpelling...@cox.net> wrote:
> 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?


0
4/16/2007 1:12:28 AM
"lvroye" <rpellington@cox.net> schreef in bericht =
news:1176677779.920120.301110@y80g2000hsf.googlegroups.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
0
4/16/2007 6:40:11 AM
"lvroye" <rpellington@cox.net> wrote

> 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 


0
bouncer (4168)
4/16/2007 2:03:01 PM
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.Column(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...@localhost.not> wrote:
> "lvroye" <rpelling...@cox.net> wrote
>
> > 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


0
4/16/2007 5:15:55 PM
Reply: