423,846 Members | 2,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,846 IT Pros & Developers. It's quick & easy.

If, IsNull, Then & Else Statements

P: 1
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
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Dim SN As String
  3.     Dim ST As String
  4.     Dim stLinkCriteria As String
  5.     Dim rsc As DAO.Recordset
  6.     Dim SNkey As Long
  7.     Dim STkey As Long
  8.     Dim TGTkey As Long
  9.     If eMode = True Then
  10.         eMode = False
  11.         Exit Sub
  12.     End If
  13.     Set rsc = Me.RecordsetClone
  14.  
  15.     SN = Me.TxtStateName
  16.     ST = Me.TxtState
  17.     SNkey = Nz(DLookup("StateID", "Tbl_State", "StateName=" & "'" & SN & "'"), 0)
  18.     STkey = Nz(DLookup("StateID", "Tbl_State", "State=" & "'" & ST & "'"), 0)
  19.  
  20.     'Neither exist, add record
  21.     If SNkey = 0 And STkey = 0 Then
  22.         Exit Sub
  23.     End If
  24.  
  25.     'both exist and are in same existing record, get that record
  26.     If SNkey > 0 And SNkey = STkey Then
  27.         TGTkey = SNkey
  28.         Me.Undo
  29.         'Cancel = True
  30.         MsgBox "Warning!  State of " _
  31.         & SN & " and the State Abrev " & ST & " are already in Database." _
  32.         & vbCr & vbCr & "You will now been taken to the record.", _
  33.         vbInformation, "Duplicate Information"
  34.         Call GetRecordToEdit(TGTkey)
  35.         Call CmdEdit_Click
  36.         Exit Sub
  37.     End If
  38.  
  39.     'SN exists but ST does not, get the SN record
  40.     If SNkey > 0 And STkey = 0 Then
  41.         MsgBox StateName & " exists, but " & State & " does not. I will show you the State Name record.", _
  42.         vbInformation + vbOKOnly, "   A T T E N T I O N   "
  43.         Me.Undo
  44.         Call GetRecordToEdit(SNkey)
  45.         Call CmdEdit_Click
  46.         Exit Sub
  47.     End If
  48.  
  49.     'ST exists but SN does not, get the ST record
  50.     If STkey > 0 And SNkey = 0 Then
  51.         MsgBox State & " exists, but " & StateName & " does not. I will show you the State record.", _
  52.         vbInformation + vbOKOnly, "   A T T E N T I O N   "
  53.         Me.Undo
  54.         Call GetRecordToEdit(STkey)
  55.         Call CmdEdit_Click
  56.         Exit Sub
  57.     End If
  58.  
  59.     'both SN and ST exist but not in same record, inform user and get out
  60.     If SNkey > 0 And STkey > 0 And (SNkey <> STkey) Then
  61.         MsgBox Me.State & " and " & Me.StateName & " is an invalid pair.", vbCritical + vbOKOnly, "  I N V A L I D   I N P U T   "
  62.         Me.Undo
  63.         Exit Sub
  64.     End If
  65. End Sub
1 Week Ago #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,121
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.
1 Week Ago #2

twinnyfo
Expert Mod 2.5K+
P: 2,703
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.
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.