Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Validation msgbox

Posted on 2004-10-31
36
Medium Priority
?
548 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
Comment
Question by:rouxjean
[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
  • 16
  • 11
  • 7
  • +1
36 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 12456868
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
ID: 12457163
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
ID: 12458448
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 44

Expert Comment

by:GRayL
ID: 12458473
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
ID: 12463252
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
ID: 12492503
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
ID: 12492508
This code is designed to work ONLY with TextBox fields in the SubForm, other controls are not included.
0
 

Author Comment

by:rouxjean
ID: 12495015
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
ID: 12495091
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
ID: 12495133
its the working mdb
0
 

Author Comment

by:rouxjean
ID: 12495141
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
ID: 12495181
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
ID: 12495220
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
ID: 12495389
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
ID: 12495534
Sorry, but I have to think it over,

whats the error message
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12495812
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
ID: 12496154
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
ID: 12496247
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
ID: 12503610
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
ID: 12503615
please change frmData with your subDetails
0
 

Author Comment

by:rouxjean
ID: 12505107
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
flavo earned 1000 total points
ID: 12510523
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
ID: 12510529
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:
jjafferr earned 1000 total points
ID: 12512897
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
ID: 12512910
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
ID: 12512920
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
ID: 12512923
woops..

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

Expert Comment

by:jjafferr
ID: 12512933
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
ID: 12512935
LOL!

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

Expert Comment

by:jjafferr
ID: 12512957
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
ID: 12512970
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
ID: 12523343
Real nice, works :-)
So what was the problem anyway ?
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12524507
Glad I was of help,

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

jaffer
0
 
LVL 34

Expert Comment

by:flavo
ID: 12527552
Thanks guys!

0
 

Author Comment

by:rouxjean
ID: 12544595
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
ID: 12544677
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

596 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