The Joys of converting from A97

[mohobrien]

We got told this morning that A97 is out and AccessXP(2002?) is in. I converted the mdb. Once the reference to DAO 3.6 was added, all seemed fine until I tried running a query that uses this function.

' Use this one in queries
Public Function DAmountApplied2(Dispos As String) As Currency
Dim db As DAO.Database
Dim rstDAP As DAO.Recordset
Dim qdfDAP As DAO.QueryDef
Dim DAP As Currency
Dim strSQL As String

Set db = CurrentDb()
Set qdfDAP = db.CreateQueryDef("")
strSQL = "SELECT ALLDISP.[Disposition Number], ALLDISP.[Date of 1st Lease], ALLDISP.[Effective Date], ALLDISP.[Area (Hectares)] " & _
       "FROM ALLDISP " & _
       "WHERE (((ALLDISP.[Current Status])=""ACTIVE"")) AND (((ALLDISP.[Disposition Number])= """ & Dispos & """))" & _
       "ORDER BY ALLDISP.[Disposition Number]"
       
With qdfDAP
    Set rstDAP = db.OpenRecordset(strSQL)
End With

With rstDAP
        If .OpenRecordset.Fields(0).Value = Dispos Then
            If Left(.Fields("Disposition Number"), 2) = "Q-" Or Left(.Fields("Disposition Number"), 2) = "ML" Then
                If (Date - .Fields("Date of 1st Lease")) / 365.25 < 10# Then
                    DAP = .Fields("Area (Hectares)") * 25#
                Else
                    DAP = .Fields("Area (Hectares)") * 50#
                End If
                If (Date - .Fields("Date of 1st Lease")) / 365.25 > 20# Then DAP = .Fields("Area (Hectares)") * 75#
            ElseIf Left(.Fields("Disposition Number"), 2) = "CB" Or Left(.Fields("Disposition Number"), 2) = "S-" Then
                If (Date - .Fields("Effective Date")) / 365.25 < 10# Then
                    DAP = .Fields("Area (Hectares)") * 12#
                Else
                    DAP = .Fields("Area (Hectares)") * 25#
                End If
            ElseIf Left(.Fields("Disposition Number"), 2) = "MP" Then
                If (Date - .Fields("Effective Date")) / 365.25 < 1# Then
                    DAP = .Fields("Area (Hectares)") * 1.25
                Else
                    DAP = .Fields("Area (Hectares)") * 4#
                End If
            End If
        End If
End With
       rstDAP.Close
       DAmountApplied2 = DAP
End Function

It works perfectly in A97 but bombs in AccessXP in that the recordset rstDAP doesn't have any records. Like I say the identical code works in 97. Any suggestions as to a workaround?

 

[WayneRyan]

Mo,

What's Dispos?

You didn't say what the error was!

Dispos isn't declared and has no value. Generally, the conversion to A2000+
isn't that bad.

Wayne

 

[mohobrien]

Dispos is a string that is passed into the function.
I have a query that needs a field whose value is calculated from some other fields all of which are in a table called ALLDISP. If this calculated field is less than a certain number then parts of that record plus the calculated field is shown in the query result.
This calculated field is calculated by running this function on the field values in the SQL select query as shown. Dispos is one of the fields and is unique for each record in ALLDISP table.
The error is that while dispos has the value I'm expecting in the function there is no recordset. The recordset has an EOF and BOF as True and number of records as -1. It should be 1. I run the 97 version on one computer and XP version on another and step through the function. The 97 version picks up a record and the XP version does not.
This still isn't very clear is it?

 

[WayneRyan]

Mo,

Yeah, it's clear.

Didn't see Dispos in the Declaration.

You can't go by the recordcount unless you do a .MoveLast, but that's
not the problem.

You get .EOF and .BOF true.

That really means there are no records.

You seem to have all of the double-quotes covered and your code looks
OK, but what's with the QueryDef. You aren't using it. How about just
opening the recordset based on the SQL?

The QueryDef is = "" then you use the "With". As far as I can see your
QueryDef is Nothing.

I'll look at it again, just wanted to respond.

Wayne

 

[WayneRyan]

Mo,

Just don't see why it shouldn't return records.

Set db = CurrentDb()
strSQL = "SELECT ALLDISP.[Disposition Number], ALLDISP.[Date of 1st Lease], ALLDISP.[Effective Date], ALLDISP.[Area (Hectares)] " & _
         "FROM ALLDISP " & _
         "WHERE (((ALLDISP.[Current Status])=""ACTIVE"")) AND (((ALLDISP.[Disposition Number])= """ & Dispos & """))" & _
         "ORDER BY ALLDISP.[Disposition Number]"
       
Set rstDAP = db.OpenRecordset(strSQL)

Try removing the Where Clause. That better get you some records.

Then restore the Where Clause piece by piece.

"Where [Current Status] = 'Active' And [Disposition Number] = """ & Dispos & """"

If you know how to use the Debugger, this is a snap.

Let me know,
Wayne

 

[mohobrien]

You're right, chuck out the where clause and lots of records. There must be something in my syntax that isn't read the same way in xp vs 97. I'll get it soon. Thanks for pointing me the right way Wayne. I'll post the solution when I get it.

 

[yeatmanj]

What is the exact error message you are getting?

 

[mohobrien]

Error 3021
No Current Record

 

[The_Doc_Man]

The error message is spot-on for an empty record set. ("No records" means "no current records" every time I've ever hit this situation.)

So the next step is, using debugger, find out what is being passed in to your Dispos string. Write it down. If quotes are involved, make sure you get all blanks properly located too.

Then close that module, let it fail, have it do whatever it does. When you are back to the form or DB window or however you end up after a failure, get to either your table pane or your query pane.

Open a table or query that includes your field called Disposition Number. Using the Binocular icon, manually search the table/view for that disposition number. See if you actually HAVE that number anywhere in your table. 'cause if you don't, then your code is doing what it is supposed to do.

 

[yeatmanj]

Also, have you verified that it isn't the spaces and special characters in your naming conventions. Most of the time you have to enclose names with spaces or other special characters in brackets. Sometimes quotes aren't enough.

 

[mohobrien]

Solved

I got waylaid for a week because of autocad upgrades as well. Finally got them sorted out. I don't know why but in xp you have to check if the recordset is empty first, in 97 you don't. All I did was add a Do While ..Loop with a MoveNext and all is well! Thank you for the responses guys.

With rstDAP
        Do While Not .EOF
        If .OpenRecordset.Fields(0).Value = Dispos Then
            If Left(.Fields("Disposition Number"), 2) = "Q-" Or Left(.Fields("Disposition Number"), 2) = "ML" Then
                If (Date - .Fields("Date of 1st Lease")) / 365.25 < 10# Then
                    DAP = .Fields("Area (Hectares)") * 25#
                Else
                    DAP = .Fields("Area (Hectares)") * 50#
                End If
                If (Date - .Fields("Date of 1st Lease")) / 365.25 > 20# Then DAP = .Fields("Area (Hectares)") * 75#
            ElseIf Left(.Fields("Disposition Number"), 2) = "CB" Or Left(.Fields("Disposition Number"), 2) = "S-" Then
                If (Date - .Fields("Effective Date")) / 365.25 < 10# Then
                    DAP = .Fields("Area (Hectares)") * 12#
                Else
                    DAP = .Fields("Area (Hectares)") * 25#
                End If
            ElseIf Left(.Fields("Disposition Number"), 2) = "MP" Then
                If (Date - .Fields("Effective Date")) / 365.25 < 1# Then
                    DAP = .Fields("Area (Hectares)") * 1.25
                Else
                    DAP = .Fields("Area (Hectares)") * 4#
                End If
            End If
        End If
        .MoveNext
        Loop
End With