How to add a new record from List Box to Value List

[TinaF]

I'm trying to learning Access by doing - so this question is REALLY
basic.

I have a field in a table called "CompanyName". The field is a list
box and the list comes from another, linked table called Contractors.
Contractors is just a list of names of contracting companies that we
deal with.

When I am in my first table and I am adding a new record, when I get
to the "CompanyName" field and the contracting company I need to enter
is not already in the list I type it in. How do I get this new
contracting company that I just typed to then be automatically added
to the Contractors table so that it is then available as a selection
for future records?

 

[John W. Vinson]

I'm trying to learning Access by doing - so this question is REALLY
basic.

I have a field in a table called "CompanyName". The field is a list
box and the list comes from another, linked table called Contractors.
Contractors is just a list of names of contracting companies that we
deal with.

When I am in my first table and I am adding a new record, when I get
to the "CompanyName" field and the contracting company I need to enter
is not already in the list I type it in. How do I get this new
contracting company that I just typed to then be automatically added
to the Contractors table so that it is then available as a selection
for future records?

First, review the critique of the Lookup Field:

http://www.mvps.org/access/lookupfields.htm

The short answer is that if you're using the table datasheet to enter your
data, you CAN'T. Despite Microsoft's pressure to encourage use of tables for
routine data interaction, that's really not their function - tables are to
STORE data, not to interact with it.

If instead you are using a Form (*much* preferable!) your combo box will have
a "Not In List" event. Look at the mvps.org site above and find their
instructions for how to manage the not in list event - it's pretty
straightforward.

John W. Vinson [MVP]

 

[Albert D. Kallal]

using a combo box on a form?...

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.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.


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.