Using a variable in an update statement

[Mark Wild]

Hi,

I'm trying to slim down some of the code I've got and have a process that is massivly repetative (the update line would drop from 20 lines *6 currencies =120 lines to just 2 lines..)

What I'd like to do is replace the month number and the currency (GBP is this example) with a variable (example on first line of update)

With rstCCYRates
     rstReuters.MoveFirst
        'Do Until rstReuters.EOF
            .AddNew
                !DATE = DATE
               
               Select Case strCCY
               
                Case "GBP"
                
                    ![& int+1 &  mnth] = rstReuters![ & strCCY & ]
                    ![2 mnth] = rstReuters![GBP]
                    ![3 mnth] = rstReuters![GBP]
                    ![4 mnth] = rstReuters![GBP]
                    ![5 mnth] = rstReuters![GBP]
                    ![6 mnth] = rstReuters![GBP]
                    ![7 mnth] = rstReuters![GBP]
                    ![8 mnth] = rstReuters![GBP]
                    ![9 mnth] = rstReuters![GBP]
                    ![10 mnth] = rstReuters![GBP]
                    ![11 mnth] = rstReuters![GBP]
                    ![12 mnth] = rstReuters![GBP]
                    
                    ![2 YR] = rstReuters![GBP]
                    ![3 YR] = rstReuters![GBP]
                    ![4 YR] = rstReuters![GBP]
                    ![5 YR] = rstReuters![GBP]
                    ![6 YR] = rstReuters![GBP]
                    ![7 YR] = rstReuters![GBP]
                    ![8 YR] = rstReuters![GBP]
                    ![9 YR] = rstReuters![GBP]
                    ![10 YR] = rstReuters![GBP]

Is it possibe to do this and what is the syntax?

Thanks,

Mark

 

[cable]

Yea its possible, and its a case of using a different way to reference your objects.

In this example it uses the ("objectname") of referencing, and as its a string you can manipulate this anyhow you want.

Dim F As Form
Dim sN As String

'Open UDField table
Set myDB = CurrentDb
Set myRS = myDB.OpenRecordset("SELECT * FROM UserDefinedFields WHERE [FormName]='Applicant';")
Set F = Me

Do Until myRS.EOF
    'get the field name
    sN = myRS!FieldName
    
    'set the caption
    F("l" & sN).Caption = myRS!FieldLabel

So one of your lines would look something like:
rstccyrates(iCounter & " mnth")=rstreuters(strCurrency)
where icounter and strcurrency are looped through.

 

[Mark Wild]

THanks Chris.

The solution I did was

 For iCounter = 0 To 11 ' for the incremental months
                
                    If iCounter > 0 Then ' icounter 0 adds the first record, all others must edit that record
                        .FindFirst "Date=#" & DATE & "#" ' find the date that is being updated
                        .Edit
                    End If
                
            rstCCYRates(iCounter + 1 & " Mnth") = rstReuters(strCCY)
                    
        .Update