Using a variable to replace DAO table reference for loop

[gangel]

Hi ppl,



SO i have this code:

Set IRS = CurrentDb.OpenRecordset("Select * FROM ITEMS where [deactivate] = false and [ManualInvoicing] = false", dbOpenDynaset)
Set Tmp = CurrentDb.OpenRecordset("TmpPriceList", dbOpenDynaset)

Do Until IRS.EOF
ItName = IRS(1)
StdQty = IRS(2)
Cost = IRS(3)
Sched = IRS(5)


Tmp.AddNew
Tmp!ItemName = ItName
Tmp!CostPrice = Cost
Tmp!StdQty = StdQty
Col = 1


Do Until Col = 12
Percent = 0.1 * Col
TMP REFERENCE: THIS IS WHERE I NEED TO DO Tmp!"Col" & Col <<<<<This is where i create it
Qty = StdQty * Percent


If Qty < 1 Then
Else
Call Priceit(ItName, StdQty, Cost, Sched, Qty, Col)
TMPREFERENCE = invamt <<<<<<<<<<This is where i use it
End If
Tmp.Update
Col = 1 + Col


Loop




IRS.MoveNext
Loop

So the idea is i have 10 columns literally named Col1 Col2 and so forth.

As the Variable COL goes up during the loop then the figure is "Invamt" is saved into the column on Tmp according to the variable.

Thus When Col = 1
Tmp!Col1

when it loops and Col = 2
The the Tmp! becomes Tmp!Col2

Im sure i should be using TableDef but ive never used it and it confused me quite a bit

Thanks for any help u can give

GAngel

[June7]

You have 12 fields with similar names: Col1, Col2, Col3, etc.? This sounds like non-normalized data structure.

Consider:

For x = 1 to 12
Percent = 0.1 * x
Qty = StdQty * Percent
If Qty >= 1 Then
Call Priceit(ItName, StdQty, Cost, Sched, Qty, Col)
Tmp.Fields("Col" & x) = invamt
End If
Tmp.Update
Next

Where is invamt value set?

[ssanfu]

First, "deactivate" & "Percent" are reserved words in Access and shouldn't be used for object names.

It is difficult to suggest good code without the full function/sub, but consider this: (untested)

    Dim knt As Integer   'counter
    '.
    '.
    '.
    Set IRS = CurrentDb.OpenRecordset("Select * FROM ITEMS where [deactivate] = false and [ManualInvoicing] = false", dbOpenDynaset)
    Set tmp = CurrentDb.OpenRecordset("TmpPriceList", dbOpenDynaset)

    Do Until IRS.EOF
        ItName = IRS(1)
        StdQty = IRS(2)
        Cost = IRS(3)
        Sched = IRS(5)

        'add new record
        tmp.AddNew
        tmp!ItemName = ItName
        tmp!CostPrice = Cost
        tmp!StdQty = StdQty
        tmp.Update

        '        Col = 1  
        knt = 1

        Do Until knt = 12
            Percent = 0.1 * knt
            '  TMP REFERENCE: THIS IS WHERE I NEED TO DO Tmp!"Col" & Col <<<<<This is where i create it
            Qty = StdQty * Percent


            If Qty >= 1 Then
                Call Priceit(ItName, StdQty, Cost, Sched, Qty, knt)

                'edit record
                tmp.Edit
                tmp.Fields("Col" & knt) = invamt      'TMPREFERENCE = invamt <<<<<<<<<<This is where i use it
                tmp.Update
            End If
            knt = knt + 1
        Loop

        IRS.MoveNext
    Loop

[gangel]

amazing, thanks june.

Invamt is set in PRICEIT

[gangel]

thanks steve, i will rename those parts rightaway!