REQUIRED FIELD NOT WORKING

[MPH03]

I have a subform in a form that has two fields one of which is set to required "yes" if I do not fill in the field and hit "new record" it tells me that the field is required.... "The Field (field name)cannot contain a Null value because the Required property for htis field is set to True. Enter a value in this field" I then push ok but it does not let me enter a value but the cursor is now located on the field prior to the required field in the form not the subform.....it keeps giving me that same message over and over again.... any help would be GREATLY appreciated !!!
Sincerely,
MPH03

 

[willir]

MPH03

Three things are happening when you try to enter the record...

- Access tries to create the record. This means any autonumber field is set.
- Data integrity rules are applied which results in an unsuccessful entry
- Access cancels the update which results in Access referencing the previous valid entry.

To prevent all this, you need to create some "smarts" in the form. The "smarts" are applied by using the "events" for either the entire form or field. You have to be in design mode and have the properties window open.

Rather than using the Before Update event for the form, I would tend to use the After Update event for the field. Place the logic near the field in question, and make sure the user has a graceful way to exit (meaning they don't have the required information such as a customer number for a customer does not exist).

Richard

 

[MPH03]

Thanks !!! one quick question tho....what is my event procedure ? withing the "after update" properties....I want the cursor to automatically be placed in that required field and it is not, it is just going to last tab stop before new rec...thanks so much for replying !
~MPH03

 

[willir]

MPH03

Quick question - event procedure. Not.

Okay, event procedures are visible as follows.

- Open form in design mode.
- If you do not see a floating window with the tabs -- Format, Data, Events, Other, All visible, then from the main menu, select "View" and "Properties".

Your world just got bigger.

Their are two "modes" for the properties - form level and field level.

For the form level, view the form (in design mode). In the top left corner, at the junction where the horizontal and vertical measurement bars meet, the "box" should be coloured black. Access 2000 and beyond make it easier. The caption for the properties window at the top will display "Form".

Events include
- On Current
- Before Insert
- After Insert
- Before Update
- After Update
- On Load
- On Open
etc

Hint: order of update events are...
Before Insert -> Before Update -> After Update -> After Insert

Now select a field. The junction box is no longer black. For Access 2000 and beyond, the caption for properties will display the field name.

Events will include...
- Before update
- After update
etc
(will change depending on field type.)

Review Access help for more info. It Access gets real interesting at this point.

BTW, glad you responded to the post, here is a possible solution to your problem.

[blue]
Private Sub Form_Current()
Me.AllowAdditions = True
End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Nz(Me.YourField)) Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub


Private Sub YourField_AfterUpdate()
If Len(Nz(Me.YourField)) Then
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
End If
End Sub
[/blue]


Plus add a command button. For button wizard, select form operation and choose refresh. For the title, use "Update". For the name of the button (last step) enter "Update_btn"

Snipets of created will include the following. Add the bolded line.

[blue]
Private Sub Update_btn_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Me.AllowAdditions = True
End Sub[/blue]

This is not a perfect solution, but should help you a bit. "Play" with a backup copy of your form.

 

[MPH03]

I will give it a try.... thanks !!!
~MPH03