Ensuring a Field is Populated

[Joe Coulter]

Please help, I hope this is not too long winded, but I thought that as much
background as possible to clarify what I have.

I have a field "Update_User_ID" on a form "Frm_Update_Receipt" which is used
to validate the Username, this looks up a list of Users "User_ID" in a Table
"Tbl_Users"
I have this Code on the BeforeEvent of the "Update_User_ID" field.

Private Sub Update_User_ID_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("Update_User_ID", "Tbl_Users", "User_ID ='" &
Me!Update_User_ID & "'")) Then
MsgBox Me!Update_User_ID & " IS AN INVALID USER ID."
Me!Update_User_ID.Undo
Cancel = True
Else:
Me!Date_Sup_Manuf.Visible = True
Me!Btn_Cancel_001.Visible = False
Me!Btn_CANCEL.Visible = True
End If
End Sub

This works great and ensures that only user ids held in the table gains
access to the form and if accepted, the user id is captured.

However, if the User presses the Enter Key when the field is blank, the
focus moves to the only other visible control at that moment
"Btn_Cancel_001", I want to restrict the user from leaving the
"Update_User_ID" Field unless he/she inputs an accepted ID or presses the
Cancel_001 button.

I have tried putting code on the AfterEvent to check for null and return the
focus to the field, this seems to be ignored.

Any help would be greatly appreciated.

Thanks in anticipation

Joe

 

[Allen Browne]

Joe, if you want to guarantee that the record cannot be saved unless a field
is filled in, you cannot use the events of the *control.* If the user never
visits that control (e.g. if they click in the other control and then go to
a new record), its events will not fire, and so the field can be saved
blank.

The simplest solution is to:
1. Open your table in design view.
2. Select the field that must be filled in.
3. In the lower pane of table design, set the Required property to Yes.
Now Access won't let the record be saved if the field is left blank.

If you want to do it with code, use the BeforeUpdate event of the *form*
instead of the control's events.

 

[Joe Coulter]

Thanks for your very prompt reply,

I cannot make this field required as you suggest, as when the record is
created this field would require population but is empty at this stage, this
field is only populated later on in the process when the record is updated.

O, I also failed to mention, when the form opens, the only visible field is
the "Update_User_Id" and the Command Button "Btn_Cancel_001", only when the
Update_User_ID field is populated do other fields become visible.

Dont know if this makes a difference, but hey,

Thanks for your reply anyhow.

 

[Joe Coulter]

Allen

Just in case someone else requires a similar fix to mine, this is how I got
round the problem.

When the user pressess the Enter key in the Null Update_User_ID field the
focus is moved to the Cancel Button which offers a Yes or No option, When No
is selected The focus now goes to the Update_User_ID, therefore causing a
loop back to the Field until populated with an accepted ID, when the user
selects Yes on the Cancel Option, the form is closed.

Thanks for you help again, it inspired me to find another fix.

Joe