By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,499 Members | 829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,499 IT Pros & Developers. It's quick & easy.

How to create a quote not an order

P: n/a
I have several tables in my database. I use these to create 'quotations'
that show product code, description, selling price and quantity required.
Users can enter as many lines as possible and a running $ total is updated
after each line is updated. At the end of the 'quote' I simply want to press
a button, clear the inputted data and restart a new 'quotation'. Any help
would be appreciated.

I just don't know what code to put against such a control button.

Regards

Upsy
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Peeter Ups wrote:
I have several tables in my database. I use these to create 'quotations'
that show product code, description, selling price and quantity required.
Users can enter as many lines as possible and a running $ total is updated
after each line is updated. At the end of the 'quote' I simply want to press
a button, clear the inputted data and restart a new 'quotation'. Any help
would be appreciated.

I just don't know what code to put against such a control button.

Regards

Upsy


I would create a continous form. Your layout may be very similar to a datasheet
(Tabular). You can do this via the FormDesigner wizard. Once built, get into
design mode of the form. In the footer, create a text box and drag it under the
price field.

Let's say the price field is called Price. In the footer textbox, get the
property sheet open and in the ControlSource in the Data tab enter
=Sum([Price])
If Price is a calculated field you might have to change it
=Sum([ItemPrice] * [ItemQty])
to acheive the extended price.

Each time you enter a value, the tot price in the footer shold change.

As far as a command button....you are a little hazy about what you want to occur
so....create a query to delete the records in the quotes table and in the query
you can do something like
Docmd.SetWarnings False
Docmd.OpenQuery "YourDeleteQuery"
Docmd.SetWarnings True
and put this code in the command button OnClick event.

If you want to save them....My recommendations is you understand what a form and
subform is. IOW, you would have a a table called Quotes and another called
QUoteItems. Build a form/subform. When you switch to a new quote, if a new
quote all items are blank else the existing items are displayed.
Nov 12 '05 #2

P: n/a
"Peeter Ups" <up**@austarnet.com.au> wrote in message news:<bu**********@austar-news.austar.net.au>...
I have several tables in my database. I use these to create 'quotations'
that show product code, description, selling price and quantity required.
Users can enter as many lines as possible and a running $ total is updated
after each line is updated. At the end of the 'quote' I simply want to press
a button, clear the inputted data and restart a new 'quotation'. Any help
would be appreciated.

I just don't know what code to put against such a control button.

Regards

Upsy


Trevor Best could probably answer this better than I, but anyway, here
goes... Just set a flag in your "Invoice" table, which indicates
whether this is a quote or a sale. Then you would just filter for
'quote'/'sale' to get totals for each. No need for another table. If
you don't want to keep the data at all for a quote, you could just
delete the records... I'm not sure a Form.Undo would do it...
Nov 12 '05 #3

P: n/a
Thanx guys - much appreciated ..... BUT .....

1) Yes Pieter, I DO NOTwant to keep the records at all. I just want to see a
total $ figure, give the guy on the end of the phone that $ figure, hang up
from him and wait for another prospective customer to ring. I would then hit
the 'New Quotation' button and 'delete' (thanx Salad - that works
beautifully) the quoted lines and start with a new quotation with a blank
screen.

2) When I run the delete SQL it asks if I want to delete the records. Is
there a option to ignore this question and to just do the deletion?

3) After it has done the deletion it does not clear the screen of the
entered lines. How do I clear the screen?

4) When I go to enter new lines the screen shows '#Deleted' in several
fields for each of the lines entered in the previous quote. How do I get rid
of these? I just want a blank screen like when I first entered the form.

Thanx guys.

"Salad" <oi*@vinegar.com> wrote in message
news:40***************@vinegar.com...
Peeter Ups wrote:
I have several tables in my database. I use these to create 'quotations'
that show product code, description, selling price and quantity required. Users can enter as many lines as possible and a running $ total is updated after each line is updated. At the end of the 'quote' I simply want to press a button, clear the inputted data and restart a new 'quotation'. Any help would be appreciated.

I just don't know what code to put against such a control button.

Regards

Upsy
I would create a continous form. Your layout may be very similar to a

datasheet (Tabular). You can do this via the FormDesigner wizard. Once built, get into design mode of the form. In the footer, create a text box and drag it under the price field.

Let's say the price field is called Price. In the footer textbox, get the
property sheet open and in the ControlSource in the Data tab enter
=Sum([Price])
If Price is a calculated field you might have to change it
=Sum([ItemPrice] * [ItemQty])
to acheive the extended price.

Each time you enter a value, the tot price in the footer shold change.

As far as a command button....you are a little hazy about what you want to occur so....create a query to delete the records in the quotes table and in the query you can do something like
Docmd.SetWarnings False
Docmd.OpenQuery "YourDeleteQuery"
Docmd.SetWarnings True
and put this code in the command button OnClick event.

If you want to save them....My recommendations is you understand what a form and subform is. IOW, you would have a a table called Quotes and another called QUoteItems. Build a form/subform. When you switch to a new quote, if a new quote all items are blank else the existing items are displayed.

Nov 12 '05 #4

P: n/a
"Peeter Ups" <up**@austarnet.com.au> wrote in message news:<bu**********@austar-news.austar.net.au>...
Thanx guys - much appreciated ..... BUT .....

1) Yes Pieter, I DO NOTwant to keep the records at all. I just want to see a
total $ figure, give the guy on the end of the phone that $ figure, hang up
from him and wait for another prospective customer to ring. I would then hit
the 'New Quotation' button and 'delete' (thanx Salad - that works
beautifully) the quoted lines and start with a new quotation with a blank
screen.
One idea...
you could base a form/subform on Quotes/QuoteItems and then if the
customer accepts the quote, append the records from Quotes and
QuoteItems to the Invoices/InvoiceItems tables. After that, you could
do something like:

dim db As DAO.Database
Set db=CurrentDB

DoCmd.SetWarnings False
'append parent Invoice record to real invoice table

db.Execute "INSERT INTO Invoices ( CustomerID, FirstName, LastName,
Address, City, State, ZipCode, PhoneNo, MapscoPage ) IN
'F:\SaveUs\Comcast2.mdb'
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName,
Customer.Address, Customer.City, Customer.State, Customer.ZipCode,
Customer.PhoneNo, Customer.MapscoPage FROM Quotes;"

'append child records into real invoice details table
db.Execute ""INSERT INTO InvoiceItems( CustomerID, FirstName,
LastName, Address, City, State, ZipCode, PhoneNo, MapscoPage ) IN
'F:\SaveUs\Comcast2.mdb'
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName,
Customer.Address, Customer.City, Customer.State, Customer.ZipCode,
Customer.PhoneNo, Customer.MapscoPage FROM QuoteItems;"

db.Execute "DELETE * FROM Quotes",dbFailOnError
db.Execute "DELETE * FROM QuoteItems", dbFailOnError
DoCmd SetWarnings True

....

2) When I run the delete SQL it asks if I want to delete the records. Is
there a option to ignore this question and to just do the deletion?
Use DoCmd.SetWarnings False to turn off the warning messages
but BE SURE to turn them back on at the end of the code AND in the
error trapping of your procedure.

3) After it has done the deletion it does not clear the screen of the
entered lines. How do I clear the screen?
Me.Requery will force the form's recordsource to be requried, and if
there are no records in the table, you won't get anything in the
form...

4) When I go to enter new lines the screen shows '#Deleted' in several
fields for each of the lines entered in the previous quote. How do I get rid
of these? I just want a blank screen like when I first entered the form.

One or more of the following
Me.Requery
Me.Refresh
Me.Repaint 'repaints the form.
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.