Default price changes 1

[TadyB]

Hey again! I know this has been asked before, but I don't understand the answers that well...I'm very new!

I doing a program to write up food orders. As of now, I just have an unbound text box next to the item that shows its price.

The price amount is set in the default value in that text box.

I call upon that price in a control source calculation for the order total.

Everything works fine, but I realized a problem. I want my user to be able to update prices for an item when necessary.

I know I will have to build a table to store prices.

How should this table be formed and and how do I build an "update prices" form that would actually change the defalt value of a field? Is this even possible?

Thanks,
Stacey

 

[willir]

Stacey

Do you have an inventory or product table?

Instead if using the form value to set a price, it should be controlled by a pricing table.

The barbones of a typical order entry system

CustomerTbl
CustomerCode - primary key
CustomerName

InventoryTbl
InventoryID - primary key
InventoryName
InventoryPrice
QuantityOnHand
QuantityOnOrder

OrderTbl
OrderNumber - primary key
CustomerCode - foreign key to customer table

OrderDetail
OrderDetailID - primary key
OrderNumber - foreign key to order table
InventoryID - foreign key to inventory table
OrderQuantity
OrderPrice - use inventory table for default price with over-ride ability

Richard

 

[TadyB]

Hey Richard...thanks for the help.

I have it pretty much set up like that. But, I don't understand how to do the last part. What is over-ride ability for the default price? I've never heard of that before. How would you go about doing that?

Thanks again,
Stacey

 

[willir]

Stacey

If you have a similar design, then what you want shall be failry easy.

Usng the OrderDetail table as basis of the OrderDetail subform, the subform would normally be a contineous subform, set up the InvetoryID (which on your system may have a different name) as a combo box. When you create the combo box, the wizard would take you through much of the work.

For the On Change event, setup an event procedure that retrieves the inventory record, including the price. The InventoryPrice is displayed in the field used for the OrderPrice.

Me.OrderPrice = VariableUsedToStoreInventoryPrice

Hint:
You can use one combo box to retrieve the inventory record. Assuming the combo box uses a query similar to

SELECT InventoryID, InventoryName, InventoryPrice from InventoryTbl WHERE InventoryID = Me.InventoryComboBox

The wizard will usually hide the InventoryID field, and display the InventoryName in the combo box.

The order is important in reference to the SELECT statement. The column number can be referenced to grab the data from the select statment.

Me.OrderPrice = Me.InventoryComboBox.Column(2)

Access VBA, as in many programming languages will often start counting from "0".

Richard

 

[TadyB]

Thank you so much for your willingness to help, but I think I am in way over my head. I should probably trash this project. I am a self-taught Access person who has only done 2 other programs and I don't know VBA. I know how to do things, but I don't know what it's all called. So, most of the stuff you said, I didn't understand. Sorry.
My biggest problem is that I'm not even sure if I set up my tables correctly. I would have to go into so much to explain it all, I feel like I will be wasting this board's time, especially since I don't explain things well. I'm sorry.

Thanks again,
Stacey

 

[willir]

Stacey
I understand your frustration. It is hard to judge one's technical experience via these posts. My response, which was pretty technical, was based on your comment of setting defaults for a field.

If you want to persue this, and invest perhaps another two hours, I am sure we can muddle through this with you.

The review would include...
- review your current table designs
- setup the required form and subforms

Richard

 

[TadyB]

Hi. Thanks for the understanding. I have decided to do it even if it is not the best way to be done (if that makes any sense). I have a time pressure thing going on here that is making me crazy. But I do want to learn the proper way so that I could change it one day in the future. So, if you have the time and patience, I will try to go through what I have done.

A local swim club (small, only about 179 members) asked me to do a program for them that would: keep track of members and their guests; keep track of inventory and create invoices; AND (my headache) replace the register to ring up the snack bar sales.

As of now, they only have one computer that is for Quick Books and email--the one I went in to fix when they sprung this upon me--I thought it would be a fun undertaking. hehehehe

So, I am building the two other systems needed and networking them together. The front computer will be for member check-in, the office computer will be for reports and invoices, and the third will be for the snack bar. I have the first two done.

I have four databases set up. Three to hold the forms and reports for the systems above and the fourth for to hold all the data to be shared. All three are linked to the fourth. I don't have a server dedicated to this.

The front desk and manager programs work perfect.

Here is how I have set up the "cash register" database that I think needs to be done in a more efficient way. Or, it could be right.
I have 5 tables:

Members
Orders
Drinks
Sandwich
Snack

I know the last 3--Drinks, Sandwich, Snack--seem unnecessary and I'm missing the Order Details table. But this is the only way I could get it to work the way I wanted. The example you gave above is almost identical to one I've already created. So, I understand it. But I don't get how to make it look like what I've done.

In the Drinks, Sandwich, & Snack tables each food item is a field. That field stores that item's quantity. There is also a key field--DrinkID, SandwichID, SnackID. Along with OrderID to link it with the main Orders table.

My Orders table has these fields:
OrderID --key field
OrderDate --defaults to current day
MemberID --for use when a member charges to their account
Cash --Yes/No
Charge --Yes/No
Total

I know all this sounds crazy so far but it's how I got my main for to look like this:

First screen (page) hold two buttons--one for cash, one for charge. If charge, it goes to a page containing a drop down box to select the member, then a button to go to the order. If cash, goes directly to order.

The order page has 3 subforms, one for each of the food tables. Each subform has the food quantity fields. The rest are text boxes and buttons.

The text boxes are for the items' price which is set in the default properties. The buttons open a macro which tells the quantity field to increase by one.

Another text box calculates the subtotals for that subform
using the [items field] * [the price text box] and added together.

All three of these subtotals are totaled in the main form under Total.

All of this works perfectly. It is the only way I could think of to make it this user friendly. All the employee has to do is push buttons and total it. Oh, I also have the amount tendered and change text boxes set up.

So, that's it. But, to me, it seems like a crazy way to have to do it. It makes more sense to do it the way you described in your post, but how do you make it look like I did with buttons and everything on one screen and no drop downs to have to select the product?

I tried it several different ways before coming to this one. I don't know VBA. If I did keep it this way, what problems do you see with it? Is this so inefficient that it will run slow. This club is only open 3 months out of the year. There won't be a ton of orders. And maybe, by next year, I could write it better!

Sorry this was so long, but I didn't know any other way to explain what I'm doing.

Thanks for everything,
Stacey

 

[willir]

Thanks Stacey, no appolgies for length - I tend to be verbose too. Let me digest this.

I do want to say that local swim club certainly have demonstrated a lot of confidence in you.

 

[TadyB]

They don't know me very well!!!

 

[willir]

Haha

Okay, first this is a point of sale system + membership system + invoiceing system. A little more than the order entry system I had first thought.

To do it "right", I feel we have to take a couple of steps back and re-do the tables - sorry. But there will be problems later otherwise.

"Light reading" that are well worth your time...
Fundamentals of Relational Database Design by Paul Litwin

http://www.abcdataworks.com/fundamentals.htm

or

http://r937.com/relational.html

"Ten Commandments" by Dev Ashish et al

http://www.mvps.org/access/tencommandments.htm

I am also going to take a few short cuts to save time in development and explanation - break rule #2, on lookups. This can be fixed later, but for now...

Note:
PK - primary key
FK - foreign key

So far, I have identified 9 tables

Membership component

MemberTbl
MemberID - PK
MemberLN - last name, text
MemberFN - first name, text
+ other membership info (membership expiration date, original start date, contact info - phone, address, etc)
table is key to who owns the membership

FamilyMemberTbl
FamilyMemberID - PK
MemberID - FK to Membership table
FamilyLN - last name, text
FamilyFN - first name, text
+ other info (birthday, etc)
table is for a membership family members, assumes 1:M

Point of Sale component

SalesRep
SalesRepCode - PK (can be initials - low security, alphanumeric - better security
SalesRepLN - last name, text
SalesRepFN - first name, text
- You should track who made the sale

ItemTbl
ItemID - PK (or ItemCode)
ItemName - text
ItemDefPrice - currency
ItemSalePrice - currency
ItemEndOfSaleDate - date
ItemOnHand
ItemOnOrder
TaxExmpt - yes / no flag

OrderTbl
OrderID - PK
MemberID - FK
SalesRepCode - FK
OrderDate - date
PayType
PayAmount
Taxes
SalesTotal
Reference - text, credit card#, cheque#
CommitOrder - yes / no flag to lock sale
Comment - memo field to allow notes or comment on sale

OrderDetailTbl
OrderDetailID - PK
OrderID - FK
ItemID - FK
ItemQty
ItemPrice
ItemTotal
Comment - memo field to track any issues

Simple Invoicing System

InvoiceTbl
InvoiceNumber - PK
MemberID - FK
InvoiceDate
Reconciled - yes / no

InvoiceDetailTbl
InvoiceDetailID - PK
InvoiceNumber - FK
InvoiceItem - text - charge, membership, interest
InvoiceAmount - currency

TransactionTbl
TransactionID - PK
MemberID - PK
TransType - text - charge, membership, interest
TransDate
TransAmount
- I am still working this one through. Since you are dealing with cash, you should be able to do a simple journal entry system

Okay, now for the rule breaking, the lookup tricks

Order Table revisited...
When you create the Order table and get to the MemberID field the the table design view, select the LookUp tab at teh bottom of the window. Change the type from "text" to combo box. For the row source, select the Member table, and then invoke the query builder by clicking on the "..." icon to the left of the row source. Drag the last name, then first name and lastly the MemberID field to the query fields. Set the last name and first name to search in ascending order. Exit out of the query builder, and accept the change. Now conitnuing on with the LookUp setup - set Bound Column to 3, and Column Count to 3.

Why take this extra step? When you create the subsquent forms, Access will automatically create a combo box using the query you just created - saves time, and exaplantions later. BUT, per the "Ten Commandments" by Dev Ashish, there are draw backs to this approach. In your case, I feel the drawbacks are minimal, and we can delete the LookUp section later. I just want to get you going.

Note: The Combo Box will display the first field on the form, and it will use the "bound" field for integerity / relational links.

Setup a LookUp for the SalesRep in the same manner.

Order date - set your preferred date type (medium / short), and then for the Default Value, use "Date()" to grab the current system date.

PayType - setup another LookUp combo box, but with a twist.
For the Row Source Type, select "Value List". Then for the "Row Source", cut and paste the following...
"Cash";"Cheque";"CreditCard";"Charge";"Coupon";"No Charge";"Misc"

Syntax for a value list is
"Item1" in quotes + ; (semi-colon) + next item in quotes...

You can change this per your needs. Basically, instead of looking up the information in a supporting table, you created a list that will be used in a combo box automatically created when you create the form. Later you can create a proper supporting table, and revisit this.


For the OrderDetail table
Setup a LookUp for the ItemID, but it will have to be a tad more complex... For the query builder
Field 1 - ItemName
2 - ItemType
3 - ItemID
4 - ItemDefaultPrice
5 - ItemEndOfSaleDate
6 - ItemSalePrice
7 - TaxExmpt
Bound field = 3, number of columns = 7

Invoice table,
Setup a LookUp for the MemberID field as previously done for the Order table.

Create you Item form and any other forms such as your membership / family forms. I will get back to you with the Order table and OrderDetail subform. This will have a bit of code - most of it pretty simple.

Richard

 

[TadyB]

Hey!

That's I had set it up on the first attempt. I had an orders table form, with the order details as it's subform and in Order Details I had the items to choose from. I didn't know how to set it up by just pushing buttons. Everything you did makes perfect sense to me.

Also, I don't need a SalesRep table. She doesn't care to keep track...I've already asked.
And they can only pay cash or charge it to thier account. She sends out a monthly invoice to collect. No checks or credit cards. There are also no taxes to be charged. It is a not for profit business.

Thank you so much for doing this. I really look forward to learning VBA...got any good book suggestions? I'm reading over the sites you gave. I like the 10 commandments, real cute! I've been feeling so stupid because I understand how to build a database and the relationships, but I hit a point in this program where my brain turned to mush. I think it's the pressure. I will probably kick myself after you finish explaining!!

Thanks again,
Stacey

 

[TadyB]

Richard,
Our last two posts got deleted. I think it was because of the email thing. I didn't think it was forbidden as long as you post the solution back on the forum...which I intended to do. Any suggestions?

Stacey

 

[willir]

I asked for the eMail address to be deleted so you were no inidated with spammers. (Since this is a public post, who knows who is watching. In the hardware sections, if you ask for advice on hardware, you will probably be solicited by a vender.)

eMail is on its way!