cbo box Catch 22 situation

[Hugh self taught]

Hi all you helpful gurus,

I have an unbound cbo box with Limit to List = No for a specific reason (If
not in list then do something else)
My dilema is that in order to have it as LtL = No, then the 1st visible
field must be the Bound to Column. That causes the PK field in my query to
show in the cbo box after selection instead of the field I base my selection
on. The 1st column in the column widths is set at 0.002 so that it doesn't
show up when using the drop down. I've tried using an unbound text box to
display over the text display portion of the cbo box without success & I
can't hide the cbo in case the original selection was incorrect.

Any suggestions how to get around this situation will be so appreciated.
That is if you can make sense of my scenario described here.

 

[Paul Shapiro]

I've used a combo box with LimitToList set to Yes, and then captured the
OnNotInList event to respond to that event. In my case, I use code to parse
the text that was entered and add the data to the underlying table. This
works fine with the first column set to zero width. The OnNotInList has a
Response parameter you can use to suppress the error. From the online help:

The Response argument can be one of the following intrinsic constants:

acDataErrDisplay (Default) Displays the default message to the user. You can
use this when you don't want to allow the user to add a new value to the
combo box list.

acDataErrContinue Doesn't display the default message to the user. You can
use this when you want to display a custom message to the user. For example,
the event procedure could display a custom dialog box asking if the user
wanted to save the new entry. If the response is Yes, the event procedure
would add the new entry to the list and set the Response argument to
acDataErrAdded. If the response is No, the event procedure would set the
Response argument to acDataErrContinue.

acDataErrAdded Doesn't display a message to the user but enables you to add
the entry to the combo box list in the NotInList event procedure. After the
entry is added, Microsoft Access updates the list by requerying the combo
box. Microsoft Access then rechecks the string against the combo box list,
and saves the value in the NewData argument in the field the combo box is
bound to. If the string is not in the list, then Microsoft Access displays
an error message.

 

[Hugh self taught]

I'm puzzled by your question in that if the value is not in the list the user
needs to do something else, but at the same time you do not want to limit to
list. If the user is free to enter a value in the list, how will that
require the user to do something else?

If the value is not in the list you need to set Limit to list to Yes, and use
the Not In List event to direct the "something else." The details depend on
what you mean by "something else."


--
Message posted via AccessMonster.com


.

In order to achieve the end result I have to merge 2 tables of which one has
a field used for reference & the other not. ie registered & not registered. I
have to do this so I can have the correct number of participants to calculate
the results which is my goal.

So if the participant is not "registered" then there is no reference in that
field. That reference is my primary source of searching. So if I type in the
reference in the cbo & it is not there then I hide that cbo & make visible
another cbo which uses other criteria as the lookup ie name on non registered
participant. The resultant record will allow me to then do whatever comes
next by code.

So my situation is such that if Not in List I can't select a record but I
also can't add a record as I need to search again using other criteria. Hence
Limit to List = No & my display issue.

Does that make the mud a little more clear? I'm trying to be descriptive
without elaborating extensively & wasting space & time.

 

[Paul Shapiro]

In order to achieve the end result I have to merge 2 tables of which one
has
a field used for reference & the other not. ie registered & not
registered. I
have to do this so I can have the correct number of participants to
calculate
the results which is my goal.

So if the participant is not "registered" then there is no reference in
that
field. That reference is my primary source of searching. So if I type in
the
reference in the cbo & it is not there then I hide that cbo & make visible
another cbo which uses other criteria as the lookup ie name on non
registered
participant. The resultant record will allow me to then do whatever comes
next by code.

So my situation is such that if Not in List I can't select a record but I
also can't add a record as I need to search again using other criteria.
Hence
Limit to List = No & my display issue.

Does that make the mud a little more clear? I'm trying to be descriptive
without elaborating extensively & wasting space & time.

It's still not so clear, and it would help if you include the table
structures so we know what you’re talking about. But as a guess, it sounds
like you have data in 2 tables and you want to merge those lists for a
lookup? If so, look at a union query, which combines data from two tables
and generates a single output. Maybe something like this:

Select participantID, "Registered" as source, LastName, FirstName, etc.
From TableRegistered

Union All

Select participantID, "UnRegistered" as source, LastName, FirstName, etc.
From TableUnRegistered

Order By LastName, FirstName

 

[Amy E. Baggott]

Actually, I've used the Not In List event to trigger other reactions. My
favorite use is to prompt the user to add something to the list, which might
be useful in this instance. I use the Not In List event to trigger a dialog
box that says something like "This item is not on the list. Would you like
to add it?" and has buttons for Yes or No. If the user clicks Yes, it opens
a form for them to add the new item, and the On Close event for that form
requeries the original combo box. If they choose No, they get a message to
please choose an item from the list, and it goes back to the original combo
box.

Might be worth a try in this case.

--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

The thing about the bound column needing to be the first visible column with
Limit to List set to No is that you need to be storing the value you are
actually typing. Say you have the following two columns in the Row Source
query:

ID LastName
1 Jones
2 Smith

Now you want to add Martinez, so you type it in, but what about the hidden
but bound column? You can't add a value to ID without opening a recordset.

I'm unclear about the merged tables, but if they form the Row Source for the
combo box, perhaps a union query as has been suggested.

About the best I can suggest is to use the Not In List event in some way such
as this:

MsgBox "Try the other combo box"
Response = acDataErrContinue
Me.ComboBoxName.Undo

The Not In List event seems to be pretty much useless for doing anything
other than working with the list. You can't set focus to another control, so
the best you can do is to undo the combo box entry, which will at least allow
you to click elsewhere. I may be wrong about this, but I have not been able
to find a solution that uses the Not In List event.

I'm puzzled by your question in that if the value is not in the list the user
needs to do something else, but at the same time you do not want to limit to

[quoted text clipped - 19 lines]

Any suggestions how to get around this situation will be so appreciated.
That is if you can make sense of my scenario described here.

In order to achieve the end result I have to merge 2 tables of which one has
a field used for reference & the other not. ie registered & not registered. I
have to do this so I can have the correct number of participants to calculate
the results which is my goal.

So if the participant is not "registered" then there is no reference in that
field. That reference is my primary source of searching. So if I type in the
reference in the cbo & it is not there then I hide that cbo & make visible
another cbo which uses other criteria as the lookup ie name on non registered
participant. The resultant record will allow me to then do whatever comes
next by code.

So my situation is such that if Not in List I can't select a record but I
also can't add a record as I need to search again using other criteria. Hence
Limit to List = No & my display issue.

Does that make the mud a little more clear? I'm trying to be descriptive
without elaborating extensively & wasting space & time.

 

[Hugh self taught]

The thing about the bound column needing to be the first visible column with
Limit to List set to No is that you need to be storing the value you are
actually typing. Say you have the following two columns in the Row Source
query:

ID LastName
1 Jones
2 Smith

Now you want to add Martinez, so you type it in, but what about the hidden
but bound column? You can't add a value to ID without opening a recordset.

I'm unclear about the merged tables, but if they form the Row Source for the
combo box, perhaps a union query as has been suggested.

About the best I can suggest is to use the Not In List event in some way such
as this:

MsgBox "Try the other combo box"
Response = acDataErrContinue
Me.ComboBoxName.Undo

The Not In List event seems to be pretty much useless for doing anything
other than working with the list. You can't set focus to another control, so
the best you can do is to undo the combo box entry, which will at least allow
you to click elsewhere. I may be wrong about this, but I have not been able
to find a solution that uses the Not In List event.

I'm puzzled by your question in that if the value is not in the list the user
needs to do something else, but at the same time you do not want to limit to

[quoted text clipped - 19 lines]

Any suggestions how to get around this situation will be so appreciated.
That is if you can make sense of my scenario described here.

In order to achieve the end result I have to merge 2 tables of which one has
a field used for reference & the other not. ie registered & not registered. I
have to do this so I can have the correct number of participants to calculate
the results which is my goal.

So if the participant is not "registered" then there is no reference in that
field. That reference is my primary source of searching. So if I type in the
reference in the cbo & it is not there then I hide that cbo & make visible
another cbo which uses other criteria as the lookup ie name on non registered
participant. The resultant record will allow me to then do whatever comes
next by code.

So my situation is such that if Not in List I can't select a record but I
also can't add a record as I need to search again using other criteria. Hence
Limit to List = No & my display issue.

Does that make the mud a little more clear? I'm trying to be descriptive
without elaborating extensively & wasting space & time.

It seems that my need is not clearly explained. I merge 2 tables into one so
I can do a lookup. I also use that merged table to manipulate other fields
based on the calculated results of other input data.

In the table of "registered" participants there is a reference field which
often times is my only resource to identifying the participant. The first
name & last names are also stored.

In the table of "unregistered" participants there is obviously no value in
the reference field. So when I do a lookup on the reference field, the
reference for a participant at the particular event will not be found since
they only have a temporary reference for the event.

I then hide the combo box & make visible another cbo which uses the combined
names of all the records without a value in the reference field. If I don't
find the participant in that lookup then I open a new form to add them as an
unregistered participant.

So on my original cbo I cannot use the Not in List as it requires data to be
added or selected & as Bruce said you cannot use setfocus to move to another
control either. Hence my use of Limit to List = No. While this cbo is the
active control I have no need to add a record if the reference I'm looking
for is not found. That situation only arises when the other cbo is made
visible where I lookup on the merged names criteria.

Thus the fields relevant are the PK of the merged table, the reference
field, First Name & Last Name.

Hope someone has a solution for me with my issue & that I've explained the
scenario more clearly

 

[Beetle]

After reading this thread I'm still not sure I completely
understand what you're trying to do, but here's my 2c worth.

In your first post you said the combo box is unbound. If that's
the case then it should make no difference which column you
use as the combo box's Bound Column. Since the combo box
itself is not bound to a field in any underlying table you would
have to use code to write any value from it to a table, in which
case you can write any value you want from the combo box,
whether it's the bound column or not.

So it seems to me you should just make the First & Last Name
column the Bound Column, then use the After Update event
to either;

1) Pop a message box and direct the user to the other combo
box, or;

2) Write the appropriate value from the selected row to an
underlying table.


Assuming that your combo box has, for example;

[PKValue] in the first column (Column 0)
[RefNo] in the second column (Column 1)
[First & Last Name] in the third column (Column 2)

Then the procedure might look something like;

***********************************

Private Sub Combo1_AfterUpdate()

If Nz(Me.Combo1.Column(1), vbNullString) = vbNullString Then

MsgBox "There is no reference number for this name." & _
" Please use Combo2 to select a name."
Me.Combo2.Visible = True
Me.Combo2.SetFocus

Else

Dim strSQL As String
strSQL = "Insert Into tblYourTable ([RefNo]) Values (" _
& Me.Combo1.Column(1) & ");"
CurrentDb.Execute strSQL, dbFailOnError

End If

End Sub

***********************************

The above assumes that RefNo is the value you want to write
to some underlying table and that it is an integer data type.
If not you'll need to modify the SQL statement accordingly.
Also, with the above scenario I would think that you would
want to set the LTL property of the first combo to Yes.

Then again, I may be completely misunderstanding what
it is you're trying to do.

--
_________

Sean Bailey

The thing about the bound column needing to be the first visible column with
Limit to List set to No is that you need to be storing the value you are
actually typing. Say you have the following two columns in the Row Source
query:

ID LastName
1 Jones
2 Smith

Now you want to add Martinez, so you type it in, but what about the hidden
but bound column? You can't add a value to ID without opening a recordset.

I'm unclear about the merged tables, but if they form the Row Source for the
combo box, perhaps a union query as has been suggested.

About the best I can suggest is to use the Not In List event in some way such
as this:

MsgBox "Try the other combo box"
Response = acDataErrContinue
Me.ComboBoxName.Undo

The Not In List event seems to be pretty much useless for doing anything
other than working with the list. You can't set focus to another control, so
the best you can do is to undo the combo box entry, which will at least allow
you to click elsewhere. I may be wrong about this, but I have not been able
to find a solution that uses the Not In List event.

I'm puzzled by your question in that if the value is not in the list the user
needs to do something else, but at the same time you do not want to limit to
[quoted text clipped - 19 lines]
Any suggestions how to get around this situation will be so appreciated.
That is if you can make sense of my scenario described here.

In order to achieve the end result I have to merge 2 tables of which one has
a field used for reference & the other not. ie registered & not registered. I
have to do this so I can have the correct number of participants to calculate
the results which is my goal.

So if the participant is not "registered" then there is no reference in that
field. That reference is my primary source of searching. So if I type in the
reference in the cbo & it is not there then I hide that cbo & make visible
another cbo which uses other criteria as the lookup ie name on non registered
participant. The resultant record will allow me to then do whatever comes
next by code.

So my situation is such that if Not in List I can't select a record but I
also can't add a record as I need to search again using other criteria. Hence
Limit to List = No & my display issue.

Does that make the mud a little more clear? I'm trying to be descriptive
without elaborating extensively & wasting space & time.

It seems that my need is not clearly explained. I merge 2 tables into one so
I can do a lookup. I also use that merged table to manipulate other fields
based on the calculated results of other input data.

In the table of "registered" participants there is a reference field which
often times is my only resource to identifying the participant. The first
name & last names are also stored.

In the table of "unregistered" participants there is obviously no value in
the reference field. So when I do a lookup on the reference field, the
reference for a participant at the particular event will not be found since
they only have a temporary reference for the event.

I then hide the combo box & make visible another cbo which uses the combined
names of all the records without a value in the reference field. If I don't
find the participant in that lookup then I open a new form to add them as an
unregistered participant.

So on my original cbo I cannot use the Not in List as it requires data to be
added or selected & as Bruce said you cannot use setfocus to move to another
control either. Hence my use of Limit to List = No. While this cbo is the
active control I have no need to add a record if the reference I'm looking
for is not found. That situation only arises when the other cbo is made
visible where I lookup on the merged names criteria.

Thus the fields relevant are the PK of the merged table, the reference
field, First Name & Last Name.

Hope someone has a solution for me with my issue & that I've explained the
scenario more clearly

 

[Hugh self taught]

Hi Sean,

Hmmm an angle I never thought of. I'll give it a go tomorrow & see if I can
make that route work for me. Logically it sounds as though it's the answer
but I'll post the outcome either way.

After reading this thread I'm still not sure I completely
understand what you're trying to do, but here's my 2c worth.

In your first post you said the combo box is unbound. If that's
the case then it should make no difference which column you
use as the combo box's Bound Column. Since the combo box
itself is not bound to a field in any underlying table you would
have to use code to write any value from it to a table, in which
case you can write any value you want from the combo box,
whether it's the bound column or not.

So it seems to me you should just make the First & Last Name
column the Bound Column, then use the After Update event
to either;

1) Pop a message box and direct the user to the other combo
box, or;

2) Write the appropriate value from the selected row to an
underlying table.


Assuming that your combo box has, for example;

[PKValue] in the first column (Column 0)
[RefNo] in the second column (Column 1)
[First & Last Name] in the third column (Column 2)

Then the procedure might look something like;

***********************************

Private Sub Combo1_AfterUpdate()

If Nz(Me.Combo1.Column(1), vbNullString) = vbNullString Then

MsgBox "There is no reference number for this name." & _
" Please use Combo2 to select a name."
Me.Combo2.Visible = True
Me.Combo2.SetFocus

Else

Dim strSQL As String
strSQL = "Insert Into tblYourTable ([RefNo]) Values (" _
& Me.Combo1.Column(1) & ");"
CurrentDb.Execute strSQL, dbFailOnError

End If

End Sub

***********************************

The above assumes that RefNo is the value you want to write
to some underlying table and that it is an integer data type.
If not you'll need to modify the SQL statement accordingly.
Also, with the above scenario I would think that you would
want to set the LTL property of the first combo to Yes.

Then again, I may be completely misunderstanding what
it is you're trying to do.

--
_________

Sean Bailey

The thing about the bound column needing to be the first visible column with
Limit to List set to No is that you need to be storing the value you are
actually typing. Say you have the following two columns in the Row Source
query:

ID LastName
1 Jones
2 Smith

Now you want to add Martinez, so you type it in, but what about the hidden
but bound column? You can't add a value to ID without opening a recordset.

I'm unclear about the merged tables, but if they form the Row Source for the
combo box, perhaps a union query as has been suggested.

About the best I can suggest is to use the Not In List event in some way such
as this:

MsgBox "Try the other combo box"
Response = acDataErrContinue
Me.ComboBoxName.Undo

The Not In List event seems to be pretty much useless for doing anything
other than working with the list. You can't set focus to another control, so
the best you can do is to undo the combo box entry, which will at least allow
you to click elsewhere. I may be wrong about this, but I have not been able
to find a solution that uses the Not In List event.

Hugh self taught wrote:
I'm puzzled by your question in that if the value is not in the list the user
needs to do something else, but at the same time you do not want to limit to
[quoted text clipped - 19 lines]
Any suggestions how to get around this situation will be so appreciated.
That is if you can make sense of my scenario described here.

In order to achieve the end result I have to merge 2 tables of which one has
a field used for reference & the other not. ie registered & not registered. I
have to do this so I can have the correct number of participants to calculate
the results which is my goal.

So if the participant is not "registered" then there is no reference in that
field. That reference is my primary source of searching. So if I type in the
reference in the cbo & it is not there then I hide that cbo & make visible
another cbo which uses other criteria as the lookup ie name on non registered
participant. The resultant record will allow me to then do whatever comes
next by code.

So my situation is such that if Not in List I can't select a record but I
also can't add a record as I need to search again using other criteria. Hence
Limit to List = No & my display issue.

Does that make the mud a little more clear? I'm trying to be descriptive
without elaborating extensively & wasting space & time.

It seems that my need is not clearly explained. I merge 2 tables into one so
I can do a lookup. I also use that merged table to manipulate other fields
based on the calculated results of other input data.

In the table of "registered" participants there is a reference field which
often times is my only resource to identifying the participant. The first
name & last names are also stored.

In the table of "unregistered" participants there is obviously no value in
the reference field. So when I do a lookup on the reference field, the
reference for a participant at the particular event will not be found since
they only have a temporary reference for the event.

I then hide the combo box & make visible another cbo which uses the combined
names of all the records without a value in the reference field. If I don't
find the participant in that lookup then I open a new form to add them as an
unregistered participant.

So on my original cbo I cannot use the Not in List as it requires data to be
added or selected & as Bruce said you cannot use setfocus to move to another
control either. Hence my use of Limit to List = No. While this cbo is the
active control I have no need to add a record if the reference I'm looking
for is not found. That situation only arises when the other cbo is made
visible where I lookup on the merged names criteria.

Thus the fields relevant are the PK of the merged table, the reference
field, First Name & Last Name.

Hope someone has a solution for me with my issue & that I've explained the
scenario more clearly

 

[Hugh self taught]

Hi Sean "Beetle" Bailey,

You were on the money. Your suggestion works perfectly thanks

Thanks a mil

After reading this thread I'm still not sure I completely
understand what you're trying to do, but here's my 2c worth.

In your first post you said the combo box is unbound. If that's
the case then it should make no difference which column you
use as the combo box's Bound Column. Since the combo box
itself is not bound to a field in any underlying table you would
have to use code to write any value from it to a table, in which
case you can write any value you want from the combo box,
whether it's the bound column or not.

So it seems to me you should just make the First & Last Name
column the Bound Column, then use the After Update event
to either;

1) Pop a message box and direct the user to the other combo
box, or;

2) Write the appropriate value from the selected row to an
underlying table.


Assuming that your combo box has, for example;

[PKValue] in the first column (Column 0)
[RefNo] in the second column (Column 1)
[First & Last Name] in the third column (Column 2)

Then the procedure might look something like;

***********************************

Private Sub Combo1_AfterUpdate()

If Nz(Me.Combo1.Column(1), vbNullString) = vbNullString Then

MsgBox "There is no reference number for this name." & _
" Please use Combo2 to select a name."
Me.Combo2.Visible = True
Me.Combo2.SetFocus

Else

Dim strSQL As String
strSQL = "Insert Into tblYourTable ([RefNo]) Values (" _
& Me.Combo1.Column(1) & ");"
CurrentDb.Execute strSQL, dbFailOnError

End If

End Sub

***********************************

The above assumes that RefNo is the value you want to write
to some underlying table and that it is an integer data type.
If not you'll need to modify the SQL statement accordingly.
Also, with the above scenario I would think that you would
want to set the LTL property of the first combo to Yes.

Then again, I may be completely misunderstanding what
it is you're trying to do.

--
_________

Sean Bailey

The thing about the bound column needing to be the first visible column with
Limit to List set to No is that you need to be storing the value you are
actually typing. Say you have the following two columns in the Row Source
query:

ID LastName
1 Jones
2 Smith

Now you want to add Martinez, so you type it in, but what about the hidden
but bound column? You can't add a value to ID without opening a recordset.

I'm unclear about the merged tables, but if they form the Row Source for the
combo box, perhaps a union query as has been suggested.

About the best I can suggest is to use the Not In List event in some way such
as this:

MsgBox "Try the other combo box"
Response = acDataErrContinue
Me.ComboBoxName.Undo

The Not In List event seems to be pretty much useless for doing anything
other than working with the list. You can't set focus to another control, so
the best you can do is to undo the combo box entry, which will at least allow
you to click elsewhere. I may be wrong about this, but I have not been able
to find a solution that uses the Not In List event.

Hugh self taught wrote:
I'm puzzled by your question in that if the value is not in the list the user
needs to do something else, but at the same time you do not want to limit to
[quoted text clipped - 19 lines]
Any suggestions how to get around this situation will be so appreciated.
That is if you can make sense of my scenario described here.

In order to achieve the end result I have to merge 2 tables of which one has
a field used for reference & the other not. ie registered & not registered. I
have to do this so I can have the correct number of participants to calculate
the results which is my goal.

So if the participant is not "registered" then there is no reference in that
field. That reference is my primary source of searching. So if I type in the
reference in the cbo & it is not there then I hide that cbo & make visible
another cbo which uses other criteria as the lookup ie name on non registered
participant. The resultant record will allow me to then do whatever comes
next by code.

So my situation is such that if Not in List I can't select a record but I
also can't add a record as I need to search again using other criteria. Hence
Limit to List = No & my display issue.

Does that make the mud a little more clear? I'm trying to be descriptive
without elaborating extensively & wasting space & time.

It seems that my need is not clearly explained. I merge 2 tables into one so
I can do a lookup. I also use that merged table to manipulate other fields
based on the calculated results of other input data.

In the table of "registered" participants there is a reference field which
often times is my only resource to identifying the participant. The first
name & last names are also stored.

In the table of "unregistered" participants there is obviously no value in
the reference field. So when I do a lookup on the reference field, the
reference for a participant at the particular event will not be found since
they only have a temporary reference for the event.

I then hide the combo box & make visible another cbo which uses the combined
names of all the records without a value in the reference field. If I don't
find the participant in that lookup then I open a new form to add them as an
unregistered participant.

So on my original cbo I cannot use the Not in List as it requires data to be
added or selected & as Bruce said you cannot use setfocus to move to another
control either. Hence my use of Limit to List = No. While this cbo is the
active control I have no need to add a record if the reference I'm looking
for is not found. That situation only arises when the other cbo is made
visible where I lookup on the merged names criteria.

Thus the fields relevant are the PK of the merged table, the reference
field, First Name & Last Name.

Hope someone has a solution for me with my issue & that I've explained the
scenario more clearly

 

[Beetle]

Glad I could help.