redesign tables. Redesign Forms?

G

Guest

In my unending wisdom, I designed a table in my database to not force some
fields to be required. <idiot!> Who would think that when you are counting
a student absent, you would not include the student's name. <sigh>

So I had to go back and make the student ID field required to force the
teachers to include it. Am I going to have to go back and put in new field
boxes on each of the forms that uses the field with the requirement change or
will access make that change for me. I use access 03 with the 2000 back end.
 
D

Dirk Goldgar

Ripper said:
In my unending wisdom, I designed a table in my database to not force
some fields to be required. <idiot!> Who would think that when you
are counting a student absent, you would not include the student's
name. <sigh>

So I had to go back and make the student ID field required to force
the teachers to include it. Am I going to have to go back and put in
new field boxes on each of the forms that uses the field with the
requirement change or will access make that change for me. I use
access 03 with the 2000 back end.

No, you won't have to do anything to the form's, unless you want to trap
and replace the default error message. The Required property is
enforced at the level of the database engine, so users won't be able to
add any record, by any means, that doesn't have a value for the required
field.

Just make sure that the field is also set to not allow zero-length
strings, since that's not quite the same as a Null value for the field.
 
G

Guest

Ok, I do want to put in custom error messages. I re-designed all the forms
to get rid of the do.cmd(close) error. I was having trouble with the error
messages not being displayed using the close button I created. I now use the
X in the corner to close all my forms.

Let's say one form has 3 fields, TeacherName, cboStudentName (combo box used
to select the name of the student but really only keeps the student ID
number, and period.

Would I code the form on the beforeUpdate event using something like this:
If IsNull(Me!cbostudentname) Then
If MsgBox("Student Name must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to close the record WITHOUT saving.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close
Else
Me!cobstudentname.SetFocus
End If
Else
If IsNull(Me!combo12) Then
If MsgBox("Teacher Name must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to close the record WITHOUT saving.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close
Else
Me!combo12.SetFocus
End If
 
D

Dirk Goldgar

Ripper said:
Ok, I do want to put in custom error messages. I re-designed all the
forms to get rid of the do.cmd(close) error. I was having trouble
with the error messages not being displayed using the close button I
created. I now use the X in the corner to close all my forms.

Let's say one form has 3 fields, TeacherName, cboStudentName (combo
box used to select the name of the student but really only keeps the
student ID number, and period.

Would I code the form on the beforeUpdate event using something like
this: If IsNull(Me!cbostudentname) Then
If MsgBox("Student Name must contain a value." & Chr(13) & Chr(10)
& _ "Press 'OK' to return and enter a value." & Chr(13) & Chr(10) &
_ "Press 'Cancel' to close the record WITHOUT saving.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close
Else
Me!cobstudentname.SetFocus
End If
Else
If IsNull(Me!combo12) Then
If MsgBox("Teacher Name must contain a value." & Chr(13) & Chr(10)
& _ "Press 'OK' to return and enter a value." & Chr(13) & Chr(10) &
_ "Press 'Cancel' to close the record WITHOUT saving.", _
vbOKCancel, "A Required field is Null") = vbCancel Then
DoCmd.Close
Else
Me!combo12.SetFocus
End If

That's close to functional, but you're going to have to cancel the
update and undo the form before you can close it. It would be something
like this (in the BeforeUpdate event:

If IsNull(Me!SomeControl) Then
If MsgBox(....) = vbCancel Then
Cancel = True
Me.Undo
DoCmd.Close acForm, Me.Name, acSaveNo
Exit Sub
Else
Cancel = True
Exit Sub
End If
End If

I haven't tested that, so I'm not sure it's quite right. Also, I added
the Exit Sub statements so that the code wouldn't go on to test other
controls after this one. When I do this kind of editing, I usually
check all controls and compile a list of required fields, and only then
display a message and set the focus to the first bad control in the
list.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top