Required field added to existing records

[Guest]

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

 

[Guest]

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

 

[grep]

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

 

[Guest]

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

 

[Douglas J. Steele]

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.

 

[Guest]

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

 

[Douglas J. Steele]

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

 

[Guest]

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

Thanks,

Robin

 

[Douglas J. Steele]

On which line is the error happening?

 

[Guest]

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

 

[Douglas J. Steele]

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.

 

[Guest]

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

 

[Douglas J. Steele]

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