Entering New Records in a Form - Validation

I have a table where all the fields are set as required and a form based on
this table with Data Entry set to True. I only want the user to be able to
save a record on the form if all the fields are filled in. If the user
decides to cancel/close at some point I just want the form to close or
prompt with a message whether they want to save. From my research, I've
found that any validation should go in the form's beforeupdate property. My
code at the moment is :
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.cboProductID & "") = 0 Then
MsgBox "Please select Product"
Me.ProductID.SetFocus
Cancel = True
Me.Undo
ElseIf Len(Me.txtProductName & "") = 0 Then
MsgBox "Please enter Product Name"
Me.txtProductName.SetFocus
Cancel = True
Me.Undo
ElseIf Len(Me.cboProductType & "") = 0 Then
MsgBox "Please select Product Type"
Me.cboProductType.SetFocus
Cancel = True
Me.Undo
.............'Continues'.......................... ......

I have a save and exit button which attempts to save the record with the
code : If Me.Dirty = True Then Me.Dirty = False. If the form's before
update validation is not met, I get runtime error 2101 : The setting you
entered isn't valid for this property. I have handled this in my error
handling. If the user then closes the form I get "you can't save this
record at this time" - How can I get this not to show? or replace it with my
own "Are you sure you wish to exit message box"? Also, is this the best way
to perform form validation? thanks

The Len function returns the number of characters in a string or string
variable. I think the problem is that you're trying to get Access to use Len
with 2 comboboxes, not just string or string variables. I think you need to
do your validation check on whatever controls on your form these comboboxes
are filling in; I assume you're using them to fill in controls on your
records.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via http://www.accessmonster.com

Will,

If your BeforeUpdate event finds a blank control, you should exit the sub
and set the focus to the offending control such that the Me.Dirty test is
never performed. Also, it's easier to loop through the controls and use the
Tag property to store part of the field-specific message:

Dim ctl as Control
Dim strMsg as String
Dim intResponse as Integer

For Each ctl in Me.Controls
If IsNull(ctl) Then
Select Case ctl.ControlType
Case acTextbox
strMsg = "Please enter " & ctl.Tag
Case acComboBox, acListBox, acOptionGroup
strMsg = "Please select " & ctl.Tag
End Select
intResponse = MsgBox (strMsg, vbOKCancel, "Required Field")
If intResponse = vbOK Then
Cancel = True
Me![ctl].SetFocus
Exit Sub
Else
Me.Undo
End If
End If
Next ctl

Hope that helps.
Sprinks


"Will" wrote:

I have a table where all the fields are set as required and a form based on
this table with Data Entry set to True. I only want the user to be able to
save a record on the form if all the fields are filled in. If the user
decides to cancel/close at some point I just want the form to close or
prompt with a message whether they want to save. From my research, I've
found that any validation should go in the form's beforeupdate property. My
code at the moment is :
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.cboProductID & "") = 0 Then
MsgBox "Please select Product"
Me.ProductID.SetFocus
Cancel = True
Me.Undo
ElseIf Len(Me.txtProductName & "") = 0 Then
MsgBox "Please enter Product Name"
Me.txtProductName.SetFocus
Cancel = True
Me.Undo
ElseIf Len(Me.cboProductType & "") = 0 Then
MsgBox "Please select Product Type"
Me.cboProductType.SetFocus
Cancel = True
Me.Undo
.............'Continues'.......................... ......

I have a save and exit button which attempts to save the record with the
code : If Me.Dirty = True Then Me.Dirty = False. If the form's before
update validation is not met, I get runtime error 2101 : The setting you
entered isn't valid for this property. I have handled this in my error
handling. If the user then closes the form I get "you can't save this
record at this time" - How can I get this not to show? or replace it with my
own "Are you sure you wish to exit message box"? Also, is this the best way
to perform form validation? thanks