Solved

Determine Missing data on subform from button on mainform

Posted on 2014-02-12
6
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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 51

Expert Comment

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

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

626 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