Form/Subform?

[susan]

I have:
1. a form that a user use to enter info like buyer name, supplier name,
program #, part #, part description, current price and volume.
2. a table that has the standard supplier name, part # and price unique to
specific program#.

I want the form to automatically populate certain fields based on the
program # entered instead of filling out all the information manually.

for example, when the user type in a program # that already exist in the
table, the fields "price, part# and supplier name" will be populated on the
form. if the program # doesn't exist, then nothing happens and the user will
update manually.

is this possible? do i use a form/subform to do this?
i am using access 2003 and am new to forms/access.

thanks!
susan

 

[Golfinray]

You could just use a single form. Allow the form wizard to build the form for
you and then get the formatting the way you want it. You may have to go into
design view and move things around to get them to display exactly as needed.
Then put a combo box (from the toolbar) on your form. Allow the combo box
wizard to build it for you, using either the program #, buyer, or supplier,
whichever you wouls like to use. The after the combo is in place, right click
on the combo box to get properties. In propeties there will be one property
called events. Go to events. On the afterupdate event, click on the button to
the right of the event and select code builder. In code builder, type:
Me.filter = "[either program# or the other] = """ & Me.combo# & """"
Me.filteron = true

When you go into code builder the combo box # will be listed, like combo2 or
combo16. Put that in place of combo#. Save and close the code builder. Now
your form will autopopulate for you based on the selection you make in the
combo box.

 

[Beetle]

Before you get too involved in creating forms, I have a question.

The following statement;

2. a table that has the standard supplier name, part # and price unique to
specific program#.

would seem to imply that you have all of your information in one table. If
that is the case, you need to address this issue before you go about creating
your form(s). Otherwise, you'll just have to create all new forms sometime
down the road after you discover that your one table approach is not
workable.

Or perhaps I misunderstood what you said.

 

[susan]

beetle: you actually helped me create my form yesterday ( i asked about the
calculations using prices and volume).

here's what i am trying to do: users are filling out an excel spreadsheet
for product information (supplier name, price, volume, dates, po#, etc.) and
i want to store the information in a db instead of in excel. so instead of
having me uploading the excel spreadsheets into a db, i am creating a form
for users to directly enter info into access - that's why i created my first
form.

the reason for the subform is: some of the information that users enter may
already have a standard value...for example if user put in program #1, there
is already a standard price and supplier for program #1 so instead of having
the user type those again, we want the form to automatically populate.
that's where the table comes from....

the table has the standard price/supplier name/etc. for certain program
#...so if the user put in a program # that is new, then we want them to fill
out all the info...but if it's a program # already on the list, then we want
the field to populate so users don't waste time filling them out again.

i hope this make sense...

suggestions?

thanks again,
susan

 

[Beetle]

I agree that it will be easier to have your users enter data directly into
Access, rather than importing from Excel, but you need to be aware that
Access and Excel are two completely different animals.

For example, let's look at two of your entities of data. I don't know how
your Program# relates to the rest of your data, so I'll just stick to
Suppliers and Products for now. In Excel the data might look like;

Supplier Name Product No. Description Price
ABC Products 123 Widget $1.00
ABC Products 456 Thing $2.00
XYZ Products 321 Doohickey $1.50


However, in Access a Supplier and the Products they provide are
considered two separate entities. They are related, but they are not
the same entity, so they belong in two separate tables. The table
structure might look like;

tblSuppliers
*********
SupplierID (autonumber primary key)
SupplierName
Address
City
State


tblProducts
********
ProductID (primary key)
SupplierID (foreign key to Suppliers table)
PartNumber
Description
Price

So the same data in Access looks like;

(Suppliers table)

SupplierID SupplierName Address City State
1 ABC Products 123 1st st. Chicago IL
2 XYZ Products 345 2nd st. NY NY

(Products table)

ProductID SupplierID PartNo Desc Price
1 1 123 Widget $1.00
2 1 456 Thing $2.00
3 2 321 Doohickey $1.50


Then you would typically setup a Main form / subform with the Main
form based on the Suppliers table and the subform based on the
Products table. The link between the main form and subform
would be SupplierID.

Apologies if I'm oversimplifying or telling you things you already know, I'm
just trying to clarify the point that treating Access like a spreadsheet
(which seems like what you are currently doing) will only lead to
problems later on. In Access, you create a table for each "entity" of
of your data, and each "attribute" of that entity becomes a field in that
table.

 

[susan]

thanks beetle for the explaination - i am totally new to access so i don't
already know what you just told me. i do need to digest your suggestion and
try it out to see if it will work. i will definitely post again if i get
stuck! thanks! susan