using a buttom to add products to an invoice

[csumb]

i have a table called products
and a form with buttons for each product
i want to be able to press a button and it adds the product to the customers
invoice.
if i click the same button twice it would double the order.

examble: if i click on a button called mug it would add the information
about the mug to the customers order and place 12 ea in the qty field. then
if i click it again it would change the 12 to 24 and so on.

I also need to keep this order for future reference.

can anyone help
thanks

 

[Graham Mandeno]

Hi csumb

I'm going to make some assumptions about your database structure. If my
assumptions are incorrect then you should redesign your database so that my
assumptions *are* correct <g>

You have the following tables:

Customers
========
cstID (autonumber, primary key)
cstName
.... other customer details - address, phone, etc

Products
=======
prdID (autonumber, primary key)
prdName (e.g "Mug")
prdUnitName (e.g. "ea")
prdUnitPrice
prdUnitsOfSale (e.g. 12)

Orders
=====
ordNumber (primary key)
ordCustomer (CstID of the customer placing the order)
ordDate
ordStatus (new, packed, despatched, completed, cancelled, etc)

OrderItems
========
odiOrder (order number of the related order)
odiProduct (PrdID of the product being ordered)
odiUnits (number of units on order)
odiUnitPrice
odiDiscount (percent discount)

You have set up the following one-to-many relationships:
cstID -< ordCustomer
ordNumber -< odiOrder
prdID -< odiProduct

Now, you say you want a button on your form for each product. Of course you
don't want to have to modify your form design every time you add a new
product, so you do this with a continuous subform bound to the Products
table. Each record has a textbox bound to the PrdName field that *looks*
like a button (raised, centred text, etc, and locked).

You also have a standard form/subform for the Orders and OrderItems (similar
to that in the NorthWind sample database). You place your "product
selector" subform also on this main order form (or maybe it would work
better as a popup). Let's say the main form is named frmOrders and the
OrderItems subform is in a control named sbfOrderItems.

Now, all you need is a Click event procedure for the "button" textbox on
your product selector form. This procedure must do the following:
1. Search for a record in sbfOrderItems corresponding to the product being
clicked.
2. If there is none, create a new record, filling in odiProduct and
odiUnitPrice and setting odiUnits to prdUnitsOfSale.
3. If there is already a record for that product, increase odiUnits by
prdUnitsOfSale.

Something like this:

Private Sub txtProductButton_Click()
Dim f as Form
Set f = Forms!frmOrders!sbfOrderItems.Form
With f.RecordsetClone
.FindFirst "odiProduct=" & Me.prdID
If .NoMatch then
.AddNew
!odiProduct = Me.prdID
!odiUnitPrice = Me.prdUnitPrice
!odiUnits = Me.prdUnitsOfSale
.Update
f.Requery
.FindFirst "odiProduct=" & Me.prdID
Else
!odiUnits = !odiUnits + Me.prdUnitsOfSale
End If
f.Bookmark = .Bookmark
End With
End Sub

 

[csumb]

Thanks for the info.
what i am trying to do is set up a form lets say for mugs. it would have a
button with a bmp of the mug for customers to look at. If the customer
decides they want to purchase that pitcular mug, I can click the piture and
and it would add it to their invoice. If i click it twice it would douible
the amount. example each time i click the picture it would add 12 to the
quantity field for that item, as this item is sold in qty of 12.

is this possible?

is it also possible to have just the items the customer ordered show up as a
bmp with the amount ordered on a different form so the customer can view and
verify
that this is what they want to order?
I have the data base built but have been trying to see if I can make it work
with just
clicking pictures ( bmp).

Thanks for your help and your last reply was helpful

 

[Graham Mandeno]

So does your Products table include a field containing the picture of the
product?

If so, then include in in a bound object frame on your product selector
form, and also on the form showing the items that have been ordered.

I would recommend that you store your pictures outside the database and just
store the filename in a text field. This will greatly reduce the likelihood
of database corruption, but it will have the minor disadvantage of not being
able to show the pictures of multiple products on a continuous form.

A workaround is to place an image control in the header or footer of your
continuous form and load the image for the current record when the record
changes (in the Form_Current event procedure).
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand