Compare value between Main and Sub forms

[deancarstens]

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_Bef oreUpdate(Cance l 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_C OMBO.[LENGTH].Value
'Get the value edited by the user
MaxLenCondInv = Forms!frmCOND_C OMBO!subCOND_NE W.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_C OMBO!subCOND_NE W.Form![TO CHAINAGE].Value =
MaxLenDef
End If

End Sub

[Arno R]

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
Cancel=true
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

<de**********@y ahoo.com> schreef in bericht news:11******** **************@ j33g2000cwa.goo glegroups.com.. .

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_Bef oreUpdate(Cance l 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_C OMBO.[LENGTH].Value
'Get the value edited by the user
MaxLenCondInv = Forms!frmCOND_C OMBO!subCOND_NE W.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_C OMBO!subCOND_NE W.Form![TO CHAINAGE].Value =
MaxLenDef
End If

End Sub

[deancarstens]

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