How can I use SET programmically.

  • Thread starter Thread starter Rodger
  • Start date Start date
R

Rodger

All,I would like to be able to loop through a subform to get all of the
controls and what each value is. I am going to put them all in an array. I
am trying to create a function to do this. How can sendThe name of my
"Main" form and Subform so I can SET then as object?This is what I am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this does not
work but this is the normal syntax for this method.TIA,Rodger
 
Hi Rodger,

Incomplete air-code snippet:

Dim C As Control
Dim j As Long
Dim Values() As Variant

...
With Forms("FormName").Controls("TheSubformControl").Form
Redim Values(.Controls.Count - 1)
For j = 0 to .Controls.Count
Set C = .Controls(j)
'do stuff with C
Values(j) = C.Value
Next j
End With

"FormName" is the name of the form. "TheSubformControl" is the name of
the subform control on the form (not the name of the subform itself). If
this code is in the form's class module, you can use the Me keyword
instead of referring to the form by name, e.g.
With Me.TheSubformControl.Form

The snippet above may not work as you expect. Remember that many
controls don't have a Value property. I don't know what you're trying to
achieve by putting the values of controls on a subform into an array -
but there's almost certainly a better way of doing it.
 
Rodger said:
All,I would like to be able to loop through a subform to get all of the
controls and what each value is. I am going to put them all in an array. I
am trying to create a function to do this. How can sendThe name of my
"Main" form and Subform so I can SET then as object?This is what I am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this does not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...


If you really need the name of the objects, it would be:

x = MyFunction("main", "subformCONTROLNAME")

Function MyFunction(fname As String, sfname As String)
Dim frm As Form
Dim sfm As Form
Dim ctl As Control
Dim ary(. . .) As Variant
Dim k As integer

Set frm = Forms(fname)
Set sfm = frm(sfname).Form
For Each ctl In sfm
ary(k) = ctl.Value
k = k + 1
Next ctl
. . .

OTHO, the function (shouldn't this be a Sub?) would be more
general if you use a form object as the argument:

call from main form:
x = MyFunction(Me.subformCONTROLNAME.Form)

call from subform:
x = MyFunction(Me)

Function MyFunction(frm As Form)
Dim ctl As Control
Dim ary(. . .) As Variant
Dim k As integer

For Each ctl In frm
ary(k) = ctl.Value
k = k + 1
Next ctl
. . .

Still questioning your assumptions. Why use an array? The
need for an array in Access is rare enough to raise a red
flag about your design.
 
John/John,

Thank you Both for your reply. The reason I was doing this in an array, is
because I copied the code from Microsoft . . . :) I have been trying to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19 of those
tabs have at least one subform on them. I want to be able to create a
function that will let me know when a user makes a change to the data. So I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all the values
of the form into an array, then on the BeforeUpdate I compare the values of
the current form to what is in the array and if there is a change I write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the code that I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event **************************
'***************************************************************
'At the momment this is on the form and I call it from the BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub

'*********************************************************************
 
Assuming you're talking about bound forms, the Dirty property will be True
when anything's changed on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
John/John,

Thank you Both for your reply. The reason I was doing this in an array,
is
because I copied the code from Microsoft . . . :) I have been trying to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19 of those
tabs have at least one subform on them. I want to be able to create a
function that will let me know when a user makes a change to the data. So
I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all the
values
of the form into an array, then on the BeforeUpdate I compare the values
of
the current form to what is in the array and if there is a change I write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the code that
I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event **************************
'***************************************************************
'At the momment this is on the form and I call it from the BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub

'*********************************************************************
Rodger said:
All,I would like to be able to loop through a subform to get all of the
controls and what each value is. I am going to put them all in an array. I
am trying to create a function to do this. How can sendThe name of my
"Main" form and Subform so I can SET then as object?This is what I am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this does not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
Click to expand...
 
Doug,

So how would I use this with my current design, or would I have to re-design
everything?

Thank you,
Rodger



Douglas J. Steele said:
Assuming you're talking about bound forms, the Dirty property will be True
when anything's changed on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
John/John,

Thank you Both for your reply. The reason I was doing this in an array,
is
because I copied the code from Microsoft . . . :) I have been trying to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19 of those
tabs have at least one subform on them. I want to be able to create a
function that will let me know when a user makes a change to the data. So
I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all the
values
of the form into an array, then on the BeforeUpdate I compare the values
of
the current form to what is in the array and if there is a change I write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the code that
I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event **************************
'***************************************************************
'At the momment this is on the form and I call it from the BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub

'*********************************************************************
Rodger said:
All,I would like to be able to loop through a subform to get all of the
controls and what each value is. I am going to put them all in an
Click to expand...
array.
I
am trying to create a function to do this. How can sendThe name of my
"Main" form and Subform so I can SET then as object?This is what I am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this does not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
Click to expand...
Click to expand...
 
I would think that all you'd need to do is put code in the BeforeUpdate
event of each of the forms. I don't see an advantage to having all the logic
in one place.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
Doug,

So how would I use this with my current design, or would I have to
re-design
everything?

Thank you,
Rodger



Douglas J. Steele said:
Assuming you're talking about bound forms, the Dirty property will be
True
when anything's changed on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
John/John,

Thank you Both for your reply. The reason I was doing this in an
array,
is
because I copied the code from Microsoft . . . :) I have been trying
to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19 of
those
tabs have at least one subform on them. I want to be able to create a
function that will let me know when a user makes a change to the data. So
I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all the
values
of the form into an array, then on the BeforeUpdate I compare the
values
of
the current form to what is in the array and if there is a change I write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the code that
I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event **************************
'***************************************************************
'At the momment this is on the form and I call it from the BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub

'*********************************************************************
All,I would like to be able to loop through a subform to get all of
the
controls and what each value is. I am going to put them all in an array.
I
am trying to create a function to do this. How can sendThe name of my
"Main" form and Subform so I can SET then as object?This is what I am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this does not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
Click to expand...
Click to expand...
 
I am not sure what you mean. I want all my code in one place so I only have
to make the change once. So should I use just the BeforeUpdate or the
onDirty? I guess I am not sure of the direction I need to take.


Douglas J. Steele said:
I would think that all you'd need to do is put code in the BeforeUpdate
event of each of the forms. I don't see an advantage to having all the logic
in one place.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
Doug,

So how would I use this with my current design, or would I have to
re-design
everything?

Thank you,
Rodger



Douglas J. Steele said:
Assuming you're talking about bound forms, the Dirty property will be
True
when anything's changed on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John/John,

Thank you Both for your reply. The reason I was doing this in an
array,
is
because I copied the code from Microsoft . . . :) I have been trying
to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19 of
those
tabs have at least one subform on them. I want to be able to create a
function that will let me know when a user makes a change to the
Click to expand...
data.
So
I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all the
values
of the form into an array, then on the BeforeUpdate I compare the
values
of
the current form to what is in the array and if there is a change I write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the code that
I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event **************************
'***************************************************************
'At the momment this is on the form and I call it from the BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub

'*********************************************************************
All,I would like to be able to loop through a subform to get all of
the
controls and what each value is. I am going to put them all in an array.
I
am trying to create a function to do this. How can sendThe name of my
"Main" form and Subform so I can SET then as object?This is what I am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this does not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
Click to expand...
Click to expand...
 
While I understand the desire to centralize, I think it's better to put the
logging functionality into each of the forms. Remember that a form can be
used by itself, or as a subform for more than one parent form.

My recommendation is that you put the code into the BeforeUpdate of each of
the forms involved.

Yes, I realize that's a huge rework based on what you already seem to have,
but I believe it'll be more reliable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
I am not sure what you mean. I want all my code in one place so I only
have
to make the change once. So should I use just the BeforeUpdate or the
onDirty? I guess I am not sure of the direction I need to take.


Douglas J. Steele said:
I would think that all you'd need to do is put code in the BeforeUpdate
event of each of the forms. I don't see an advantage to having all the logic
in one place.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
Doug,

So how would I use this with my current design, or would I have to
re-design
everything?

Thank you,
Rodger



message
Assuming you're talking about bound forms, the Dirty property will be
True
when anything's changed on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John/John,

Thank you Both for your reply. The reason I was doing this in an
array,
is
because I copied the code from Microsoft . . . :) I have been
trying
to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19 of
those
tabs have at least one subform on them. I want to be able to create a
function that will let me know when a user makes a change to the data.
So
I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all
the
values
of the form into an array, then on the BeforeUpdate I compare the
values
of
the current form to what is in the array and if there is a change I
write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the
code
that
I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event
**************************
'***************************************************************
'At the momment this is on the form and I call it from the BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub

'*********************************************************************
All,I would like to be able to loop through a subform to get all of
the
controls and what each value is. I am going to put them all in an
array.
I
am trying to create a function to do this. How can sendThe name of my
"Main" form and Subform so I can SET then as object?This is what I am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this
does
not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
Click to expand...
Click to expand...
 
But my forms are never and cannot be open without the main parent form. So
should I still do this each form?


Douglas J. Steele said:
While I understand the desire to centralize, I think it's better to put the
logging functionality into each of the forms. Remember that a form can be
used by itself, or as a subform for more than one parent form.

My recommendation is that you put the code into the BeforeUpdate of each of
the forms involved.

Yes, I realize that's a huge rework based on what you already seem to have,
but I believe it'll be more reliable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
I am not sure what you mean. I want all my code in one place so I only
have
to make the change once. So should I use just the BeforeUpdate or the
onDirty? I guess I am not sure of the direction I need to take.


Douglas J. Steele said:
I would think that all you'd need to do is put code in the BeforeUpdate
event of each of the forms. I don't see an advantage to having all the logic
in one place.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

So how would I use this with my current design, or would I have to
re-design
everything?

Thank you,
Rodger



message
Assuming you're talking about bound forms, the Dirty property will be
True
when anything's changed on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John/John,

Thank you Both for your reply. The reason I was doing this in an
array,
is
because I copied the code from Microsoft . . . :) I have been
trying
to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19 of
those
tabs have at least one subform on them. I want to be able to
Click to expand...
create
a
function that will let me know when a user makes a change to the data.
So
I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all
the
values
of the form into an array, then on the BeforeUpdate I compare the
values
of
the current form to what is in the array and if there is a change I
write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the
code
that
I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event
**************************
'***************************************************************
'At the momment this is on the form and I call it from the BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub

'*********************************************************************
All,I would like to be able to loop through a subform to get all of
the
controls and what each value is. I am going to put them all in an
array.
I
am trying to create a function to do this. How can sendThe name
Click to expand...
of
my
"Main" form and Subform so I can SET then as object?This is what
Click to expand...
I
am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this
does
not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
Click to expand...
Click to expand...
 
Others may disagree, but in my opinion, yes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
But my forms are never and cannot be open without the main parent form.
So
should I still do this each form?


Douglas J. Steele said:
While I understand the desire to centralize, I think it's better to put the
logging functionality into each of the forms. Remember that a form can be
used by itself, or as a subform for more than one parent form.

My recommendation is that you put the code into the BeforeUpdate of each of
the forms involved.

Yes, I realize that's a huge rework based on what you already seem to have,
but I believe it'll be more reliable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
I am not sure what you mean. I want all my code in one place so I only
have
to make the change once. So should I use just the BeforeUpdate or the
onDirty? I guess I am not sure of the direction I need to take.


message
I would think that all you'd need to do is put code in the
BeforeUpdate
event of each of the forms. I don't see an advantage to having all the
logic
in one place.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

So how would I use this with my current design, or would I have to
re-design
everything?

Thank you,
Rodger



message
Assuming you're talking about bound forms, the Dirty property will be
True
when anything's changed on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John/John,

Thank you Both for your reply. The reason I was doing this in an
array,
is
because I copied the code from Microsoft . . . :) I have been
trying
to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19
of
those
tabs have at least one subform on them. I want to be able to create
a
function that will let me know when a user makes a change to the
data.
So
I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all
the
values
of the form into an array, then on the BeforeUpdate I compare the
values
of
the current form to what is in the array and if there is a change I
write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the
code
that
I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event
**************************
'***************************************************************
'At the momment this is on the form and I call it from the
BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub


'*********************************************************************
All,I would like to be able to loop through a subform to get all of
the
controls and what each value is. I am going to put them all in an
array.
I
am trying to create a function to do this. How can sendThe name of
my
"Main" form and Subform so I can SET then as object?This is what I
am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim
myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this
does
not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
Click to expand...
Click to expand...
 
And me being one of them :) I will look into it but I am not sure there is
an advantage to doing that . . . . just see me having to update lots of code
when my end users want a change or add more subforms . . . . .


Douglas J. Steele said:
Others may disagree, but in my opinion, yes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rodger said:
But my forms are never and cannot be open without the main parent form.
So
should I still do this each form?


Douglas J. Steele said:
While I understand the desire to centralize, I think it's better to put the
logging functionality into each of the forms. Remember that a form can be
used by itself, or as a subform for more than one parent form.

My recommendation is that you put the code into the BeforeUpdate of
Click to expand...
each
of
the forms involved.

Yes, I realize that's a huge rework based on what you already seem to have,
but I believe it'll be more reliable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am not sure what you mean. I want all my code in one place so I only
have
to make the change once. So should I use just the BeforeUpdate or the
onDirty? I guess I am not sure of the direction I need to take.


message
I would think that all you'd need to do is put code in the
BeforeUpdate
event of each of the forms. I don't see an advantage to having all the
logic
in one place.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

So how would I use this with my current design, or would I have to
re-design
everything?

Thank you,
Rodger



message
Assuming you're talking about bound forms, the Dirty property
Click to expand...
will
be
True
when anything's changed on the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John/John,

Thank you Both for your reply. The reason I was doing this in an
array,
is
because I copied the code from Microsoft . . . :) I have been
trying
to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19
of
those
tabs have at least one subform on them. I want to be able to create
a
function that will let me know when a user makes a change to the
data.
So
I
took an example from Microsoft, that uses 2 events the first is the
onCurrent of the form, this looks at the form and then places all
the
values
of the form into an array, then on the BeforeUpdate I compare the
values
of
the current form to what is in the array and if there is a
Click to expand...
change
I
write
that back into a HISTORY table.

This is my basic idea . . . . .does that help. Also here is the
code
that
I
am using . . . .

'*********************** onCurrent Event ***************************

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub

'***************************************************************
'********************** BeforeUpdate Event
**************************
'***************************************************************
'At the momment this is on the form and I call it from the
BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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

'Check each data entry control for change and record old
Click to expand...
value
of
Control.
'Set the Array Counter
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub


'*********************************************************************
All,I would like to be able to loop through a subform to get
Click to expand...
all
of
the
controls and what each value is. I am going to put them all
Click to expand...
in
an
array.
I
am trying to create a function to do this. How can sendThe
Click to expand...
name
of
my
"Main" form and Subform so I can SET then as object?This is
Click to expand...
what
I
am
thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim
myForm,
mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know this
does
not
work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
Click to expand...
Click to expand...
 
I think I at least partially agree with Doug. The logging
code you posted is too simple to deal with a broad range of
forms. It does not distinguish between **fields** that you
want to log and unbound or calculated **controls** that have
no business being logged. You should should probably
replace checking the control type with a test of the
control's Tag property. You also need to combine the Date
and Time fields into one field. There is no need to
duplicate the recordset update code three times, move the If
down so it only affects the one field:

For Each D In frm.Controls
X = X + 1
If D.Tag = "Log" Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.ControlSource '<<<<
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.RecordSource
If frm.NewRecord = True Then
myRS![HIS_OLD_VALUE] = "This is a new record"
ElseIf IsNull(myArray(X)) Then
myRS![HIS_OLD_VALUE] = "Previous value was blank."
ElseIf myValue <> myArray(X) Then
myRS![HIS_OLD_VALUE] = myArray(X)
End If
myRS![HIS_DATETime_CHANGE] = Now '<<<<
myRS.Update
End If
Next D

With the use of the Tag property, it may be that the Sub
procedures are general enough to be used for most or even
all of the forms.

You probably should call the procedures from each form's
Current and BeforeUpdate events instead of from the main
form's events.
--
Marsh
MVP [MS Access]

And me being one of them :) I will look into it but I am not sure there is
an advantage to doing that . . . . just see me having to update lots of code
when my end users want a change or add more subforms . . . . .


"Douglas J. Steele" wrote
Others may disagree, but in my opinion, yes.
Click to expand...
the values of the current form to what is in the array and if there is a
change I write that back into a HISTORY table.
This is my basic idea . . . . .does that help. Also here is the code that I am using . . . .

'*********************** onCurrent Event

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub


'********************** BeforeUpdate Event
'At the momment this is on the form and I call it from the
BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub '*********************************************************************
All,I would like to be able to loop through a subform to get
Click to expand...
Click to expand...
all of the controls and what each value is. I am going to put them all
in an array. I am trying to create a function to do this. How can sendThe
name of my "Main" form and Subform so I can SET then as object?This is
what I am thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDimthis does no work but this is the normal syntax for this method.TIA,Rodger
Click to expand...
 
Thank you all for you suggestions, but the more I look at this the more I
want this as a function. I do not want to have to copy this code to each
form, and then have to make a change next week or next month . . . and then
to make sure if I make all change the changes to all of the forms. I was
able to create a module and I have my code there. Then I call that code for
each forms onCurrent and BeforeUpdate.

I do very much appriciate the suggestions and if I find things not working
as desired, to stronly consider your recommendations.. I will look at the
change about the newRecord solution as that makes more sence than the way I
had it.




Marshall Barton said:
I think I at least partially agree with Doug. The logging
code you posted is too simple to deal with a broad range of
forms. It does not distinguish between **fields** that you
want to log and unbound or calculated **controls** that have
no business being logged. You should should probably
replace checking the control type with a test of the
control's Tag property. You also need to combine the Date
and Time fields into one field. There is no need to
duplicate the recordset update code three times, move the If
down so it only affects the one field:

For Each D In frm.Controls
X = X + 1
If D.Tag = "Log" Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.ControlSource '<<<<
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.RecordSource
If frm.NewRecord = True Then
myRS![HIS_OLD_VALUE] = "This is a new record"
ElseIf IsNull(myArray(X)) Then
myRS![HIS_OLD_VALUE] = "Previous value was blank."
ElseIf myValue <> myArray(X) Then
myRS![HIS_OLD_VALUE] = myArray(X)
End If
myRS![HIS_DATETime_CHANGE] = Now '<<<<
myRS.Update
End If
Next D

With the use of the Tag property, it may be that the Sub
procedures are general enough to be used for most or even
all of the forms.

You probably should call the procedures from each form's
Current and BeforeUpdate events instead of from the main
form's events.
--
Marsh
MVP [MS Access]

And me being one of them :) I will look into it but I am not sure there is
an advantage to doing that . . . . just see me having to update lots of code
when my end users want a change or add more subforms . . . . .


"Douglas J. Steele" wrote
Others may disagree, but in my opinion, yes.


But my forms are never and cannot be open without the main parent form.
So should I still do this each form?


While I understand the desire to centralize, I think it's better to put
the
logging functionality into each of the forms. Remember that a form
Click to expand...
can
be
used by itself, or as a subform for more than one parent form.

My recommendation is that you put the code into the BeforeUpdate
of each of the forms involved.

Yes, I realize that's a huge rework based on what you already seem to
have, but I believe it'll be more reliable.


I am not sure what you mean. I want all my code in one place so I
only have
to make the change once. So should I use just the BeforeUpdate or
Click to expand...
the onDirty? I guess I am not sure of the direction I need to take.
I would think that all you'd need to do is put code in the
BeforeUpdate
event of each of the forms. I don't see an advantage to having all
the logicin one place.


So how would I use this with my current design, or would I have to
re-design everything?


Assuming you're talking about bound forms, the Dirty property
will be True when anything's changed on the form.


Thank you Both for your reply. The reason I was doing this in
an array, is
because I copied the code from Microsoft . . . :) I have been
trying to
modify it on my own but have not been able to figure it out.

Here is my goal. I have a form with about 20 tabs on it and 19
of those
tabs have at least one subform on them. I want to be able to
create a
function that will let me know when a user makes a change to
Click to expand...
the data So I took an example from Microsoft, that uses 2 events the first is
the onCurrent of the form, this looks at the form and then places all
the values
of the form into an array, then on the BeforeUpdate I
Click to expand...
compare
the values of the current form to what is in the array and if there is a
change I write that back into a HISTORY table.
This is my basic idea . . . . .does that help. Also here is
Click to expand...
the code that I am using . . . .
'*********************** onCurrent Event

Public Sub myCurrent(frm As Form)
ReDim myArray(frm.Controls.Count - 1)
X = -1

For Each C In frm.Controls
X = X + 1
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
'Skip
Updates field.
myArray(X) = C.Value
End Select
TryNextC:
Next C

End Sub


'********************** BeforeUpdate Event
'At the momment this is on the form and I call it from the
BeforeUpdate
Event

Public Sub myHistory(frm As Form, myID, sfrm As SubForm)

Dim D As Control
Dim myDB, myRS, myNewRecord, myTable, myValue

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
X = -1
For Each D In frm.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 frm.NewRecord = True Then
myNewRecord = "New Record"
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.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."
If IsNull(myArray(X)) Then
myRS.AddNew
myRS![HIS_USER] = useUserName
myRS![HIS_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID
myRS![HIS_TABLE_NAME] = frm.RecordSource
myRS![HIS_OLD_VALUE] = "Previous
Click to expand...
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_FIELD] = D.Name
myRS![HIS_FORM] = frm.Name
myRS![HIS_TABLE_ID] = myID 'CHANGE THIS
myRS![HIS_TABLE_NAME] = frm.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

End Sub '*********************************************************************
All,I would like to be able to loop through a subform to
Click to expand...
get
all of the controls and what each value is. I am going to put them all
in an array. I am trying to create a function to do this. How can sendThe
name of my "Main" form and Subform so I can SET then as object?This is
what I am thinking . . . . . . Dim frm1 as ObjectDim sfrm1 as ObjectDim
myForm, mySubFormSet frm1 = myFormSet sfrm1 = frm1.mySubFormI know
Click to expand...
this does no work but this is the normal syntax for this
Click to expand...
method.TIA,Rodger
Click to expand...
 
Rodger said:
Thank you all for you suggestions, but the more I look at this the more I
want this as a function. I do not want to have to copy this code to each
form, and then have to make a change next week or next month . . . and then
to make sure if I make all change the changes to all of the forms. I was
able to create a module and I have my code there. Then I call that code for
each forms onCurrent and BeforeUpdate.

I do very much appriciate the suggestions and if I find things not working
as desired, to stronly consider your recommendations.. I will look at the
change about the newRecord solution as that makes more sence than the way I
had it.
Click to expand...


I can't argue with the general idea of not duplicating code,
it's absolutely the way to go. However, the specific
question is whether you can make a procedure that works for
all your cases/forms. If you find yourself adding code
specific to one of the forms, treat that as a red flag that
it is time to rethink the situation.

Sometimes you can break things down to a few categories of
forms and use a different procedure (maybe even using a sub
subprocedure for any common parts) for each category of
form.

Go for the single procedure now and if/when you run into
complications, consider using different procedres at that
time.
 
Yes I will consider all of that . . . . and again thank you for your help.
You actually gave me the one line of code that I was missing on how to code
the SET method. Once I had that I was off and running. I have the code now
working for a form or subform only having to call the routine for the
onCurrent and the BeforeUpdate Events. So far it is working great. . . .
Just wait till I turn it over to the users, I am sure they will break it . .
..
 

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

Saving Subform Record 1
Requery a background form 2
Vis/Invis SetFocus conundrum 2
Trouble resetting subform after update 1
How do I move focus to a control in a nested subform ? 2
Why an Insert does not Occur 2
Accessing recordsource of a subforms datasheet 7
Change data and form crashes Access 3

Back
Top