Hi all,
New to access and need some help. I have a form with two fields on it. TxtStateName and TxtState. I neeed to write an if statement if either one IsNull. Such as:
If IsNull (TxtState) then
msgbox "Need to add State" vbOK
SetFocus back on txt box TxtState
Else continue on ?????
I need this for both txtboxes on the form and has to fire before the before update event, actually it could be wrote in with that code if possible. See Below
- Private Sub Form_BeforeUpdate(Cancel As Integer)
-
Dim SN As String
-
Dim ST As String
-
Dim stLinkCriteria As String
-
Dim rsc As DAO.Recordset
-
Dim SNkey As Long
-
Dim STkey As Long
-
Dim TGTkey As Long
-
If eMode = True Then
-
eMode = False
-
Exit Sub
-
End If
-
Set rsc = Me.RecordsetClone
-
-
SN = Me.TxtStateName
-
ST = Me.TxtState
-
SNkey = Nz(DLookup("StateID", "Tbl_State", "StateName=" & "'" & SN & "'"), 0)
-
STkey = Nz(DLookup("StateID", "Tbl_State", "State=" & "'" & ST & "'"), 0)
-
-
'Neither exist, add record
-
If SNkey = 0 And STkey = 0 Then
-
Exit Sub
-
End If
-
-
'both exist and are in same existing record, get that record
-
If SNkey > 0 And SNkey = STkey Then
-
TGTkey = SNkey
-
Me.Undo
-
'Cancel = True
-
MsgBox "Warning! State of " _
-
& SN & " and the State Abrev " & ST & " are already in Database." _
-
& vbCr & vbCr & "You will now been taken to the record.", _
-
vbInformation, "Duplicate Information"
-
Call GetRecordToEdit(TGTkey)
-
Call CmdEdit_Click
-
Exit Sub
-
End If
-
-
'SN exists but ST does not, get the SN record
-
If SNkey > 0 And STkey = 0 Then
-
MsgBox StateName & " exists, but " & State & " does not. I will show you the State Name record.", _
-
vbInformation + vbOKOnly, " A T T E N T I O N "
-
Me.Undo
-
Call GetRecordToEdit(SNkey)
-
Call CmdEdit_Click
-
Exit Sub
-
End If
-
-
'ST exists but SN does not, get the ST record
-
If STkey > 0 And SNkey = 0 Then
-
MsgBox State & " exists, but " & StateName & " does not. I will show you the State record.", _
-
vbInformation + vbOKOnly, " A T T E N T I O N "
-
Me.Undo
-
Call GetRecordToEdit(STkey)
-
Call CmdEdit_Click
-
Exit Sub
-
End If
-
-
'both SN and ST exist but not in same record, inform user and get out
-
If SNkey > 0 And STkey > 0 And (SNkey <> STkey) Then
-
MsgBox Me.State & " and " & Me.StateName & " is an invalid pair.", vbCritical + vbOKOnly, " I N V A L I D I N P U T "
-
Me.Undo
-
Exit Sub
-
End If
-
End Sub