rouxjean
asked on
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.Records
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
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.Records
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
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.
ASKER
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 !
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
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
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.
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.
ASKER
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 ?
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.RecordC
' MsgBox Ctl.Form.Recordset.RecordC
'Move the Record pointer to the top
Ctl.Form.Recordset.MoveLas
Ctl.Form.Recordset.MoveFir
'Lets Count the number of Columns we have
For Each Ctl2 In Forms(MyFormName)(MySubFor
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.Cont
' For Each Ctl2 In Forms(frmCurrentForm.Name)
For Each Ctl2 In Forms(MyFormName)(MySubFor
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.MoveNex
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
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.RecordC
' MsgBox Ctl.Form.Recordset.RecordC
'Move the Record pointer to the top
Ctl.Form.Recordset.MoveLas
Ctl.Form.Recordset.MoveFir
'Lets Count the number of Columns we have
For Each Ctl2 In Forms(MyFormName)(MySubFor
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.Cont
' For Each Ctl2 In Forms(frmCurrentForm.Name)
For Each Ctl2 In Forms(MyFormName)(MySubFor
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.MoveNex
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
This code is designed to work ONLY with TextBox fields in the SubForm, other controls are not included.
ASKER
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.Records
MsgBox NumberOfRecords
'Move the Record pointer to the top
Me.subDetails.Form.Records
Me.subDetails.Form.Records
'Lets Count the number of Columns we have
For Each Ctl In Me.subDetails.Form.Control
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.Control
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.Records
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 ?
==========================
Private Sub Commande98_Click()
Dim Ctl As Control
Dim Ctl2 As Control
Dim Col() As String
NumberOfRecords = Me.subDetails.Form.Records
MsgBox NumberOfRecords
'Move the Record pointer to the top
Me.subDetails.Form.Records
Me.subDetails.Form.Records
'Lets Count the number of Columns we have
For Each Ctl In Me.subDetails.Form.Control
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.Control
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.Records
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 ?
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
I didn't change anything, and it works OK.
Please do it now, as I have to leave after 30 minutes.
jaffer
its the working mdb
ASKER
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 !
here you go,
www.almosawe.ae/jaffer/EmptyFields-97.zip
No changes at all, its the same code as posted above.
www.almosawe.ae/jaffer/EmptyFields-97.zip
No changes at all, its the same code as posted above.
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
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
ASKER
crashing here : NumberOfRecords = Ctl.Form.Recordset.RecordC
Then no msgbox at all, same problem as before :-((
Then no msgbox at all, same problem as before :-((
Sorry, but I have to think it over,
whats the error message
whats the error message
Are you getting the correct results from your msgbox, other than Not looping?
its this line that makes the Records loop
Me.subDetails.Form.Records
so maybe you can play with it a little bit
its this line that makes the Records loop
Me.subDetails.Form.Records
so maybe you can play with it a little bit
ASKER
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.Records
And yes i'm using Me.subDetails.Form.Records
ASKER
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.
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.
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))
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
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.
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))
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
please change frmData with your subDetails
ASKER
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 :-(
Can you try with the recordsetclone property to see if that works for you ?? I really can't use recordset :-(
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There are too many buttons there for my test,
The button you should use is
"Take 3 A97 option"
The button you should use is
"Take 3 A97 option"
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 :-)
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 :-)
woops..
Jaffer not Jafer... too tired :-(
Jaffer not Jafer... too tired :-(
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)
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)
LOL!
i must get confussed with all those extra j's and r's :-)
i must get confussed with all those extra j's and r's :-)
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)
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)
Flavo ->
If you type Davo into a Nokia phone, it comes up with Favo... Some how it turned into Flavo along the way...
If you type Davo into a Nokia phone, it comes up with Favo... Some how it turned into Flavo along the way...
ASKER
Real nice, works :-)
So what was the problem anyway ?
So what was the problem anyway ?
Glad I was of help,
I think flavo is the illigibile person to answer this question.
jaffer
I think flavo is the illigibile person to answer this question.
jaffer
Thanks guys!
ASKER
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 ???
ASKER
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 ?