Solved

Validation msgbox

Posted on 2004-10-31
539 Views
Last Modified: 2008-02-01
Hi !

I'd like to loop through a subform datasheet from a main form and detect which fields are empty, the show an appropriate msgbox. Someting like :
=============================
With Me.subDetails.Form.RecordsetClone
    Do Until .EOF
        'check all the empty fields
    Loop
End With  
=============================
Now since for record 1, the first fields could be empty, for record 2 also, I need to show the empty field name only once !!! Also, if record 3 has another field empty, I need to show both (but the first field only once !). I'd like something that gets the field names automatically so If i change the names, the msgbox is still relevant.
Thanks  
0
Question by:rouxjean
    36 Comments
     
    LVL 44

    Expert Comment

    by:GRayL
    I'm not sure I understand the question.  You could consider looking at the recordset in a subform datasheet view.  You can see in an instant what is empty and what is not.
    0
     

    Author Comment

    by:rouxjean
    GRayL: that's what I want to do, but I want to put empty fields in a single msgbox, without repeating, say field1, even if it's empty on more than 1 record !
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    Hi rouxjean, GRayL,

    What I do usually in this cases is the following:
    You have a few messeges to display, say
    msgbox1
    msgbox2
    msgbox3
    based on certain conditions,
    You only want the msgbox to show the messege only once, no matter how many times the condition apply,

    I use something I call a lock, to lock the code from entering in the same place again, for example:


    Lock1=0
    for i=1 to 10
      if Lock1=0 and int(i/2)=i/2 then
       msgbox "I got it"
       Lock1=1
      endif
    next i

    As you can see, although all the integers should pass into the code, but Lock1 only allows one value to enter, then it locks the code.

    I hope this helps,

    jaffer
    0
     
    LVL 44

    Expert Comment

    by:GRayL
    Something like:

    Select Sum(iif(fld1 is null,1,0))as fld1, Sum(iif(fld2 is null,1,0)) as fld2, Sum(iif(fld3 is null,1,0)) as fld3, etc. from myTable;

    will produce a query where every field is listed along with the number of nulls in each field in the recordset.
    0
     

    Author Comment

    by:rouxjean
    jjafferr: seems like a good idea, however I'm having trouble implementing it...because I need to loop through all records, field by field than set the lock accordingly. Could you suggest code sample more complete please ?
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    Hi rouxjean
    It was a very interesting problem to solve.
    Please make a command button on your main Form, call it Command2 and paste this code On Click of its [Event Procedure].



    Private Sub Command2_Click()
        Dim Ctl As Control
        Dim Ctl2 As Control
        Dim frmCurrentForm As Form
        Dim Col() As String

       
    'Lets Get the Main Form Name
        Set frmCurrentForm = Screen.ActiveForm
    '    MsgBox "Current form is " & frmCurrentForm.Name
         MyFormName = frmCurrentForm.Name

    'Lets Get the SubForm Name
        For Each Ctl In Me.Controls
    '    MsgBox Ctl.ControlName

    'Go through the Form Controls, and detect the SubForm Control
            Select Case TypeName(Ctl)
            Case "SubForm"
           
    'We are in the SubForm Now
                MySubFormName = Ctl.ControlName
                NumberOfRecords = Ctl.Form.Recordset.RecordCount
    '            MsgBox Ctl.Form.Recordset.RecordCount

    'Move the Record pointer to the top
        Ctl.Form.Recordset.MoveLast
        Ctl.Form.Recordset.MoveFirst
       
    'Lets Count the number of Columns we have
           For Each Ctl2 In Forms(MyFormName)(MySubFormName).Controls
            Select Case TypeName(Ctl2)
            Case "TextBox"
                ColumnNumber = ColumnNumber + 1
             End Select
            Next Ctl2
       
       
       
    'Now that we have all the info required for the loop, lets find the Nulls ONLY once per field
       
       ReDim Col(ColumnNumber)
       
        For i = o To NumberOfRecords - 1    'loop through the Records
            ColumnNumber = 0 'initialize to zero
           
    'Go through the SubForm Controls, and detect the Textbox Control
    '      For Each Ctl2 In Forms!frmMain!frmData.Controls
    '      For Each Ctl2 In Forms(frmCurrentForm.Name)(Ctl.ControlName).Controls
           For Each Ctl2 In Forms(MyFormName)(MySubFormName).Controls
            Select Case TypeName(Ctl2)
            Case "TextBox"

    'We are in the SubForm's Textbox fields
                'MsgBox Ctl2.ControlName & " with a value of " & Ctl2.OldValue & "+" & Ctl2.Controls.Count
                ColumnNumber = ColumnNumber + 1

                If (IsNull(Ctl2.OldValue)) Then
                 If Col(ColumnNumber) <> "Null Exists" Then
                    Col(ColumnNumber) = "Null Exists"
                    MsgBox "Null Exists in Field " & Ctl2.ControlName
                 End If
                End If


            End Select  'Ctl2
           Next Ctl2

        Ctl.Form.Recordset.MoveNext
        Next i

             End Select 'Ctl
       Next Ctl


    'Lets

    'All the Controls
    'Case "TextBox"
    'Case "Checkbox"
    'Case "Label"
    'Case "CommandButton"
    'Case "OptionButton"
    'Case "OptionGroup"
    'Case "Combobox"
    'Case "ListBox"
    'Case "Image"
    'Case "BoundObjectFrame"
    'Case "ObjectFrame"
    'Case "PageBreak"
    'Case "TabControl"
    'Case "Page"
    'Case "SubForm"
    'Case "Line"
    'Case "Rectangle"
    'Case "ToggleButton"

    End Sub




    jaffer
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    This code is designed to work ONLY with TextBox fields in the SubForm, other controls are not included.
    0
     

    Author Comment

    by:rouxjean
    Using this (I had to modify your code, it was crashing, probably because I use 97) :
    =====================================================================
    Private Sub Commande98_Click()
    Dim Ctl As Control
    Dim Ctl2 As Control
    Dim Col() As String

    NumberOfRecords = Me.subDetails.Form.RecordsetClone.RecordCount
    MsgBox NumberOfRecords

    'Move the Record pointer to the top
    Me.subDetails.Form.RecordsetClone.MoveLast
    Me.subDetails.Form.RecordsetClone.MoveFirst

    'Lets Count the number of Columns we have
    For Each Ctl In Me.subDetails.Form.Controls
    Select Case TypeName(Ctl)
    Case "TextBox"
    columnnumber = columnnumber + 1
    End Select
    Next Ctl
    MsgBox columnnumber

    'Now that we have all the info required for the loop, lets find the Nulls ONLY once per field
    ReDim Col(columnnumber)
    For i = 0 To NumberOfRecords - 1    'loop through the Records
        columnnumber = 0 'initialize to zero
       
        For Each Ctl2 In Me.subDetails.Form.Controls
        Select Case TypeName(Ctl2)
        Case "TextBox"
       
        MsgBox Ctl2.Controlname & " with a value of " & Ctl2.OldValue & "+" & Ctl2.Controls.Count
        columnnumber = columnnumber + 1
       
        If (IsNull(Ctl2.OldValue)) Then
        If Col(columnnumber) <> "Null Exists" Then
           Col(columnnumber) = "Null Exists"
           MsgBox "Null Exists in Field " & Ctl2.Controlname
        End If
        End If
       
        End Select
        Next Ctl2
        Me.subDetails.Form.RecordsetClone.MoveNext
    Next i
    End Sub
    =====================================================================
    Everything works well except it doesn't loop through the records !!!! If I have 3 records, it'll loop three times on the same record...Any ideas ?
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    email me please so that I can send you the A97 version, which I converted from A2k.

    I didn't change anything, and it works OK.

    Please do it now, as I have to leave after 30 minutes.

    jaffer
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    its the working mdb
    0
     

    Author Comment

    by:rouxjean
    jjafferr: what's your email ? Can you put it on a website...not sure if it will get through our firewall...every attachments are scanned !!! Anyway, when I pasted it, it didn't loop in the subform but in the form. Also I cannot get the recordset property but recordsetclone only !
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    here you go,

    www.almosawe.ae/jaffer/EmptyFields-97.zip

    No changes at all, its the same code as posted above.
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    open Form frmMain,
    and click on The BIG button,
    it will go throught teh frmData SubForm and show you the results,
    you can change the data in the subform and see the results.

    jaffer
    0
     

    Author Comment

    by:rouxjean
    crashing here : NumberOfRecords = Ctl.Form.Recordset.RecordCount changed to recordsetclone everywhere you are using recordset.
    Then no msgbox at all, same problem as before :-((
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    Sorry, but I have to think it over,

    whats the error message
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    Are you getting the correct results from your msgbox, other than Not looping?

    its this line that makes the Records loop

    Me.subDetails.Form.RecordsetClone.MoveNext

    so maybe you can play with it a little bit
    0
     

    Author Comment

    by:rouxjean
    jjafferr: error msg from your code with the recordset property: error 2465, error defined by the application or object (sorry it's in french).
    And yes i'm using Me.subDetails.Form.RecordsetClone.MoveNext to loop through the record...it's the only way I know of !
    0
     

    Author Comment

    by:rouxjean
    jjafferr: using my code, it always loops in the first record...it doesn't do the movenext. So it displays 3 times the same correct information.
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    Please try this code

        Dim Ctl2 As Variant
        Dim tmprs As Variant
        Dim Col() As String
       
        Set tmprs = Me.frmData.Form.Recordset
        Set Ctl2 = Me.frmData.Form.Controls

    'Now that we have all the info required for the loop, lets find the Nulls ONLY once per field
        columnnumber = Me.frmData.Form.Recordset.RecordCount
        ReDim Col(columnnumber)

        If Not tmprs.BOF Then
            tmprs.MoveFirst
        End If
        While Not tmprs.EOF
        columnnumber = 0 'initialize to zero
       
            For Each tmpcontrol In Ctl2
                If TypeName(tmpcontrol) = "TextBox" Then


    '                MsgBox tmpcontrol.ControlName & " with a value of " & tmpcontrol.Value
                    columnnumber = columnnumber + 1
       
                    If (IsNull(tmpcontrol.Value)) Then
                     If Col(columnnumber) <> "Null Exists" Then
                        Col(columnnumber) = "Null Exists"
                        MsgBox "Null Exists in Field " & tmpcontrol.ControlName
                     End If
                    End If


                End If
            Next tmpcontrol
           
            tmprs.MoveNext
        Wend
        tmprs.MoveFirst
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    please change frmData with your subDetails
    0
     

    Author Comment

    by:rouxjean
    Same thing....Had to use recordsetclone...Looks like it loops only through the first record. When I have more than one field with a null value, I have an out of range error on this line : If Col(columnnumber) <> "Null Exists" Then.
    Can you try with the recordsetclone property to see if that works for you ?? I really can't use recordset :-(
    0
     
    LVL 34

    Assisted Solution

    by:flavo
    Thanks for the pointer Jafer.

    rouxjean,

    Jafer sent me his verison of the db that he did up for you.  I have A97 at work, and some some stupid reason, im at work on the weekend.

    I assume you have got the sample db that jafer was sooo kind to post for you.

    I have edited (not very nice looking) the Command2_Click() sub.

    Here's what i have, it seems to work over this end.

    Good Luck!

    Private Sub Command2_Click()
        Dim Ctl As Control
        Dim Ctl2 As Control
        Dim frmCurrentForm As Form
        Dim Col() As String
        Dim rs As DAO.Recordset
        Dim fld As DAO.Field
    'Lets Get the Main Form Name
        Set frmCurrentForm = Screen.ActiveForm
    '    MsgBox "Current form is " & frmCurrentForm.Name
         MyFormName = frmCurrentForm.Name

    'Lets Get the SubForm Name
    For Each Ctl In Me.Controls
    'Go through the Form Controls, and detect the SubForm Control
            Select Case TypeName(Ctl)
            Case "SubForm"
           
    'We are in the SubForm Now
                MySubFormName = Ctl.ControlName
                Set rs = CurrentDb.OpenRecordset(Ctl.Form.RecordSource)
                'MsgBox Ctl.Form.Recordset.RecordCount

    'Move the Record pointer to the top
        If Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
        End If
       
        While Not rs.EOF
            For Each fld In rs.Fields
                If IsNull(fld.Value) Or fld.Value = "" Then
                    MsgBox "Null found in field " & fld.Name & " in record " & rs.PercentPosition / 100 * rs.RecordCount + 1
                End If
            Next ' fld
        rs.MoveNext
        Wend
       
       
       End Select
    Next

    End Sub


    Dave :-)
    0
     
    LVL 34

    Expert Comment

    by:flavo
    please note!

    You'll need to refrence the DAO Obj. Lib.


    In VBA window, Tools - Refrences and tick it from the list provided (if not alrady ticked)

    Dave
    0
     
    LVL 27

    Accepted Solution

    by:
    Dave,
    You are a HERO, Thanks a bunch for accepting my help request, IOU one.

    rouxjean,
    With Daves code, I was able to modify it to give you the results you are looking for,
    Don't hardcode your SubForm name, as the code will automatically pick it up (I thought that was the original purpose of this question).

    I have uploaded the mdb too on
    www.almosawe.ae/jaffer/EmptyFields-97.zip


    Private Sub Command6_Click()
        Dim Ctl As Control
        Dim Ctl2 As Control
        Dim frmCurrentForm As Form
        Dim Col() As String
        Dim feeld() As String
        Dim rs As DAO.Recordset
        Dim fld As DAO.Field
       
        ReDim feeld(50)

    'Lets Get the Main Form Name
        Set frmCurrentForm = Screen.ActiveForm
         MyFormName = frmCurrentForm.Name
    '     MsgBox "Current form is " & MyFormName

    'Lets Get the SubForm Name
    For Each Ctl In Me.Controls
    'Go through the Form Controls, and detect the SubForm Control
            Select Case TypeName(Ctl)
            Case "SubForm"
           
    'We are in the SubForm Now
                MySubFormName = Ctl.ControlName
                Set rs = CurrentDb.OpenRecordset(Ctl.Form.RecordSource)
    '            MsgBox "SubFormName is " & MySubFormName
    '            MsgBox "Number of Records are " & Ctl.Form.Recordset.RecordCount

    'Move the Record pointer to the top
        If Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
        End If
       
    'Lets Count the number of Columns we have in the SubForm
    '       MsgBox "Number of Table Fields is " & rs.Fields.Count

           For Each Ctl2 In Forms(MyFormName)(MySubFormName).Controls
            Select Case TypeName(Ctl2)
            Case "TextBox"
    'MsgBox Ctl2.ControlName
                ColumnNumber = ColumnNumber + 1
                feeld(ColumnNumber) = Ctl2.ControlName
    'MsgBox feeld(ColumnNumber)
             End Select
            Next Ctl2
            ManColumnNumber = ColumnNumber
    '        MsgBox columnnumber
    '        MsgBox ManColumnNumber
           
    'Now that we have all the info required for the loop, lets find the Nulls ONLY once per field
       ReDim Col(ColumnNumber)
    '   ReDim feeld(ColumnNumber)
       
       
        While Not rs.EOF
            ColumnNumber = 0 'initialize to zero

            For Each fld In rs.Fields
                ColumnNumber = ColumnNumber + 1
    '            MsgBox feeld(ColumnNumber) & "+" & ColumnNumber
                If IsNull(fld.Value) Or fld.Value = "" Then
               
    For j = 1 To ManColumnNumber
        If feeld(j) = fld.Name Then
    'MsgBox "Null found in field " & fld.Name & " in record " & rs.PercentPosition / 100 * rs.RecordCount + 1
                    If Col(j) <> "Null Exists" Then
                        Col(j) = "Null Exists"
                        MsgBox "Null Exists in Field " & fld.Name
                    End If
       End If
    Next j

                End If
            Next ' fld
        rs.MoveNext
        Wend
       
       
       End Select 'Ctl
    Next Ctl

    End Sub



    jaffer
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    There are too many buttons there for my test,
    The button you should use is
    "Take 3 A97 option"
    0
     
    LVL 34

    Expert Comment

    by:flavo
    Jafer,

    To easy mate.  Didnt really have enough time to tidy up the code / see what was really needed.  Just tried to make something that looked like you wanted to work...

    Top job Jafer.

    Dave :-)
    0
     
    LVL 34

    Expert Comment

    by:flavo
    woops..

    Jaffer not Jafer... too tired :-(
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    Jafer or jaffer
    it doesn't matter, as long as I know it is me who you are talking about,

    in fact when I studied in California, they spell the j as an H, so guess what they used to call me :o)
    0
     
    LVL 34

    Expert Comment

    by:flavo
    LOL!

    i must get confussed with all those extra j's and r's :-)
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    Well Dave, you might be the first to know the reason for all those extras,
    I wanted to open a Hotmail account for me and tries jaffer, but I was told it was taken and it proposed jaffer53,
    I said, the hell not, I am not number 53, I am number 1,
    so I had to come with a unique name which allows no duplicates,
    and you know the rest of the story,

    Now jjafferr is my trade mark, so maybe I should register it somewhere ;o)
    0
     
    LVL 34

    Expert Comment

    by:flavo
    Flavo ->

    If you type Davo into a Nokia phone, it comes up with Favo... Some how it turned into Flavo along the way...
    0
     

    Author Comment

    by:rouxjean
    Real nice, works :-)
    So what was the problem anyway ?
    0
     
    LVL 27

    Expert Comment

    by:jjafferr
    Glad I was of help,

    I think flavo is the illigibile person to answer this question.

    jaffer
    0
     
    LVL 34

    Expert Comment

    by:flavo
    Thanks guys!

    0
     

    Author Comment

    by:rouxjean
    Problem: when I use navigation buttons, it always gets the first record recordset...Ie: Even when I'm on record #2 of the form it displays the message according to the record #1 form subform's content...any fix ???
    0
     

    Author Comment

    by:rouxjean
    Not sure if I'm clear enough on this one. I open the form with record#1 displayed, then click on the button, it then displays that fieldx is empty which is true. I then use my navigation buttons to get to record#2...of course the subform content is updated with new records. So I click on the button again and I get that fieldx is empty again, which is not true and it don't even see that other fields are empty. My conclusion, it is still checking on record#1. How could I fix it ?
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    909 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

    13 Experts available now in Live!

    Get 1:1 Help Now