The typical code that you would use is:
Private Sub Distributee_NotInList(NewData As String, Response As Integer)
if MsgBox("Do you want to add this value to the list?", _
vbYesNo) = vbYes then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
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.
The above is actualy all you need...less then 10 lines of code.....
However, we can refine this futher....
, in the forms on-load event, I go:
if isnull(me.OpenArgs) = false then
me.CompanyName = me.Openargs
endif
It is important to note that while you are saving the id in the combo box
list, the newdata is in fact the actual text used, and if you change that
text, then you need to modify the newdata to MATCH what your user typed in
that form.
The solution is to simply grab what value the user entered into the form.
The
trick in doing this is to simply modify the above code as:
strF = "frmAddClient"
if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm strF, , , , acFormAdd, acDialog, NewData
if isloaded(strF) = true then
Response = acDataErrAdded
NewData = forms(strF)!CompanyName
docmd.Close acForm,strF
else
Response = acDataErrContinue
endif
Else
Response = acDataErrContinue
End If
To see how you get a form to RETURN values, but wait for user input, simply
read the following article of mine:
http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html
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 following 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.
