Required field added to existing records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robin - 07 May 2007 19:08 GMT

Hello,

I have a existing table with data already created. I need to add a field to
this table that is going to be required from now on. When I open the form to
update the record it allows me to add the information to the required field.
But when I tested the record to see if it would flag me on the required field
by closing it, it closes without flaging me to enter the information in the
required field. Can I update existing records on a form for a newly added
required field and have it flag me if the data is not enter on a form.

Thanks,

Robin

Reply to this Message

Chris Reveille - 07 May 2007 20:40 GMT

You need to add the field to the table first.
Open table in design view
Add new field and set required property to "Yes"
save and close table

Open form in design view
Add new filed from table
save form

> Hello,
>
[quoted text clipped - 9 lines]
>
> Robin

Reply to this Message

Robin - 09 May 2007 17:02 GMT

Hell Chris,

The field was originally added to the table and set up as a required field.
I then added the field to the forms but when I open the form with the
existing data that comes up the newly opened field does not flag me if there
is no data. Is it because of the existing data? When I add a new record the
field works fine. It flags me if there is no data. I am working with a
table not a query. I have also added code to the forms field properties
under the "Before update" which reads as:

Private Sub Verification_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Verification) Then
MsgBox "You must add data here"
Me.Verification.SetFocus
Cancel = True
End If
End Sub

What else can I do to control this field so that it will flag the user to
put in information before closing. The problem is where the table already
has existing data. If adding new records it works fine, but not with old
data.

Thanks,

Robin

> You need to add the field to the table first.
> Open table in design view
[quoted text clipped - 18 lines]
> >
> > Robin

Reply to this Message

Douglas J. Steele - 09 May 2007 17:11 GMT

The BeforeUpdate event only fires before Access is about to update a record.

Sounds as though you also need code in the form's Current event. along the
lines of:

If Me.NewRecord = False Then
If IsNull(Me.Verification) Then
MsgBox "You must add data here"
Me.Verification.SetFocus
End If
End If

The reason for checking the form's NewRecord property is to ensure that it
doesn't fire when you move to a new record.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hell Chris,
>
[quoted text clipped - 55 lines]
>> >
>> > Robin

Reply to this Message

Robin - 09 May 2007 19:29 GMT

Hello;

I added the code that you suggested to the "On Current" properties of the
form. Which is:

Private Sub Form_Current()
If Me.NewRecord = False Then
If IsNull(Me.Verification) Then
MsgBox "You must add data here"
Me.Verification.SetFocus
End If
End If
End Sub

I am getting a compile error msg (461). I was hoping that you could guide
in on what to do.

Here is the msg that I receive when I open the form:

Method or data member not found : compile error (461)

Thanks,

Robin

> The BeforeUpdate event only fires before Access is about to update a record.
>
[quoted text clipped - 70 lines]
> >> >
> >> > Robin

Reply to this Message

Douglas J. Steele - 09 May 2007 19:57 GMT

See whether using Me!Verification instead of Me.Verification makes a
difference.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hello;
>
[quoted text clipped - 108 lines]
>> >> >
>> >> > Robin

Reply to this Message

Robin - 09 May 2007 20:44 GMT

I am still not have any success with the change. Any other suggestions?

Thanks,

Robin

> See whether using Me!Verification instead of Me.Verification makes a
> difference.
[quoted text clipped - 111 lines]
> >> >> >
> >> >> > Robin

Reply to this Message

Douglas J. Steele - 09 May 2007 22:19 GMT

On which line is the error happening?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I am still not have any success with the change. Any other suggestions?
>
[quoted text clipped - 129 lines]
>> >> >> >
>> >> >> > Robin

Reply to this Message

Robin - 11 May 2007 13:51 GMT

Hello,

Here is the error message that I receive when I open the form.

The expression on current you entered as the event property setting produced
the following error: Procedure declaration does not match description of
event or procedure having the same name.

Code used on the Before update:

Private Sub Form_BeforeUpdate()
If IsNull(Me.Verification) Then

MsgBox "You must add data here"

Me.Verification.SetFocus

Cancel = True

End If
End Sub
*********************************************
Code used on the On current:

Private Sub Form_Current()
If Me.NewRecord = False Then
If IsNull(Me.Verification) Then
MsgBox "You must add data here"
Me!Verification.SetFocus
End If
End If
End Sub

Thanks,

Robin

> On which line is the error happening?
>
[quoted text clipped - 131 lines]
> >> >> >> >
> >> >> >> > Robin

Reply to this Message

Douglas J. Steele - 12 May 2007 12:51 GMT

The declaration for Form_BeforeUpdate is incorrect.

It's supposed to be

Private Sub Form_BeforeUpdate(Cancel As Integer)

I suspect you must have typed that yourself, as opposed to letting Access
creating the declaration (and End Sub) for you.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello,
>
[quoted text clipped - 179 lines]
>> >> >> >> >
>> >> >> >> > Robin

Reply to this Message

Robin - 14 May 2007 20:59 GMT

Hello,

I copied the the Before Update from another user on this forum. I am glad
you caught this mistake. I made the change below that you suggested to the
berupdate and now when I open the form, it opens with "you must add data
here" and the error message reads "Run-time error '438': Object doesn't
support this property or method" I then click the Debug option and it
highlights "Me!Verification.SetFocus" on the OnCurrent section. Any
suggetions??

Thanks,

Robin

> The declaration for Form_BeforeUpdate is incorrect.
>
[quoted text clipped - 188 lines]
> >> >> >> >> >
> >> >> >> >> > Robin

Reply to this Message

Douglas J. Steele - 14 May 2007 21:54 GMT

Assuming your text box and recordset field are both named Verification,
rename the text box to, say, txtVerification, and then change that to
Me!txtVerification.SetFocus

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello,
>
[quoted text clipped - 220 lines]
>> >> >> >> >> >
>> >> >> >> >> > Robin

Reply to this Message

grep - 07 May 2007 21:17 GMT

Robin,

I don't think you can flag the new field as "required" while there are
existing records in the table that don't have it filled. I don't
remember, to be honest, but I seem to recall that.

So what you might want to do is run an update query to add *something*
to the field on all existing records, and then try it.

The other thing you might consider not using the Required setting on the
field, and instead write some code on the form that checks for a value
in that field before updating the record, and doesn't allow you to move
on if it's empty.

grep

> Hello,
>
[quoted text clipped - 9 lines]
>
> Robin

Reply to this Message