Validating Input (Access 97 /SR2)

Topic

New Reply

I have a form which has a number of fields, all of which need to be filled in.
I have set the required option on these fields to yes – but this just returns the standard Access error description. I need to write validation into the form but do not know where to put it. I tried writing it in the BEFORE UPDATE section and including a save record routine in the function but that didn’t work (so if the fields were okay it’d save, but if they weren’t it’d exit the sub routine).

I also need to write a routine which writes a combination of data into a field to make an identifier, and i’m not sure of the best place to write this either – AFTER INSERT seems to work fine, would there be any likely problems using this method?

Any help on either of these problems would be much appreciated – specially the save one as I am completely stuck.

Reply | Quote

Replies

The before update of the form is the right place to do your test.
Then If the test fail, open a message box with what’s going wrong and set the variable Cancel = True
This will return the user to the unsaved form.
You can also set the focus to the wrong field before the Cancel = True
Somethung like this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!textBox1) Then
   MsgBox "Enter something in textBox1"
   Me!textbox1.SetFocus
   Cancel = True
End If
End Sub

For the Identifier, if the field is in the recordset of the form, put it on the form (maybe Visible Property = NO), and update the field in the Before Update event. If you have to open another recordset for it, do it in the After Insert event.

Reply | Quote

I found this post which was exactly what I needed. But…

After my message comes up about the field being required, and I try to send them back to the form, I get this window that say “The DoMenuItem action was canceled.” Is there any way to avoid getting this window? I’d just like them to click ok. on my message window and take them back to the required field on the form. I tried putting in a docmd.setwarnings (messageoff) in the vba code, turning it back on after the cancel, but that didn’t work.

I’ve attached a copy of the message.

Thanks.

Reply | Quote

AFTER INSERT is probably the wrong place to create that identifier. This takes place AFTER a new record has been written to the table, so you are writing the record without the identifier then going back and updating the record with the identifier (not very efficient).

Put your code in the form’s BeforeUpdate event.

Reply | Quote

Can you post the code you are using?

Reply | Quote

Yes, thank you.

If IsNull(Me!SR_SECTION_NUM) Or Me!SR_SECTION_NUM = 0 Then
Call MsgBox(“Section Number is required”, vbInformation, “Master Schedule”)
Me!SR_SECTION_NUM.SetFocus
Cancel = True
DoCmd.SetWarnings (warningson)
Exit Sub
End If

Reply | Quote

Please post the entire procedure, from Sub … to End Sub.

Reply | Quote

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!Checkchange = True
Me!txtuserid = Forms!frmdept!Text11
Me!txtuseriddate = Now()
If Me!holdadd = True And Me!Check83 = False Then
Me!Check83 = True
End If
DoCmd.SetWarnings (warningsoff)
If IsNull(Me!Combo88) Or Me!Combo88 = ” ” Then
Call MsgBox(“Course Number is required”, vbInformation, “Master Schedule”)
Me!Combo88.SetFocus
Cancel = True
DoCmd.SetWarnings (warningson)
Exit Sub
End If
If IsNull(Me!SR_SECTION_NUM) Or Me!SR_SECTION_NUM = 0 Then
Call MsgBox(“Section Number is required”, vbInformation, “Master Schedule”)
Me!SR_SECTION_NUM.SetFocus
Cancel = True
DoCmd.SetWarnings (warningson)
Exit Sub
End If

Me!SEQ = Nz(DMax(“[seq]”, “extractdata”, “[sr_course_code] = ‘” & Me.Combo88 & “‘ and sr_section_num = ‘” & Me!SR_SECTION_NUM & “‘”), 0)
End Sub

Reply | Quote

Try moving the two sections with If … End If up to the beginning of the procedure. There’s no sense in setting things if you’re going to cancel anyway.

Unless you have defined constants or variables named warningsoff and warningson, the DoCmd.SetWarnings instructions will not have the effect you intend. To turn off warnings, use

DoCmd.SetWarnings False

and to turn them on again, use

DoCmd.SetWarnings True

You should not need these lines here.

Reply | Quote

I moved it around and even tried setting the warnings off inside the if, but I still got the “domenuitem” message after my message box. I started wondering if it had something to do with the docmd in the sub for the save button on the form. When I looked at that code the error handling had a msg box. When I commented that out it worked fine.

Private Sub Command80_Click()
On Error GoTo Err_Command80_Click

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings True

Exit_Command80_Click:
Exit Sub

Err_Command80_Click:
‘MsgBox Err.Description
Resume Exit_Command80_Click

End Sub

Thanks for your help!! I’m the only one here using Access and without this bulletin board I would have no resources to go to. Thanks for responding so fast.

Reply | Quote

Try this:

Private Sub Command80_Click()
On Error GoTo Err_Command80_Click
RunCommand acCmdSaveRecord
Exit Sub

Err_Command80_Click:
If Err = 2501 Then
‘ canceled – ignore
Else
MsgBox Err.Description
Else If
End Sub

If an action is canceled, it usually causes error number 2501. I just made the error handler ignore that error.

Reply | Quote

I changed the code in this sub as you suggested and it works great. Thanks again.

Reply | Quote