Validate filds and go ahead

[Guest]

Hello everybody, I'm just a newbie in programming and I need some advices.
What I want to do is to have all fields filled in before to go to a new
record.
The code below appended to AddTitle button changes the backcolor of each
field if null. So far so good.
What I'd like to do now is to go to a new record if all fields have been
filled in but I don't know how to modify the routine. Besides, if a field has
not been filled in and the backcolor is changed due to the Click event, this
backcolor remains also on previous records, if I scroll through them with the
mouse wheel. Since previous records should be OK, fields should be formatted
with the default color.
Every suggestion would be very appeciated and if you have a different or
easier approach to do that, please feel free to redesign everything.
As you can understand from the msgbox I'm Italian. My English is not so good
so if something is unclear, please ask me.

Tks in advance

Private Sub AddTitle_Click()
For Each ctl In Screen.ActiveForm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ListBox _
Or TypeOf ctl Is ComboBox Then
If IsNull(ctl.Value) Then
MsgBox "Inserire dati mancanti ", _
vbInformation, _
"DVD Collection"
MarkFieldsToEdit
Exit For
End If
End If
Next ctl
End Sub

Public Sub MarkFieldsToEdit()
For Each ctl In Screen.ActiveForm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
If IsNull(ctl.Value) Then
With ctl
..BackColor = RGB(255, 128, 128)
..SetFocus
End With
End If
End If
Next ctl
End Sub

 

[John Spencer]

Are you using continuous view of forms or are you only displaying ONE record
at a time?

You can use conditional formatting (available in Access 2000 and later) on
your fields to highlight any that are null. That way you don't need to call
Mark Fields to Edit.

If you want to go to a new record if all the fields are filled in, then you
can use your current code. If you are not using continuous view and want to
highlight fields and don't want to use condifitional formatting, then you
need to run the highlighting code (MarkFieldsToEdit) in the Current Event of
the form.

Private Sub AddTitle_Click()
Dim tfNotDone as Boolean

For Each ctl In Screen.ActiveForm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ListBox _
Or TypeOf ctl Is ComboBox Then
If IsNull(ctl.Value) Then
tfNotDone = True
MsgBox "Inserire dati mancanti ", _
vbInformation, _
"DVD Collection"
MarkFieldsToEdit 'Not needed if using conditional formatting
Exit For
End If
End If
Next ctl

If tfNotDone = False then
'Force record to save
Me.Dirty = False
Docmd.GoToRecord ,,acNewRec
End If

End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thks John, a big step forward for me.

Are you using continuous view of forms or are you only displaying ONE record
at a time?

You can use conditional formatting (available in Access 2000 and later) on
your fields to highlight any that are null. That way you don't need to call
Mark Fields to Edit.

If you want to go to a new record if all the fields are filled in, then you
can use your current code. If you are not using continuous view and want to
highlight fields and don't want to use condifitional formatting, then you
need to run the highlighting code (MarkFieldsToEdit) in the Current Event of
the form.

Private Sub AddTitle_Click()
Dim tfNotDone as Boolean

For Each ctl In Screen.ActiveForm.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ListBox _
Or TypeOf ctl Is ComboBox Then
If IsNull(ctl.Value) Then
tfNotDone = True
MsgBox "Inserire dati mancanti ", _
vbInformation, _
"DVD Collection"
MarkFieldsToEdit 'Not needed if using conditional formatting
Exit For
End If
End If
Next ctl

If tfNotDone = False then
'Force record to save
Me.Dirty = False
Docmd.GoToRecord ,,acNewRec
End If

End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..