The easiest way to make a point of sale

[eskybel]

Wow, I can't believe how much time went by. I apologize for not updating, but so much as happened these last few months.



I am starting back into the project again, and unfortunately, I am now designing in Access 2003. I effectively started over because when I tried to save the 2010 DB in 2003 format, it just keeps crashing when opening in 2003...I am also unable to open your DB for the same reason.

At any rate, I am at the point where I am trying to calculate the sub/grand totals for the transaction, and I'm not sure how to make the control source for the subtotal text box total all the other boxes above. The retail price, the quantity, and the extended price all seem to look and work normally (except for a little rounding issue). These calculations are made on the fly, as suggested. I attempted a Sum([ItemRetailExt]*[ItemQty]) but just get "#Error"

Much appreciated!

[jzwp11]

I am starting back into the project again, and unfortunately, I am now designing in Access 2003. I effectively started over because when I tried to save the 2010 DB in 2003 format, it just keeps crashing when opening in 2003...I am also unable to open your DB for the same reason.

I have had database crashing issues when using Access 2007/2010 in a Windows XP environment or more specifically designing a database while in XP and then using it in Windows 7 or vice versa. Decompiling and then recompiling the database helps (see attached). I have also had issues if multiple versions of Access are on the same computer. Going from an Access 2007/2010 database back to a 2003 will also cause some issues because of the new features in Access 2007/2010.

At any rate, I am at the point where I am trying to calculate the sub/grand totals for the transaction, and I'm not sure how to make the control source for the subtotal text box total all the other boxes above. The retail price, the quantity, and the extended price all seem to look and work normally (except for a little rounding issue). These calculations are made on the fly, as suggested. I attempted a Sum([ItemRetailExt]*[ItemQty]) but just get "#Error"

I'll assume that you are trying to do this on form. I will also assume that your transaction details are in a subform. In the footer of that subform, add a control with the control source you indicated Sum([ItemRetailExt]*[ItemQty]). I will assume that your control names are spelled correctly.

To bring the subtotal from the subform to the main form, you will need another control. The control source will look something like this:

=[frmOrderDetails].[Form]![itemsubtot]

You will have to substitute your subform name and the control name that holds the subtotal.

I have recompiled the orders database I provided earlier. Hopefully you will be able to open it.

[eskybel]

I'll assume that you are trying to do this on form. I will also assume that your transaction details are in a subform. In the footer of that subform, add a control with the control source you indicated Sum([ItemRetailExt]*[ItemQty]). I will assume that your control names are spelled correctly.

Yes this is happening in a form. And yes the transaction details are in a sub form. I put a control in the footer, with the control source as stated, Sum([ItemRetailExt]*[ItemQty]). Control names are spelled correctly. This control reports #error.

To bring the subtotal from the subform to the main form, you will need another control. The control source will look something like this:

=[frmOrderDetails].[Form]![itemsubtot]

You will have to substitute your subform name and the control name that holds the subtotal.

I did this also, and it carries #error down below too.

I have recompiled the orders database I provided earlier. Hopefully you will be able to open it.

I'll check this out when I get back home.

Any thoughts on why the control subtotal displays #error?

Thanks for the input!

[jzwp11]

I'm not sure why you are getting the #Error. Any chance that you can post a copy of your database with any sensitive data removed/altered?

[eskybel]

Well I've found out my details are not working quite like I thought they were either. I've still got a number of things wrong, but here it is.
My goal was to have the DB tab down the item number lines, skipping everything else, and have a barcode scanner enter the SKUs/UPCs in, and update all details accordingly.


db1.zip

[jzwp11]

First, why do you have two item tables and two customer account tables?

The other major problem is that your relationships are not set up correctly. In the TransItem-T, you have the itemID field of ItemInfo-T joined to the TransID field. It should be joined to the ItemID field in TransItem-T. The transID field of Trans-T should be joined to the TransID field of Transitem-T. I have corrected those relationships in the attached.

I also created an example form (frmCustomerAccountInfo) that illustrates how the forms should be structured given the correct table structure. I used bound forms throughout. Since you are autopopulating the date/time in trans-T, the form needs another control that the user has to enter in order join the transaction record to the customer. To do this I added a reference number field to the trans-T table as well as a corresponding control to the frmCustomerTransaction (which is a subform within frmCustomerAccountInfo). The frmTransItem (that has the items purchase for that transaction and customer) is a subform within frmCustomerTransaction.

I saw that you were using both unbound and bound forms in TransMenu-F. That probably would not work without additional code to pass the key values from the main to the subform and from subform to subform

[eskybel]

I must have messed something up when rebuilding the DB on the relationships, because it wouldn't make any sense to have done it the way I apparently did. Guess I missed that mistake.

I have two tables for items and customers, because I had designed it to have a new customer's info entered on a separate form, then a command button would take the info being added in that "temporary" table, and carry it over to the master customer table, then the "temp" table was emptied out. I tried entering it directly to the master, but I didn't want existing customers to be accessed while in the "add customer" form. This seemed like a way to separate them, but there's a better way, I assume? Same with Items.

I see the way you setup the frmCustomerTransaction, and that works, I guess I just envisioned the transaction details being viewed a little differently than in the datasheet look. Can that form be put in single form view, and have it auto add another line (record) when the first is filled in, so a second, third, etc can be rung, without having to use the record selectors to go between them?

Then, as far as the item entry to the transaction, I see you select items by combo boxes, which I also had envisioned differently, as I may have an item table that has a 1000 items, and you don't want to scroll through or see all items, just want to scan a barcode, and have it populate the rest of the fields with the item info. I would imagine you can just scan while in this field and it will do effectively the same thing, but I didn't want an accidental scroll on the mouse wheel to flip items in the transaction and have it get missed until the transaction was completed.

I see the main form (frmCustomerAccountInfo) is bound to the master customer info table (CustAcctInfo-T). If I understand right, I wouldn't want the transaction to open with anyone's info, I want a clean slate, waiting for me to enter a customer's ID. Then, I wanted the CustAcctID field on the main form to "dlookup" all the customer's info, and fill in accordingly, only for display. The current form will let me change most of the customer's info on the main form (which I wouldn't really want, but I imagine I can just lock the fields), but it won't let me change the ID to see it autopopulate another customer's info? I can move through the records with the selector on the bottom of the form, but that wouldn't be ideal for customer lookup.

Then, once a transaction is complete, how do you clear the form, transaction info, and have another blank slate to ring up on (autopopulated TransID, unchangeable)? It seems when I move off the customer# 1, it also removes the transaction information, but if I go back, it brings the transaction info back up. Then, I'd also want the ability to cancel a transaction or items run up without having to manually delete a record.

Maybe I titled this whole thread wrong...hah.

Thanks for the time!

[jzwp11]

I tried entering it directly to the master, but I didn't want existing customers to be accessed while in the "add customer" form. This seemed like a way to separate them, but there's a better way, I assume? Same with Items.

You can change the Data Entry property to yes, to open a form to enter new data only (existing records would not be visible). You could also use an unbound form, but you would need code to enter the data. Either approach requires only 1 table.

Can that form be put in single form view, and have it auto add another line (record) when the first is filled in, so a second, third, etc can be rung, without having to use the record selectors to go between them?

Yes, the form can be put in single form view (just need to change that in the property settings of the form). I've never done the second thing you ask, but I assume it is possible, perhaps some code in the after update event of the control whose value is entered last to move to a new record might work.

Then, as far as the item entry to the transaction, I see you select items by combo boxes, which I also had envisioned differently, as I may have an item table that has a 1000 items, and you don't want to scroll through or see all items, just want to scan a barcode, and have it populate the rest of the fields with the item info. I would imagine you can just scan while in this field and it will do effectively the same thing, but I didn't want an accidental scroll on the mouse wheel to flip items in the transaction and have it get missed until the transaction was completed.

I have read about people using bar code scanners with Access, but I personally have never done that. I'm afraid I can't help you out on that one. Perhaps you can post that specific question in a new thread on the forum?

I see the main form (frmCustomerAccountInfo) is bound to the master customer info table (CustAcctInfo-T). If I understand right, I wouldn't want the transaction to open with anyone's info, I want a clean slate, waiting for me to enter a customer's ID. Then, I wanted the CustAcctID field on the main form to "dlookup" all the customer's info, and fill in accordingly, only for display. The current form will let me change most of the customer's info on the main form (which I wouldn't really want, but I imagine I can just lock the fields), but it won't let me change the ID to see it autopopulate another customer's info? I can move through the records with the selector on the bottom of the form, but that wouldn't be ideal for customer lookup.

I was just illustrating how the basic forms would look and how the subtotal controls would work. There are many different ways to handle things in Access. I would probably use an unbound form ahead of any that you currently have where the user selects and existing customer from a combo box. Then when a selection is made (after update event of the combo box), open a form bound to trans-T (with a subform for the transaction items) since you are just entering a new transaction for that existing customer. You can show the relevant information about the customer in the main form via your DLookup() approach. You would have to pass the ID of the selected customer to the form that opens of course. If it is a new customer you could just open the frmCustomerAccountInfo to a new record. You would use the Not in List Event of the combo box to trigger opening that form.

Then, once a transaction is complete, how do you clear the form, transaction info, and have another blank slate to ring up on (autopopulated TransID, unchangeable)? It seems when I move off the customer# 1, it also removes the transaction information, but if I go back, it brings the transaction info back up. Then, I'd also want the ability to cancel a transaction or items run up without having to manually delete a record.

You would need some event to close the form after data entry. Then reopen the form in data entry mode (i.e. to a new record) for the next transaction.

To cancel a transaction, you could use a series of delete queries bound to some event like a button click.

You can do this with unbound forms as well, but it will take a lot of coding to make it all happen.

[eskybel]

Thank you for all the info again. This is great direction!

A quick question before I forget about it...

Doing it in the method shown above, where the prices are calculated on the fly, etc. It was stated that the prices for each transaction should not be stored in a table for the transactions. My question for this is, if I do not store the prices as they were during the transaction, and later the price of the item changes...then after that I go to do a reprint of the receipt, won't the reprint of the receipt show the adjusted price, instead of the original purchase price? Unless I misunderstood?

[jzwp11]

You would store the unit prices (I did that in the attached database) and the quantities and the itemID in the transaction detail table, you would not store the subtotals or grand totals in any tables of course. Storing the price in the transaction detail table will preserve the price of the item at the time of the transaction.

[eskybel]

I apologize I did misunderstand. Yes that's what I just implemented on my copy here, and it is working accordingly. Back for more tomorrow, I'm sure!
Thanks!

[jzwp11]

You're welcome

[eskybel]

Ok, I have almost all of my transaction working! One stickler before it's actually doing what it should. I can't seem to get the CustAcctID and TransID to carry to the other forms. I type the Customer ID in the main form (top) and it should put it in the TransDetails subform so they will link, right? Then, I have the TransID autopopulate with the RefID field being entered via the "afterupdate" when the Customer ID is entered. This triggers a control box in the TransDetails subform to be filled with a random "1" and the autonumber enters the next TransID. Then, I need the TransID to be carried to the TransItems form, so at the end, all info is linked in the tables.

So, I was able to get the CustAcctID field to pass down to the TransDetails form with the "after update" where the CustAcctID is originally entered, but for some reason, this info never goes into the TransDetails table, but the TransID, Date, Time, and RefID do?

And in the TransItems form, all I get is #Name? for the TransID trying to pass. Is this something I can pass with some global variable, since it doesn't seem to want to cooperate with a =Forms![TransDetail-T].[TransID] (also tried with two !s, and with just Form![etc])?

[eskybel]

Ok, sort of found a way around the pass issue...will report in a bit.

[eskybel]

Ok, I have the CustAcctID from the main form passing to the TransDetails form, and I have the TransID in the TransDetails form passing to the TransItems form. One hiccup I ran into is that with the TransItems form being tied to the TransItems table, once I move to the next record, it would wipe out the TransID field on that form, and further items would not go to the table. I got around this with an "after update" dlookup on the ItemID field in the TransItems form.

The (I think) last issue to resolve is the Dlookup I'm doing for populating the Item's description that is being rung up. Since I do this with a Dlookup, and I have the output to an unbound field on the form which is otherwise bound to the TransItems table, every time I ring up an item, it populates the proper info, but the next item (record), if a different item, would change all the descriptions for all rung up items to the most recent rung up item, which obviously is not right. So, I'll have a transaction with 5 items, all different ItemIDs, different prices, quantities, etc, and everything is correct, except all items descriptions are the same.

The only thing I've found around this is to have the item description bound to a field in the table, which, if I understand this right, makes this part not "normalized" because I'm duplicating data (the trans ID (normal), and the item description (not normal)).

Thoughts?