Why Does This Code Not Work? 1

[david6633]

Can anyone point me in the right direction? I am a bit rusty with VBA (last used in Access 97)

I have the following code that, in principle, works. What it is doing is a calculation for output on a report. The problem is that it is using the first record in the database every time.

Function WarrantRenewalDate()
    
    Const RenewDay = 31
    Const RenewMonth = 3
    Const RenewPeriod = 5
    
    Dim rs As New ADODB.Recordset
    Dim StartMonth, StartYear, CurrentYear, RenewYear As Integer
    
    CurrentYear = Year(Now())
    
    rs.Open "SELECT * from LeaderData", CurrentProject.Connection
    
    If (rs!WarrantRenewDate) Then
        StartMonth = Month(rs!WarrantRenewDate)
        StartYear = Year(rs!WarrantRenewDate)
    Else
        StartMonth = Month(rs!LeaderStartDate)
        StartYear = Year(rs!LeaderStartDate)
    End If
    
    If (StartMonth) > RenewMonth Then
        StartYear = StartYear + 1
    End If
    
    RenewYear = Round(((CurrentYear - StartYear) / RenewPeriod) + 0.5)
    If (RenewYear) = 0 Then
        RenewYear = CurrentYear + RenewPeriod
    Else
        RenewYear = StartYear + (RenewYear * RenewPeriod)
    End If
    WarrantRenewalDate = DateSerial(RenewYear, RenewMonth, RenewDay)

End Function

Where have I gone wrong?


David
Remember: You only know what you know
and - you don't know what you don't know!

 

[hneal98]

If you are trying to do this function for the whole recordset, you don't have anything to advance you through the recordset. You should have in your code a movenext someplace.

rs.movenext

 

[Darrylles]

Hi,

It's difficult to tell without the data.

At a glance,
RenewYear = StartYear + (RenewYear * RenewPeriod)
seems dodgey - do you really want to multiply 2004 by 2 (if renewPeriod is 2) - this would give 4008.

I suggest you right-click the first IF statment and select Toggle -> Breakpoint, then run the form.

This will stop at the first IF statement and highlight it. You can then press F8 to step through each line of code.
If you then hover the mouse-pointer over the fields / variables - their current content will be displayed.

This way, you can evaluate whether your fields / variables contain 'reasonable' values.

ATB

Darrylle







"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@hotmail.com

 

[Darrylles]

Hi,

hneal - roger that - lol.

You need a loop construct in there (repeat until etc).

ATB

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@hotmail.com

 

[Golom]

This line
[tt]
If (rs!WarrantRenewDate) Then
[/tt]
may be giving you a problem. VBA is going to convert the condition in an If statement to a Boolean. The rule that it uses if "if the value is zero then FALSE; otherwise TRUE". Any date other than midnight, December 31, 1899 will be non-zero so this will probably always be TRUE. Is that what you intend? Do you perhaps want
[tt]
If IsNull(rs!WarrantRenewDate) Then
[/tt]?

 

[david6633]

Thanks for the responses but I think I am loosing the plot.

Why do I have to loop through the recordset when I call this function for each line on the report? Surely it should calculate as each line is produced?

David
Remember: You only know what you know
and - you don't know what you don't know!

 

[RoyVidar]

The way this is set up, you don't have any where condition, so it opens the whole table, and you don't loop thru the recordset, so basically you'll get the same value every time you call the function (first record of the table).

If you need it to perform different per each line, then you'll need to make the function avare of it, for instance thru passing a control value to the function and either use it as criteria (see below), or issue a .find on the recordset:

[tt]Function WarrantRenewalDate([highlight cyan]YourVal as <SomeDataType>[/highlight]) [highlight cyan] as Date[/highlight]

' your other stuff

rs.Open "SELECT * from LeaderData [highlight cyan]WHERE SomeField = " & YourVal[/highlight], CurrentProject.Connection
...[/tt]

Then just pass the value to the function...

Added also a datatype for the return value, else it's variant.

Roy-Vidar

 

[david6633]

Thanks Roy-Vider. It is now working as I expeced it to, about three weeks ago!

David
Remember: You only know what you know
and - you don't know what you don't know!