Solved

Determine Missing data on subform from button on mainform

Posted on 2014-02-12
6
350 Views
Last Modified: 2014-02-13
I need to determine if all fields contain data.  Here is what I have so far:

I tried it with for each ctl - ran into variable not found issue.

tried to simplify with the if statement - but this does capture any or all the values or lack of value.

what is the best approach to validate fields are not left blank?

Private Sub cmdPrintCodingSlip_Click()
Dim X As Integer
Dim ctl As Control
Dim frm As AccessObject

   
   On Error GoTo cmdPrintCodingSlip_Click_Error
 '        frm = Forms![frmCodingSlip]![Sub1]
'        For Each ctl In frm.Controls
'            If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
'                 If ctl.Tag = "X" Then
'                     If ctl & "" = "" Then
'                          Call MsgBox("Please enter missing Data", vbCritical, "Data Missing!")
'                          Exit Sub
'                     End If
'                   End If
'              End If
'        Next
'
        gFrmName = GetgfrmName()
        
        UserAccess
       
            If Forms![frmCodingSlip]![Sub1].Form![cboVendorName] = Null Or _
                Forms![frmCodingSlip]![Sub1].Form![InvoiceNumber] = Null Or _
                Forms![frmCodingSlip]![Sub1].Form![InvoiceDate] = Null Or _
                Forms![frmCodingSlip]![Sub1].Form![NetAmount] = Null Then
                Call MsgBox("Please enter missing Data", vbCritical, "Data Missing!")
                Exit Sub
            End If

Open in new window

0
Comment
Question by:Karen Schaefer
  • 3
  • 2
6 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39855036
Why not handle this from the subform instead of the mainform?  When the focus changes between the main and sub forms, the data is automatically saved, so it seems you could run this similar code in the BeforeUpdate event of the subform itself rather than adding complexity on by trying to do it through the main form.

That said, I'd either use a Tag, like you tried, or just all controls if I knew they were all bound and required:

Private Sub YourSubform_BeforeUpdate(Cancel As Integer)

  Dim ctl As Access.Control
  Dim Valid As Booean
  
  Valid = True

  For Each ctl In Me.Controls
    If ctl.Tag = "X" Then
      If IsNull(ctl.Value) Then
         Valid = False
      End If
  Next ctl

  If Not Valid Then
     MsgBox "Please fill out all fields"
     Cancel = True
  End If

End Sub

Open in new window


hth
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 39855638
Minor correction:
Private Sub YourSubform_BeforeUpdate(Cancel As Integer)

  Dim ctl As Access.Control
  
  For Each ctl In Me.Controls
    If ctl.Tag = "X" Then
      If IsNull(ctl.Value) Then
         Cancel = True
         Exit For
      End If
  Next ctl

  If Cancel = True Then
     MsgBox "Please fill out all fields"
  End If

End Sub 

Open in new window

/gustav
0
 

Author Comment

by:Karen Schaefer
ID: 39856431
Getting compile issue - doesn't like the next without a for.

thanks for your help.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Karen Schaefer
ID: 39856474
Nevermind turns out it was missing a second END IF.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39856479
Thanks for the input - here is the corrected cod:

Private Sub Form_BeforeUpdate(Cancel As Integer)

  Dim ctl As Access.Control
 
    For Each ctl In Me.Controls
        If ctl.Tag = "X" Then
            If IsNull(ctl.Value) Then
               Cancel = True
               Exit For
            End If
        End If
    Next ctl

  If Cancel = True Then
     MsgBox "Please fill out all fields"
  End If

End Sub
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39856525
Oh, I see it now. Thanks!

/gustav
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question