If, IsNull, Then & Else Statements

[D9pierce]

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

[NeoPa]

It's good that you've included your code, but expecting people to work out what your question is - or rather should be - by their reading and interpreting your non-working code is not so good.

Why don't you take a step back and put your actual question into clear English so that we have enough understanding of what you need and can help you without wasting inordinate amounts of time trying to guess what that is.

I can see you're a new member, but bear in mind there are threads at the top of the forum that explain what is expected as a minimum when asking for help.

[twinnyfo]

D9pierce,

Wecome to Bytes!

Another (bot more complicated) approach is to highlight the labels that require entry. Whenever one moves to a different record (using the OnCurrent event), highlight all the required fields. as the user enters data into these fields, if the data is valid, the highlight goes away. If the data entry is not valid, set the focus to the control the user just updated.

But, as NeoPa stated, it's a bit difficult ot understand exactly what your code is trying to do. My first question would be, "Why must the user enter the State and the State Name?" If these items are both from the same table, entering one (or simply selecting one from a combo box) would be sufficient.

The bottom line is that we really need some clarifying information before we can provide more thorough advice.

Thanks.