The easiest way to make a point of sale

[jzwp11]

No problem; I've done that before myself.

[eskybel]

Ok now it's been another forever since I got to spend some time on this. I think it's starting to become a pattern...

So, I was messing around with this as an option to get the receipt to print. Maybe I don't fully understand this command and its syntax, but here was my thought for getting the receipt to print automatically after the transaction, based on the TransID. The below code is part of the event on click:
===================
Dim intx As Integer


intx = Me.TransID


DoCmd.OpenReport "rptTransPrint", acViewPreview, , "[TransID] = intx"
==================

I also tried:

, , [TransID] Like intx
, , "TransID" like intx

a few others...

I know intx carries the TransID to just before the report loads, as I did a MsgBox (Intx) and it displays the TransID. Now, maybe if I just have the syntax wrong, I can get this to work...or maybe I simply can't do what I'm trying to in this fashion...?

[jzwp11]

Since intx is a variable, it cannot be enclosed in the double quotes; it has to be concatenated to the string via the &. Also if transID is a long number data type it would be best to have intx as a long number as well

Dim intx As Long


intx = Me.TransID


DoCmd.OpenReport "rptTransPrint", acViewPreview, , "[TransID] =" & intx

[eskybel]

Ok thank you for the direction there. I attempted this, and my receipt comes up blank/errors in many fields. I know my variable carries again, because the msgbox displays, but apparently the report is not applying the condition properly?

[jzwp11]

Does the data populate in the report when it is not filtered to one transID? I assume that the report is based on a query; does that query give you the correct data both filtered with 1 transID as well as with all transIDs?

[eskybel]

Yes if i remove the where condition, the report will populate with all the data from the query.

[jzwp11]

I assume that there are joins in the query. Did the transID you pick initially show up in the query results when it was unfiltered?

[eskybel]

If I remove the entire OpenReport line, the transaction closes out and goes to the main menu. If I go to the tables, the transaction I just closed is there.

If I put this line in the transaction close process:
DoCmd.OpenReport "rptTransPrint", acViewPreview, , "[TransID] =" & intx - 1

The report will display the transaction previous to the one I just completed. So, the condition works, but apparently the closing of the transaction isn't committing the values to the table before the condition? Here's my code for the transaction close:

Private Sub cmdTransClose_Click()

Dim intx As Long

'Assigns transaction $/details (frmTransDetails) to the frmTrans, which commits to the main transaction table (tblTrans)
Forms![frmTransMenu]![frmTransDetail].Form![TransRetail] = Me.TransGrandTotal
Forms![frmTransMenu]![frmTransDetail].Form![TransDiscount] = Me.TransDiscount
Forms![frmTransMenu]![frmTransDetail].Form![TransPaid] = Me.TransPaid
Forms![frmTransMenu]![frmTransDetail].Form![TransChange] = Me.TransChange
Forms![frmTransMenu]![frmTransDetail].Form![TransCost] = Me.TransCost
Forms![frmTransMenu]![frmTransDetail].Form![TransNotes] = Me.TransNotes

intx = Me.TransID

'Closes transaction menu, for purpose of preventing further the transaction edits, and committing line items entered to tblTransItems
DoCmd.Close acForm, "frmTransMenu"

'Appends transaction line items (tblTransItems) to main transaction items table (tblTransItemsComplete)
DoCmd.OpenQuery "qryTransComplete"

'Clears temporary transaction items table (tblTransItems)
DoCmd.OpenQuery "qryTransCompleteClear"

'Generate receipt for current transaction
DoCmd.OpenReport "rptTransPrint", acPreview, , "[TransID] =" & intx

End Sub

[jzwp11]

If I remove the entire OpenReport line, the transaction closes out and goes to the main menu. If I go to the tables, the transaction I just closed is there.

Is the form on which the button is located a bound form or unbound? If it is bound, it sounds like you will have to force a save of the current record at the beginning of the code otherwise it does not save the record until the form closes (just a guess).

[eskybel]

The sub form frmTransItems is bound to table tblTransItems. That's what I thought as well, so that's why I set the variable intx to the TransID while the form is open, close the form (even tried doing a close of the tables, a save, etc), ran the queries, then run the report with the condition, but it still doesn't display in the receipt. When I tried to save the tables before the report gets run, it didn't make a difference. When I tried to save the form in the code before the report, the database crashes. I also tried doing part of the process in VBA, and part in a macro, to see if it would help, but no dice.

[jzwp11]

On what form is the transID control located?

[eskybel]

It is on two of them, but both are sub forms. The one I am referencing, at least I assume, is on the frmTransItems, which is where all the items are being rung up, where the $s are calculated, etc, which as long as it knows the value of TransID, I would assume it should be basing the receipt on it. It is brought down there from the frmTransDetails (tblTrans) via the "before update" of the ItemID field.

varx = DLookup("[TransID]", "tblTransItems")
Me.TransID = varx

This way, every time an item gets rung up, it assigns the TransID to that record in my tblTransItems table.

This is the solution I used to make sure each item being rung up had the proper TransID attached to it, because otherwise they were not tied to the TransID in frmTransDetails (tblTrans) at all, which broke pretty much everything else.

The only thing I can figure right now is my variable has the value of the proper TransID, but when it goes to get data from the table, that TransID has not been saved in it yet, so it returns nothing.

[jzwp11]

I wondered if that was the issue. You might try to force a save of the current record in your code. This is the command for that:

DoCmd.RunCommand acCmdSaveRecord

[eskybel]

Unfortunately, that didn't work either.

I would assume it would run the code line by line, but it doesn't seem to make a difference where I put certain actions in the code. It's interesting. I wonder why it is that if I do everything but the report at the end of my code, it will close the transaction, and I can look in the tables and all the info is there. Wouldn't it mean if I stuck the report at the end of all the code, that it would run through everything like normal, then go and get the info out of the tables (via the query bound to the report) and display it?

I don't know anything about declaring a public variable, but I might have a way to get around this if I can get help on declaring a public variable, assigning a value to it, then calling it in another action.

[jzwp11]

It sounds like the record is not being committed to the table until you close the form. The code executes before the form is closed so the record is not yet committed. Perhaps you can prompt for the report in the on close event of the form by opening another form that asks the user if they want to print the report and in the background pass the transID to that form.