MS Access database support - Compare value between Main and Sub forms

Author

Compare value between Main and Sub forms

deancarstens@yahoo.com

2006-04-04, 11:34 am

Hi,

I'm a novice with VB and I've written this pretty simple script, but
for the life of me, I can't get the last bit of the script to work. It
should compare the value entered by the user under the BeforeUpdate
event and compare it to the value in the main form. It's doing that
just okay, but when I want to reset the value using the original value
(from the main form), it seems the BeforeUpdate event prevents me from
changing it back. What could the problem be here? This should be
quite simple I suppose. The script I have is as follows and runs from
the SubForm's BeforeUpdate. Your help here would be GREATLY
appreciated.

Public Sub TO_CHAINAGE_BeforeUp
date(Cancel As Integer)

'If the user updates a TO CHAINAGE value, this script will warn the
user that
'the value entered is higher than the maximum value set for the road
Dim MaxLenDef As Double
Dim MaxLenCondInv As Double

'Assign the length value from the definition table, representing the
maximum value
MaxLenDef = Forms!frmCOND_COMBO.[LENGTH].Value
'Get the value edited by the user
MaxLenCondInv = Forms!frmCOND_COMBO!
subCOND_NEW.Form![TO
CHAINAGE].Value

'Prompt the user if the value is higher than the definition length
If MaxLenCondInv > MaxLenDef Then
MsgBox "The value entered exceeds the maximum length value for this
road:" _
& vbNewLine & vbNewLine & " " & MaxLenDef & "
Kilometres" & vbNewLine _
& vbNewLine & "Please request a ROAD DEFINITION change from your
Database Administrator."
'Assign the old value back to the cell
Forms!frmCOND_COMBO!
subCOND_NEW.Form![TO CHAINAGE].Value =
MaxLenDef
End If

End Sub

Arno R

2006-04-04, 1:32 pm

You can not assign a value during the beforeupdate event. Impossible.
You are validating the input and you can simply cancel the new value =
with a line like=20
Cancel=3Dtrue
This will prevent the user from leaving the control until the input is =
OK.
Hitting escape will put back the previous value, so you could 'tell' =
this to the users.

If you really need to reset the value, you can to use the =
Afterupdate-event.
I would immediately save the record after that to prevent an 'Escape' =
with the Escape key

HTH
Arno R

<deancarstens@yahoo.com> schreef in bericht =
news:1144163797.772684.194670@j33g2000cwa.googlegroups.com...
> Hi,
>=20
> I'm a novice with VB and I've written this pretty simple script, but
> for the life of me, I can't get the last bit of the script to work. =
It
> should compare the value entered by the user under the BeforeUpdate
> event and compare it to the value in the main form. It's doing that
> just okay, but when I want to reset the value using the original value
> (from the main form), it seems the BeforeUpdate event prevents me from
> changing it back. What could the problem be here? This should be
> quite simple I suppose. The script I have is as follows and runs from
> the SubForm's BeforeUpdate. Your help here would be GREATLY
> appreciated.
>=20
> Public Sub TO_CHAINAGE_BeforeUp
date(Cancel As Integer)
>=20
>=20
> 'If the user updates a TO CHAINAGE value, this script will warn the
> user that
> 'the value entered is higher than the maximum value set for the road
> Dim MaxLenDef As Double
> Dim MaxLenCondInv As Double
>=20
> 'Assign the length value from the definition table, representing the
> maximum value
> MaxLenDef =3D Forms!frmCOND_COMBO.[LENGTH].Value
> 'Get the value edited by the user
> MaxLenCondInv =3D Forms!frmCOND_COMBO!
subCOND_NEW.Form![TO
> CHAINAGE].Value
>=20
> 'Prompt the user if the value is higher than the definition length
> If MaxLenCondInv > MaxLenDef Then
> MsgBox "The value entered exceeds the maximum length value for this
> road:" _
> & vbNewLine & vbNewLine & " " & MaxLenDef & "
> Kilometres" & vbNewLine _
> & vbNewLine & "Please request a ROAD DEFINITION change from your
> Database Administrator."
> 'Assign the old value back to the cell
> Forms!frmCOND_COMBO!
subCOND_NEW.Form![TO CHAINAGE].Value =3D
> MaxLenDef
> End If
>=20
> End Sub
>

deancarstens@yahoo.com

2006-04-05, 9:35 am

Hi Arno,

I tried your advice and started playing around with this and found that
the best option is using GotFocus and LostFocus. This worked fine, but
then whenever the user clicked on a blank cell (the "Add New" record)
it also referenced to it and failed because the cell is blank, but when
I used an If Then statement to check whether the cell is blank, before
storing the value, it workd like a charm! Although I didn't use you
suggestion exactly, it helped by just looking at it from a different
angle.

Thanks mate.

Dean Carstens
Scott Wilson Plc
DFID Support to Feeder Roads
Ghana

Sponsored Links