create tables from form etc

[Guest]

I am just starting Access. Can one enter data in a form and create tables from the data entered? Also I have created an invoice in Design View. Three of the fields will be Units Ordered, Quantity and Price. I would like to fill each of these in (depending on the order) from a drop down list. I then need to multiply Quantity x Price to get a Subtotal. I then need all the Subtotals to automatically get a Grand Subtotal (I would then add S&H 7% min $3.95) to this + Sales Tax if appropriate to get Total - any advice on how to do this greatly appreciated - thanks.

 

[Douglas J. Steele]

That's the opposite of how it's done. You don't create tables from forms.
Rather, you create the tables, then you use the forms to provide a "window"
into your table. As well, computed fields (such as Subtotal and Grand
Subtotal) shouldn't be stored in the table: recalculate them if required.

You might take a look at the Northwinds sample database that came with
Access: it has specific examples handing invoices.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am just starting Access. Can one enter data in a form and create tables

from the data entered? Also I have created an invoice in Design View. Three
of the fields will be Units Ordered, Quantity and Price. I would like to
fill each of these in (depending on the order) from a drop down list. I then
need to multiply Quantity x Price to get a Subtotal. I then need all the
Subtotals to automatically get a Grand Subtotal (I would then add S&H 7% min
$3.95) to this + Sales Tax if appropriate to get Total - any advice on how
to do this greatly appreciated - thanks.

 

[Guest]

Yes I know but I couldn't create a form from the table so I created the form first. I have now created the table but still cannot get them to connect - keeps coming up with Runtime Error 2452. I right click on form but cannot see anywhere to alter to make connection. It keeps asking me to debug but the info that comes up is, at present, a foreign language to me. Is there a tutorial with Northwinds sample database indicating how it was built? Also say in the table I have a customer who wants to order five items - at present it is creating five records when I want them all on one record - is this possible - thanks for your patience.

 

[Reggie]

tei, Open your form in design view. Before clicking on anything select the
View/Properties option from the main menu at the top. On the All tab the
first item is Record Source. Click the drop down box and select the table
or query you want to use for the record source. Close this window. Before
clicking on anything again select View/Field List from the main menu. A
window will pop up with the available fields from your record source. As for
a customer ordering 5 items all you have to do is use a quantity to order
field..

--
Reggie

----------

Yes I know but I couldn't create a form from the table so I created the

form first. I have now created the table but still cannot get them to
connect - keeps coming up with Runtime Error 2452. I right click on form but
cannot see anywhere to alter to make connection. It keeps asking me to debug
but the info that comes up is, at present, a foreign language to me. Is
there a tutorial with Northwinds sample database indicating how it was
built? Also say in the table I have a customer who wants to order five
items - at present it is creating five records when I want them all on one
record - is this possible - thanks for your patience.novice stage - hope I don't grow old before then!

 

[Guest]

Hi Reggie, Thank you for your info. I followed your instructions and the View/Field List shows the correct headings and although the form indicates the correct number of records (2) I cannot see anything else except "#Name?" so I am not sure what is happening - thanks.

 

[Reggie]

tei, click on each control on your form and open the properties window.
Make sure the control source is set the one of the available fields from the
drop-down list(which should be the same as your View/Fields). This is for
controls that are bound to fields within your recordset. The other thing to
check is try opening the table/query by itself to see if you are in fact
getting records returned.

--
Reggie

----------

Hi Reggie, Thank you for your info. I followed your instructions and the

View/Field List shows the correct headings and although the form indicates
the correct number of records (2) I cannot see anything else except "#Name?"
so I am not sure what is happening - thanks.

--
Take care, David

tei, Open your form in design view. Before clicking on anything select the
View/Properties option from the main menu at the top. On the All tab the
first item is Record Source. Click the drop down box and select the table
or query you want to use for the record source. Close this window. Before
clicking on anything again select View/Field List from the main menu. A
window will pop up with the available fields from your record source. As for
a customer ordering 5 items all you have to do is use a quantity to order
field..

--
Reggie

----------

Yes I know but I couldn't create a form from the table so I created

the
form first. I have now created the table but still cannot get them to
connect - keeps coming up with Runtime Error 2452. I right click on form but
cannot see anywhere to alter to make connection. It keeps asking me to debug
but the info that comes up is, at present, a foreign language to me. Is
there a tutorial with Northwinds sample database indicating how it was
built? Also say in the table I have a customer who wants to order five
items - at present it is creating five records when I want them all on one
record - is this possible - thanks for your patience. complete
novice stage - hope I don't grow old before then!

:

That's the opposite of how it's done. You don't create tables from forms.
Rather, you create the tables, then you use the forms to provide a "window"
into your table. As well, computed fields (such as Subtotal and Grand
Subtotal) shouldn't be stored in the table: recalculate them if required.

You might take a look at the Northwinds sample database that came with
Access: it has specific examples handing invoices.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am just starting Access. Can one enter data in a form and create tables
from the data entered? Also I have created an invoice in Design

View.
Three

of the fields will be Units Ordered, Quantity and Price. I would like to
fill each of these in (depending on the order) from a drop down

list. I
then

need to multiply Quantity x Price to get a Subtotal. I then need all the
Subtotals to automatically get a Grand Subtotal (I would then add

S&H 7%
min

$3.95) to this + Sales Tax if appropriate to get Total - any advice

on
how

to do this greatly appreciated - thanks.

 

[Guest]

thanks, I'm afraid it is a bit of a mess - say I right click School in file frmInvoice - then on control source - 3 columns in Expression Builder. I click on the appropriate table in first column (tblOrders) - the right drop down list appears in column 2 (only "<value>" is in column 3). In the top dialog box should it just say School as in column 2 or should it say "[tblOrders]![School]" in dialog box (get this by double clicking on School in column 2). Once I know this I'll go through all controls and ascertain that they are bound correctly - we're getting there -thanks. David

 

[Guest]

thanks but I worked that out - I'm sure I'll be back - is it best to carry on this thread or start a new one? - your help greatly appreciated.
--
Take care, David

thanks, I'm afraid it is a bit of a mess - say I right click School in file frmInvoice - then on control source - 3 columns in Expression Builder. I click on the appropriate table in first column (tblOrders) - the right drop down list appears in column 2 (only "<value>" is in column 3). In the top dialog box should it just say School as in column 2 or should it say "[tblOrders]![School]" in dialog box (get this by double clicking on School in column 2). Once I know this I'll go through all controls and ascertain that they are bound correctly - we're getting there -thanks. David
--
Take care, David

tei, click on each control on your form and open the properties window.
Make sure the control source is set the one of the available fields from the
drop-down list(which should be the same as your View/Fields). This is for
controls that are bound to fields within your recordset. The other thing to
check is try opening the table/query by itself to see if you are in fact
getting records returned.

--
Reggie

----------

View/Field List shows the correct headings and although the form indicates
the correct number of records (2) I cannot see anything else except "#Name?"
so I am not sure what is happening - thanks.

 

[Guest]

Hope you don't mind me posting again. If a client purchasers say 4 items of papyrus - first is blank papyrus 13 @ $1.30, second Tutankhamen 1@ $1.30, third is Nubian Princess 1@ $1.30, fourth Map of Roman Empire 5@ $1.30. They are all same purchaser - how do I enter them so they are all entered for ID1? Can I then create form so Quantity automatically multiplies Price to give Subtotal of each then how would I create so that all Subtotals automatically total - I can then add Sales Tax and S&H manually but want Total Price to automatically calculate. Finally I would manually insert Amount Paid so that Amount Owing automatically calculates (usually nil) - thanks in advance.

 

[Reggie]

David, I would start by looking at the example of the Orders form in the
Northwind sample database that ships with access. On my machine it's in
C:\Program Files\Microsoft Office\Office\Samples folder. If you can find it
do a search for Northwind.mdb. If you still can't locate it let me know and
I will email a copy to you. I'm using Office2K/WXP Pro. Let me know. Not
trying to side-step your question, but I think you will get much more out of
seeing it for yourself and checking out how the queries/tables/relationships
etc. are setup.

--
Reggie

----------

Hope you don't mind me posting again. If a client purchasers say 4 items

of papyrus - first is blank papyrus 13 @ $1.30, second Tutankhamen 1@ $1.30,
third is Nubian Princess 1@ $1.30, fourth Map of Roman Empire 5@ $1.30. They
are all same purchaser - how do I enter them so they are all entered for
ID1? Can I then create form so Quantity automatically multiplies Price to
give Subtotal of each then how would I create so that all Subtotals
automatically total - I can then add Sales Tax and S&H manually but want
Total Price to automatically calculate. Finally I would manually insert
Amount Paid so that Amount Owing automatically calculates (usually nil) -
thanks in advance.tables from the data entered? Also I have created an invoice in Design View.
Three of the fields will be Units Ordered, Quantity and Price. I would like
to fill each of these in (depending on the order) from a drop down list. I
then need to multiply Quantity x Price to get a Subtotal. I then need all
the Subtotals to automatically get a Grand Subtotal (I would then add S&H 7%
min $3.95) to this + Sales Tax if appropriate to get Total - any advice on
how to do this greatly appreciated - thanks.

 

[Charles M. Fish, Sr.]

Tei,

I was where you were not too long ago with becoming familiar with
Access. I bought a Learnkey Access 2000 tutorial on 3 CDs on eBay a few
weeks back. It will tell you exactly everything you are looking for.
It tells you how to make a copy your Northwinds database (so you won't
zap the original); and how to design an Orders database that's precisely
what your asking.

But if your installed version of Access won't let you design tables, I
can only suggest that you fix your version or you acquire a completely
functional version.

Consider the time you've spent thus far as 'educational'. It's really
best if you design your tables first.

Here's a design hint: (BTW, set your current Form aside.)

You'll need several tables

Customer
Order
OrderItems
Products
ProductCategories

You might be in a hurry, but for you to have what you want in a hurry
won't come about unless someone does it for you; or you become more
familiar with designing a database quickly. With a tutorial (or as you
mentioned that you would be attending an Access class) you can
accomplish what you want.

C
==