help with recordset "Fields"

[opopanax666]

[SOLVED] help with recordset "Fields"

Hi everyone,

not familiar with the "Fields" attribute (I think that's the problem), so asking the experts

Excerpt:

    Select Case intDAG
        Case 1
            strDAG = "MAA"
        Case 2
            strDAG = "DIN"
        Case 3
            strDAG = "WOE"
        Case 4
            strDAG = "DON"
        Case 5
            strDAG = "VRY"
        Case 6
            strDAG = "ZAT"
        Case 7
            strDAG = "ZON"
    End Select


For intWINKEL = 23 To 28

    strSQL = "SELECT [Basis_item_naam], [" & strDAG & "] FROM qryBasis_items_verdeel_new WHERE (([Bestemming_sub_ID] = " & intWINKEL & ") AND ([" & strDAG & "] IS NOT NULL))"

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
    If rst.EOF Then
        GoTo CleanUp2
    End If


    lngBESTELLING = Nz(Me.Bestelling_ID, 0)
    
    intTYPE = 1

    rst.MoveFirst
    With rst
        Do Until .EOF
            CurrentDb.Execute "INSERT INTO dbo_tblBESTELLING_DETAIL (bestelling_id, bestelling_detail_type, bestelling_detail_tekst, bestelling_detail_aantal) VALUES (" & lngBESTELLING & ", " & intTYPE & ", '" & rst![basis_item_naam] & "', " & rst.Fields(strDAG) & ")"
            .MoveNext
        Loop
    End With

next intWINKEL

The SELECT query works and outputs 2 columns ([basis_item_naam] & [WOE], when i choose SELECT CASE 3). So far, so good.

The INSERT INTO works too ("bestelling_id", "bestelling_detail_type" & "bestelling_detail_tekst" are filled in).

Problem is: the "bestelling_detail_aantal" field is empty. This is an integer field, and so is "[WOE]", so I think something is wrong with my "rst.Fields(strDAG)" thingy...

Can anybody see what could be wrong?


TIA,
James

 

[Minty]

You can refer to a recordset field in many ways

rst.Fields("YourFieldName")
rst!YourFieldName
rst(2) - would return the second field in the recordset

 

[pbaldy]

I would think that would work. Use a string and this to see how the SQL comes out:

http://www.baldyweb.com/ImmediateWindow.htm

You can also use it to see what the recordset value is.

Minty, I believe it would be rst(1).

 

[Minty]

Minty, I believe it would be rst(1).

Doh - of course it would be.

Just in case anyone reading is confused, the recordset fields index is numbered from 0 so rst(0) is the first field in the recordset.

 

[opopanax666]

Guys, i'm sooooooo sorry, and i'm such a dumb*ss.

I was looking at the wrong piece of code, that's activated by an other set of buttons, and when activated by these buttons, it works as intended...

So, no problems, everything OK

Thanks for looking into it, and again, so sorry for wasting your time :banghead:

 

[pbaldy]

No problem, glad you have it working.