PLEASE HELP! creating fields on a form that autopopulate

[Guest]

Hello, I have a database that I must run reports from. The problem is the
personnal at times are entering the wrong data. I would like to set up the
form to auto populate fields once a salesperson ID code is entered. Once the
ID code is entered I would like the salespersons name and location to
autopopulate into two seperate fields. How can I do this? Any suggestions
would be welcome.

 

[Ken Snell \(MVP\)]

See this article at The ACCESS Web:
http://www.mvps.org/access/forms/frm0058.htm

It shows how to do what you seek when a combo box is used to select an ID.

 

[John Vinson]

Hello, I have a database that I must run reports from. The problem is the
personnal at times are entering the wrong data. I would like to set up the
form to auto populate fields once a salesperson ID code is entered. Once the
ID code is entered I would like the salespersons name and location to
autopopulate into two seperate fields. How can I do this? Any suggestions
would be welcome.

If you're storing the salesperson's name and location into a separate
table...

DON'T.

Storing that information redundantly is almost certainly a Bad Idea.

Instead, base the Report on a Query joining the invoice table to the
Salesperson table by SalespersonID; pull the invoice info from the
invoice table, and the name and address from the salesperson table.

For data entry, the users should have a Combo Box displaying the name
and location, and *storing* the ID.

John W. Vinson[MVP]

 

[Steve Schapel]

Susan,

This article may be of interest to you...
http://accesstips.datamanagementsolutions.biz/lookup.htm

 

[Richard W. \(Rich\) McCabe]

Susan, Look at having the salesperson ID number and the other information
you want to autopopulate in a combo-box with the ID number as the linked
field. Use the click property to assign the other values. The one form I
did this way required the name of a flower to be chosen from the vcombination
box and the color an number of petals was filled in with the following
commands: Me.txtFlowerColor = Me.CboName.Column(3)
Me.txtFlowerPetals = Me.CboName.Column(4). There is a discussion fo the
column property in the access help. Rich (e-mail address removed)

 

[Richard W. \(Rich\) McCabe]

Susan, Look at having the salesperson ID number and the other information
you want to autopopulate in a combo-box with the ID number as the linked
field. Use the click property to assign the other values. The one form I
did this way required the name of a flower to be chosen from the vcombination
box and the color an number of petals was filled in with the following
commands: Me.txtFlowerColor = Me.CboName.Column(3)
Me.txtFlowerPetals = Me.CboName.Column(4). There is a discussion fo the
column property in the access help. Rich (e-mail address removed)

 

[Guest]

Thanks Ken, I will try this when I get back to work on Monday. If I can't get
it to work I will be letting you know....lol..

 

[Guest]

If you're storing the salesperson's name and location into a separate
table...

DON'T.

Storing that information redundantly is almost certainly a Bad Idea.

Instead, base the Report on a Query joining the invoice table to the
Salesperson table by SalespersonID; pull the invoice info from the
invoice table, and the name and address from the salesperson table.

For data entry, the users should have a Combo Box displaying the name
and location, and *storing* the ID.

John W. Vinson[MVP]

 

[Guest]

Thanks Steve, I appreciate it! I will be back if I can't get one of these
posts to work. You guys are great.

 

[John Vinson]

On Sat, 1 Apr 2006 19:04:01 -0800, Susan

Hi John, Thanks for posting, however I have no where else to store
this information. I don't have invoices or anything like that. I work
for a wireless company and the database is for contract compliance.
When cells are activated I get this report that gives me the data that
must be keyed into the data base. Once the info is keyed in, we
receive the contracts any where form a day to weeks later. We go back
into the database, find the record the contract is for and enter in if
the contract was compliant or not.


It SOUNDS like you're trying to copy the data from one table into
another table. Could you explain what the REcordsource of your form
might be? What tables DO you have? Do you have a Contract table,
separate from the table of names and addresses?

John W. Vinson[MVP]

 

[Guest]

On Sat, 1 Apr 2006 19:04:01 -0800, Susan

Hi John, Thanks for posting, however I have no where else to store
this information. I don't have invoices or anything like that. I work
for a wireless company and the database is for contract compliance.
When cells are activated I get this report that gives me the data that
must be keyed into the data base. Once the info is keyed in, we
receive the contracts any where form a day to weeks later. We go back
into the database, find the record the contract is for and enter in if
the contract was compliant or not.


It SOUNDS like you're trying to copy the data from one table into
another table. Could you explain what the REcordsource of your form
might be? What tables DO you have? Do you have a Contract table,
separate from the table of names and addresses?

John W. Vinson[MVP]
yes, that is what I am trying to do. We have what we call a new activations table- that is what we are entering data into, I want to pull the salespersons ID, name and location into that table as they are entering into the new activations table. When they enter the salesperson ID, I want the salespersons name and location to auto populate in their respected fields. Can it be done?

 

[John Vinson]

Susan... You are mistaken. You do *not* want to copy the salesperson's
name and location into the new activations table.

You are using a relational database - use it relationally! One of the
*most* basic concepts of relational databases is what I call the
"Grandmother's Pantry Principle" - "A place - ONE place! - for
everything, everything in its place".

If you have a table of Salespeople, then the *only* field that you
need to put into the Activations table is the SalespersonID. If you
need to see the name of the salesperson in conjunction with other
information in the New Activations table, you can use a Query linking
the tables.

One (of many!) reasons for this: suppose salesperson Janet Hendrix
gets married and becomes Janet Richardson. Do you want to track down
every single record in New Activations (and all the other tables where
you've copied her name) and correct them all? Nope. Fix it ONCE, in
the *one* place where the name is stored, and it fixes it everywhere.

You can easily *display* the name and location on the form, for
verification; normally I'd recommend using a Combo Box to select the
salesperson, and have its bound column be the unique salesperson ID,
while its first non-zero width visible column is the name. You can
have a separate textbox on the form with a control source like

=cboSalespersonID.Column(2)

to display the third column (it's zero based) of the combo's
rowsourcee query, e.g. to display the location.

John W. Vinson[MVP]

 

[Guest]

Thank you John, you are absolutely right! I am more or less a "layman" when
it comes to access, but I am learning quickly! What an amazing software. I
did what you said and have it queried (is that spelling right?) Anyway- I am
becoming quite good at autopopulating now and using combo boxes! Thanks to
all for all your help! You guys Rock!

 

[Nancy]

I have the following:
T_Orders
T_OrderDetails
Q_Orders
Q_OrdersDetailsExtended
F_PurchaseOrder
Subform for F_PurchaseOrder is F_OrderDetails

F_CoreInformation

I now have the F_PurchaseOrder that lists my main purchase order info: i.e.
PO#, Shipping, Order Date, Vendor, etc.

My subform lists all the detail info of my purchase order: i.e.
nomenclature, part number, qty, serial number (if applicable), Unit cost,
Core Return required, and warranty info.

My Core Return is now a check box (I had a yes/no, but after rereading
Allenbrowne.com again, chose to go with just a check box) If the check box is
activated, a core return is required. When I click to activate this
checkbox, I have an event procedure, using a macro, to open
F_CoreInformation. This is now working fine. Everything is being saved in
the queries and tables as it should.

On my F_CoreInformation form, it lists the following fields:

OrderID
Core ID
Nomenclature
PartNumber
Serial number
Vendor
Return date
Shipping

Most of this information required on the F_CoreInformation is listed in the
F_PurchaseOrder & F_OrderDetails. When I activate the core return check box
and the F_CoreInformation opens up, I would like the basic info to auto
populate the F_CoreInformation . Then, I can manually enter the remaining
information that applies as it becomes available. For instance, the core may
not be returned for 2-3 weeks. I need to be able to come back and complete
the rest of the form at a later date. Does this make sense and can it be
done. If so how do I do it?

I have read the various posts about autopopulate and the suggested website
offered below, but I am not sure if I can autopopulate off the info from the
Main form and the subform.

Thank you