Form - Control Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that retrieves records that a user needs to manually update.
They need to enter a value in a control PL for each record on the form. I am
trying to program validation that when the user clicks the Save and Close
button the program will check all the records retrieved by the form to ensure
that they now have a value in the PL control.

I can't seem to get this to work. The way I have it, the program only
checks the current record to see if it has a value.

Making the PL field in the underlying table a required field is not an
option in this case. There are scenarios where this particular field is not
required. Also, putting a validation rule (Is not null) on th PL control in
the form doesn't work. It only works if I first enter something in the
control and then delete it.

Does anybody have any suggestions.

Thanks,
Bill Horton
 
William said:
I have a form that retrieves records that a user needs to manually update.
They need to enter a value in a control PL for each record on the form. I am
trying to program validation that when the user clicks the Save and Close
button the program will check all the records retrieved by the form to ensure
that they now have a value in the PL control.

I can't seem to get this to work. The way I have it, the program only
checks the current record to see if it has a value.

Making the PL field in the underlying table a required field is not an
option in this case. There are scenarios where this particular field is not
required. Also, putting a validation rule (Is not null) on th PL control in
the form doesn't work. It only works if I first enter something in the
control and then delete it.
Click to expand...


That sounds like an unusual validatation, but you can loop
through all the records in a form's dataset using something
like this air code:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull(!PLfield) Then
MsgBox "Forgot to enter PL"
Me.Bookmark = .Bookmark
PL.Sectocus
End If
.MoveNext
Loop
End With
 
Marshall,

Thanks very much for your reply. This gives me a good jump start on what
I'm trying to do. I am having one problem however. The line of code
"[Group-Description].Sectocus" is producing a compile error "Method or data
member not found." I can't seem to find Sectocus in the help files anywhere
either. Could you elaborate on what Sectocus is perhaps?

Below is the actual code I used in my program.

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull([Forms]![F_Q_Data_NewAccounts]![Group-Description]) Then
MsgBox "Please enter a P&L designation from the drop down list."
Me.Bookmark = .Bookmark
[Group-Description].Sectocus
End If
.MoveNext
Loop
End With
 
Sorry, my fumble fingered typo. It should be SetFocus.
--
Marsh
MVP [MS Access]


William said:
Thanks very much for your reply. This gives me a good jump start on what
I'm trying to do. I am having one problem however. The line of code
"[Group-Description].Sectocus" is producing a compile error "Method or data
member not found." I can't seem to find Sectocus in the help files anywhere
either. Could you elaborate on what Sectocus is perhaps?

Below is the actual code I used in my program.

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull([Forms]![F_Q_Data_NewAccounts]![Group-Description]) Then
MsgBox "Please enter a P&L designation from the drop down list."
Me.Bookmark = .Bookmark
[Group-Description].Sectocus
End If
.MoveNext
Loop
End With

Marshall Barton said:
That sounds like an unusual validatation, but you can loop
through all the records in a form's dataset using something
like this air code:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull(!PLfield) Then
MsgBox "Forgot to enter PL"
Me.Bookmark = .Bookmark
PL.Sectocus
End If
.MoveNext
Loop
End With
Click to expand...
Click to expand...
 
Marshall,

Thanks very much. I was able to get it to work. Below is the code I wound
up using. I guess I could have used the regular recordset property but went
with the clone version per your suggestion. I'm not sure I understand the
difference / benefits between the 2. I will have to use similar
functionality in a lot of other forms I have so your help is much appreciated.

DoCmd.SetWarnings False
Dim X As Boolean
X = False
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Me.Bookmark = .Bookmark
If IsNull([Group-Description]) Then
X = True
End If
.MoveNext
Loop
End With
If X = True Then
MsgBox "Please choose a P&L designation from the drop down list for all
cost center / cost elelments listed below"
Else
DoCmd.Close acForm, "F_Q_Data_NewAccounts"
End If
DoCmd.SetWarnings True

Marshall Barton said:
Sorry, my fumble fingered typo. It should be SetFocus.
--
Marsh
MVP [MS Access]


William said:
Thanks very much for your reply. This gives me a good jump start on what
I'm trying to do. I am having one problem however. The line of code
"[Group-Description].Sectocus" is producing a compile error "Method or data
member not found." I can't seem to find Sectocus in the help files anywhere
either. Could you elaborate on what Sectocus is perhaps?

Below is the actual code I used in my program.

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull([Forms]![F_Q_Data_NewAccounts]![Group-Description]) Then
MsgBox "Please enter a P&L designation from the drop down list."
Me.Bookmark = .Bookmark
[Group-Description].Sectocus
End If
.MoveNext
Loop
End With

William Horton wrote:
I have a form that retrieves records that a user needs to manually update.
They need to enter a value in a control PL for each record on the form. I am
trying to program validation that when the user clicks the Save and Close
button the program will check all the records retrieved by the form to ensure
that they now have a value in the PL control.

I can't seem to get this to work. The way I have it, the program only
checks the current record to see if it has a value.

Making the PL field in the underlying table a required field is not an
option in this case. There are scenarios where this particular field is not
required. Also, putting a validation rule (Is not null) on th PL control in
the form doesn't work. It only works if I first enter something in the
control and then delete it.
Click to expand...
Marshall Barton said:
That sounds like an unusual validatation, but you can loop
through all the records in a form's dataset using something
like this air code:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull(!PLfield) Then
MsgBox "Forgot to enter PL"
Me.Bookmark = .Bookmark
PL.Sectocus
End If
.MoveNext
Loop
End With
Click to expand...
Click to expand...
Click to expand...
 
I think that will work, but, by setting the bookmark every
time through the loop, you're going to get a lot of screen
flashing.

As for the difference between Recordset and RecordsetClone,
the formaer is the form's actual recordset and anything you
do to it is immediately(?) reflected on the screen.
RecordsetClone is a separate, but identical recordset that
you can move around in without affecting the display.
--
Marsh
MVP [MS Access]


William said:
Thanks very much. I was able to get it to work. Below is the code I wound
up using. I guess I could have used the regular recordset property but went
with the clone version per your suggestion. I'm not sure I understand the
difference / benefits between the 2. I will have to use similar
functionality in a lot of other forms I have so your help is much appreciated.

DoCmd.SetWarnings False
Dim X As Boolean
X = False
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Me.Bookmark = .Bookmark
If IsNull([Group-Description]) Then
X = True
End If
.MoveNext
Loop
End With
If X = True Then
MsgBox "Please choose a P&L designation from the drop down list for all
cost center / cost elelments listed below"
Else
DoCmd.Close acForm, "F_Q_Data_NewAccounts"
End If
DoCmd.SetWarnings True

Marshall Barton said:
Sorry, my fumble fingered typo. It should be SetFocus.


William said:
Thanks very much for your reply. This gives me a good jump start on what
I'm trying to do. I am having one problem however. The line of code
"[Group-Description].Sectocus" is producing a compile error "Method or data
member not found." I can't seem to find Sectocus in the help files anywhere
either. Could you elaborate on what Sectocus is perhaps?

Below is the actual code I used in my program.

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull([Forms]![F_Q_Data_NewAccounts]![Group-Description]) Then
MsgBox "Please enter a P&L designation from the drop down list."
Me.Bookmark = .Bookmark
[Group-Description].Sectocus
End If
.MoveNext
Loop
End With


William Horton wrote:
I have a form that retrieves records that a user needs to manually update.
They need to enter a value in a control PL for each record on the form. I am
trying to program validation that when the user clicks the Save and Close
button the program will check all the records retrieved by the form to ensure
that they now have a value in the PL control.

I can't seem to get this to work. The way I have it, the program only
checks the current record to see if it has a value.

Making the PL field in the underlying table a required field is not an
option in this case. There are scenarios where this particular field is not
required. Also, putting a validation rule (Is not null) on th PL control in
the form doesn't work. It only works if I first enter something in the
control and then delete it.


:
That sounds like an unusual validatation, but you can loop
through all the records in a form's dataset using something
like this air code:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull(!PLfield) Then
MsgBox "Forgot to enter PL"
Me.Bookmark = .Bookmark
PL.Sectocus
End If
.MoveNext
Loop
End With
Click to expand...
Click to expand...
Click to expand...
 
I used the bookmark statements because when I didn't the program didn't
actually loop. It performed the steps in the loop the correct # of times but
performed them all on the current record. When I used the bookmark
statement, however, it worked fine. Did I do something wrong that it did the
looping all on just the current record?

Marshall Barton said:
I think that will work, but, by setting the bookmark every
time through the loop, you're going to get a lot of screen
flashing.

As for the difference between Recordset and RecordsetClone,
the formaer is the form's actual recordset and anything you
do to it is immediately(?) reflected on the screen.
RecordsetClone is a separate, but identical recordset that
you can move around in without affecting the display.
--
Marsh
MVP [MS Access]


William said:
Thanks very much. I was able to get it to work. Below is the code I wound
up using. I guess I could have used the regular recordset property but went
with the clone version per your suggestion. I'm not sure I understand the
difference / benefits between the 2. I will have to use similar
functionality in a lot of other forms I have so your help is much appreciated.

DoCmd.SetWarnings False
Dim X As Boolean
X = False
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Me.Bookmark = .Bookmark
If IsNull([Group-Description]) Then
X = True
End If
.MoveNext
Loop
End With
If X = True Then
MsgBox "Please choose a P&L designation from the drop down list for all
cost center / cost elelments listed below"
Else
DoCmd.Close acForm, "F_Q_Data_NewAccounts"
End If
DoCmd.SetWarnings True

Marshall Barton said:
Sorry, my fumble fingered typo. It should be SetFocus.


William Horton wrote:
Thanks very much for your reply. This gives me a good jump start on what
I'm trying to do. I am having one problem however. The line of code
"[Group-Description].Sectocus" is producing a compile error "Method or data
member not found." I can't seem to find Sectocus in the help files anywhere
either. Could you elaborate on what Sectocus is perhaps?

Below is the actual code I used in my program.

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull([Forms]![F_Q_Data_NewAccounts]![Group-Description]) Then
MsgBox "Please enter a P&L designation from the drop down list."
Me.Bookmark = .Bookmark
[Group-Description].Sectocus
End If
.MoveNext
Loop
End With


William Horton wrote:
I have a form that retrieves records that a user needs to manually update.
They need to enter a value in a control PL for each record on the form. I am
trying to program validation that when the user clicks the Save and Close
button the program will check all the records retrieved by the form to ensure
that they now have a value in the PL control.

I can't seem to get this to work. The way I have it, the program only
checks the current record to see if it has a value.

Making the PL field in the underlying table a required field is not an
option in this case. There are scenarios where this particular field is not
required. Also, putting a validation rule (Is not null) on th PL control in
the form doesn't work. It only works if I first enter something in the
control and then delete it.


:
That sounds like an unusual validatation, but you can loop
through all the records in a form's dataset using something
like this air code:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull(!PLfield) Then
MsgBox "Forgot to enter PL"
Me.Bookmark = .Bookmark
PL.Sectocus
End If
.MoveNext
Loop
End With
Click to expand...
Click to expand...
Click to expand...
 
The difference is that you are testing the form's Current
record [Group-Description] so you need to keep changing the
current record. If you'll check my suggested code
carefully, you'll see that I was checking the
RecordsetClone's field (note the ! before the field name in
my code).
--
Marsh
MVP [MS Access]


William said:
I used the bookmark statements because when I didn't the program didn't
actually loop. It performed the steps in the loop the correct # of times but
performed them all on the current record. When I used the bookmark
statement, however, it worked fine. Did I do something wrong that it did the
looping all on just the current record?

Marshall Barton said:
I think that will work, but, by setting the bookmark every
time through the loop, you're going to get a lot of screen
flashing.

As for the difference between Recordset and RecordsetClone,
the formaer is the form's actual recordset and anything you
do to it is immediately(?) reflected on the screen.
RecordsetClone is a separate, but identical recordset that
you can move around in without affecting the display.


William said:
Thanks very much. I was able to get it to work. Below is the code I wound
up using. I guess I could have used the regular recordset property but went
with the clone version per your suggestion. I'm not sure I understand the
difference / benefits between the 2. I will have to use similar
functionality in a lot of other forms I have so your help is much appreciated.

DoCmd.SetWarnings False
Dim X As Boolean
X = False
With Me.RecordsetClone
.MoveFirst
Do Until .EOF
Me.Bookmark = .Bookmark
If IsNull([Group-Description]) Then
X = True
End If
.MoveNext
Loop
End With
If X = True Then
MsgBox "Please choose a P&L designation from the drop down list for all
cost center / cost elelments listed below"
Else
DoCmd.Close acForm, "F_Q_Data_NewAccounts"
End If
DoCmd.SetWarnings True

:
Sorry, my fumble fingered typo. It should be SetFocus.


William Horton wrote:
Thanks very much for your reply. This gives me a good jump start on what
I'm trying to do. I am having one problem however. The line of code
"[Group-Description].Sectocus" is producing a compile error "Method or data
member not found." I can't seem to find Sectocus in the help files anywhere
either. Could you elaborate on what Sectocus is perhaps?

Below is the actual code I used in my program.

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull([Forms]![F_Q_Data_NewAccounts]![Group-Description]) Then
MsgBox "Please enter a P&L designation from the drop down list."
Me.Bookmark = .Bookmark
[Group-Description].Sectocus
End If
.MoveNext
Loop
End With


William Horton wrote:
I have a form that retrieves records that a user needs to manually update.
They need to enter a value in a control PL for each record on the form. I am
trying to program validation that when the user clicks the Save and Close
button the program will check all the records retrieved by the form to ensure
that they now have a value in the PL control.

I can't seem to get this to work. The way I have it, the program only
checks the current record to see if it has a value.

Making the PL field in the underlying table a required field is not an
option in this case. There are scenarios where this particular field is not
required. Also, putting a validation rule (Is not null) on th PL control in
the form doesn't work. It only works if I first enter something in the
control and then delete it.


:
That sounds like an unusual validatation, but you can loop
through all the records in a form's dataset using something
like this air code:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
If IsNull(!PLfield) Then
MsgBox "Forgot to enter PL"
Me.Bookmark = .Bookmark
PL.Sectocus
End If
.MoveNext
Loop
End With
Click to expand...
Click to expand...
Click to expand...
 
Back
Top