Solved

Determine Missing data on subform from button on mainform

Posted on 2014-02-12
6
334 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

707 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

18 Experts available now in Live!

Get 1:1 Help Now