• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Validation msgbox

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
rouxjean
Asked:
rouxjean
  • 16
  • 11
  • 7
  • +1
2 Solutions
 
GRayLCommented:
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
 
rouxjeanAuthor Commented:
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
 
jjafferrCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
GRayLCommented:
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
 
rouxjeanAuthor Commented:
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
 
jjafferrCommented:
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
 
jjafferrCommented:
This code is designed to work ONLY with TextBox fields in the SubForm, other controls are not included.
0
 
rouxjeanAuthor Commented:
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
 
jjafferrCommented:
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
 
jjafferrCommented:
its the working mdb
0
 
rouxjeanAuthor Commented:
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
 
jjafferrCommented:
here you go,

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

No changes at all, its the same code as posted above.
0
 
jjafferrCommented:
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
 
rouxjeanAuthor Commented:
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
 
jjafferrCommented:
Sorry, but I have to think it over,

whats the error message
0
 
jjafferrCommented:
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
 
rouxjeanAuthor Commented:
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
 
rouxjeanAuthor Commented:
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
 
jjafferrCommented:
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
 
jjafferrCommented:
please change frmData with your subDetails
0
 
rouxjeanAuthor Commented:
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
 
flavoCommented:
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
 
flavoCommented:
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
 
jjafferrCommented:
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
 
jjafferrCommented:
There are too many buttons there for my test,
The button you should use is
"Take 3 A97 option"
0
 
flavoCommented:
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
 
flavoCommented:
woops..

Jaffer not Jafer... too tired :-(
0
 
jjafferrCommented:
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
 
flavoCommented:
LOL!

i must get confussed with all those extra j's and r's :-)
0
 
jjafferrCommented:
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
 
flavoCommented:
Flavo ->

If you type Davo into a Nokia phone, it comes up with Favo... Some how it turned into Flavo along the way...
0
 
rouxjeanAuthor Commented:
Real nice, works :-)
So what was the problem anyway ?
0
 
jjafferrCommented:
Glad I was of help,

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

jaffer
0
 
flavoCommented:
Thanks guys!

0
 
rouxjeanAuthor Commented:
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
 
rouxjeanAuthor Commented:
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 16
  • 11
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now