Not in List problem

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I have a form for Production Procedures, and a subform for
the parts that are covered by the procedure. The subform
works by selecting a part number from a combo box. The
Row Source for the combo box is a table of all parts
(actually, a query based on the table). The combo box
does auto-complete or whatever exactly they call it in
Access, so that typing "8" jumps to the first part number
starting with "8", "89" jumpts to the first part number
starting with "89", etc.
Sometimes the user needs to add a part, in which case
there should be a command button to allow this. At other
times the user starts to enter a part number in the combo
box, and discovers it is not among those listed. In that
case there needs to be something inherent to the combo box
that announces there is no such part number, and provides
a way of adding one.
One suggestion I have received is to have the combo box
Not In List event generate a message box directing the
user to double click the combo box. The Double Click
event of the combo box opens the form for entering a new
part number, clears whatever was previously entered in the
combo box (i.e. the nonexistent part number), and
requeries the combo box so that the new Part Number shows
up right away.
So I get the message box to generate the message, and the
combo box's double click event is:

Me![cboPartNumber] = Null
DoCmd.OpenForm "FrmPart", , , , , acDialog
Me![cboPartNumber].Requery

I also tried it with "" instead of Null. In both case I
received notification of error 3162, and this gem: "You
tried to assign the null value to a variable that is not a
variant data type." Eliminating the part about Null leads
to an error at Requery, which I apparently can't do
without saving the record, although it works from the
command button.
Here's what I would like to do: The code works fine when
I attach it to a command button for opening the form to
add a part number, except that the code doesn't have that
line about Null or "". What I would really like is to use
the same command button from the Not In List event. I had
thought that the message box could direct the user to the
command button, or even open a form directly. The idea is
that entering a new part number should be accomplished in
the same way at all times.
To sum up, when a part is not on the combo box list in the
subform, I would like notification of that, and an easy
and consistent way of adding a new part number using the
part number form.
 
One suggestion I have received is to have the combo box
Not In List event generate a message box directing the
user to double click the combo box

The above is not such a good idea. Your suggestion/idea that if the entry is
NOT in the list, then why not at this point ask the user:

Do you want to add this "customer" to the list?

I don't telling a use to go off and double click on something when the
computer at this point can do this for you!

The code to do what you want looks like:


If MsgBox("Add new group type?", vbOKCancel) = vbOK Then

DoCmd.OpenForm "frmAddGroupType", acNormal, , , acFormAdd, acDialog

Response = acDataErrAdded

End If


The above is ALL you need to accomplish what you want. Note that settig the
respnse to

acDataErrAdded


The above tells ms-access:

That you added a new entry to the combo list
Tells ms-access to re-load, and re-query the combo list for you
After re-loading the combo, the combo is set to the value that caused
the no in list (very nice!)
Allows you to continue to the next field with no additional nag messages
and prompts

So, that one setting of acDataErrAdded does a whole bunch of huge work for
you. Just use it..and you will be home free.
 
That is SO cool! It is exactly what I needed. Thank you
very much. I really try hard to avoid redundant
clicking. Message boxes should direct the user to
something productive. This one does that, and without a
lot of elaborate coding.
A follow-up question:
I am actually adding a part number, so the combo box is
cboPartNumber. Next to it is txtPartDescription, with the
Control Source set to =[cboPartNumber].Column(2). The row
source for the combo box is a query based on tblPart. The
second column only, or Column(1) in AccessSpeak, is
visible in the combo box. I have set the combo box to
autoexpand, but the effect of this on the Part Description
text box is to add the first possibility and leave it
until leaving the combo box. If Part# 900 is a bolt and
911 is a washer, typing "9" in the combo box sets the text
box to "bolt", no matter what other numbers are added in
the combo box. I would like to leave the text box blank
until the combo box selection is complete.
 
Hum, I don't have a good solution for your problem.

(and, your attention to small details like not wanting display the
description text until done is rather nice idea!).

I suppose you could try something like turning the visibility of the control
off when the combo gets the focus, and turning it on when it looses the
focus. (you might have to try that....but I not too keen on how well this
will work either...).
 
I believe that more applications fail because of the user
interface than for any other reason. When it is difficult
to use a program, at least some users will use it
incorrectly. The people using this database have other
jobs to do in exacting production processes. My job is to
manage information. The database is supposed to make
their jobs easier. It will only make my job easier if
they are able to use it correctly. The fewer things to
confuse the users, the more likely the database will do
what it needs to do, and everybody's job will be easier.
Also, and maybe more to the point, it annoys me when
unwanted results show up.
Enough philosophy. To sum up a point made earlier, the
place I want to hide the text is in the active record in a
junction table. The relevant tables are tblProcessPlan,
tblPart, and tblPartListing. tblPartListing is a junction
table containing PKs from the other two tables, enabling
many-to-many between them. Forms are named similarly to
tables: the main form is frmProcessPlan, and the subform
is fsubPartListing.
After entering the Process Plan information (Document
Number, Revision, Process, etc.) I enter the relevant part
numbers in the subform (continuous format). I attempted
several variants of conditional formatting, borrowing
somewhat from Lebans procedure for highlighting the
current record. The conditional formatting is to make the
font color the same as the back color. I have tried
various things (referring to the key field, referring to a
combo box containing the key field, referring to an
unbound combo box into which the key field is copies at
Current, etc.) in the combo box's On Got Focus event (I
have tried the form's On Current event and a few other
things also) to make the conditional formatting apply only
to the current record, but no matter what I have done so
far the formatting applies to all records in the subform.
I feel like I am on the right track, but I can't figure
out how to make it work. I need to turn my attention to
other things for now, but I would really like to get this
to work.
 
Back
Top