Resolving Object name at runtime (VBA)

[ChrisW75]

Hi,
I've got a 2 dimensional array containing a field (text box) name and it's initial value. When I come to update my database, I want to cycle through the array and check each value against the current value to check for changes (I'll then be sending changes to an audit table). How can I get the field name out of the aray and resolved as a text box name.

I was looking at something like

If oldValues(2, 1).Text <> oldValues(2, 2) Then ...

but it doesn't resolve the "oldValues(2, 1)" as an object name. [oldValues(2,1) is the name of the text box and oldValues(2,2) is the initial value]

How can I get Access to resolve this at runtime rather than compile time?

 

[alehawk]

Maybe the index is wrong.
Supose you got txtdata(0) and you load a laber for each db record...

do until rs1.bof or rs1.eof
counter=counter+!
load txtdata(counter)
txtdata(counter)= your rs1 value
loop

if txtdata(1).text<>txtdata(2).text then


Hope it helps.

 

[ChrisW75]

I got this working in the end. I ended up cycling through each item on the screen, checking to see if it was a text box, then matching it's name property to the name stored in the array. {Array defined as oldvalues(i,1) is the fieldname and oldvalues(i,2) is the initial value}

Private Sub updAudit()
Dim i As Integer
Dim myControl As Control
Dim newVal As String
Dim n As Integer
Dim blah As Variant

'cycle through each control on the form
For Each myControl In Controls
'Continue with checking is control is a text box
If myControl.ControlType = 109 Then
'cylce through each record in the array
For i = 0 To UBound(oldValues())
'Check to see if the text box name is in the array
If oldValues(i, 1) = myControl.Properties("Name") Then
'See if the initial value stored is different from the value in the text box
If oldValues(i, 2) <> myControl.Value Then
'write an audit record passing out values for...
' table table key fieldname field value new value
WriteAuditUpdate "tblAssessorDetails", oldValues(0, 2), oldValues(i, 1), oldValues(1, 2), myControl.Value
End If
'once we've matched our record, exit loop
Exit For
End If
Next
End If
Next


End Sub

 

[alehawk]

Well, congratulations, I hate those kind of errors where u dont know where to start!
Cya!

 

[ChrisW75]

Yep. The whole problem that I've been trying to solve is this.
We have an existing Access appliation to which I have to add auditing capability, so that if certain fields are changed through the forms, we record the old and new values, when it was changed, who changed it etc. The part above was the second half of the solution. The first half of the solution was to make a change to the data load procedure to store the fieldname and value in an array as it gets loaded as follows:-


AssessorID = rst.Fields("AssessorID")
oldValues(0, 1) = "AssessorID"
oldValues(0, 2) = rst.Fields("AssessorID")
PERSON_CERT_ASSESSOR_NBR = rst.Fields("PERSON_CERT_ASSESSOR_NBR")
oldValues(1, 1) = "PERSON_CERT_ASSESSOR_NBR"
oldValues(1, 2) = rst.Fields("PERSON_CERT_ASSESSOR_NBR")

and so on. This way, when I click on OK, I direct processing to the procedure updAudit shown above and hey presto, we have auditing.

Now I just have to crowbar this process into another 9 forms...

It all works now!

Cheers alehawk.

 

[ChrisW75]

PS- 1 error in the code.

WriteAuditUpdate "tblAssessorDetails", oldValues(0, 2), oldValues(i, 1), oldValues(1, 2), myControl.Value

should be

WriteAuditUpdate "tblAssessorDetails", oldValues(0, 2), oldValues(i, 1), oldValues(i, 2), myControl.Value

 

[strongm]

If this is VBA in Access, then the Eval function is your friend...