Form Validation (2003)

Topic

New Reply

Hi,

I am using the following codes to validate all fields on the form are inputed before update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String, ctl As Control

DL = vbNewLine & vbNewLine

For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Then
If Trim(ctl.Value & “”) = “” Then

Msg = “‘” & ctl.Name & “‘ is Required!” & DL & _
“Please enter a value to proceed . . .”
Style = vbInformation + vbOKOnly
Title = “Required Data Missing! . . .”
MsgBox Msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next
End Sub

I have a add button on the form with codes in on click event below:

DoCmd.GoToRecord , , acNewRec

Now if I have some fields are blank and then click the add button, the warning message pops up to to inform user that which field is missing. Then I get a error message: Run-time error ‘2105’. You can’t go to the specified record. If I click “Debug,” and then it open VB window with yellow highlight on DoCmd.GoToRecord , , acNewRec.

Any idea why it happened?

Thanks

Reply | Quote

Replies

I’d either insert a line

On Error Resume Next

above the line with DoCmd.GoToRecord. or build an error handler.

Reply | Quote

Thanks, Hans. It works.

If I force to close the form and I will get a Access standard error message “You can’t save this record at this time. Microsoft Office Acess may have encountered an error while…..” How can I customize this error message?

Also I have a combo box on the form, what codes should I add to validate the combo box is inputed before user add new data?

Thanks again.

Regards

Reply | Quote

You can use the Form_Error event to handle general errors. See post 667,850 and the thread starting at post 451,828 for examples. The latter thread also explains how to find the relevant error numbers.

Reply | Quote

Thank you so much, Hans.

One last questison, what codes should I add to the following to validate combo box value on the form:

Dim Msg As String, Style As Integer, Title As String
Dim DL As String, ctl As Control

DL = vbNewLine & vbNewLine

For Each ctl In Me.Controls

If ctl.ControlType = acTextBox Then
If Trim(ctl.Value & “”) = “” Then

Msg = “‘” & ctl.Name & “‘ is Required!” & DL & _
“Please enter a value to proceed . . .”
Style = vbInformation + vbOKOnly
Title = “Required Data Missing! . . .”
MsgBox Msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If

Next

Thanks

Reply | Quote

Change

If ctl.ControlType = acTextBox Then

to

If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then

Reply | Quote

Thank you so much, Hans.

Reply | Quote