The easiest way to make a point of sale

[eskybel]

Hello everyone,
Did a lot of searching around, found some solutions that looked like they should work, but didn't, and have been stuck for a few days now. Now, I'm still quite new to Access and SQL, but so far I've completed a number of things in my program.

What I am building is a Point of Sale system. I have a main screen with buttons that ask "Add Customer," "Add Item into Inventory," "Register," "Quit." My add customer works great, my add items works great, quit works great.

The part I'm getting hung up on is creating the form that acts as the register for the POS. I have a form (TransMenu-F) that's Control Source is a table (ItemAdd-T) with fields for the Sale screen, such as "TransNumber, TransDate, TransTime, TransItem1, TransCost1, etc. The goal is to have a user enter information in the form, which puts it in the ItemAdd table, and then the ItemAdd table would be used for printing the receipt, as well as appending the ItemAdd information to a master table where all transactions are held.

And, I am trying to set the (TransMenu-F) up so there is a text box where a SKU (TransItem1 above) could be entered by hand or by barcode scanner, and upon entering this SKU, it would query my inventory table (ItemInfo-T), find the SKU, and populate another text box on my transaction form (TransMenu-F) just to the right of the SKU text box, that would display the products description, and in another text box to the right of that, the price of the item.

If you're following me, the final goal is to have a form, be able to enter a SKU into it, have it query my inventory table, populate the form with the item's description and price, and be able to go to the next line and do the same thing until all items are run up, then the bottom would be "tax, total" fields, and then enter amount paid, figure out change, then finally add the transaction info into the master transaction table, as well as print out a receipt of all that transactions info.

I've been messing around with dlookup, I tried a couple queries, but read that it would be more efficient to query all tables involved, and have the form control source be that query, and a couple other solutions I can't remember.

Is my thought process correct/efficient, and what's the best way to implement this feature?



I apologize if that's a lot of reading, and since I'm stuck on how to make it work properly, I don't have much code to present.

TIA

Adam

[jzwp11]

Before you get to forms, you have to make sure your tables are structured properly. You mentioned that you had 2 tables that probably look something like this

tblCustomers
-pkCustomerID primary key, autonumber
-txtFName
-txtLName
-txtAddr
-txtCity
etc.

Your item table

tblItems
-pkItemID
-txtBarCode
-txtItemDescription
-currItemPrice

You will need a transaction table (not sure if you have one). You would use this table to tie in the customer for which the transaction applies. This assumes that a customer can have many transactions (over time) which describes a one-to-many relationship

tblTransactions
-pkTransID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-TransNumber
-TransDateAndTime (keep date & time together in 1 field)

Since a transaction can have many items and an item can show up on more than one transaction, you have a many-to-many relationship which is handled with a junction table.
Here is what the junction table would look like

tblTransactionItems
-pkTransItemID primary key, autonumber
-fkTransID foreign key relating to tblTransactions
-fkItemID foreign key relating to tblItems
-currItemUnitCost (copied from tblItems via your form)
-QTY (a field to hold the number of each item being purchased)

[eskybel]

Sorry it took so long for me to get back in here. I sure appreciate your direction. Does the attached picture look like it is setup the way you explained? This is my first time working with a junction table, foreign keys, and the like, so it took me a little bit to figure this much out.

I guess my next step is building the form that gets filled in for the actual transaction?

[jzwp11]

The Trans-T is incorrect. You should not have TransItem1, TransItem2 etc. (that is a repeating group which is not normalized). The TransItems-T handles the items pertinent to a transaction. Also using the hyphen in your table name is not recommended, I would use an underscore _ instead of the hyphen. I would also recommend have the date & time in 1 field rather than two. You can always display just the date with the datevalue() function

[eskybel]

The Trans-T is incorrect. You should not have TransItem1, TransItem2 etc. (that is a repeating group which is not normalized). The TransItems-T handles the items pertinent to a transaction. Also using the hyphen in your table name is not recommended, I would use an underscore _ instead of the hyphen. I would also recommend have the date & time in 1 field rather than two. You can always display just the date with the datevalue() function

Ok, thank you very much again (learning tons of new stuff). The reason I had the date and time separated out is because I assumed it would be easier to run reports based on a specific date or time if they weren't together. This would be for determining traffic flow based on days or time of day. Is that not the case?

I will dive into this hopefully within the next few days and report back...Been so busy!
Adam

[jzwp11]

You're welcome!

[eskybel]

Ok, so I should take all the item#s, cost#, subtotal, tax, total out of that table, and put them in the TransItems table? After that is completed, what would be the next step in making the form act in a way where there are multiple lines for adding items onto the sale, figuring the subtotal, tax, and grand totals? Not really sure if I've grasped the connection of how the junction table will help me gather the info I want in a form/receipt and keep record of the info.
Thanks!

[jzwp11]

Subtotals and totals would not be stored in any table; they need to be calculated on the fly when you need them in forms, reports or queries. As to tax, does the rate vary based on anything? Are all items taxed at the same rate?

As to the forms, you would probably base a main form on the transaction table and supply the customer via a combo box on that form. You would then have a subform based on the transaction item table. Each item applicable to the transaction would be a separate record in the table (a separate row in the subform). You would use a combo box based on the item table to populate the item in the subform.

[eskybel]

The tax rate should remain constant, though I'd like to be able to have a place where it can be changed globally if necessary (move to another state, have someone in another state use the POS). Would that sub form for entering the items have the Item1, Item2, combo boxes, and it queries the item table for each box filled?

[jzwp11]

Regarding the tax rate, you can put it in a table with a location reference and then pull the applicable rate into the transaction form. As to the sub form for the items, the combo box would be tied to the item table. In a typical form each item would be a new line not a new column;like this

Item1, cost, Qty
Item2, cost, Qty
etc.

Now if you want to have each item as a column that would take some form manipulation and probably some code to append each item into the underlying table structure.. The advantage of the row approach is that you can add an unlimited number of items to a transaction where as if you had them as columns you would be limited by the number of controls you put on the form (there is a limit, I just do not recall what that limit is)

[eskybel]

Nope, not looking to have columns. When I design the form, I was planning on using text boxes for entering/scanning the barcode into the form, then having the text boxes next to the SKU/UPC autofill with the item information, then there would be another blank line below that with a few text boxes doing the same thing as above. Do the combo boxes serve a better function for manual entry of barcodes, etc? I think I might have gotten a little lost there.

[jzwp11]

In the row source of the combo box, you can include other fields from the item table including the barcode, price, item description etc.. The combo box would allow you to select an item and populate other textbox controls on the form with the information brought in by the combo box such as the cost of the item. You would use the after update event of the combo box to populate those controls. You would typically only store the key field's value and the cost, but you can display (but not store) whatever info from the combo box in other controls. If you were to store other information that is already in another table, that would violate normalization rules. The cost is an exception to that because the cost in the item table can change over time so you want to capture the cost of the item at the time of the transaction, so that is why you would store the cost. Now as to bar code scanning, I have heard of Access applications that do that but I have never worked with one to know exactly how they would work in terms of form interaction.

[eskybel]

Ok, if I am understanding right, the combo box is going to be the drop down box, that would list all the IDs for items in the item table, then I select one and it will populate the rest of the fields. A lot of that is what I want, but I don't want to have to select a SKU/UPC out of a drop down to add it to the transaction, I simply want to have a blank box where SKUs/UPCs could be entered, and based on what is entered, it will query the item table and either find it and populate the rest, or not find it and present an error. I have a bar code activex addin for MS Access, and it allows the creation of barcodes, as well as being able to scan barcodes and have it translate into a SKU/UPC number into a field, which is what mos POS systems do for easy item ring up.

I'm probably misunderstanding, but as it sits, I played with the transaction form/subform, and it looks great, and does what I want it to in terms of look and basic function, and I can type in an ID number for an item and it will complain if it doesn't exist, and they aren't drop downs, so it looks like it's ready, now I just need to get it to autofill some info, and work on the finalization of the sale, and recording info on it.

[jzwp11]

OK, if you do not want the combo box to populate the other controls, you will have to do it using Dlookup() functions (one for each control). You would still do it in the after update event of the textbox.

[eskybel]

Ok,I'm in the After Update event Code Builder for my ItemID field, which is the TransItems-T table. So far I have some variables set, called fldItemID, fldItemDesc, and fldItemCost. Then I have each set to a =dlookup(), so:

Dim fldItemID, fldItemDesc, fldItemCost As String

fldItemID = DLookup(ItemID, "ItemInfo-T")
fldItemDesc = DLookup(ItemDesc, "ItemInfo-T")
fldItemCost = DLookup(ItemCost, "ItemInfo-T")

which as I understand it, should be pulling in the info I'm looking to display. Now, if that is correct, what do I do to have these pieces of info display in their respective text boxes? I tried to take the next step to get each of these to display in their boxes, but I'm seeing "invalid use of null" when the event occurs.

Again, not too great at Access, so I'm probably confusing or missing some steps/syntax/others.