COMPARE MAIN FORM TEXT BOX ES TO SUBFORM TEXTBOX AND CHANGE BACKCOLOR

[pepino13]

Hello,

I have a a form called frmMissyFact, I have copied this form and
called it frmMissyFactBac kup which reads data from a backup database
of the week before. I brought frmMissyFactBac kup into frmMissyFact as
a subform. What I am trying to do is compare each control value from
frmMissyFact to that of frmMissyFactBac kup and if the controlvalue is
different change the backcolor to Yellow of the control so the user
looking at it knows that this field has been changed from last week.
I was able to do this with the following code.

Private Sub Form_Current()

Dim lngYellow As Long

lngYellow = RGB(255, 255, 0)

If Me.STR_PROJECTE D_VOLUME.Value <>
Me.frmMissyFact backup.Form.STR _PROJECTED_VOLU ME.Value Then
Me!STR_PROJECTE D_VOLUME.BackCo lor = lngYellow
Else
End If

If Me.STR_OPEN_DAT E.Value <>
Me.frmMissyFact backup.Form.STR _OPEN_DATE.Valu e Then
Me!STR_OPEN_DAT E.BackColor = lngYellow
Else

End If

End Sub

I would have to do this with every control I have on the form.

The primary key and linked field is Str_Num for the data in both forms
The main problem I have is if when the current database has a new
record (Store Number) and the backup does not. I get an error because
of the code does not see a value in the subform.

Does anybody know how to fix this?
Thanks,

[MacDermott]

You could check whether the Recordset of the subform has a recordcount of 0
at the top of your routine, and exit if it does.

HTH
- Turtle

"pepino13" <pe******@hotma il.com> wrote in message
news:b6******** *************** ***@posting.goo gle.com...

Hello,

I have a a form called frmMissyFact, I have copied this form and
called it frmMissyFactBac kup which reads data from a backup database
of the week before. I brought frmMissyFactBac kup into frmMissyFact as
a subform. What I am trying to do is compare each control value from
frmMissyFact to that of frmMissyFactBac kup and if the controlvalue is
different change the backcolor to Yellow of the control so the user
looking at it knows that this field has been changed from last week.
I was able to do this with the following code.

Private Sub Form_Current()

Dim lngYellow As Long

lngYellow = RGB(255, 255, 0)

If Me.STR_PROJECTE D_VOLUME.Value <>
Me.frmMissyFact backup.Form.STR _PROJECTED_VOLU ME.Value Then
Me!STR_PROJECTE D_VOLUME.BackCo lor = lngYellow
Else
End If

If Me.STR_OPEN_DAT E.Value <>
Me.frmMissyFact backup.Form.STR _OPEN_DATE.Valu e Then
Me!STR_OPEN_DAT E.BackColor = lngYellow
Else

End If

End Sub

I would have to do this with every control I have on the form.

The primary key and linked field is Str_Num for the data in both forms
The main problem I have is if when the current database has a new
record (Store Number) and the backup does not. I get an error because
of the code does not see a value in the subform.

Does anybody know how to fix this?
Thanks,

[Pieter Linden]

if both forms have exactly the same control names, use the controls
collection to get the names of the controls on the main form, then
create a variable to point to the "related" control on the subform.
Then just loop through the controls collection.

'-really lame air code, but I hope you get the idea

dim ctl as control
dim frmMain as form
dim frmSub as form

set frm = me
set frmSub = (Can't remember the syntax... see www.mvps.org/access and
there's an article by Keri Hardwick that has the entire listing)...

for each ctl in frm.controls
if ctl.value = frmsub.ctl(ctl. name).value then
'set to normal color
else
ctl.backcolor = vbYellow
end if
end sub

and could ya please not shout?

[pepino13]

I am having trouble with the loop. Heres the code

Dim ctl As Control
Dim frmMain As Form
Dim frmSub As Form

Set frmMain = Me
Set frmSub = Me!frmMissyFact backup.Form

For Each ctl In frmMain.Control s
If ctl.Value = frmSub.ctl(ctl. Name).Value Then
'set to normal color
Else
ctl.BackColor = vbYellow
End If
Next ctl
End Sub

I keep getting an error on
If ctl.value = frmSub.ctl(ctl. name).value then
How does it know what controls to compare?
Thanks,


pi********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.com>...

if both forms have exactly the same control names, use the controls
collection to get the names of the controls on the main form, then
create a variable to point to the "related" control on the subform.
Then just loop through the controls collection.

'-really lame air code, but I hope you get the idea

dim ctl as control
dim frmMain as form
dim frmSub as form

set frm = me
set frmSub = (Can't remember the syntax... see www.mvps.org/access and
there's an article by Keri Hardwick that has the entire listing)...

for each ctl in frm.controls
if ctl.value = frmsub.ctl(ctl. name).value then
'set to normal color
else
ctl.backcolor = vbYellow
end if
end sub

and could ya please not shout?