Urgent Required Field Advice Needed

[photoguy53]

I'm looking for advice on the best method to accomplish the following from the esteemed members of this Forum (You all have provided excellent advice in the past to this Access Dummy, with my thanks), (I've also searched the forums without result):

I would like to make several fields "required" fields on my form, easy enough, in that I set the Required property on the table to "Yes".

What I would like to happen on the form is that when a user tabs out of a required field, a message box pops up that says "This is a required field" and/or when they click any of the following command buttons I've created, "Save Record", "New Record" or "Close Form", that a message box pop up and list the required fields that they missed.

Any ideas, with code, macros, or other solutions would be greatly appreciated, keeping in mind that I'm just not that swift to start with.

Many Thanks,
Photoguy

 

[ssteinke]

Just did a quick search for 'Required Field', won't this work?

http://www.access-programmers.co.uk/forums/showthread.php?t=87223&highlight=required+field

 

[photoguy53]

Thanks, It certainly might, HOWEVER, I just don't know VB, so I'm not sure. I'm still learning. It "looks" like it should work.....

I paste the code, and fill in the proper textbox names and see what happens.

Where would I paste this code? I mean I can't just choose one of the required fields and go the the properties and build and event...right?


Many thanks for the link, I must have been searching in the wrong forum....

 

[photoguy53]

SST,
Thanks for pointing me to the message that has me on the right path. I copied and pasted the code, inserted it in the right place, changed the names of the fields to mine, saved and tried it out. However, the message box popped up, for the field that DID have text in it, not the field that I left intentionally black to test if it worked.
Here's the code that I used, when I clicked OK on the message box I got a run-time error 424bject required. Then when I ran the debugger, it highlighted the line containing: LastName.SetFocus

Anyway, here's the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(LastName) Or LastName = "" Then
MsgBox "Last Name Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
LastName.SetFocus
Cancel = True
Exit Sub
 
ElseIf IsNull(FirstName) Or FirstName = "" Then
MsgBox "First Name Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
FirstName.SetFocus
Cancel = True
Exit Sub
 
ElseIf IsNull(Station) Or Station = "" Then
MsgBox "Station Is A Required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
Station.SetFocus
Cancel = True
Exit Sub
 
Else
Cancel = False
End If
 
End Sub

 

[ssteinke]

You might want to try GHudson's sample in this thread, it seams a little cleaner than using all those if/elseif statements. Make sure to put the 'me.' prefix in front of your field name also, this may help.

http://www.access-programmers.co.uk/forums/showthread.php?t=89367

 

[photoguy53]

ssteinke,

Thanks a TON!! That last link did the trick. I had to modify the code considerably, but it worked like a charm after that, with the ADDED BONUS of me expanding my knowledge of VB and Access.

Again, Many Thanks for pointing me in the right direction!

Here's how I modified it for my purposes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Last_Name = "" Or IsNull(Me.Last_Name) Then
    MsgBox "LAST NAME IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Last_Name.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
If Me.First_Name = "" Or IsNull(Me.First_Name) Then
     MsgBox "FIRST NAME IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.First_Name.SetFocus
      DoCmd.CancelEvent
      Exit Sub
    End If
   
If Me.Station = "" Or IsNull(Me.Station) Then
    MsgBox "STATION NUMBER IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Station.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
If Me.APP_Category = "" Or IsNull(Me.APP_Category) Then
    MsgBox "APP CATEGORY IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.APP_Category.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
If Me.SERVICE = "" Or IsNull(Me.SERVICE) Then
    MsgBox "SERVICE IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.SERVICE.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
If Me.Type = "" Or IsNull(Me.Type) Then
    MsgBox "TYPE IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Type.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
  
 If Me.Estimated_Issue_Date = "" Or IsNull(Me.Estimated_Issue_Date) Then
    MsgBox "EST. ISSUE DATE IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Estimated_Issue_Date.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
If Me.Estimated_Award_Date = "" Or IsNull(Me.Estimated_Award_Date) Then
    MsgBox "EST. AWARD DATE IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Estimated_Award_Date.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
If Me.Estimated_Issue_Quarter = "" Or IsNull(Me.Estimated_Issue_Quarter) Then
    MsgBox "EST. ISSUE QUARTER IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Estimated_Issue_Quarter.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
If Me.HtHc_Description = "" Or IsNull(Me.HtHc_Description) Then
    MsgBox "DESCRIPTION IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.HtHc_Description.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
   
If Me.Product_Service_Code = "" Or IsNull(Me.Product_Service_Code) Then
    MsgBox "PRODUCT SERVICE CODE IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
      Me.Product_Service_Code.SetFocus
      DoCmd.CancelEvent
      Exit Sub
   End If
End Sub

Thanks!!

 

[Pat Hartman]

The - DoCmd.CancelEvent - should be replaced with - Cancel = True
Notice the Cancel argument in the event procedure header. It is there specifically for this purpose - to cancel the event when it is necessary.

Bear in mind that the code you posted will run whenever the form data is saved so it is correct and what I would recommend but it will not give immediate notification to the user when he tabs out of a required field and leaves it empty. To do that, you would need to put code in each individual control's lost focus event.

 

[photoguy53]

Thanks Pat, good advice.

How would you place the code into each control's lost focus event? I'm not familiar with the lost focus event.

 

[Pat Hartman]

I am not recommending that you do this, I only mentioned it as a possibilty. You would copy the code for an individual field to that field's lostFocus event. Don't forget, a field's LostFocus event only fires if the field had focus to begin with. So, if a field is left empty but the cursor was never moved to the field, no error would appear because the LostFocus event for that field would never fire. The point of course is that code in the LostFocus event is insufficient by itself. That's why it is necessary to put the code in the FORM's BeforeUpdate event.

 

[lisa.ogle]

Hi I used this code that was in the previous question modifying it to my needs but I needed one more set at the beginning with Me.Return_Date.SetFocus and it says I do not have that value available to me. When I create the code the only option I get where SetFocus is is Value when I tried to use it the code errors out. So I deleted it and the form works correctly all the way thru. Any suggestions on how to add it in and make it work along with the rest of the code?

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Initiating_Person = "" Or IsNull(Me.Initiating_Person) Then
MsgBox "INITIATING PERSON IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Initiating_Person.SetFocus
Cancel = True
Exit Sub
End If

If Me.Client = "" Or IsNull(Me.Client) Then
MsgBox "CLIENT IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Client.SetFocus
Cancel = True
Exit Sub
End If

If Me.Return_Number = "" Or IsNull(Me.Return_Number) Then
MsgBox "RETURN NUMBER IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Return_Number.SetFocus
Cancel = True
Exit Sub
End If

If Me.Cust_Name = "" Or IsNull(Me.Cust_Name) Then
MsgBox "CUST NAME IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Cust_Name.SetFocus
Cancel = True
Exit Sub
End If

If Me.Product_Name = "" Or IsNull(Me.Product_Name) Then
MsgBox "PRODUCT NAME IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Product_Name.SetFocus
Cancel = True
Exit Sub
End If


If Me.Item_Num = "" Or IsNull(Me.Item_Num) Then
MsgBox "ITEM NUM IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Item_Num.SetFocus
Cancel = True
Exit Sub
End If

If Me.Lot_Num = "" Or IsNull(Me.Lot_Num) Then
MsgBox "LOT NUM IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Lot_Num.SetFocus
Cancel = True
Exit Sub
End If

If Me.Exp_Date = "" Or IsNull(Me.Exp_Date) Then
MsgBox "EXP DATE IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Exp_Date.SetFocus
Cancel = True
Exit Sub
End If

If Me.QTY = "" Or IsNull(Me.QTY) Then
MsgBox "QUANTITY IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.QTY.SetFocus
Cancel = True
Exit Sub
End If

If Me.Product_Cond = "" Or IsNull(Me.Product_Cond) Then
MsgBox "PRODUCT CONDITION IS A REQUIRED FIELD", vbCritical + vbOKOnly + vbDefaultButton2, "MISSING DATA"
Me.Product_Cond.SetFocus
Cancel = True
Exit Sub
End If
End Sub