Result set access in VBA

[swmagic]

Why is this not a valid construct from within a form? Specifically, when I put in the msgbox the whole operation just simply fails (no msgbox, no breakpoint). It works fine when I take out the Msgbox

Dim db As Database
Dim QD As QueryDef
Dim RS As Recordset
Set db = CurrentDb
'
Set QD = db.QueryDefs("A")
Set RS = QD.OpenRecordset
Do While Not RS.EOF
MsgBox "test=" & RS![SumOfChargeNetOut]
Me!txtExample = RS![SumOfChargeNetOut]
Me![txtChargesInCurrMo] = RS![SumOfChargeNetIn]
RS.MoveNext
Loop
RS.Close
db.Close
Set db = Nothing

 

[lupins46]

How are you running the code?

 

[swmagic]

I'm running the code from a command button on an Access form. I've tried other ways also:

Private Sub cboTestUpdate_Click()
'current month charges
Dim db As Database
Dim QD As QueryDef
Dim rs As Recordset
Set db = CurrentDb
Dim BufAnnualizedNPI As Variant

Set QD = db.QueryDefs("qryA")
Set rs = QD.OpenRecordset("qryA", dbOpenTable)

'This is my true goal!!!!!!
'BufAnnualizedNPI = rs![LastName]

MsgBox "hold"
rs.Close
db.Close
Set db = Nothing

End Sub

 

[PHV]

Have you tried this ?
Dim RS As [highlight]DAO.[/highlight]Recordset

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya swmagic . . . .

If its failing with the message box, then the detected error has to be with [blue]RS![SumOfChargeNetOut][/blue]. But I don't think this is the actual error. If for some reason the QueryDef is returing no records then [blue]RS![SumOfChargeNetOut][/blue] would return a null. This would cause the error (can't concatenate a null). So the error is precursor with your QueryDef.

[blue]Set QD = db.QueryDefs("qryA")[/blue]

Here your saying you have an existing query [blue]qryA[/blue]. The question is, does the query already exist, or was it your intent to create a query? If it was to create, that it should be [purple]Set QD = db.CreateQueryDefs("qryA")[/purple]. Also if you include a name, a permanent query is created. An empty string creates an temporary one.

See Ya! . . . . . .

 

[nabiS2K]

Try this instead and see if you get the same error...

    Dim db As Database
    Dim QD As QueryDef
    Dim RS As Recordset

    Set db = CurrentDb
    Set RS = db.QueryDefs("A").OpenRecordset

    Do While Not RS.EOF
        MsgBox "test=" & nz(RS![SumOfChargeNetOut], "")
        Me!txtExample = nz(RS![SumOfChargeNetOut], "")
        Me![txtChargesInCurrMo] = RS![SumOfChargeNetIn]
        RS.MoveNext
    Loop

    RS.Close
    db.Close
    Set db = Nothing

...or...

Private Sub cboTestUpdate_Click()
    'current month charges
    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDb
    Dim BufAnnualizedNPI As Variant
    
    Set rs = db.QueryDefs("qryA").OpenRecordset

    'This is my true goal!!!!!!
    If rs.RecordCount > 1 Then
         BufAnnualizedNPI = Nz(rs![LastName], 0)
    Else
         BufAnnualizedNPI = 0
    End If

    MsgBox "hold"
    rs.Close
    db.Close
    Set db = Nothing
    
End Sub

 

[CajunCenturion]

I think the issue is that you haven't moved to the first record yet.

Set RS = QD.OpenRecordset
RS.MoveFirst
Do While Not RS.EOF
     MsgBox "test=" & RS![SumOfChargeNetOut]

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein