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

Expand|Select|Wrap|Line Numbers

 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.

If, IsNull, Then & Else Statements

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ