Solved

Determine Missing data on subform from button on mainform

Posted on 2014-02-12
6
342 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 49

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 49

Expert Comment

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

/gustav
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now