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 frmMissyFactBackup which reads data from a backup database
of the week before. I brought frmMissyFactBackup into frmMissyFact as
a subform. What I am trying to do is compare each control value from
frmMissyFact to that of frmMissyFactBackup 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_PROJECTED_VOLUME.Value <>
Me.frmMissyFactbackup.Form.STR_PROJECTED_VOLUME.Va lue Then
Me!STR_PROJECTED_VOLUME.BackColor = lngYellow
Else
End If

If Me.STR_OPEN_DATE.Value <>
Me.frmMissyFactbackup.Form.STR_OPEN_DATE.Value Then
Me!STR_OPEN_DATE.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******@hotmail.com> wrote in message
news:b6**************************@posting.google.c om...

Hello,

I have a a form called frmMissyFact, I have copied this form and
called it frmMissyFactBackup which reads data from a backup database
of the week before. I brought frmMissyFactBackup into frmMissyFact as
a subform. What I am trying to do is compare each control value from
frmMissyFact to that of frmMissyFactBackup 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_PROJECTED_VOLUME.Value <>
Me.frmMissyFactbackup.Form.STR_PROJECTED_VOLUME.Va lue Then
Me!STR_PROJECTED_VOLUME.BackColor = lngYellow
Else
End If

If Me.STR_OPEN_DATE.Value <>
Me.frmMissyFactbackup.Form.STR_OPEN_DATE.Value Then
Me!STR_OPEN_DATE.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!frmMissyFactbackup.Form

For Each ctl In frmMain.Controls
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********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. 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?