Require fields and refresh

[george 16-17]

Greetings all,

I am in need of some assistance with coding a function that checks bound
required fields on a form before saving a new record. It works fine, but the
problem that I am having is that when all the fields are complete, I would
like the form to be refreshed. As the code is written now, it refreshes the
data even if the required fields are incomplete. So essentially: Incomplete
data = no refresh, Complete data = refresh.

Here is my code:
Function SaveNewRecord(frm As Form)
' save function for new record - prompts save msgbox

Dim strMsg As String
strMsg = "New record has not been saved."
strMsg = strMsg & " Do you wish to save?"
Dim lngAnswer As Long
lngAnswer = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Save New Record?")

Dim ctl As Control

If frm.NewRecord And frm.Dirty Then
Select Case lngAnswer
Case vbYes 'Saves new record and checks if required fields are
null
For Each ctl In frm.Controls
If ctl.Tag = "reqd" Then 'required fields are tagged
with reqd
If Len(ctl.Value & vbNullString) = 0 Then
MsgBox "You must enter a value into """ & _
ctl.Controls(0).Caption & """.",
vbOKOnly, "Required field"
Cancel = True
ctl.SetFocus
ctl.BackColor = 16777088 'highlights ctl
Else
frm.refresh
End If
End If
Next ctl
Case vbNo 'Discards new record
Cancel = True
frm.Undo
Case vbCancel 'cancels save
Cancel = True
End Select
End If

End Function

This discussion group has been great and has helped me many times already.
Thanks in advance and any assistance is appreciated,
george

 

[Marshall Barton]

I am in need of some assistance with coding a function that checks bound
required fields on a form before saving a new record. It works fine, but the
problem that I am having is that when all the fields are complete, I would
like the form to be refreshed. As the code is written now, it refreshes the
data even if the required fields are incomplete. So essentially: Incomplete
data = no refresh, Complete data = refresh.

Here is my code:
Function SaveNewRecord(frm As Form)
' save function for new record - prompts save msgbox

Dim strMsg As String
strMsg = "New record has not been saved."
strMsg = strMsg & " Do you wish to save?"
Dim lngAnswer As Long
lngAnswer = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Save New Record?")

Dim ctl As Control

If frm.NewRecord And frm.Dirty Then
Select Case lngAnswer
Case vbYes 'Saves new record and checks if required fields are
null
For Each ctl In frm.Controls
If ctl.Tag = "reqd" Then 'required fields are tagged
with reqd
If Len(ctl.Value & vbNullString) = 0 Then
MsgBox "You must enter a value into """ & _
ctl.Controls(0).Caption & """.",
vbOKOnly, "Required field"
Cancel = True
ctl.SetFocus
ctl.BackColor = 16777088 'highlights ctl
Else
frm.refresh
End If
End If
Next ctl
Case vbNo 'Discards new record
Cancel = True
frm.Undo
Case vbCancel 'cancels save
Cancel = True
End Select
End If

End Function

Try using this kind of logic:

Dim ctl As Control
Dim AllGood As Boolean
. . .
Case vbYes
AllGood = True
For Each ctl In frm.Controls
If ctl.Tag = "reqd" Then
If Len(ctl.Value & vbNullString) = 0 Then
MsgBox "You must enter . . .
Cancel = True
ctl.SetFocus
ctl.BackColor = 16777088 'highlights ctl
AllGood = False
End If
End If
Next ctl
If AllGood Then frm.refresh
Case vbNo 'Discards new record

 

[george 16-17]

Hi Marshall,

Perfect, that is exactly what I needed. I appreciate the advice!

george

 

[george 16-17]

Hi Marshall,

I have added your suggestion, but I am geting the message "Object variable
or with Block variable not set". What am I doing wrong? I am too new at this
to figure it out on my own. Any suggestions?

Here is the revised code:
Function SaveNewRecord(frm As Form)
' save function for new record - prompts save msgbox

Dim strMsg As String
strMsg = "New record has not been saved."
strMsg = strMsg & " Do you wish to save?"
Dim lngAnswer As Long
lngAnswer = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Save New Record?")

Dim ctl As Control
Dim AllGood As Boolean

If frm.NewRecord And frm.Dirty Then
Select Case lngAnswer
Case vbYes 'Saves new record and checks if required fields are
null
AllGood = True
For Each ctl In frm.Controls
If ctl.Tag = "reqd" Then 'required fields are
tagged with reqd
If Len(ctl.Value & vbNullString) = 0 Then
MsgBox "You must enter a value into """
& _
ctl.Controls(0).Caption & """.",
vbOKOnly, "Required field"
Cancel = True
ctl.SetFocus
ctl.BackColor = 16777088 'highlights ctl
AllGood = False
End If
End If
Next ctl
If AllGood Then
ctl.BackColor = 12632256
frm.Refresh
End If
Case vbNo 'Discards new record....

Thanks again and your time is much appreciated,
george

 

[Marshall Barton]

I have added your suggestion, but I am geting the message "Object variable
or with Block variable not set". What am I doing wrong? I am too new at this
to figure it out on my own. Any suggestions?

Here is the revised code:
Function SaveNewRecord(frm As Form)
' save function for new record - prompts save msgbox

Dim strMsg As String
strMsg = "New record has not been saved."
strMsg = strMsg & " Do you wish to save?"
Dim lngAnswer As Long
lngAnswer = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Save New Record?")

Dim ctl As Control
Dim AllGood As Boolean

If frm.NewRecord And frm.Dirty Then
Select Case lngAnswer
Case vbYes 'Saves new record and checks if required fields are
null
AllGood = True
For Each ctl In frm.Controls
If ctl.Tag = "reqd" Then 'required fields are
tagged with reqd
If Len(ctl.Value & vbNullString) = 0 Then
MsgBox "You must enter a value into """
& _
ctl.Controls(0).Caption & """.",
vbOKOnly, "Required field"
Cancel = True
ctl.SetFocus
ctl.BackColor = 16777088 'highlights ctl
AllGood = False
End If
End If
Next ctl
If AllGood Then
ctl.BackColor = 12632256
frm.Refresh
End If
Case vbNo 'Discards new record....

When you got the error dialog, which line of code (via Debug
button) was highlighted?

I'll take a guess that it was the line:
ctl.BackColor = 12632256
because ctl probably has no meaning outside the loop.
I'm pretty sure that you will have to delete that line.

 

[george 16-17]

You are correct! I did delete that line and it is working fine now.

Thanks again and I appreciate your expertise.

george

 

[george 16-17]

Hi Marshall,

I hate to take advantage of your expertise...could you help me again?

In the original loop, the control's backcolor (16777088) is changed to
highlight the empty control. Is there a method to return it back to its
orginal color (12632256) after the control is updated? I was able to do this
with conditional formatting, but there are mutliple controls, and a few lines
of code would make this easier. I can't seem to get this right.

Thanks,
george

 

[Marshall Barton]

In the original loop, the control's backcolor (16777088) is changed to
highlight the empty control. Is there a method to return it back to its
orginal color (12632256) after the control is updated?

You should use each control's AfterUpdate event to reset the
color as soon as the control is changed.

If you don't mind waiting until the next time the loop is
executed, then you can do it this way:

If Len(ctl.Value & vbNullString) = 0 Then
MsgBox ...
. . .
ctl.BackColor = 16777088
AllGood = False
Else
ctl.BackColor = 12632256
End If
. . .

 

[george 16-17]

Thanks for the prompt reply...again very helpful.

I will probably go with the next time the loop is executed.

Thanks again,
george

 

[george 16-17]

Hi Marshall,

Now I know I am really taking advantage...I apologize in advance.

New problem...
How can I code this to also loop through subform controls tagged "reqd"?

Thanks and I really appreciate your help,
george

 

[Marshall Barton]

How can I code this to also loop through subform controls tagged "reqd"?

I don't believe that is a viable concept.

A record that has been edited in a subform is saved before
the focus is moved back to the main form or another subform.

Another issue is that a subform can contain multiple records
for each main form record and multiple subform records can
be edited and saved before you can click on the main form
button.

If you need to validate records before they are saved, then
each form/subform should perform its own checks. The
standard practice is to do the checks in each form's
BeforeUpdate event procedure (instead of a button) where you
can set the Cancel argument to True to prevent an
unacceptable record from being saved.

 

[george 16-17]

Again, I appreciate the extremely prompt reply.

Yes, your rationale makes sense. I did not think that through. I will look
to use the before update property for each form.

I like the concept of "standard practices" in design, which I am sure
improves the usability of the db. Do you have a reference (book, website,
etc.) of typical standard design practices?

Again, your expertise and experience is much appreciated,

george

 

[Marshall Barton]

Prior to A2007, I use Access xxxx Developers Handbook by
Litwin, Getz, et al, but it might not be appropriate for
beginners. Another is Access 2003 Inside Out by Viescas

I am sure that there are others that are also good, but I
have not used them enough to say which ones.

 

[george 16-17]

Thanks.

I will look into those references.

Understating the obvious, but you have been of great assistance.

george

Prior to A2007, I use Access xxxx Developers Handbook by
Litwin, Getz, et al, but it might not be appropriate for
beginners. Another is Access 2003 Inside Out by Viescas

I am sure that there are others that are also good, but I
have not used them enough to say which ones.
--
Marsh
MVP [MS Access]

Yes, your rationale makes sense. I did not think that through. I will look
to use the before update property for each form.

I like the concept of "standard practices" in design, which I am sure
improves the usability of the db. Do you have a reference (book, website,
etc.) of typical standard design practices?

 

[Marshall Barton]

Understating the obvious, but you have been of great assistance.

Glad to have helped. Good luck as you progress through your
application/