Copying value from previous record in a continuous form

[b-ballstyle]

Alright, so I have a continous form on a form and i want to take a value in the previous record (BLUE), subtract one value in the current record (GREEN) and put that calculated value in the field (BLACK). All this by pressing the button on the current record (RED).. Is it possible? I can find any documentation on how to reference that previous record.. Please help

 

[AccessMSSQL]

do you want to do this per record or for all the records in the continuous form at once?

 

[AccessMSSQL]

sorry..just looked at your form I see you need it per record. Are you comfortable using VBA?

 

[b-ballstyle]

Yes I am confortable using vba and yes I need it per record because for the first record of the list I only have the balance.

 

[AccessMSSQL]

You can write a function in VBA that returns the previous balance. Then call the function in the after_update event of your GREEN field.

You would have to pass the current month, and customeriD to the function.

Something like this:

Public Function GetPreviousBalance(Month as integer,  ClientID as integer) as double
Dim rs as dao.recordset
dim strSQL as string
 
strSQL = "SELECT Balance FROM [YourTableName] WHERE Month=" & Month-1
strsql = strsql & " AND ClientID = " & ClientID
 
set rs = currentdb.openrecordset(strsql,dbopendynaset,dbopendynamic)
 
if not rs.eof then
  getpreviousbalance = rs("Balance")
end if
rs.close
set rs = nothing
end function

in after update call it like this:
me.CurrentBalance = GetPreviousBalance(me.month, me.clientid) - GreenValue

 

[b-ballstyle]

The only thing is that my "month" field is only a text field.. And sometimes I can have 2-3 rows for one specific month..

 

[AccessMSSQL]

Can you post your table structure that stores the balances?

 

[b-ballstyle]

Yes here it is.. We are looking at the Cheques part. So the Cheque inventory details are the information on my main form and cheque inventory monthly is my continous form.

 

[b-ballstyle]

Anyone...?

 

[AccessMSSQL]

Just got your post. I'll look at it.

 

[AccessMSSQL]

First thing I see is that you don't have a primary key on your cheque inventory monthly table. This is a problem. You should have a field in that table called CheckInventoryMonthlyID autonumber and make it primary. I don't see any way in that table to determine which order records were entered in. So if you have two entries for the same month, how do you know which one was entered first? Also, I would name your primary key fields differently than ID. It's not intuitive and makes life easier if you have descriptive names, like your Check Inventory Details table would be CheckInventoryDetailID instead of ID. And you would name it the same in your monthly table. But if your database is already established I understand it would be difficult to change at this point. I'm assuming Used is green and Balance is black?

 

[b-ballstyle]

Yes I know, I dont have a primary key because for one month I can have multiple rows.. And yes used green, balance black

 

[AccessMSSQL]

You should still have a primary key in that table. It doesn't have to be on the Month column or the ID column. Each table you have set up should have it's own primary key. You should add a new column to that table as an autonumber column and make that the primary.

 

[b-ballstyle]

Why would I put an extra field if I really dont need it?

 

[nanscombe]

I would presume you do need it so that you can consistently determine the correct order of the records.

If you only had one per month (for a given year) it may be less of a problem but if there is more than one what order would they be in?

If not an autonumber, maybe just a dateCreated field which would could be automatically populated, default value = now() in the table definition, when you created the record.

 

[AccessMSSQL]

But you do need it if you have properly structured tables that follow good relational database design principles.

Here's a link:
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

This is a great tutorial on relational design