Securing data in a form field

[ssignore]

Hello, Community.
I've created a data entry form and have set all fields except two as
'required'. However, I've noticed that when I click 'previous record' or
'next record', I can go into these fields and delete the data without a
problem. Is there a macro that I can write to warn a User that the required
field is blank? I want them to be able to edit, but not leave the field
blank. [Oh, the added challenge - I don't speak VB!]
Many thanks!
S

 

[John W. Vinson]

Hello, Community.
I've created a data entry form and have set all fields except two as
'required'. However, I've noticed that when I click 'previous record' or
'next record', I can go into these fields and delete the data without a
problem. Is there a macro that I can write to warn a User that the required
field is blank? I want them to be able to edit, but not leave the field
blank. [Oh, the added challenge - I don't speak VB!]
Many thanks!
S

Use the Form's BeforeUpdate event. I don't know if this can be done with
Macros, which are pretty limited, so I'll suggest that you dip your toes into
the VBA pool. You'll need code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!SomeField & "" = "" Then
MsgBox "SomeField is required, please do not leave blank", vbOKOnly
Cancel = True
End If
End Sub

This checks to see if SomeField (the name of a control on your form) contains
a blank or NULL value; if so it gives the user a warning message and cancels
the update.

If you'ld like help adapting this to your case post the names of the relevant
controls.

 

[ssignore]

Thank you, John. I appreciate the response.
I can give this a shot.
Would the first step be to open the form in Design View and then toggle into
Visual Basic?
Some of the relevant control names are:
Last Name
First Name
Case Number
Case Location Code
SSN
Thanks,
ssignore

Hello, Community.
I've created a data entry form and have set all fields except two as
'required'. However, I've noticed that when I click 'previous record' or
'next record', I can go into these fields and delete the data without a
problem. Is there a macro that I can write to warn a User that the required
field is blank? I want them to be able to edit, but not leave the field
blank. [Oh, the added challenge - I don't speak VB!]
Many thanks!
S

Use the Form's BeforeUpdate event. I don't know if this can be done with
Macros, which are pretty limited, so I'll suggest that you dip your toes into
the VBA pool. You'll need code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!SomeField & "" = "" Then
MsgBox "SomeField is required, please do not leave blank", vbOKOnly
Cancel = True
End If
End Sub

This checks to see if SomeField (the name of a control on your form) contains
a blank or NULL value; if so it gives the user a warning message and cancels
the update.

If you'ld like help adapting this to your case post the names of the relevant
controls.

 

[John W. Vinson]

Thank you, John. I appreciate the response.
I can give this a shot.
Would the first step be to open the form in Design View and then toggle into
Visual Basic?
Some of the relevant control names are:
Last Name
First Name
Case Number
Case Location Code
SSN
Thanks,
ssignore

Actually you would open the form in design view; view its Properties; find the
"Before update" line on the Events tab; and click the ... icon by that line.
Choose the "Code Builder" option. Access will give you a Sub and an End Sub
line for free. Edit it to something like

Private Sub Form_BeforeUpdate(Cancel as Integer) << given automatically
If Me![Last Name] & "" = "" Then
MsgBox "Please fill in last name"
Cancel = True
Exit Sub
End If
If Me![First Name] & "" = "" Then
MsgBox "Please fill in first name"
Cancel = True
Exit Sub
End If
<and so on, just repeat for each control>
End Sub << also given automatically

 

[ssignore]

Ahh. I used "Last_Name" and got a syntax error probably because I didn't
bracket (i.e., [Last Name]).
Appreciate the script. Will give it a whirl and tell you how I fare.
Many thanks, John.
Simone

Thank you, John. I appreciate the response.
I can give this a shot.
Would the first step be to open the form in Design View and then toggle into
Visual Basic?
Some of the relevant control names are:
Last Name
First Name
Case Number
Case Location Code
SSN
Thanks,
ssignore

Actually you would open the form in design view; view its Properties; find the
"Before update" line on the Events tab; and click the ... icon by that line.
Choose the "Code Builder" option. Access will give you a Sub and an End Sub
line for free. Edit it to something like

Private Sub Form_BeforeUpdate(Cancel as Integer) << given automatically
If Me![Last Name] & "" = "" Then
MsgBox "Please fill in last name"
Cancel = True
Exit Sub
End If
If Me![First Name] & "" = "" Then
MsgBox "Please fill in first name"
Cancel = True
Exit Sub
End If
<and so on, just repeat for each control>
End Sub << also given automatically

 

[John W. Vinson]

Ahh. I used "Last_Name" and got a syntax error probably because I didn't
bracket (i.e., [Last Name]).
Appreciate the script. Will give it a whirl and tell you how I fare.
Many thanks, John.

That's one of several reasons not to use blanks or other special characters in
field or controlnames.

 

[ssignore]

I hear you - I'll remember for next time.

I must be missing some code because with the script you generously
recommended, I can't add a new record. I'm just 'stuck' in the record. When
I hit 'escape' it will, of course, not save any of the data I put in. After
hitting 'escape' I can move again AND delete items, however, the original
data will be restored (that's one good thing.)
I'll keep trying to debug.
Appreciate all the guidance!
Regards,
Simone

Ahh. I used "Last_Name" and got a syntax error probably because I didn't
bracket (i.e., [Last Name]).
Appreciate the script. Will give it a whirl and tell you how I fare.
Many thanks, John.

That's one of several reasons not to use blanks or other special characters in
field or controlnames.

 

[John W. Vinson]

I hear you - I'll remember for next time.

I must be missing some code because with the script you generously
recommended, I can't add a new record. I'm just 'stuck' in the record. When
I hit 'escape' it will, of course, not save any of the data I put in. After
hitting 'escape' I can move again AND delete items, however, the original
data will be restored (that's one good thing.)
I'll keep trying to debug.
Appreciate all the guidance!

Please post your actual code. Is there SOME pathway through the code that
doesn't hit a Cancel - e.g. if all the required fields are filled in, none of
the IF statements should be true, and the line Cancel = True should never be
executed.

 

[ssignore]

John,
I was missing an "Exit If" - pretty sure this is working now, thanks to you!
Here's the code if you'd like to give it a once-over.
Again, much appreciated!
Simone

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me![Last Name] & "" = "" Then
MsgBox "Please enter Last Name"
Cancel = True
Exit Sub
End If
If Me![First Name] & "" = "" Then
MsgBox "Please enter First Name"
Cancel = True
Exit Sub
End If
If Me![Case Number] & "" = "" Then
MsgBox "Please enter Case Number"
Cancel = True
Exit Sub
End If
If Me![Case Year-Two Digit] & "" = "" Then
MsgBox "Please enter two-digit Case Year"
Cancel = True
Exit Sub
End If
If Me![Social Security Number] & "" = "" Then
MsgBox "Please enter Social Security Number"
Cancel = True
Exit Sub
End If
If Me![Case Location Code] & "" = "" Then
MsgBox "Please enter Case Location Code"
Cancel = True
Exit Sub
End If
If Me![List Title] & "" = "" Then
MsgBox "Please enter List Title"
Cancel = True
Exit Sub
End If

End Sub

 

[John W. Vinson]

I was missing an "Exit If" - pretty sure this is working now, thanks to you!
Here's the code if you'd like to give it a once-over.

odd, since there's no Exit If in the code you posted.

This is rather crude code (it would be more elegant to have a loop through the
Controls collection, with perhaps a Tag value to identify required controls)
but it's a good start.

The annoyance for the user is that if they leave multiple controls blank,
they'll get a new prompt every time they try to save the record.

One thing I'll often do is set the backcolor property of required controls to
yellow, and perhaps put a label on the form indicating that yellow controls
must be filled in.

 

[ssignore]

Odd because I believe I meant "End If", not "Exit If".
Of course it's crude - I don't know how to speak VB!
We don't need elegance - crude is fine for our purposes. And we do want the
Users to be annoyed so that they avoid skipping the completion of required
fields. If they do what they are supposed to do, then the number of annoying
messages will be cut down considerably and they'll learn that fairly quickly.
Thanks for all the help.
Regards,
Simone