How to update the update the contents of the variable not the variable itself?

[BigPaul]

I look at a lot of files to see when they were last updated.
I wanted to write a generic procedure to manage that so …..

[FONT=Arial][COLOR=blue][COLOR=blue][FONT=Arial]Public fDate As Variant[/FONT][/COLOR]
[COLOR=blue][FONT=Arial]Public vField As String[/FONT][/COLOR]
[COLOR=blue][FONT=Arial]Public vFile As String[/FONT][/COLOR]
 
[COLOR=blue][FONT=Arial]'GTSdata    [/FONT][/COLOR]
[COLOR=blue][FONT=Arial]vField = "txt_gts_data"[/FONT][/COLOR]
[COLOR=blue][FONT=Arial]vFile = "c:\gtsdata\gts_data.txt"  [/FONT][/COLOR]
[COLOR=blue][FONT=Arial]Call form_open_data(vField, vFile)[/FONT][/COLOR]
 
[COLOR=blue][FONT=Arial]------------------------------------------------------ [/FONT][/COLOR]
 
[COLOR=blue][FONT=Arial]Private Sub form_open_data(vField, vFile)[/FONT][/COLOR]
[COLOR=blue][FONT=Arial]fDate = Format(FileDateTime(vFile), "dd/mm/yyyy")[/FONT][/COLOR]
[COLOR=blue][FONT=Arial]Me.vField = fDate  '<<<<<<<<<<<<<<WRONG !!!!![/FONT][/COLOR]
[COLOR=blue][FONT=Arial]If DateValue(txt_DateToday) - DateValue(vField) < 5 Then[/FONT][/COLOR]
[COLOR=blue][FONT=Arial]    vField.BackColor = "64636"  'Green[/FONT][/COLOR]
[COLOR=blue][FONT=Arial]        etc. etc.[/FONT][/COLOR]
 
[/COLOR][/FONT]

What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate

What actually happens is the variable vfield gets updated from “txt_gts_data” to 19/08/2014 then later code falls over because the fieldname is lost :-(.

Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)

How do I say update the contents of the variable, not the variable itself?

 

[Mile-O]

Not sure I quite get what you're asiking here. The contents of the variable is all that it is. It's an empty box that you put a value or an object into.

 

[Mile-O]

Ah, hold on.

Sounds like you have a variable and a control both called vField.

Does using ByVal in your sub help?

Private Sub form_open_data(ByVal vField, ByVal vFile)

(To remove ambiguity, this is why it's a good practice to use prefixes for controls, variables, everything.

tblTables
qryQueries
frmForms
rptReports
mcrMacros
fldFields
strStrings
lngLongs
dteDates
txtTextboxes
cboComboboxes
lblLabels
etc.

 

[JHB]

Try the below, (not tested)

[FONT=Arial][COLOR=blue][COLOR=blue][FONT=Arial]Me(vField) = fDate [/FONT][/COLOR]
[/COLOR][/FONT]

 

[BigPaul]

Not quite Mike.

I have a public variable vField.

In a procedure I assign the name of a form control (a text box) which is called "txt_gts_data" to vField

I then call the Sub form_open_data and pass that form control name as vField

1  Private Sub form_open_data(vField, vFile)
2  fDate = Format(FileDateTime(vFile), "dd/mm/yyyy")
3  Me.vField = fDate  '<<<<<<<<<<<<<<WRONG !!!!!
4  If DateValue(txt_DateToday) - DateValue(vField) < 5 Then

Line 1 vField = "txt_gts_data"
Line 2 vField = "txt_gts_data"
Line 3 vField = "19/08/2014"
Line 4 crashes as vField is not the form control name "txt_gts_data" anymore.


I didn't want to change vField, I wanted to change the contents of the form control txt_gts_data (aka me.txt_gts_data) to 19/08/2014 (the date of the last update of the file, which is found in line 2)

 

[BigPaul]

Try the below, (not tested)

[FONT=Arial][COLOR=blue][COLOR=blue][FONT=Arial]Me(vField) = fDate [/FONT][/COLOR]
[/COLOR][/FONT]

That cracked it ~ simple when you know how. Thank you JHB. In case anyone later wants this solution here is the updated (working) code;

 Option Explicit
    Public fDate As Date
    Public vField, vFile As String
    Public vLeeWay As Integer
__________________________
Code within a procedure
    'GTSdata
    vField = "txt_gts_data"  'Name of the form field to update
    vFile = "c:\GTSdata\gts_data.txt"  'Path of the file to check
    vLeeWay = 3  'number of days before the warning turns amber
    Call form_open_data(vField, vFile)
__________________________
Private Sub form_open_data(vField, vFile)
    fDate = Format(FileDateTime(vFile), "dd/mm/yyyy")
    Me(vField) = fDate
    If DateValue(txt_DateToday) - DateValue(fDate) < vLeeWay Then
        Me(vField).BackColor = "64636"  'Green
        Me(vField).ForeColor = "0"      'Black
    Else
        If DateValue(txt_DateToday) - DateValue(vField) > (vLeeWay + 3) Then
            Me(vField).BackColor = "3937500"  'Red
            Me(vField).ForeColor = "16777215"  'White
        Else
            Me(vField).BackColor = "1030655"  'Amber
            Me(vField).ForeColor = "0"        'Black
        End If
    End If
End Sub

 

[Mile-O]

Ah, you were passing the control name. Got it! As, I see, have you now.

 

[JHB]

That cracked it ~ simple when you know how. Thank you JHB.

Like always - simple if you know how!
You're welcome.