Pull previous record value into current record

Where are you getting the error and what is the error?

I'm running the function in a query and the result I am getting is "#Error"

Call the function from a module and see step through the code to see where the error is happening or where the unexpected result shows up.

The error is on this line:  Run-time error '91'  Object variable or With block variable not set

Set rst = db.OpenRecordset("SELECT * FROM tblAVSFSNID WHERE tblNonCalcSpreads.ClientNumber= '" & ClientNumber & "'tblNonCalcSpreads.DateOfData = #" & Format(DateOfData, "yymm") = Format(DateAdd("m", -1, Date), "yymm") & "#")

Looks like your where criteria for DateOfData is creating a true/false instead of selecting a date.

Format(DateOfData, "yymm") = Format(DateAdd("m", -1, Date), "yymm")

this statement compares Format(DateOfData, "yymm")  to Format(DateAdd("m", -1, Date), "yymm")  and askes where they are equal.
Your criteria for your query should be either
  Format(DateOfData, "yymm") = Format(DateAdd("m", -1, Date), "yymm")
or
  Format(DateOfData, "yymm") not both.
Try this:

Set rst = db.OpenRecordset("SELECT * FROM tblAVSFSNID WHERE tblNonCalcSpreads.ClientNumber= '" & ClientNumber & "' tblNonCalcSpreads.DateOfData = #" & Format(DateAdd("m", -1, Date), "yymm") & "#")

Also you need a space between the apostrophe and tblNonCalcSpreds.DateOfData. (<"'tbl> should be <"' tbl>)

I'm still getting the same error on that line.

In this statement It seems that you are asking for all records from tblAVSFSNID but your criteria is from another table.  This table must be shown in the from portion of the statement.  though I think maybe you want all records from tblNonCalcSpreads where the client number matches and the YYMM format of DataofData matches the previous month <Format(DateAdd('m',-1,Date()),'yymm'))>.  If so then this below should give you that.  If not I will need more direction.

See if this does not get what you want.

Set rst = db.OpenRecordset("SELECT * FROM tblNonCalcSpreads WHERE (((tblNonCalcSpreads.ClientNumber)='" & ClientNumber & "') AND ((Format([DateOfData],'yymm'))=Format(DateAdd('m',-1,Date()),'yymm')));")

I'm not getting the error, but the result is still #Error.  The field EndingGrossAR is in the tblAVSFSNID table.  I even tried putting both tables in the FROM statement and I receive the error.  Maybe the date still isn't right?

Ummm... does both the tblAVSFSNID and tblNonCalcSpreads contain client numbers and dates for the data?

Let me make sure I understand what you are trying to do.  You want to find the last EndingGrossAR (which seems to be captured monthly) and populate that as the StartingGrossAR.  You do this by looking in two different tables based on whether BeginningGrossARCalc is blank or not.
If BeginningGrossARCalc is blank then you look for the record in one of the two tables (i don't know which since the field name is passed into the function) where the client number and date match (whatever is passed in) in the tblNonCalcSpreads table.
If BeginningGrossARCalc is blank then you look all records in tblAVSFSNID where the client number and date match (whatever is passed in) in the tblNonCalcSpreads table.  This data is then assigned to the function variable and used to update tblAVSPSNID.

So what do the tblAVSFSNID and tbleNonCalcSpreads tables contain?

Also in the true part of the if statement you are using beginningGrossARCalc as a field name, is it a field or a value?
Also do you intend on updating the value in tblAVSFSNID only whan the if statement is false?

Yes, the ClientNumber and DateOfData are the primary keys for the tblAVSFSNID and tblNonCalcSpreads tables.

Yes, you have the logic correct.

tblAVSFSNID contain fields for client data that require calculation.
tblNonCalcSpreads contain fields for client data that do not require calculations.

BeginningGrossARCalc is a field in the tblAVSFSNIDCalc table which stores a formula.

So if BeginningGrossARCalc contains a formula for that client, I want the result of that formula to be updated to tblAVSFSNID.  If there is no formula then pull the EndingGrossAR from the previous month and update this to tblAVSFSNID.

Can you give me an example of what the formula is for BeginningGrossARCalc?

Is the previous month data stored in tblAVSFSNID or tblNonCalcSpreads?

BeginningGrossARCalc doesn't actually have a formula in the table since I didn't know how to state the previous months EndingGrossAr in a way that Access would understand.  I have other calculation fields that I'm using the same concept for and was going to apply this solution to the others.  Maybe this one is a bad example.

The EndingGrossAR is stored in tblAVSFSNID.

I'm still getting #Error as the result in the query.  I do have other calculations taking place in the query.  Could it be the order of the calculations?  Or if one calculation relies on the result from another?

Lets see what the sql looks like when you run the function by taking the sql and assigning it to a variable (see attached function).  Step through the function until you get one line past where the variable (strTempSQL) is assigned the sql.  at this point go to the imediate window at type:
?strTempSQL
This will give you what text assigned to the variable.
Paste this text here as a comment so we can see the whole picture and try to figure out what is causing the error.

Public Function BeginningGrossARReturnCalc(ByVal ClientNumber As String, ByVal DateOfData As Date, ByVal BeginningGrossARCalc As String) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strTempSQL As String
    
    If BeginningGrossARCalc <> "" And IsNull(BeginningGrossARCalc) = False Then
        Set db = CurrentDb
        strTempSQL = "SELECT " & BeginningGrossARCalc & " " & _
                        "FROM tblNonCalcSpreads, tblAVSFSNID " & _
                        "WHERE (((tblNonCalcSpreads.ClientNumber)='" & ClientNumber & "') " & _
                        "AND ((tblNonCalcSpreads.DateOfData)=#" & DateOfData & "#));"
        Set rs = db.OpenRecordset(strTempSQL)
        BeginningGrossARReturnCalc = rs.Fields(0)
    Else
        'BeginningGrossARReturnCalc = Null
        strTempSQL = "SELECT * " & _
                        "FROM tblAVSFSNID " & _
                        "WHERE (((tblAVSFSNID.ClientNumber)='" & ClientNumber & "') " & _
                        "AND ((Format([DateOfData],'yymm'))=Format(DateAdd('m',-1,Date()),'yymm')));"
        Set rst = db.OpenRecordset(strTempSQL)
        BeginningGrossARReturnCalc = rst!EndingGrossAR
        'Recordset Cleanup
        rst.Close
        Set rst = Nothing
    End If
    'This will save the value back to the table
    strSQL = "UPDATE tblAVSFSNID " & _
                "SET BeginningGrossAR =" & BeginningGrossARReturnCalc & " " & _
                "WHERE (((tblAVSFSNID.ClientNumber)='" & ClientNumber & "') " & _
                "AND ((tblAVSFSNID.DateOfData)=#" & DateOfData & "#));"
    CurrentDb.Execute strSQL, dbFailOnError
    rs.Close
    Set rs = Nothing
    'db.Close
    Set db = Nothing
End Function

Select allOpen in new window

The error is occuring on the below line:
Set rst = db.OpenRecordset(strTempSQL)
Run-time error 91 Object variable or With block variable not set

here is what was in the immediate window:
SELECT * FROM tblAVSFSNID WHERE (((tblAVSFSNID.ClientNumber)='ABC0') AND ((Format([DateOfData],'yymm'))=Format(DateAdd('m',-1,Date()),'yymm')));

Put this text into a new query (sql view) and see what you get.  It maybe that the [dateofdate] field needs to be preceded by the table name.

That statement doesn't give me any results.  I tried to add the table name to the DateOfData field and it gives me a syntax error.

Can you post a sterilized copy (dummy data) of the database I can look at, that my be the only way to get to the bottom of this problem.

function in AVSFSNICalcFields module
PCDB-test2ee.zip

Now I see what is causing the runtime error 91..  Move your
SET db = CurrentDB
to before the if statement.  you established the object in the true part of the if statement but not the false part of the statement.

Do that and see where we are at.  This will likely cause other problems now that we get pass this one.

Also change the syntax on the second sql statement from

        strTempSQL = "SELECT * " & _
                        "FROM tblAVSFSNID " & _
                        "WHERE (((tblAVSFSNID.ClientNumber)='" & ClientNumber & "') " & _
                        "AND (((Format(DateOfData),'yymm')=Format(DateAdd('m',-1,Date()),'yymm')));"
to
        strTempSQL = "SELECT * " & _
                        "FROM tblAVSFSNID " & _
                        "WHERE (((tblAVSFSNID.ClientNumber)='" & ClientNumber & "') " & _
                        "AND ((Format([tblAVSFSNID]![DateOfData],'yymm'))=Format(DateAdd('m',-1,Date()),'yymm')));"

OK, now I'm getting error 3021 - No current record

That is because in the false part of the if statment you are looking for the previous month's data.  In this case there is no date in tblAVSFSNID for the month of 0805.  You can add a trap if no records are found like below:

        If rst.RecordCount > 0 Then
            BeginningGrossARReturnCalc = rst!EndingGrossAR
        Else
            BeginningGrossARReturnCalc = "N/A"
        End If

Or
You may want to search for the previous month based on the dateofdata variable.  The sql would change to:
        strTempSQL = "SELECT * " & _
                        "FROM tblAVSFSNID " & _
                        "WHERE (((tblAVSFSNID.ClientNumber)='" & ClientNumber & "') " & _
                        "AND ((Format([tblAVSFSNID]![DateOfData],'yymm'))=Format(DateAdd('m',-1,#" & DateOfData & "#),'yymm')));"

I changed the sql to your statement above.  I don't get any errors now, but the query result is still showing #Error.

In which query are using this function?

I was testing it with qryCalcTest, but I will be using it in qryAVSCalc.

The syntax you are using to call the function in the query is wrong.  It is never getting to the function to find the answer.  Change your qryCalcTest sql to

SELECT tblAVSFSNID.ClientNumber, tblAVSFSNID.DateOfData, tblAVSFSNIDCalc.BeginningGrossARCalc, BeginningGrossARReturnCalc([tblAVSFSNID].[ClientNumber],[tblAVSFSNID].[DateOfData],nz([tblAVSFSNIDCalc].[BeginningGrossARCalc])) AS BeginningGrossAR
FROM tblNonCalcSpreads INNER JOIN (tblDelqCalc INNER JOIN (tblAVSFSNID INNER JOIN tblAVSFSNIDCalc ON tblAVSFSNID.ClientNumber = tblAVSFSNIDCalc.ClientNumber) ON (tblAVSFSNIDCalc.ClientNumber = tblDelqCalc.ClientNumber) AND (tblDelqCalc.ClientNumber = tblAVSFSNID.ClientNumber)) ON (tblNonCalcSpreads.DateOfData = tblAVSFSNID.DateOfData) AND (tblNonCalcSpreads.ClientNumber = tblAVSFSNID.ClientNumber)
WHERE (((tblAVSFSNID.ClientNumber)="abc0") AND ((tblAVSFSNID.DateOfData)=#3/31/2007#));

I did remove the form requirements and replaced them with the test data you entered into the function.

That did work, but it didn't pull the correct number.  It should have pulled 7592, but instead I got 7813 which is the BeginningGrossAR for the previous month, not the EndingGrossAR.

ok, put a debug stop on the function and run the query again.  You can then step through and see where it is giving you unexpected result.

Something else I forgot (i changed it and did not tell you). Move the rs.close and set rs = nonthing from the bottom of the function to right above the else statement.

Ok, now I'm getting the correct result in the qryCalcTest.  But when I run it in the qryAVSCalc, I'm still getting #Error.

The syntax you are using to call the function in qryAVSCalc is still wrong.  You have to change it match qryCalcTest.

The syntax should be for qryAVSCalc should be
BeginningGrossAR: BeginningGrossARReturnCalc([tblNonCalcSpreads].[ClientNumber],[tblNonCalcSpreads].[DateOfData],nz([tblAVSFSNIDCalc].[BeginningGrossARCalc]))

you have the values listed [tablename.fieldname] instead of [tablename].[fieldname]

I would be willing to bet if you looked at the syntax used to call function in
CombinedAvailability
UnearnedInterestPct
CollectionsPctToGrossAR
NetAvailability
RenewalRatesPct

you will find the similar syntax problems.

I went through and changed all the syntax in the functions.  It seems that EndingGrossAR is not calculating correctly.  I just get 0.

I can look... what query is the EndingGrossARCalcReturn function called?

it's in the qryAVSCalc

I am not sure you noticed or not but the criteria you have listed for the clientNumber in this query does not exist.  I Think it is suppose to be  Forms![frmEditSpreads]![cmbClientNum]

I think the problem lies in two places.
First, your formulas contained in tblAVSFSNID use the same syntax/format that we identifed as bad above.  However, I don't think that format [tablename.fieldname] is neccesarlly wrong unless there is a space in the table or file name.  The syntax problem from above was that fact you where passing in null values which we fixed by adding nz() to the function.  The formulas in this table handle the null problem because they all seem to have the field contained in nz()  (<Example: Nz([tblAVSFSNID.BeginningGrossAR])+Nz([tblAVSFSNID.TotalVolume])-Nz([tblAVSFSNID.TotalLiquidation]) >).  So, I think it is up to you to fix the missing brackets in the middle of the tablename.fieldname if you want.

Second, the sql needed to calculate the EndingGrossARCalcReturn is flawed.  That there are two tables called for in the from clause but there is no way to relate these two fields.  According to you entity relationship diagram tblAVSFSNID and tblNonCalcSpreads are related 1 to 1 on both the clientNumber and dateofData fields.  This means that you can not have a record in one table without having it in the other table.  So given that, there is no need to use both tables in the sql just use tblAVSFSNID and change the SQL in that function to:

        strTempSQL = "SELECT " & EndingGrossARCalc & " " & _
                        "FROM tblAVSFSNID " & _
                        "WHERE tblAVSFSNID.ClientNumber = '" & ClientNumber & "' AND " & _
                        "tblAVSFSNID.DateOfData = #" & DateOfData & "#"

This same change probably needs to happen in all of your calculation functions, they all seem to be of the same design.  This change gives you a correctly calcualted EndingGrossARCalcReturn.

Thanks for catching that for me.

I have fixed the syntax and the sql, but am still having some issues with some of the calculations coming out correctly.  They are all in the qryAVSCalc.

TotalAvailability
EffectiveAdvanceRate
ExcessAvailability
CashCollectionsPct
UnearnedsDiscountsDlrReservesEtcPct
IneligiblesPct
EligibleReceivables

Would you mind taking a look for me?

given that some of these calculations call for field in both the tblavsfsnid and tblnoncalcspreads i would recomend the below change be made to each function calculating the values

TotalAvailability
EffectiveAdvanceRate
ExcessAvailability
CashCollectionsPct
UnearnedsDiscountsDlrReservesEtcPct
IneligiblesPct
EligibleReceivables
All of these need the sql adjusted.

This would be how they need to change
from this

Set rs = db.OpenRecordset("SELECT " & EligibleReceivablesCalc & " FROM tblNonCalcSpreads, tblAVSFSNID WHERE tblNonCalcSpreads.ClientNumber = '" & ClientNumber & "' AND tblNonCalcSpreads.DateOfData = #" & DateOfData & "#")

to this (with name changes)

Set rs = db.OpenRecordset("SELECT " & EligibleReceivablesCalc & " FROM tblNonCalcSpreads INNER JOIN tblAVSFSNID ON (tblNonCalcSpreads.DateOfData = tblAVSFSNID.DateOfData) AND (tblNonCalcSpreads.ClientNumber = tblAVSFSNID.ClientNumber) WHERE tblNonCalcSpreads.ClientNumber = '" & ClientNumber & "' AND tblNonCalcSpreads.DateOfData = #" & DateOfData & "#")

This change basicly cause the joins between the two tables.

Awesome!  Thank you so much for all of your help!