Arrays

R

Rodger

OK I know you all told me that I should do this diffently, but I got it to
work. I am just having one problem. Here is the thing. When my form loads
I have a Event Run on the OnCurrent for each subform and this removes the
array for the previos form, so my array only has the data for the last
subform. Does anyone know how I could pass a variable so I could have
multiple arrays? Or should I store this in a temp table.

The Goal is to store history for the record when the user makes a change to
the data on any form . . . .I want to show the old value as well as the new
value. all the other fileds work it is only the old value where I am having
an issue. If I have one subform it works just fine it is when I start
adding multiple that only the last one works . . . .

Here is my code:

I included the myHistory, but the real issue is in myCurrent Sub . . . . .

'****************************************************************
Private Sub Form_Current()
Call myCurrent("frm_Main", "frm_Customres")
End Sub

'****************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call myHistory("frm_Main", Me.CUS_ID, "frm_Customres")
End Sub
'****************************************************************
Public Sub myCurrent(myForm, mySubForm)

Dim myText As Control, C As Control, X As Integer
Dim form1 As Form, form2 As Form

If Nz(mySubForm, " ") >= " " Then
Set form1 = Forms(myForm)
Set form2 = form1(mySubForm).Form
Else
Set form2 = Forms(myForm)
End If

ReDim myArray(form2.Controls.Count - 1)


X = -1
For Each C In form2.Controls

X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip
Updates field.
'If C.Name = "Updates" Then GoTo TryNextC
myArray(X) = C.Value

End Select

TryNextC:
Next C

Set form1 = Nothing
Set form2 = Nothing

End Sub
'********************************************************************

Public Sub myHistory(myForm, myID, mySubForm)

Dim D As Control, form1 As Form, form2 As Form
Dim myDB, myRS, myNewRecord, myTable, myValue, myArrayValue

Set myDB = CurrentDb()
Set myRS = myDB.openrecordset("HISTORY")

'Check each data entry control for change and record old value of
Control.
'Set the Array Counter

If Nz(mySubForm, " ") >= " " Then
Set form1 = Forms(myForm)
Set form2 = form1(mySubForm).Form
Else
Set form2 = Forms(myForm)
End If


X = -1
For Each D In form2.Controls

' Only check data entry type controls.
X = X + 1
Select Case D.ControlType

Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip Updates field.
myValue = D.Value

'If D.Name = "Updates" Then GoTo TryNextD
If form2.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] =
Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = "This is a new record"
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
GoTo TryNextD 'Exit Sub
End If


' If control was previously Null, record "previous value was
blank."
'myArrayValue = myArray(X)
If IsNull(Array(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = "Previous value was blank."
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
ElseIf myValue <> myArray(X) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = myArray(X)
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
End If
End Select
TryNextD:
Next D

Set form1 = Nothing
Set form2 = Nothing

End Sub
'**************************************************************************
 
R

Roger Carlson

You got it to work, but you didn't get it to work. Seems to me this is
precisely the problem that Marshal and Doug warned you about. Time to
re-think.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Rodger said:
OK I know you all told me that I should do this diffently, but I got it to
work. I am just having one problem. Here is the thing. When my form
loads
I have a Event Run on the OnCurrent for each subform and this removes the
array for the previos form, so my array only has the data for the last
subform. Does anyone know how I could pass a variable so I could have
multiple arrays? Or should I store this in a temp table.

The Goal is to store history for the record when the user makes a change
to
the data on any form . . . .I want to show the old value as well as the
new
value. all the other fileds work it is only the old value where I am
having
an issue. If I have one subform it works just fine it is when I start
adding multiple that only the last one works . . . .

Here is my code:

I included the myHistory, but the real issue is in myCurrent Sub . . . . .

'****************************************************************
Private Sub Form_Current()
Call myCurrent("frm_Main", "frm_Customres")
End Sub

'****************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call myHistory("frm_Main", Me.CUS_ID, "frm_Customres")
End Sub
'****************************************************************
Public Sub myCurrent(myForm, mySubForm)

Dim myText As Control, C As Control, X As Integer
Dim form1 As Form, form2 As Form

If Nz(mySubForm, " ") >= " " Then
Set form1 = Forms(myForm)
Set form2 = form1(mySubForm).Form
Else
Set form2 = Forms(myForm)
End If

ReDim myArray(form2.Controls.Count - 1)


X = -1
For Each C In form2.Controls

X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip
Updates field.
'If C.Name = "Updates" Then GoTo TryNextC
myArray(X) = C.Value

End Select

TryNextC:
Next C

Set form1 = Nothing
Set form2 = Nothing

End Sub
'********************************************************************

Public Sub myHistory(myForm, myID, mySubForm)

Dim D As Control, form1 As Form, form2 As Form
Dim myDB, myRS, myNewRecord, myTable, myValue, myArrayValue

Set myDB = CurrentDb()
Set myRS = myDB.openrecordset("HISTORY")

'Check each data entry control for change and record old value of
Control.
'Set the Array Counter

If Nz(mySubForm, " ") >= " " Then
Set form1 = Forms(myForm)
Set form2 = form1(mySubForm).Form
Else
Set form2 = Forms(myForm)
End If


X = -1
For Each D In form2.Controls

' Only check data entry type controls.
X = X + 1
Select Case D.ControlType

Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip Updates field.
myValue = D.Value

'If D.Name = "Updates" Then GoTo TryNextD
If form2.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] =
Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = "This is a new record"
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
GoTo TryNextD 'Exit Sub
End If


' If control was previously Null, record "previous value
was
blank."
'myArrayValue = myArray(X)
If IsNull(Array(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = "Previous value was blank."
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
ElseIf myValue <> myArray(X) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = myArray(X)
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
End If
End Select
TryNextD:
Next D

Set form1 = Nothing
Set form2 = Nothing

End Sub
'**************************************************************************
Click to expand...
 
R

Rodger

Well I got 95% of it to work . . . . . it just won't give me one field . . .
Yes I know that is what they warned me about . . . . but I still had to try
and learn for my self . . . . Do you think i would have better luck if i
moved the Event fro the OnCurrent to onFocus?


Roger Carlson said:
You got it to work, but you didn't get it to work. Seems to me this is
precisely the problem that Marshal and Doug warned you about. Time to
re-think.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Rodger said:
OK I know you all told me that I should do this diffently, but I got it to
work. I am just having one problem. Here is the thing. When my form
loads
I have a Event Run on the OnCurrent for each subform and this removes the
array for the previos form, so my array only has the data for the last
subform. Does anyone know how I could pass a variable so I could have
multiple arrays? Or should I store this in a temp table.

The Goal is to store history for the record when the user makes a change
to
the data on any form . . . .I want to show the old value as well as the
new
value. all the other fileds work it is only the old value where I am
having
an issue. If I have one subform it works just fine it is when I start
adding multiple that only the last one works . . . .

Here is my code:

I included the myHistory, but the real issue is in myCurrent Sub . . . . ..

'****************************************************************
Private Sub Form_Current()
Call myCurrent("frm_Main", "frm_Customres")
End Sub

'****************************************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call myHistory("frm_Main", Me.CUS_ID, "frm_Customres")
End Sub
'****************************************************************
Public Sub myCurrent(myForm, mySubForm)

Dim myText As Control, C As Control, X As Integer
Dim form1 As Form, form2 As Form

If Nz(mySubForm, " ") >= " " Then
Set form1 = Forms(myForm)
Set form2 = form1(mySubForm).Form
Else
Set form2 = Forms(myForm)
End If

ReDim myArray(form2.Controls.Count - 1)


X = -1
For Each C In form2.Controls

X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip
Updates field.
'If C.Name = "Updates" Then GoTo TryNextC
myArray(X) = C.Value

End Select

TryNextC:
Next C

Set form1 = Nothing
Set form2 = Nothing

End Sub
'********************************************************************

Public Sub myHistory(myForm, myID, mySubForm)

Dim D As Control, form1 As Form, form2 As Form
Dim myDB, myRS, myNewRecord, myTable, myValue, myArrayValue

Set myDB = CurrentDb()
Set myRS = myDB.openrecordset("HISTORY")

'Check each data entry control for change and record old value of
Control.
'Set the Array Counter

If Nz(mySubForm, " ") >= " " Then
Set form1 = Forms(myForm)
Set form2 = form1(mySubForm).Form
Else
Set form2 = Forms(myForm)
End If


X = -1
For Each D In form2.Controls

' Only check data entry type controls.
X = X + 1
Select Case D.ControlType

Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip Updates field.
myValue = D.Value

'If D.Name = "Updates" Then GoTo TryNextD
If form2.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] =
Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = "This is a new record"
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
GoTo TryNextD 'Exit Sub
End If


' If control was previously Null, record "previous value
was
blank."
'myArrayValue = myArray(X)
If IsNull(Array(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = "Previous value was blank."
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
ElseIf myValue <> myArray(X) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
myRS![HIS_FIELD] = D.name
myRS![HIS_FORM] = form2.name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = form2.RecordSource
myRS![HIS_OLD_VALUE] = myArray(X)
myRS![HIS_NEW_VALUE] = D.Value
myRS![HIS_DATE_CHANGE] = Date
myRS![HIS_TIME_CHANGE] = Time()
myRS.Update
End If
End Select
TryNextD:
Next D

Set form1 = Nothing
Set form2 = Nothing

End Sub
'**************************************************************************
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Tracking Changes in both the main form and the subform 1

Top