How to auto fill previous record value as the new record value on a form? (1 Viewer)

zoober1

Registered User.
Local time
Today, 15:08
Joined
Jun 24, 2014
Messages
11
Hey everyone,

I apologize if my question doesn't make any sense. I'm new to access so please forgive me.

I have a table called TblTransactions. The fields are as follows:

Transaction_ID (PK, auto number)
TransactionDate (Date/Time)
TransactionTime (Date/Time)
CustomerName (Text)
CompanyName (Number - CboBox)
OPSInitials (Text)
BankTotal (Number)

This table was used to create a query, and a form.

On the form, I would like the value in BankTotal from the previous record auto populate in an unbound text box called TxtCurrentBank. Here's what I've tried so far without success:

I set the Control Source of txtCurrentBank to : DLookup("[BankTotal]","[TblTransactions]","[Transaction_ID] =" & [Transaction_ID]-1)

If I delete a record, then Transaction_ID-1 doesn't exist, and the text box is blank. Also, once i close and re-open the form, the Tex Box doesn't auto populate.

I have read other threads where people have had the same problem, but couldn't figure out how to make the code work for my form.

I have tried to combine dlookup and dmax together in an expression but couldn't get that to work either.

Please help! Thank you in advance!

-Zoober
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:08
Joined
Feb 19, 2013
Messages
16,553
Your requirement needs clarification

As you have surmised, the autonumber cannot be used for anything other than uniquely identifying a record. So you need to use something like dmax to find the previous record.

But as you probably know, previous based on what order? the order entries were input based on the ID (not totally reliable to define an order of input)?, the date/time of entries were made(i.e. a timestamp - perhaps this is your transaction date/time fields?)? the date/time the entries should be ordered? (e.g. you have missed an entry from yesterday and now need to add it in placed in the right order), etc

And then is the previous record based on the whole table? or based on the entries for a specific customer or client?

With regards your form - is it a continuous form? or a single form?

And you may want to consider putting the determination of the previous record into your form recordsource rather than a domain function on the form

Finally, why? is it to provide a running balance? in which case you would not store the value but calculate as required.
 

zoober1

Registered User.
Local time
Today, 15:08
Joined
Jun 24, 2014
Messages
11
Thank you for the prompt reply. I'll try to answer your questions as best I can.

My form is a single form. It has a subdatasheet attached based on a table called tblTransactionDetails. This table keeps track of specific line items that are purchased as a part of the whole transaction.

The why. I can try to explain to you how our system is set up. We have a bank that we try to keep $100 in at all times. The purpose of the bank is to break change, and to do refunds for customers. When we sell items, the cash from each transaction is deposited into a different account. So, if we sell 2 items at $10 each, the bank total at the end of that shift is still $100. If we refund someone for an item that they are returning, for example $-5.00, the bank total at the end of that shift is $95. We have three shifts with multiple people who perform transactions. At the end of each shift, a report is printed out that lists each transaction that occurred during that shift. So in essence, we are keeping a running total.

Your question about order got me thinking that using Transaction_ID to find the most recent entry might not be ideal. As you said, If the user enters a missed transaction from yesterday, the bank total would be thrown off. In that case, I would like the BankTotal to be based off of the most recent date/time stamp.

Does this help?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:08
Joined
Feb 19, 2013
Messages
16,553
Does this help?
it does, but this

cash from each transaction is deposited into a different account

implies you have many identical tables - is this correct?

if so, then your design needs to reviewed, the normal expectation is you would have a single table with an additional column for the account identifier.

Back to timestamp - are the transactiondate/time fields a timestamp? or do you need to add an additional field?
 

Users who are viewing this thread

Top Bottom