Bonus Question

  • Thread starter Thread starter Dan M
  • Start date Start date
D

Dan M

This one is a doozy. Nothing like bound forms, data
validations and errors.
Access XP adp file with SQL Server 2K back end.

.. I have a bound form with bound textboxes, 1 bound combo
box, and 2 unbound listboxes (providing the user with
helpful info).
.. Form recordsource is simply the table (not a SQL
statement or stored procedure).
.. Adding a new record.
.. First textbox accepts my entry fine.
.. Combo box has LimitToList set to true.
.. Also has NotInList event that catches user's new entry
and asks if they want it added.
.. If they say Yes, code uses ADO Command object to run an
insert SQL statement, putting the entry into the table.
.. If they say No, they are told to only pick an item from
the list.
.. Regardless of user's answer, if they delete their entry
in the combo box, then attempt to leave the combo box to
fill in other fields or use a listbox on the form to
gather more info first, the form's OnError event fires
with DataErr 3162 "Attempted to assign a Null value to a
variable that is not of Variant datatype."
.. If they DON'T delete their entry and try to move on, the
NotInList event fires again (of course).
.. Record has not been saved at this point.
.. The combo box will not release the focus.
.. If I use Response = acDataErrContinue, the focus is
locked on the combo box until they select from the list or
add an entry (unacceptable).
.. Without Response = acDataErrContinue, the provider's
error message is displayed (assign a null), which can be
confusing to the user.
.. Would like to avoid the form's OnError firing altogether.
.. Don't want to remove table/field based
definitions/validations (the field bound to the combo box
is non-nullable).
.. Don't want to have to manually verify everything entered
(that's what tables are for).
.. Don't want the trouble of an unbound form and having to
code all verifications.
.. Just want the combo box to allow user to begin their
entry, delete their partial entry, utilize other parts of
the form, then return to complete their entry.

Ice cream and cookies to the first with the right answer.
Thanks.
 
If the field is not allowed to be null, then of course you have effectively
made the field a required field. However, lets (actually you) will have to
deal with that issue latter.


The first thing in you long explain is that you don't mention if your code
works ok when in fact the user does add a new value. Does that work?

Here is some points/help:

Of course, if the you DO in fact add a new entry to the table that the comb
box is based on, then you have to tell ms-access to re-load the combo, and
re-set the not in list error you are currently in. Well, since the not
inlist is in fact designed to do this..then you can tell ms-access about all
this stuff by simply setting

Response = acDataErrAdded

The above will automatically re-query and re-load the combo box. This means
that whatever the user entered will be accepted (assuming your ado code
added the new entry).

So, does the above code work? In other words, can your users freely add a
new entry..and then simply tab on tot the next control on the screen? The
above use of acDataErrAdded should fix this. (and, lets get one thing
working at a time!).

If the user decides to NOT add the entry, , and hits "no" to your prompt,
then you set response = acDataErrContinue. This will suppress the built in
error message. At this point, they should be RIGHT BACK to the combo box
with their un-allowed entry. At this point they can then erase the current
value, and tab out of the field, or simply edit/modify the current text in
the combo and try again.

So, you need to tell the not in list event what YOU want to happen. If you
don't want a error message, then use acDataErrContinue, if you actually did
add a new value, then again you need to tell ms-access that you did so via
acDataErrAdded (and, ms-access will thoughtfully re-load, re-query the combo
box, and let you return to the combo box with the newly added entry)

If you are using a2003, the help explains the above perfeclity. The help for
2000, and 2002 missed out the above inportant sutff!

Try the above....
 
Thanks for the info.

Yes, I do have acDataErrAdded and it works fine if the
user enters a new value.

You're right, when the user clicks NO, they are right back
at the combo box. I must reiterate, however, that if they
erase their entry and tab out of the combo box, they
can't. This is where my problem is. The form's OnError
event is triggered at this point.

If you have further ideas, excellent. However, I'm simply
going to add some instruction to my msgbox instructing the
user to finish their entry in the combo box before moving
on, or allow them to choose to undo. I found that the
undo method allows the form to see the combo box as if it
never had an entry.

Thanks again.
 
Back
Top