Command button to update choices in a combo box

[accessbabe]

I have two table (tblA and tblB). Each has a form (frmA and frmB).
Table A contains names of people ("names"). Table B contains a combo box
that looksup to the Table A "names." The combo box properties are set to
"limit to list: yes."

Now, when the names are displayed in the drop down for the combo box, and I
don't see the name I need, I use a command button to open frmB and I add a
new name. So far, simple enough. BUT, the problem is that once I've added
the new name to frmB and go back to frmA (using another typical command
button), the name I have just added (in frmB) is not displayed in the combo
box. In order for it to be displayed, I have to do all sorts of silly
clicking (such as selecting a name I don't want, then moving back on record,
then forward one record, or something equally ridiculous!)

Please, does anyone know how to update the combo box entries, when those
entries are based on records that are newly added to another form?

Thank you so much!

AB

 

[Albert D. Kallal]

The easy way to do this is have ms-access do ALL of the work for you.

Set the combo box limit to = yes, and then use the not in list even.

When the user types in somting not in the list, you prmopt him.

So, given that new data is the actually text you typed into the combo, then
you
can do the following:

Private Sub MailingList_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.