Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Determine Missing data on subform from button on mainform

Posted on 2014-02-12
6
Medium Priority
?
356 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 2000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

719 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