Validate Form before navigating to next record

[marcuscoker]

Hi

I was wondering if anybody could help with this. I have searched through this forum and there doesnt seem to be an answer to this.

I have a form that i would like to validate. At present i have put the data validation on the save button which triggers a script that checks to see if certain boxes have been filled in on the form. I can only get this to work be attaching it to the onclick event on a save button, however, what i really want is to be able to ditch the save button (since access writes to the DB as it goes along) and have this script triggered whenever the user tries to navigate to either a new record/ another existing record or close the form

I tried putting this on the beforer update event, but this does not work, additionally tried doing before update event with the code inside an if me.form.dirty = true statement
this did not work either, has anybody got any suggestions

Please see my code below

Thanks

Marcus

Dim sDeliveryto As String
Dim sDeliveryValid As String
Dim sDept As String
Dim sDeptValid As String
Dim sReq As String
Dim sReqValid As String
Dim sReqNo As String
Dim sReqNoValid As String
Dim sReqPoint As String
Dim sReqPointValid As String
Dim sOrderDetailsValid As String
Dim sQuantity1 As String
Dim sQuantity1Valid As String
Dim sDetails1 As String
Dim sDetails1Valid As String
Dim sPrice1 As String
Dim sPrice1Valid As String
Dim sSupplier1 As String
Dim sSupplier1Valid As String
Dim sCostCentre1 As String
Dim sCostCentre1Valid As String
Dim sAccountCode1 As String
Dim sAccountCode1Valid As String
Dim sAuth As String
Dim sAuthValid As String



sDeliveryto = Me.TBDeliveredTo & ""
sDept = Me.TBDept & ""
sReq = Me.tbrequisitioner & ""
sReqNo = Me.TBRequisitionNo & ""
sReqPoint = Me.TBReqPoint & ""
sQuantity1 = Me.TBQ1 & ""
sDetails1 = Me.TBD1 & ""
sPrice1 = Me.tbup1 & ""
sSupplier1 = Me.tbs1 & ""
sCostCentre1 = Me.ccc1 & ""
sAccountCode1 = Me.tbac1 & ""
sAuth = Me.TBAUTH & ""



Select Case sDeliveryto
Case Is = ""
Me.TBDeliveredTo.BackColor = "8421631"

sDeliveryValid = "Invalid"

Cancel = True
Case Else
sDeliveryValid = "valid"
Me.TBDeliveredTo.BackColor = "16777215"

End Select


Select Case sDept
Case Is = ""
Me.TBDept.BackColor = "8421631"

sDeptValid = "Invalid"

Cancel = True
Case Else
sDeptValid = "valid"
Me.TBDept.BackColor = "16777215"

End Select

Select Case sReq
Case Is = ""
Me.tbrequisitioner.BackColor = "8421631"

sReqValid = "Invalid"

Cancel = True
Case Else
sReqValid = "valid"
Me.tbrequisitioner.BackColor = "16777215"

End Select

Select Case sReqNo
Case Is = ""
Me.TBRequisitionNo.BackColor = "8421631"

sReqNoValid = "Invalid"

Cancel = True
Case Else
sReqNoValid = "valid"
Me.TBRequisitionNo.BackColor = "16777215"

End Select

Select Case sReqPoint
Case Is = ""
Me.TBReqPoint.BackColor = "8421631"

sReqPointValid = "Invalid"

Cancel = True
Case Else
sReqPointValid = "valid"
Me.TBReqPoint.BackColor = "16777215"

End Select

If Len(sReqPoint) < 6 Then
sReqPointValid = "Invalid"
Me.TBReqPoint.BackColor = "8421631"
Cancel = True
Me.lblReqPoint.Visible = True

Else
sReqPointValid = "valid"
Me.TBReqPoint.BackColor = "16777215"
Me.lblReqPoint.Visible = False
End If

Select Case sQuantity1
Case Is = ""
Me.TBQ1.BackColor = "8421631"

sQuantity1Valid = "Invalid"

Cancel = True
Case Else
sQuantity1Valid = "valid"
Me.TBQ1.BackColor = "16777215"

End Select


Select Case sDetails1
Case Is = ""
Me.TBD1.BackColor = "8421631"

sDetails1Valid = "Invalid"

Cancel = True
Case Else
sDetails1Valid = "valid"
Me.TBD1.BackColor = "16777215"

End Select


Select Case sPrice1
Case Is = ""
Me.tbup1.BackColor = "8421631"

sPrice1Valid = "Invalid"

Cancel = True
Case Else
sPrice1Valid = "valid"
Me.tbup1.BackColor = "16777215"

End Select

Select Case sSupplier1
Case Is = ""
Me.tbs1.BackColor = "8421631"

sSupplier1Valid = "Invalid"

Cancel = True
Case Else
sSupplier1Valid = "valid"
Me.tbs1.BackColor = "16777215"

End Select

Select Case sCostCentre1
Case Is = ""
Me.ccc1.BackColor = "8421631"

sCostCentre1Valid = "Invalid"

Cancel = True
Case Else
sCostCentre1Valid = "valid"
Me.ccc1.BackColor = "16777215"

End Select


Select Case sAccountCode1
Case Is = ""
Me.tbac1.BackColor = "8421631"

sAccountCode1Valid = "Invalid"

Cancel = True
Case Else
sAccountCode1Valid = "valid"
Me.tbac1.BackColor = "16777215"

End Select

Select Case sAuth
Case Is = ""
Me.TBAUTH.BackColor = "8421631"

sAuthValid = "Invalid"

Cancel = True
Case Else
sAuthValid = "valid"
Me.TBAUTH.BackColor = "16777215"

End Select


' Display message box warning

If sDetails1Valid = "Invalid" Or sQuantity1Valid = "invalid" Or sReqPointValid = "Invalid" Or sReqNoValid = "Invalid" Or sReqValid = "Invalid" Or sDeptValid = "Invalid" Or sDeliveryValid = "Invalid" Or sPrice1Valid = "Invalid" Or sCostCentre1Valid = "Invalid" Or sAccountCode1Valid = "Invalid" Or sAuthValid = "Invalid" Then
MsgBox "Please fill all highlighted fields on the form!!!!!"
Else

DoCmd.Save
MsgBox "Is all the information Correct?", vbOKCancel


'open report

Me.btnClose.Visible = True
Me.btnInvoice.Visible = True
Me.btnDeleteClose.Visible = False




End If

 

[RuralGuy]

Hi Marcus,
I would just use a sequence of If...Else...End If statements rather than a sequence of Select Case structures. Why doesn't the BeforeUpdate event of the Form work for you? What happened? Did you get an error?

 

[marcuscoker]

Hi Rural Guy

Have managed to get it working with before update event now, i had to remove docmd.gotorecord acnew.....
from the onopen event

thanks

marcus

 

[RuralGuy]

Hi Marcus,
Glad to hear you got it working. Thanks for posting back.