NotInList, Combo boxes, evil day

[Kevin McAbee]

Hello all,

I was wondering if you could answer a quick question for me.

I have a combo box that is using a query as the recordsource. I am having
the users type in the name of the organization in the combo box and using
the id of the organization as the hidden (width of 0") bound column.

I have written code for the notinlist event that will trigger a dialog form
so the user can enter data about new organizations. However, the name they
enter in the dialog form may end being slightly different (more complete I
should say), than the name they were typing in the combo box. If the name
is different, then when the dialog form closes, the combo box will kick out
a "item not found in list" error. If I set the combo box's value to the
integer id of the newly created organization, it will still kick out the
answer and not perform an autoexpand on the organization name.

Now here's the kicker - if I have the bound id column visible and as the
column to match when typing in the combo box, all works well. However, it
does not work when that bound column is hidden.

Any suggestions on how I can overcome this?

Thanks,

Kevin McAbee

 

[Rick B]

Please do not post separate messages to multiple newsgroups for the same
issue. It means that more than one person will spend their time answering
your question only to find it already answered in another group.

 

[Albert D.Kallal]

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) 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 hte combo box is re-laoded, and re-set.

Of couse, the above (not yet) still does have your problem. In the above
example note how I am passing the newdata as a openargs.

Obivlarty, 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 hte actual text used, and if you change that
text, then you need to modify the newdata to MATCH what your user typyed in
that form.

The solution is to simply grab what value the user enterd into the form. The
trick in doing this is to somply 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 follwing 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 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.

 

[Albert D.Kallal]

oops, bumped the send key....lets try this again....

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) 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.

Of course, the above (not yet) still does have your problem. In the above
example note how I am passing the newdata as a openargs.

Obviously, 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.

 

[Kevin McAbee]

Thanks for the information, Albert. I am currently doing what you say via
the following code:

Private Sub orgId_NotInList(NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & _
" isn't an existing organization. " & _
"Add a new organization?"
mbrResponse = MsgBox(strMsg, _
vbYesNo + vbQuestion, "Invalid Organization")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmOrganizationQuick", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData

'Stop herre and wait until the form
'goes away.
If IsLoaded("frmOrganizationQuick") Then
Response = acDataErrAdded
DoCmd.Close acForm, "frmOrganizationQuick"
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub

I am also copying the organization's name from the original form to the
dialog form's name via the OpenArgs and the dialog form's Onload event. But
if somebody only types half the name and hits enter, the NotInList triggers,
the dialog form opens and they finish entering the name. So now we have two
different sets of text.

The problem I am having is with the combobox on the original form after the
record has been created, added to the combo box via acDataErrAdded and the
dialog form closed. But since the names are different, I get the "no such
item found in list" error. I have tried setting the value of the combo box
to the id created on the dialog form (since that is the value type being
used in the bound column) but I still get the same error. I have tried
adding the text that is on the visible column, but I get the same error. In
either case, and if I change the value of the combobox after it already has
a value set, the value stays in the field but the actual text that is being
shown in the combo box does not. So I need to find a way to have the value
store in the 0" wide bound column after the dialog form is closed but
display the corresponding text of the value found in the refreshed
recordsource of the combobox.

I know there has to be a way but I just can't seem to find it.

 

[Kevin McAbee]

Thanks everybody. I finally realized what I had been doing wrong. While I
was setting the value of the combobox, I was not requerying the combobox.
It was that simple, something totally small and innocent that I had
completely overlooked. To add insult to injury, my first post in this
newsgroup and I double posted to both groups.

Oh how stupid I feel. However, the problem is now fixed, as evidenced by
the addition of the line below. I appreciate the help from you all!

 

[Albert D.Kallal]

you should not need the re-query.

Note the following:

if isloaded(strF) = true then
Response = acDataErrAdded
NewData = forms(strF)!CompanyName
docmd.Close acForm,strF

you did miss what I said:

You have to set the value to of newdata to whatever the user actually winds
up typing in the form. And, this is the 'text' value.

Note above how newdata is set to what the user typed in. (since, they MIGHT
change the newdata). Newdata never gets passed back in your posted code..and
thus it fails. It would seem that using the requery and setting the ID also
does the trick, but the whole thing failed in the first place because
newdata was not set, and the above line of code fixes this...

However, if what you got works...then go with it...and you leaned a way cool
means to bring back data from a form in the process!