At the start and end of every shift at my workplace, we record the amounts of each currency in our cash register, such as 5 $100 bills, 1 $50 bill, etc. The previous record's ending balance is displayed as well so that as you count the starting balance you know if you're numbers match what is expected. We do this by loading a hidden form and populating it with the values from the previous record. The code we have is as follows:
stDocName = "frmPreviousRecordEndingBalances"
'Subtract 1 from current ID to get previous record ID
stLinkCriteria = "[ID]=" & (Me![ID] - 1)
'Open form in hidden mode (acHidden)
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
'Set values from previous record to current record
[Forms]![frmCashCount]![PrevID] = [Forms]![frmPreviousRecordEndingBalances]![ID]
[Forms]![frmCashCount]![PS100s] = [Forms]![frmPreviousRecordEndingBalances]![E100s]
[Forms]![frmCashCount]![PS50s] = [Forms]![frmPreviousRecordEndingBalances]![E50s]
The problem we have is twofold.
1. Sometimes we need to skip a record, such as if we had two people on one register. The employee would still need to log her register even though it would not change. In those cases, we might need to look back two records to find the true previous balance. We have a checkbox the user can select to "skip" the record, but that field is just used for reporting purposes, not as a criteria for finding the previous record number.
2. If we delete a record, it errors out.
What we need:
I don't know how to look at the previous record with any intelligence. What's the best way to accomplish this? I tried filters and FindPrevious syntax, but either they are not a good fit or I just don't know how to use them properly.
Thanks!
Marc
stDocName = "frmPreviousRecordEndingBalances"
'Subtract 1 from current ID to get previous record ID
stLinkCriteria = "[ID]=" & (Me![ID] - 1)
'Open form in hidden mode (acHidden)
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
'Set values from previous record to current record
[Forms]![frmCashCount]![PrevID] = [Forms]![frmPreviousRecordEndingBalances]![ID]
[Forms]![frmCashCount]![PS100s] = [Forms]![frmPreviousRecordEndingBalances]![E100s]
[Forms]![frmCashCount]![PS50s] = [Forms]![frmPreviousRecordEndingBalances]![E50s]
The problem we have is twofold.
1. Sometimes we need to skip a record, such as if we had two people on one register. The employee would still need to log her register even though it would not change. In those cases, we might need to look back two records to find the true previous balance. We have a checkbox the user can select to "skip" the record, but that field is just used for reporting purposes, not as a criteria for finding the previous record number.
2. If we delete a record, it errors out.
What we need:
I don't know how to look at the previous record with any intelligence. What's the best way to accomplish this? I tried filters and FindPrevious syntax, but either they are not a good fit or I just don't know how to use them properly.
Thanks!
Marc
