Using VBA to check for Multiple Blank fields in a form

[accvbalearner]

Hello, first time posting with the site, so I hope I get this right.

I am building a form for entering data into a single table. I referred to another user's VBA code to check for multiple blank fields in the form by using the Other Tag property for the fields I want populated and then set the focus back to the first field in the form if there were any blanks.

The idea is to require the user entering data to enter data into these required fields. The code works, mostly, because it stops the record from being entered the first time thorugh. The problemm is that if the user tabs through or clicks the arrow to move to a new record on the form without changing or adding any data the second time, when the ok is clicked it adds the record to the table with the fields that I want populated as blank. I am using the following code on the Forms BeforeUpdate Event.

I would like to use this generalized code so that I can easily incorporate it into other forms that I will be designing in this same database.

Thanks in advance for any help you can offer!

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctr As Control
Dim strMsg As String
'Loop through every control on the form
For Each ctr In Me.Controls
'Look for a Particular Tag
If ctr.Tag = "BlkChk" Then
'Create a List of empty questions
If IsNull(ctr) Then
strMsg = strMsg & "_ " & ctr.Name & vbCrLf
End If
End If
Next ctr
'Did We Find Any Unanswered Questions?
If strMsg <> "" Then
If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
strMsg & vbCrLf & vbCrLf & "Do you want to proceed?", _
vbOKOnly) Then
Me.Work_Order__.SetFocus
End If
End If
End Sub

 

[John Big Booty]

Welcome to the forum.

You need to stop the record being committed using Cancel = True which your code as presented does not do. Try;

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctr As Control
Dim strMsg As String

'Loop through every control on the form
For Each ctr In Me.Controls
   'Look for a Particular Tag
   If ctr.Tag = "BlkChk" Then
      'Create a List of empty questions
      If IsNull(ctr) Then
         strMsg = strMsg & "_ " & ctr.Name & vbCrLf
      End If
   End If
Next ctr

'Did We Find Any Unanswered Questions?
If strMsg <> "" Then
   If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
   strMsg & vbCrLf & vbCrLf & "Do you want to proceed?", _
   vbOKOnly) Then
   Me.Work_Order__.SetFocus
      [COLOR="Green"]'Stop record being committed[/COLOR]
      [B]Cancel = True[/B]
   End If
End If
End Sub

 

[accvbalearner]

Perfect!! It works like a charm. If they can't fill out the form, the table won't be taking the data. Thank you for the quick reply!

Here a copy of the final product:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctr As Control
Dim strMsg As String
'Loop through every control on the form
For Each ctr In Me.Controls
'Look for a Particular Tag
If ctr.Tag = "BlkChk" Then
'Create a List of empty questions
If IsNull(ctr) Then
strMsg = strMsg & "_ " & ctr.Name & vbCrLf
End If
End If
Next ctr
'Did We Find Any Unanswered Questions?
If strMsg <> "" Then
If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
strMsg & vbCrLf & vbCrLf & "Do you want to proceed?", _
vbOKOnly) Then
Me.Work_Order__.SetFocus
'Stop record being committed
Cancel = True
End If
End If
End Sub

 

[kooboo]

Hi accvbalearner,

I am a novice at Access VBA programming.

I am interested in your code to use in a application I am creating.

The one line of code that is causing me a problem is as follows:

Me.Work_Order__.SetFocus

I would appreciate your assistance,

kooboo

 

[pr2-eugin]

Hello kooboo, Welcome to the Forum

If you are trying to adapt the CODE that has been written for someone else, make sure your design matches. Specifically your control names. Does your form have a control called Work_Order__? If not change it to something your forms has..

 

[kooboo]

Hi Paul,

Many thanks for your prompt response.

I made the assumption that "Me.Work_Order__.SetFocus" related to the first Control Name in the form that was blank with the Control Name being Work_Order_X (X being the final part of the Control Name.)

It works when I specify a Control Name which is on my form but not to the first blank Control detected.

Regards,

Alasdair.

 

[pr2-eugin]

The code (IMVHO) does not dictate setting the focus on the very first control that is blank. It will cancel the updating process if there is a blank value, and show the names of the controls that needs filling up..

 

[OngoingDeveloper]

Hey all,

I am Fairly new to this forum, as well as VBA. I love the code discussed in the thread and I am currently implementing it into my form. However, when I do so, I am getting an error where: it will specify the records that are blank, and upon clicking the vbOK button, will then state "You can't go to the specified record.". It will then show the blank controls again with the vbOK button, and finally clicking the button again, will kick me out of the form.

The form I am using is a form that allows users to add a maintenance record. Technically though, this shouldn't interfere with the code, right?

Any ideas?

Thanks

 

[pr2-eugin]

Could you please provide us with the code that you are using??

 

[OngoingDeveloper]

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control
Dim strMsg As String

'Loop through every control on the form --------------------------------

For Each ctrl In Me.Controls
'Look for a Particular Tag
If ctrl.Tag = "BlkChk" Then
'Create a List of empty questions --------------------------------
If IsNull(ctrl) Then
strMsg = strMsg & "- " & ctrl.Name & vbCrLf
End If
End If

Next ctrl
'Did We Find Any Unanswered Questions? -------------------------------

If strMsg <> "" Then
If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
strMsg & vbCrLf & vbCrLf, _
vbOKOnly) Then
'Stop record from being committed ---------------------------------
Cancel = True
Me.cboLookup.SetFocus
End If
End If

End Sub

It's almost the same as the previous code in this thread, the only difference is I changed the dim 'ctr' to 'ctrl' and my focus is set to cboLookup.

P.S: Is there a better way to attach code to so it doesn't move it all to the left? Sort of like how John Big booty has his?

 

[OngoingDeveloper]

Here is my 'add' form's entire code module. I am thinking it has to do with the last bit of code which actually commits the record being added. any ideas?

Option Compare Database
------------------------------------------------------------------------------------------
Private Sub cboLookup_Change()
 
Me.txtItemBrand = Me.cboLookup.Column(1)
Me.txtItemName = Me.cboLookup.Column(2)
Me.txtItemModel = Me.cboLookup.Column(3)
 
End Sub
 
------------------------------------------------------------------------------------------
Private Sub cmdClose_Click()
 
Dim Answer As Integer
 If Me.Dirty = True Then
    Dim Response As Integer
    [COLOR=yellowgreen]' Displays a message box with the yes and no options. <-----COMMENT[/COLOR]
    Response = MsgBox(Prompt:="Do you wish to discard changes?", Buttons:=vbYesNo)
    [COLOR=yellowgreen]' If statement to check if the yes button was selected. <-----COMMENT[/COLOR]
    If Response = vbYes Then
        DoCmd.RunCommand acCmdUndo
        DoCmd.Close
        Else
        Me.SetFocus
    End If
 Else
    [COLOR=yellowgreen]' The no button was selected. <-----COMMENT[/COLOR]
    DoCmd.Close
  End If
 
End Sub
 
------------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim ctrl As Control 
Dim strMsg As String 
 
For Each ctrl In Me.Controls
   [COLOR=yellowgreen]'Look for a Particular Tag <-----COMMENT[/COLOR]   
 
If ctrl.Tag = "BlkChk" Then
 
        [COLOR=yellowgreen]'Create a List of empty questions <-----COMMENT[/COLOR]        
        If IsNull(ctrl) Then
        strMsg = strMsg & "- " & ctrl.Name & vbCrLf
        End If
End If
 
Next ctrl
[COLOR=yellowgreen]'Did We Find Any Unanswered Questions? <-----COMMENT[/COLOR]
 
If strMsg <> "" Then
    If vbOK = MsgBox("The following fields require a response" & vbCrLf & vbCrLf & _
    strMsg & vbCrLf & vbCrLf, _
    vbOKOnly) Then
 
    [COLOR=yellowgreen]'Stop the record from being committed <-----COMMENT[/COLOR]   
    Cancel = True
    Me.cboLookup.SetFocus
 
    End If
 
End If
 
End Sub
 
------------------------------------------------------------------------------------------
Private Sub cmdUpdateRecordAndClose_Click()
 
On Error GoTo cmdUpdateRecordAndClose_Click_Err
    On Error Resume Next
 
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
 
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
            End If
 
cmdUpdateRecordAndClose_Click_Exit:
    Exit Sub
cmdUpdateRecordAndClose_Click_Err:
    MsgBox Error$
    Resume cmdUpdateRecordAndClose_Click_Exit
 
End Sub

 

[OngoingDeveloper]

Could you please provide us with the code that you are using??

I've provided the full code my form is based on, and figured out how to copy the code into the post so it didn't push it all to the left . I was wondering if there's a way to apply this code to the 'on-click' command rather than the 'before-update' command. I basically want my users to fill in the form, and once they click the 'Add Record' button, check for blanks, and if there are blanks, notify the user of them and set the focus to an arbitrary control. If all is well, then a MsgBox will tell them that the record has been added to the database. What's your opinion on this idea, think it's practical?

Thank you for your time and consideration Paul.