NotInList

[Mark Roberts]

I have a "Customers" form and a "Projects" form.
The "Projects" form has a combo box (Company Name) that
contains the list of company names from the "Customers"
table. I would like to be able to add a New Company Name
in the combo box on the "Projects" form.

After a new name is typed and the enter/tab key is
pressed, I would like a message box to open asking if you
want to add the new name. Then when you click the "Yes"
button, I would like an "Add Customer" form to open with
the new name in the Company Name field. That way I can
complete the rest of the new customer data on the form
without having to go back and do it later. When I close
the customer form, I would like the "Projects" form to
reopen, with the new name in it, so I can continue
completing that data. Any support would be appreciated.

 

[Albert D. Kallal]

You can use the follwing:

The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then you
can do the following:

Private Sub Distributee_NotInList(NewData As String, Response As Integer)

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code. However, there
is one thing we should do, and that is that then the frmAddClient loads, we
should put in the NewData value into the correct field so the user does not
have to re-type it. (and it helps the user "see" things a lot better". So,in
our forms on-load event, we will take the value of NewData, and set the
correct field. The code for this is:

if isnull(me.OpenArgs) = false then

me.CompanyName = me.Openargs

endif

That is all you need. However, to make life a bit easier to your users,
there are a good number of things you should set in frmAddClent to make life
better.

They are:

Set the forms "cycle" property to current. This means the cursor when on the
bottom of the screen at the last field does not jump to another record. It
is horrible when a user bumps the tab key, and access goes to another
record, so, set the forms cycle property (other tab) to current. In fact,
this is a good setting for most forms, and NOT just this add example.

Since this is just a add form, then turn off the record navigation at the
bottom, again this serves only to confuse the user (since you have a
frmAddClient, I bet you already did this too). Why confuse the user!

Further, turn off the forms ability to "add new" records. Yes, you read this
100% correct. You want turn off the forms allow additions property. The
reason for this is that the above "open" form will OVER RIDE this setting.
This is ideal, since once again, hitting page/down key, or even the mouse
wheel will not cause the form to jump to another record, and confuse the
heck out of the user (this is along the same lines as the tab key setting
above as per "cycle" setting).

Note if you need this form to do "double duty" and want to use it in other
places in the application to allow the user will add MORE THEN ONE record at
a time, then put the follwing in the forms on open

if isnull(me.OpenArgs) = false then

me.Company = me.Openargs
me.AllowAdditions = false
Me.NavigationButtons = False

endif

That way, this form can be used else where in the program, but for our combo
box, the navigation buttons, and accidental adding of MORE THEN one record
will not occur.

All in all, you don't have to do any of the additional things I mention
above, but can *just* use the short sample code, but all of the above
together makes a real slick app.