Problem: Combo boxes self-populating

[swatb98]

Hi, I am new to Access.

I have created one row containing input, calculation and combo boxes about
information on products and pricing. (For example, there is a QTY box in
which the user of the form inputs the quantity desired, a Part Name combo box
in which the user selects the part desired from a drop down list, and a Cost
box which displays the price of the part based on the data contained in Part
name).

In case a customer would like to order many different parts, however, I have
also created multiple rows (15), with the idea of placing one part per row.

PROBLEM 1) In a new record, once i select a part in the first row, ALL of
the other 14 Part Name combo boxes populate and default to the name of the
first part in the parts list. How can I populate only 1 combo box at a time?

PROBLEM 2) Rather than have 15 rows, could I just have 5 rows and create a
command button that (once clicked) adds another row immediately underneath
the 5th row with the same input boxes? Basically, i would compare this
command to Add Row in MS Excel. This command button would allow users to
input as many rows as they wanted.

Thanks for any help on this matter.

 

[PJFry]

For starters, let's move away from the 15 row set-up. A database is designed
to hold data that people use; you currently have it setup to hold the data
people use (the parts ordered) and the data people don't use (the rest of the
blank rows for the parts). This is a fairly common mistake when starting off
in Access.

What you want to start with is a single line on your form. Qty, cost and
your combo box. On your Property Sheet, you will be able to select
Continuous Form under Default View. That will create one record for each
part. Each part that is added will spawn a blank entry row. This address
question 2.

That being said, it sounds like you have a fundamental problem with the
table structure of your database. The best forms in the world won't solve a
poor table structure. From the brief description you gave, it sounds like
you have a Parent record, the company ordering and Child records are the
parts ordering. The parent items should reside in one table and the children
in another.

Clear as mud? Post back with follow up questions

 

[swatb98]

Thank you for your quick response.

I am definitely intrigued by your solution to #2. However, I am not quite
clear on the directions you provide. Here's what I think you are saying:

1) I should delete the other 14 rows so that I only have the first row
showing.
2) In design view, I should right-click on my Form and select Properties.
Once that's done, there is a place to select Continuous Form?

If this is correct, great, I will try it. Please clarify, though, if i am
not understanding correctly. Also, the Form currently contains other info
such as Billing Address, Shipping Address, and Installation Address (to name
a few). Do need to separate the Qty, Part Name, Cost fields from the address
data? In other words, do I need to put the Qty, Part Name, and Cost fields
into a subform? Is this possible?

Any help you can provide is greatly appreciated. Thank you.

 

[swatb98]

I did some investigating into your suggestion. I discovered what you meant
by selecting Continuous Form under Default View in the Property Sheet. So,
at least for that, I am on board.

However, I still have the question of whether I need to start from scratch.
As I wrote earlier, my form contains other fields (billing address, shipping
address, and installation address to name a few) aside from the Qty, Part
Name, Cost fields. I would like all of this info to appear on the same data
form. Is this possible or do I need to put the address fields in one form
and the Qty, Part Name, Cost fields into a subform so that they can all
appear in one form?

BTW, if talking directly is easier, please feel free to call at 515-327-0070
x101. If you feel more comfortabel via email, that's fine too. Thank you so
much for your help.

 

[PJFry]

This gets back to the Parent-Child relationships.

First, identify which data will occur once. For example, things like
company name, address, phone number. These are data elements that are not
unique to the part being ordered.

Then figure out which items are unique to the parts. For example, if one
company orders five parts, are there two or more installation addresses,
shipping addresses, billing addresses, etc. Items like Qty, part and cost
are almost certainly unique to the part.

Once you have figured that out, you can create your tables. Call the first
table something like tCustomer and the second table tParts. There will be
ONE customer with MANY parts. Remember, you don't want to store the same
information mulitple times. Once is good.

One item that must be in your tCustomer table is an AutoNum. This will
create a unique ID for each customer. You can call it CustomerID. This also
needs to be a Primary Key. If you create the table from scratch, Access will
automatically suggest that you make that field the PK.

Next, in tPart you will put all of our part specific information. Qty,
cost, etc. Also add a field to hold the CustomerID from the tCustomer. This
way you can always tell which part order belongs to which customer.

Take a stab at that. Post back with questions. The next step will be to
set up a form and subform to hold all of this.

 

[swatb98]

I really aprpeciate your help.

I followed your instructions and created two tables.

The first, called tCustomers, contains the following fields:

Quote Number (the PK, which is AutoNumber, as you requested)
Quote Date
Promise Date
ContactID
PurchaseOrder
Terms
CustomerName
EquipmentModel
EquipmentSerialNumber
BillingName
BililngAddress01
BillingAddress02
BillingCity
BillingStateRegion
BillingZipPostal
BillingCountry
BillingContractFullName
BillingContactTelephone
BillingContactTelephoneExt
BillingContactFax
BillingContactEmail
ShippingName
ShippingAddress01
ShippingAdress02
ShippingCity
ShippingStateRegion
ShippingZipPostal
ShippingCountry
ShippingContractFullName
ShippingContactTelephone
ShippingContactTelephoneExt
ShippingContactFax
ShippingContactEmail
InstallationName
InstallationAddress01
InstallationAdress02
InstallationCity
InstallationStateRegion
InstallationZipPostal
InstallationCountry
InstallationContractFullName
InstallationContactTelephone
InstallationContactTelephoneExt
InstallationContactFax
InstallationContactEmail
OrderDescription
EnteredByFullName


The second, called tParts, contains the following fields:

Quote Number (the PK, which is AutoNumber, as you requested)
Quantity
PartNum
Cost
Markup
UnitPrice
CustPrice
TotalPrice
Shipping
TaxRate
TotalTaxes
FinalPrice
Additional Notes

I await the next steps. Thanks!

 

[John W. Vinson]

The first, called tCustomers, contains the following fields:

Quote Number (the PK, which is AutoNumber, as you requested)
Quote Date
Promise Date
ContactID
PurchaseOrder
Terms
CustomerName
EquipmentModel
EquipmentSerialNumber

So you're implying that each Customer will have one, and only one, EVER,
quote, or purchase order? Surely you want repeat business?

A Customer is one kind of entity.
An Order is a DIFFERENT KIND of entity.
Typically each customer will have zero, one, *or many* Orders - so there needs
to be a one to many relationship from the table of Customers to the table of
Orders.

The same logic will presumably apply to Equipment, unless each customer has
one and only one piece of equipment of interest to your application.

Keep applying the logic! There'll be others.

 

[swatb98]

John,

Thank you for your guidance; however, I am a bit slow.
Like I said, I am new to access. Are you implying that I need one table for
Customers, one table for Parts, and one table for Quotes/Orders? I am
unclear about the interaction, then, between the form and tables.

The goal of the project is to create tables and forms so that when a
customer calls for a quote on parts, I can enter the information into a form
which will get populated in a table. My thought was that as long as I have
created a form, the same customer can call multiple times, each time
receiving a new quote.
Also, if the customer wants five different parts (just as an example), then
i can fill out the form with the information for the first part and then
click on a command button on the form that adds rows as needed (in this case,
i would click on this command button four more times) and fill out the
information for the other parts. There would then be a subtotal field that
would add all of these rows together, no matter how many rows there are.

Does this make sense?

Thank you for your patience and any guidance you can provide.

 

[PJFry]

When structuring your tables, ask the question of how often this data will
appear, one time or many times.

For example:

I can have ONE customer with MANY quotes.

So in this case, you would want a CustomerID as an autonumber. (And it must
be an autonumber. You cannot supply a number for access to use in a proper
relationship. Even in the autonumber means nothing on a functional level, it
needs to be there for Access to use)

So if we look at the table structure you laid out, Customer would be the
place to put the primary key, not quote. Your section table looks ok (just
don't call the PK exactly the same thing in the second table as it is in the
first.).

Let's pick a few fields that should be in the first table:
CustomerID - AutoNum Primary Key
BillingName
BililngAddress01
BillingAddress02
BillingCity
BillingStateRegion
BillingZipPostal
BillingCountry

Generally speaking, these items would only appear once. For the sake of
this exercise, let's assume you only deal with one billing location.

Now the second table:
QuoteID - AutoNum Primary Key
fkCustomerID - This allows you to trace the quotes back to the customer.
This data type needs to be a Long Integer
Quote Date
Promise Date
Quantity
PartNum
Cost
Markup
UnitPrice
CustPrice
TotalPrice
Shipping
TaxRate
TotalTaxes
FinalPrice
Additional Notes

Now you can have ONE customer with MANY quotes. Strictly speaking, we would
break this down even further to ONE customer with MANY quotes and ONE quote
with MANY parts. See how that flows? But for now we can concentrate on one
level.

The final idea is that you can create a main form based on the Customer data
and a subform based on the part data. When you insert the subform (quotes)
into the main form (customers) you can link the parent fields (CustomerID)
with the child fields (fkCustomerID) and all the data entered into the parts
subform will record that CustomerID.

I know these seems tedious, but you will be way ahead of the game if you get
the set-up right at this level.

Post back with questions.
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

 

[John W. Vinson]

John,

Thank you for your guidance; however, I am a bit slow.
Like I said, I am new to access. Are you implying that I need one table for
Customers, one table for Parts, and one table for Quotes/Orders?

Yes, exactly. You need to identify what kinds of "Entities" - real-life
people, things, or events - are of importance to your application. Each
different type of Entity gets *its own table*. tCustomers should have
information about Customers - AND ABOUT NOTHING ELSE. Every customer will have
one, and only one, record in tCustomers, and each record in tCustomers will
contain the information you need to know about that customer as an entity.

Each Quote table record will have information about one and only one Quote. It
won't contain any customer information *except* for the unique CustomerID, as
a link to tCustomers.

I am
unclear about the interaction, then, between the form and tables.

The Form - or a Form with one or more subforms, typically! - is just a window,
a tool to display and allow editing of data stored in a Table or Tables.

The goal of the project is to create tables and forms so that when a
customer calls for a quote on parts, I can enter the information into a form
which will get populated in a table. My thought was that as long as I have
created a form, the same customer can call multiple times, each time
receiving a new quote.

Exactly. Each quote would be a new record; all of the quotes for a given
customer would have that customer's CustomerID in the table.

Also, if the customer wants five different parts (just as an example), then
i can fill out the form with the information for the first part and then
click on a command button on the form that adds rows as needed (in this case,
i would click on this command button four more times) and fill out the
information for the other parts. There would then be a subtotal field that
would add all of these rows together, no matter how many rows there are.

Again, absolutely - except that this information would be stored in two
tables, not in one! You would not need to - or want to! - store things such as
the quote date or the person creating the quote five times. You would instead
have a "QuoteDetails" table, with a link to the Quote table's primary key, and
one record for each part in the order.

Does this make sense?

Thank you for your patience and any guidance you can provide.

Take a look at the Orders form in the Northwind sample database that came with
Access. It demonstrates the principles involved pretty well (though there are
things I'd do differently). For some more resources see:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[swatb98]

Here's the latest.


I setup a parent form/table called uCustomerBilling with the following fields:

CustomerID (PK, Autonumber)
Billing Name
Billing Address 01
Billing Address 02
Billing City
Billing State/Region
BillingZipPostal
BillingCountry
BillingContactFullName
BillingContactTelephone
BillingContactExtension
BillingContactFax
BillingEmail
ShippingMethod
ShippingName
ShippingAddress 01
ShippingAddress 02
ShippingCity
ShippingState/Region
ShippingZipPostal
ShippingCountry
ShippingContactFullName
ShippingContactTelephone
ShippingContactExtension
ShippingContactFax
ShippingContactEmail
InstallationName
InstallationAddress 01
InstallationAddress 02
InstallationCity
InstallationState/Region
InstallationZipPostal
InstallationCountry
InstallationContactFullName
InstallationContactTelephone
InstallationContactExtension
InstallationContactFax
InstallationEmail

Next, I setup a child subform called uQuoteDetails with the following fields:

QuoteID (PK, Autonumber)
fkCustomerID
QuoteDate
PromiseDate
ContractID
PurchaseOrder
Terms
EnteredByFullName
Total Price (not actually displayed in subform)
Shipping (not actually displayed in subform)
TaxRate (not actually displayed in subform)
TotalTaxes (not actually displayed in subform)
FinalPrice (not actually displayed in subform)
AdditionalNotes (not actually displayed in subform)


Next, I setup a child subform called uParts with the following fields:

PartID (PK, Autonumber)
fkQuoteID (long integer)
Quantity
PartNumDesc
Cost
Markup
UnitPrice
Discount
CustPrice

Finally, from the uQuoteDetails (uQuoteDetails subform 2), I created another
child subform on the parent with the following fields:

QuoteID (PK, Autonumber) (not actually displayed in subform)
fkCustomerID (not actually displayed in subform)
QuoteDate (not actually displayed in subform)
PromiseDate (not actually displayed in subform)
ContractID (not actually displayed in subform)
PurchaseOrder (not actually displayed in subform)
Terms (not actually displayed in subform)
EnteredByFullName (not actually displayed in subform)
Total Price
Shipping
TaxRate
TotalTaxes
FinalPrice
AdditionalNotes


So, my questions are as follows:
#1) is this the proper setup?
#2) how can i format the subforms so that they appear similar to the parent
form (i.e., i do not want them to appear as an access DB; i'd prefer a
regular form format)
#3) how do i setup uQuoteDetailsSubform 2 to work out the math and sum all
of the parts found in uParts?
#4) do you notice any other design flaws?

Thanks again for your help.

 

[swatb98]

I have figured out the question to #2 (i just needed to switch the subforms
from Datasheet to Continuous).

I still have some questions though and your help is truly appreciated:

1) In the uParts subform, how do i add additional parts in different rows
such that the additional parts would appear underneath the first part instead
of in a new record?

2) If in Form view, the cursor starts in the first field of the first
subform (uQuoteDetails) and i can tab my way through the different fields of
that subform, how can i then tab to the first field in the main form
(uCustomerBilling) once i reach the final field in uQuote Details? And, of
course, how can tab my way to the first field in the next subform (uParts)
once i reach the last field in uCustomer Billing?

3) How does the subform uQuoteDetails subform 2 total the price of all of
the parts entered into the uParts subform?

Thanks so much!