Combo Box - Simple Explanation

[M]

I have almost always understood programming in Access.
VBA, SQL and almost every control. However, I am still
baffled by Combo Boxes. Even with the wizards they
confound me. Here is my current/persistant dilemma:

1) I have a table with [ID] and [NAME]. Using the wizard,
I tie my ComboBox to this table.

2) I have a query with corresponding fields. The [ID]
field has criteria of [Forms]![frmMyForm]![cboID].

3)My ComboBox has been set up with the wizard to get
values from the aformentioned [ID] Table.

4)When I choose a [NAME] from the ComboBox, the underlying
query needs to filter using the corresponding value from
the ComboBox/Table [ID] field.

What am I continuing to miss here?!? AfterUpdate?
BeforeUpdate? LostFocus? Anything?

Thanks

M

 

[Albert D. Kallal]

4)When I choose a [NAME] from the ComboBox, the underlying
query needs to filter using the corresponding value from
the ComboBox/Table [ID] field.

It is not clear at all what you mean by "filter". The combo box simply lets
you select a value from a list. Usually the results of this select is a
simply id that is saved into a field IF THE combo box is bound. Are you
using a bound combo box? (ie: is there a field that the result of the combo
select is being saved to?). Perhaps by filter, you talking about limiting
the values that the form will see when a value in the combo is selected?

If your answer is yes to the above, then try building the combo box with the
wizard, but select the 3rd option: (Find a record based on the value
selected...).

If by filter, you don't mean a filter on the form's recordsouce..then feel
free to clarify and expand further as to what you are looking to accomplish.

 

[M]

I have an unbound ComboBox. The filter is the ID value
that results when a Name is selected. I've tried the
suggestion you've offered on both a form and a subform and
am still just a confused as before. This has to be a
simple thing to accomplish; MS would not allow such a
thing to continue if it weren't. Right?

Starting from the beginning of the process, I've run the
wizard, selecting the third choice. I get everything to
work but the form/query refuses to cooperate and return
the results, based on the selection in the ComboBox. I
feel that I will be restraining the FistOfDeath before the
day is over.

Thanks for your prompt help.

M

-----Original Message-----

4)When I choose a [NAME] from the ComboBox, the underlying
query needs to filter using the corresponding value from
the ComboBox/Table [ID] field.

It is not clear at all what you mean by "filter". The combo box simply lets
you select a value from a list. Usually the results of this select is a
simply id that is saved into a field IF THE combo box is bound. Are you
using a bound combo box? (ie: is there a field that the result of the combo
select is being saved to?). Perhaps by filter, you talking about limiting
the values that the form will see when a value in the combo is selected?

If your answer is yes to the above, then try building the combo box with the
wizard, but select the 3rd option: (Find a record based on the value
selected...).

If by filter, you don't mean a filter on the form's recordsouce..then feel
free to clarify and expand further as to what you are looking to accomplish.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

 

[John Vinson]

Starting from the beginning of the process, I've run the
wizard, selecting the third choice. I get everything to
work but the form/query refuses to cooperate and return
the results, based on the selection in the ComboBox. I
feel that I will be restraining the FistOfDeath before the
day is over.

Please post the following information so Albert or someone else has a
chance:

- The SQL view of the Combo's RowSource
- The Combo's Bound Column property
- The VBA code in the combo's AfterUpdate event
- The SQL view of the Form's Recordsource

 

[Albert D. Kallal]

I have an unbound ComboBox. The filter is the ID value
that results when a Name is selected.

Ok, the above sounds good. We are talking the same idea.

Starting from the beginning of the process, I've run the
wizard, selecting the third choice. I get everything to
work but the form/query refuses to cooperate and return
the results, based on the selection in the ComboBox. I
feel that I will be restraining the FistOfDeath before the
day is over.

Usually the wizard works quite well. However, the wizard does assume when
you open the form that all records are displayed. You then select a value
from the combo box, and then your form "moves" to that particular record.
So, that wizard (3rd option) does need a form with records in it.

I just tried using the wizard (3rd option) on my sample download at my web
site. Perhaps you can download the sample, and try the wizard on my sample
database...as it worked just fine for me.

The example I am talking about is a word merge example. You can try placing
a combo box on the contacts form. The download is at:

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

 

[M]

Okay. Here's are the specifics that J. Vinson asked about:

ROW SOURCE: (ComboBox.Name = cboID)
SELECT DISTINCTROW [tblID].[ID], [tblID].[NAME] FROM
[tblID];

BOUND COLUMN: ([ID])
1

FORM SQL:
SELECT tblOpenItemsTemp.*
FROM tblOpenItemsTemp
WHERE (((tblOpenItemsTemp.PAYEE_ID)=[Forms]!
[sfrmOpenItemsSub]![cboID]));

I see I don't have any VBA code in the AfterUpdate event.
Hmmm...

M

-----Original Message-----
I have an unbound ComboBox. The filter is the ID value
that results when a Name is selected. I've tried the
suggestion you've offered on both a form and a subform and
am still just a confused as before. This has to be a
simple thing to accomplish; MS would not allow such a
thing to continue if it weren't. Right?

Starting from the beginning of the process, I've run the
wizard, selecting the third choice. I get everything to
work but the form/query refuses to cooperate and return
the results, based on the selection in the ComboBox. I
feel that I will be restraining the FistOfDeath before the
day is over.

Thanks for your prompt help.

M

-----Original Message-----

4)When I choose a [NAME] from the ComboBox, the underlying
query needs to filter using the corresponding value from
the ComboBox/Table [ID] field.

It is not clear at all what you mean by "filter". The combo box simply lets
you select a value from a list. Usually the results of this select is a
simply id that is saved into a field IF THE combo box is bound. Are you
using a bound combo box? (ie: is there a field that the result of the combo
select is being saved to?). Perhaps by filter, you talking about limiting
the values that the form will see when a value in the combo is selected?

If your answer is yes to the above, then try building

the
combo box with the

wizard, but select the 3rd option: (Find a record based on the value
selected...).

If by filter, you don't mean a filter on the form's recordsouce..then feel
free to clarify and expand further as to what you are looking to accomplish.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

.

 

[tina]

do you want the form to present all table records, and
allow the user to move to a specific record based on the
combo box selection?
if so, take the WHERE clause out of the form's underlying
SQL, then use the combo box wizard option 3 as Albert
described previously.

or do you want the form to show no records until the user
makes a selection in the combo box, and then change the
record based on the next combo box selection?
if so, leave the WHERE clause in the SQL, and requery the
form on the combo box's AfterUpdate event, as

Me.Requery

hth

-----Original Message-----
Okay. Here's are the specifics that J. Vinson asked about:

ROW SOURCE: (ComboBox.Name = cboID)
SELECT DISTINCTROW [tblID].[ID], [tblID].[NAME] FROM
[tblID];

BOUND COLUMN: ([ID])
1

FORM SQL:
SELECT tblOpenItemsTemp.*
FROM tblOpenItemsTemp
WHERE (((tblOpenItemsTemp.PAYEE_ID)=[Forms]!
[sfrmOpenItemsSub]![cboID]));

I see I don't have any VBA code in the AfterUpdate event.
Hmmm...

M

-----Original Message-----
I have an unbound ComboBox. The filter is the ID value
that results when a Name is selected. I've tried the
suggestion you've offered on both a form and a subform and
am still just a confused as before. This has to be a
simple thing to accomplish; MS would not allow such a
thing to continue if it weren't. Right?

Starting from the beginning of the process, I've run the
wizard, selecting the third choice. I get everything to
work but the form/query refuses to cooperate and return
the results, based on the selection in the ComboBox. I
feel that I will be restraining the FistOfDeath before the
day is over.

Thanks for your prompt help.

M

-----Original Message-----


4)When I choose a [NAME] from the ComboBox, the underlying
query needs to filter using the corresponding value from
the ComboBox/Table [ID] field.


It is not clear at all what you mean by "filter". The combo box simply lets
you select a value from a list. Usually the results of this select is a
simply id that is saved into a field IF THE combo box

is
bound. Are you

using a bound combo box? (ie: is there a field that the result of the combo
select is being saved to?). Perhaps by filter, you talking about limiting
the values that the form will see when a value in the combo is selected?

If your answer is yes to the above, then try building

the
combo box with the

wizard, but select the 3rd option: (Find a record based on the value
selected...).

If by filter, you don't mean a filter on the form's recordsouce..then feel
free to clarify and expand further as to what you are looking to accomplish.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

.

.

 

[M]

This is exactly what I want:

or do you want the form to show no records until the user
makes a selection in the combo box, and then change the
record based on the next combo box selection?
if so, leave the WHERE clause in the SQL, and requery the
form on the combo box's AfterUpdate event,...

But after applying the changes to the AfterUpdate event of
the ComboBox, I get an error msg. stating that the
macro 'Me' can't be found. Tried '!' & "." but both still
return this error.

Thanks to everyone for the patience.

M

-----Original Message-----
do you want the form to present all table records, and
allow the user to move to a specific record based on the
combo box selection?
if so, take the WHERE clause out of the form's underlying
SQL, then use the combo box wizard option 3 as Albert
described previously.

or do you want the form to show no records until the user
makes a selection in the combo box, and then change the
record based on the next combo box selection?
if so, leave the WHERE clause in the SQL, and requery the
form on the combo box's AfterUpdate event, as

Me.Requery

hth

-----Original Message-----
Okay. Here's are the specifics that J. Vinson asked about:

ROW SOURCE: (ComboBox.Name = cboID)
SELECT DISTINCTROW [tblID].[ID], [tblID].[NAME] FROM
[tblID];

BOUND COLUMN: ([ID])
1

FORM SQL:
SELECT tblOpenItemsTemp.*
FROM tblOpenItemsTemp
WHERE (((tblOpenItemsTemp.PAYEE_ID)=[Forms]!
[sfrmOpenItemsSub]![cboID]));

I see I don't have any VBA code in the AfterUpdate event.
Hmmm...

M

-----Original Message-----
I have an unbound ComboBox. The filter is the ID value
that results when a Name is selected. I've tried the
suggestion you've offered on both a form and a subform and
am still just a confused as before. This has to be a
simple thing to accomplish; MS would not allow such a
thing to continue if it weren't. Right?

Starting from the beginning of the process, I've run the
wizard, selecting the third choice. I get everything to
work but the form/query refuses to cooperate and return
the results, based on the selection in the ComboBox. I
feel that I will be restraining the FistOfDeath before the
day is over.

Thanks for your prompt help.

M


-----Original Message-----


4)When I choose a [NAME] from the ComboBox, the
underlying
query needs to filter using the corresponding value from
the ComboBox/Table [ID] field.


It is not clear at all what you mean by "filter". The
combo box simply lets
you select a value from a list. Usually the results of
this select is a
simply id that is saved into a field IF THE combo box is
bound. Are you
using a bound combo box? (ie: is there a field that the
result of the combo
select is being saved to?). Perhaps by filter, you
talking about limiting
the values that the form will see when a value in the
combo is selected?

If your answer is yes to the above, then try building the
combo box with the
wizard, but select the 3rd option: (Find a record based
on the value
selected...).

If by filter, you don't mean a filter on the form's
recordsouce..then feel
free to clarify and expand further as to what you are
looking to accomplish.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

.

.

.

 

[Jim Carlock]

Unbounded means not tying it into an SQL statement.

Combo1.Clear
Set rs = Open gDB.Recordset
For i = 1 to rs.RecordCount
Combo1.AddItem rs.Fields("Name").Value
rs.MoveNext
Next i
rs.Close
Set rs = Nothing

sSelected = Combo1.Selected
or
sSelected = Combo1.Text

You could put that into the Change or Click event of the combo
box and then use a private string to retain whats selected.

You can give the string a Property so that every time it's updated
through the property call, all other information is filled in.

Hope that helps.
--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.


This is exactly what I want:

or do you want the form to show no records until the user
makes a selection in the combo box, and then change the
record based on the next combo box selection?
if so, leave the WHERE clause in the SQL, and requery the
form on the combo box's AfterUpdate event,...

But after applying the changes to the AfterUpdate event of
the ComboBox, I get an error msg. stating that the
macro 'Me' can't be found. Tried '!' & "." but both still
return this error.

Thanks to everyone for the patience.

M

-----Original Message-----
do you want the form to present all table records, and
allow the user to move to a specific record based on the
combo box selection?
if so, take the WHERE clause out of the form's underlying
SQL, then use the combo box wizard option 3 as Albert
described previously.

or do you want the form to show no records until the user
makes a selection in the combo box, and then change the
record based on the next combo box selection?
if so, leave the WHERE clause in the SQL, and requery the
form on the combo box's AfterUpdate event, as

Me.Requery

hth

-----Original Message-----
Okay. Here's are the specifics that J. Vinson asked about:

ROW SOURCE: (ComboBox.Name = cboID)
SELECT DISTINCTROW [tblID].[ID], [tblID].[NAME] FROM
[tblID];

BOUND COLUMN: ([ID])
1

FORM SQL:
SELECT tblOpenItemsTemp.*
FROM tblOpenItemsTemp
WHERE (((tblOpenItemsTemp.PAYEE_ID)=[Forms]!
[sfrmOpenItemsSub]![cboID]));

I see I don't have any VBA code in the AfterUpdate event.
Hmmm...

M

-----Original Message-----
I have an unbound ComboBox. The filter is the ID value
that results when a Name is selected. I've tried the
suggestion you've offered on both a form and a subform and
am still just a confused as before. This has to be a
simple thing to accomplish; MS would not allow such a
thing to continue if it weren't. Right?

Starting from the beginning of the process, I've run the
wizard, selecting the third choice. I get everything to
work but the form/query refuses to cooperate and return
the results, based on the selection in the ComboBox. I
feel that I will be restraining the FistOfDeath before the
day is over.

Thanks for your prompt help.

M


-----Original Message-----


4)When I choose a [NAME] from the ComboBox, the
underlying
query needs to filter using the corresponding value from
the ComboBox/Table [ID] field.


It is not clear at all what you mean by "filter". The
combo box simply lets
you select a value from a list. Usually the results of
this select is a
simply id that is saved into a field IF THE combo box is
bound. Are you
using a bound combo box? (ie: is there a field that the
result of the combo
select is being saved to?). Perhaps by filter, you
talking about limiting
the values that the form will see when a value in the
combo is selected?

If your answer is yes to the above, then try building the
combo box with the
wizard, but select the 3rd option: (Find a record based
on the value
selected...).

If by filter, you don't mean a filter on the form's
recordsouce..then feel
free to clarify and expand further as to what you are
looking to accomplish.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.

.

.

.

 

[John Vinson]

But after applying the changes to the AfterUpdate event of
the ComboBox, I get an error msg. stating that the
macro 'Me' can't be found. Tried '!' & "." but both still
return this error.

Thanks to everyone for the patience.

It sounds like you're typing the expression directly into the
AfterUpdate property. That's not what was intended. Instead, click the
.... icon by the AfterUpdate event, and select the "Code Builder". Put
the code between the Sub and End Sub lines that Access will provide
for you.

 

[Douglas J. Steele]

Unbound has nothing to do with whether or not the source of the combobox is
an SQL statement. It simply means that the value of the combobox isn't
obtained from the underlying recordsource of the form (and that selecting a
value from the combobox doesn't get stored anywhere in the underlying
recordsource).