The easiest way to make a point of sale

[jzwp11]

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)).

In datasheet view, only the current record will have the correct description; the only way around this is what you describe above. You are correct that it would not be normalized; this might be one case where you would compromise on normalization

[eskybel]

Ok thank you for that.
I'm at a point now where I am generating a receipt for the transaction. Since the Trans-T table holds all the transaction IDs, and details (date, time, etc), and the TransItemsComplete-T table holds all the items for each transaction (by TransID), is there a way with the query for the report that I can tell it to pull in info from the Trans-T table, the TransItemsComplete-T table, and only when the TransID matches the newest transaction? I tried using a "Like Form![TransDetails-F].[TransID]" in the criteria field in the query, but that didn't work. I'm sure I'm missing something easy...

[jzwp11]

If you are using a form that shows the current record & that is the one you want to print, it would be best to create the report and leave it unfiltered (shows all transactions). Make sure to include the transID field in the report's record source (which be the query that joins the two trans table and the transitemcomplete table). You will filter the report to the current record by adding a command button to the main form and in the command behind that button you would filter the report to show just the current record. The command would look like this

DoCmd.OpenReport "reportname", acViewNormal, , "transID=" & Me.transID, acNormal

The me.transID refers to the control on your main form that hold the transaction ID for the current transaction. The transID on the left of the equal sign refers to the field name in the report's record source

[eskybel]

Maybe you can help reel me back in here. There's a few forms/tables in there that aren't of concern right now, but I think I branched off in a way that I maybe made things a little more difficult. Right now, the database works in a way where all the pieces seem to go right, except the printing of the receipt after the transaction. This part bombs. I have a report that is a "reprint receipt" report, in which it will ask you for the TransID, and the report comes up and displays normally.

I'm sure you'll see that I have put the "tender" section of the process in the same form as the TransItems, because this seemed to be the only way I could get the data to get into a table in a way I could use it. There was a little more "not normal" in this. What I ended up doing to make it work was the TransItems table has fields "TransDiscount" and "TransPaid" in each record. All but one of the records ends up with 0s in these fields. When you click "Complete Transaction" the macro will close the form, and add the entered Discount and Paid values into the first record of the table. Then, a query takes all the values of the TransItems table, adds them to the TransComplete table (except for the Paid and Discount, because it always wants to put 0s here...), then another query that goes after just the Discount and Paid values in the TransItems table, and adds them to a standalone table for these, tied to the TransID.

When I tried to place the Tender section on the main form, I was ending up with #Error on the fields. Unfortunately, now that I've done it this way, the Discount and Paid fields do not get put in the TransItems table until after the form closes...which also means the above solution you presented doesn't work for me because the form is closed, and can't fetch the Me.TransID for the report.

But, if you look at the whole picture, this is almost exactly what I want the system to do...just ended up probably fixing a few things, and breaking a few others in the process.

If you have some time, could you help straighten me out on this?

[jzwp11]

I'm not really following the logic in your forms, but if they are working for you then that is OK. What you could do is to save the transID to a variable in the code prior to closing the form & then you can use that variable in the docmd (to print the receipt) in place of the me.transID

[eskybel]

Would this have to be a global variable? I tried setting a private variable in the command click for the button, and I know the variable is getting the TransID value, but the receipt does the same thing as before, no data.

I did:

Dim transidnum as integer
trainsidnum = Me.TransID
DoCmd.OpenReport "TransPrint-R", acViewPreview, , "[TransID]=" & transidnum

I'm guessing because the form is not open like before. If I can get it into a global variable, maybe that would work, but I am having trouble figure out how to properly assign global variables, I've never done it before.

[jzwp11]

Does the report open?

You could try this:
Dim transidnum as long
trainsidnum = Me.TransID
DoCmd.OpenReport "[TransPrint-R]", acViewPreview, , "[TransID]=" & transidnum
debug.print transidnum

The debug statement will print the value of the variable to the VBA immediate window. If it does you know that the command had the value and should have worked. Then I would look to the report's record source to make sure transID is an included field.

[eskybel]

Yes the report opens, though nothing but the design is displayed. All fields are blank or #error. The last part of the process is to open the report, but it fails to display anything on the report during the process through the button click. If I close the report, then manually reopen the same report, it populates, but with everything that is in the TransItemsComplete table. So, the report is working fine, so it seems, it's just not filtering.

When using the code above, I did not see any print of the transidnum variable anywhere? It complained about the brackets surrounding the report name, so I took them back out, then it ran, but I still get the blank report following this.

If I open my "reprint" report, and manually enter the TransID, I get the proper receipt, including all proper information for that transaction, etc...a completely correct print out.

[jzwp11]

If I use the form and enter the customerID (4) and then items 1 & 2 everything seems to work fine, but after that I cannot enter the discount or amount paid. If you do not have the money part, the query will yield no results since you include the money table in the query on which the report is based.

[eskybel]

It won't allow you to type a discount or paid value in the text boxes? I'm not sure why that is, because I can type them and it calculates accordingly. And the "complete transaction" process does what it is designed to do, just the report doesn't display properly. If I close the report, then reopen it, manually enter the TransID in the reprint report, it comes up fine.

But, to that point, discount and paid values do not get entered into the table directly like the items do, because I do not want these fields being duplicated in the lines where items are rung up. Instead, as part of the macro for completing the transaction, it carries these values into the table after the fact. I know this is a little weird, but like before, I could not get the values to populate in "subtotal, tax, grand total" etc when I placed the text boxes on the main form...I could only get them to work when I included them in the form with the items...if that could be fixed, maybe the whole problem could go away.

[jzwp11]

It won't allow you to type a discount or paid value in the text boxes?

Correct. I'm not sure why either.

[eskybel]

That's interesting...some kind of compatibility problem? It works on Access 2003, but I just tried it in 2010, and it doesn't allow me to type either. Sheesh...

[jzwp11]

Yes, it sure sounds like a version-related issue on that point.

[eskybel]

Ok, well that has me a little frustrated, so I decided to go on to a different part of the project for now. Maybe an odd question, but is there a way to have a set of text boxes on a report, such as a FirstName, LastName, StreetNum, StreetName, City, State, ZIP, and have these fields be able to shrink and grow together?
Here's an example. You have a text box which holds a first name, then another text box that holds a last name. These boxes would normally be fixed size, and in a fixed spot on a report. If the first name is really short, there's a big gap in between the first name, and the last name on the report. If the first name is long, it overruns the last name. I was wondering if there's a way to say that the first and last name fields are bound together, so if the first name shrinks or grows, the last name's text box will move over to accompany the first name, where ever it decides to end...if that makes sense?

[eskybel]

Doh, after staring at that problem for about 30 minutes, I realized I was forgetting just making the control source an expression of =[FirstName] & " " & [LastName].
Yikes...one of the easiest things in the whole project.