Help with controling data entry

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

Need help on couple of issues:

1) I have a form that has a sub form. The subform has one field in it. I
want to make sure that the user can not move to another record nor can he
close the main form without entering something in the subform field.

2) One of the fields on the main form is a LastName. This field keeps
getting messed up by the user who pushes the key board by mistake or leans on
it and as a result, sometimes the last name is over written with numbers or
any other gibberish. How can I protect against that without slowing down the
legitimate data entry process in that field.
I am using Access 97:(
Thanks
 
In the Before Insert event of the main form, here is how you can check for
the value in the subform field:

If IsNull(Me.SubFormControlName.Form.SubFormFieldName) Then
MsgBox "A Value is Required"
Cancel = True
End If

Note that SubFormControlName referes to the subform control on you main
form, not necessarily the name of the form being used as a subform.

As to the value in the last name control, that is unusual, but the only
thing I can think of would be to use the Before Update event of the form to
check the currrent value of the control with the old value and warn the user
if they really want to change it:

If Me.txtLastName <> Me.txtLastName.OldValue Then
If MsgBox("Do You Want to Change the Last Name",vbQuestion + _
vbYesNo) = vbNo Then
Cancel = True
End If
End If

Or, in the future, hire only flat chested data entry people :)
 
Need help on couple of issues:

1) I have a form that has a sub form. The subform has one field in it. I
want to make sure that the user can not move to another record nor can he
close the main form without entering something in the subform field.
Click to expand...

That's difficult. The main form record is saved the moment you set focus to
the subform - so it MUST be possible to save a record on the mainform even
though there is no record in the subform. You can use code in the mainform's
Close event to look at an Unmatched Values query, to see if there are any
mainform records without a corresponding subform record.
2) One of the fields on the main form is a LastName. This field keeps
getting messed up by the user who pushes the key board by mistake or leans on
it and as a result, sometimes the last name is over written with numbers or
any other gibberish. How can I protect against that without slowing down the
legitimate data entry process in that field.
Click to expand...

I can't imagine how to do that. You want the user to be able to type in the
field... but you want the user to not be able to type in the field. Access
can't intuit whether the user is legitimately typing or hitting the keyboard
with an elbow - the keystrokes look the same!!

I suppose you could have that textbox (and all other textboxes...?) with its
Enabled property set to False. Put a command button on the form to enable it
so the user must click the button prior to being allowed to type a LastName;
disable the textbox in its own AfterUpdate event to lock it up again. But...
train your users!!!

John W. Vinson [MVP]
 
Thank you Klatuu,
I made the following change because the code did not fire when the last name
was deleted all together:
If Me.LastName <> Me.LastName.OldValue Or IsNull(Me.LastName) Then
If MsgBox("Do You Want to Change the Last Name", vbQuestion + _
vbYesNo + vbDefaultButton2) = vbNo Then
Me.LastName.Undo
Cancel = True
End If
End If
As far as the other code, I placed that in the "on Unload" event of the main
form so that they can not close without making the entry. However, is there a
way to fire the code when they are going from one record to another? because
that is the only way they could get out without making the entry.

and by the way the users do better job then the flat chested counter parts :)
Al
 
Please see my response to Klatuu

John W. Vinson said:
That's difficult. The main form record is saved the moment you set focus to
the subform - so it MUST be possible to save a record on the mainform even
though there is no record in the subform. You can use code in the mainform's
Close event to look at an Unmatched Values query, to see if there are any
mainform records without a corresponding subform record.


I can't imagine how to do that. You want the user to be able to type in the
field... but you want the user to not be able to type in the field. Access
can't intuit whether the user is legitimately typing or hitting the keyboard
with an elbow - the keystrokes look the same!!

I suppose you could have that textbox (and all other textboxes...?) with its
Enabled property set to False. Put a command button on the form to enable it
so the user must click the button prior to being allowed to type a LastName;
disable the textbox in its own AfterUpdate event to lock it up again. But...
train your users!!!

John W. Vinson [MVP]
Click to expand...
 
Not a big deal, but the following might be simpler:

If Nz(Me.LastName, vbNullString) <> Nz(Me.LastName.OldValue, vbNullString)
Then
If MsgBox("Do You Want to Change the Last Name", vbQuestion + _
vbYesNo + vbDefaultButton2) = vbNo Then
Me.LastName.Undo
Cancel = True
End If
End If
 
Thanks Douglas

Douglas J. Steele said:
Not a big deal, but the following might be simpler:

If Nz(Me.LastName, vbNullString) <> Nz(Me.LastName.OldValue, vbNullString)
Then
If MsgBox("Do You Want to Change the Last Name", vbQuestion + _
vbYesNo + vbDefaultButton2) = vbNo Then
Me.LastName.Undo
Cancel = True
End If
End If
Click to expand...
 
Back
Top