Querying for combobox problem

  • Thread starter Thread starter wallymeister
  • Start date Start date
W

wallymeister

Hello Access Guru's,
I now have a problem that's just wierd. See if I can explain.
I have form w/ 4 controls (2 combo boxes, 2 textboxes).
The 1st combo box has a query that filters for all [TypeDesc] and
[TypePrefix].
The cboTypeDesc dropdown holds [TypeDesc] field and a txtbox next to it has
controlsource = cboTypeDesc.column(1) which is [TypePrefix] that goes with
[TypeDesc] selected.
This is working fine!

Now the other 2 controls another combobox and another textbox.

This [cboBlowerCap] runs a query that filters for all possible BlowerCaps
and Btus for the TypeDesc presently selected in the 1st combobox. This
combobox displays both fields [BlowerCap] & [Btus] in dropdown. Like the
other textbox in above example, this one has a controlsource =
cboBlowerCap.column(1) to hold the value that is linked to BlowerCap chosen.
The problem is that when I select any combination of data in this combobox
the textbox to display the Btus will always display the top btu of the
BlowerCap choices.

e.g.

BlowerCap Btus
024 040
024 060
036 *060*
036 080
<036 100>
042 080
048 080
048 100
< > Selected

So, using the above data, if I select the combination 036 - 100, the 036
displays in cboBlowerCap fine but the value displayed in txtBtus will be the
matching value of the first appearance of 036, which will be 060 (* *)

The purpose of this form is to use the values selected to build an
appendquery.
Both of the textboxes are locked to the user.

I would appreciate if anyone can shed light on what might be happening here.
I am virtually doing the same thing here that I did with the other two
controls but it is acting different.

Thanks much,
wally
 
wallymeister said:
Hello Access Guru's,
I now have a problem that's just wierd. See if I can explain.
I have form w/ 4 controls (2 combo boxes, 2 textboxes).
The 1st combo box has a query that filters for all [TypeDesc] and
[TypePrefix].
The cboTypeDesc dropdown holds [TypeDesc] field and a txtbox next to it
has
controlsource = cboTypeDesc.column(1) which is [TypePrefix] that goes with
[TypeDesc] selected.
This is working fine!

Now the other 2 controls another combobox and another textbox.

This [cboBlowerCap] runs a query that filters for all possible BlowerCaps
and Btus for the TypeDesc presently selected in the 1st combobox. This
combobox displays both fields [BlowerCap] & [Btus] in dropdown. Like the
other textbox in above example, this one has a controlsource =
cboBlowerCap.column(1) to hold the value that is linked to BlowerCap
chosen.
The problem is that when I select any combination of data in this combobox
the textbox to display the Btus will always display the top btu of the
BlowerCap choices.

e.g.

BlowerCap Btus
024 040
024 060
036 *060*
036 080
<036 100>
042 080
048 080
048 100
< > Selected

So, using the above data, if I select the combination 036 - 100, the 036
displays in cboBlowerCap fine but the value displayed in txtBtus will be
the
matching value of the first appearance of 036, which will be 060 (* *)

The purpose of this form is to use the values selected to build an
appendquery.
Both of the textboxes are locked to the user.

I would appreciate if anyone can shed light on what might be happening
here.
I am virtually doing the same thing here that I did with the other two
controls but it is acting different.

Thanks much,
wally
Click to expand...

Not sure I'm on the right track here, but you could try this:

=cboBlowerCap.column(1, cboBlowerCap.ListIndex)

(in other words provide the row index too)
 
Stuart,
Thanks for the reply! I think you're on to something. Your suggestion is
bringing back memories of when I used to get along pretty well in VBA and
control properties. I've used ListIndex a few times but for the life of me I
can't remember exactly how this works. I'm trying to search this forum for
more on this property and how it works. Meanwhile, I'll wait and see if
anyone has any suggestions.
When I just used listindex to return a number I got exactly what I was
getting before. The list index # would only change with the changes in
column 1. It would be the right number chronologically but just wouldn't
change with each row.

e.g. using =cboBlowerCap.ListIndex
BlowerCap Btus ListIndex Returned
024 040 0
024 060 0
036 *060* 2
036 080 2
<036 100> 2
042 080 5
048 080 6
048 100 6
< > Selected

Thanks again for your input.

wally


Stuart McCall said:
wallymeister said:
Hello Access Guru's,
I now have a problem that's just wierd. See if I can explain.
I have form w/ 4 controls (2 combo boxes, 2 textboxes).
The 1st combo box has a query that filters for all [TypeDesc] and
[TypePrefix].
The cboTypeDesc dropdown holds [TypeDesc] field and a txtbox next to it
has
controlsource = cboTypeDesc.column(1) which is [TypePrefix] that goes with
[TypeDesc] selected.
This is working fine!

Now the other 2 controls another combobox and another textbox.

This [cboBlowerCap] runs a query that filters for all possible BlowerCaps
and Btus for the TypeDesc presently selected in the 1st combobox. This
combobox displays both fields [BlowerCap] & [Btus] in dropdown. Like the
other textbox in above example, this one has a controlsource =
cboBlowerCap.column(1) to hold the value that is linked to BlowerCap
chosen.
The problem is that when I select any combination of data in this combobox
the textbox to display the Btus will always display the top btu of the
BlowerCap choices.

e.g.

BlowerCap Btus
024 040
024 060
036 *060*
036 080
<036 100>
042 080
048 080
048 100
< > Selected

So, using the above data, if I select the combination 036 - 100, the 036
displays in cboBlowerCap fine but the value displayed in txtBtus will be
the
matching value of the first appearance of 036, which will be 060 (* *)

The purpose of this form is to use the values selected to build an
appendquery.
Both of the textboxes are locked to the user.

I would appreciate if anyone can shed light on what might be happening
here.
I am virtually doing the same thing here that I did with the other two
controls but it is acting different.

Thanks much,
wally
Click to expand...

Not sure I'm on the right track here, but you could try this:

=cboBlowerCap.column(1, cboBlowerCap.ListIndex)

(in other words provide the row index too)
Click to expand...
 
Ok, until I find out how to reference the second column in combobox list and
get the right row value that I need I have done this temporarily to get
around the problem. Well, it should work indefinitely, it's just that I know
it can be done the other way too so, I'd like to learn the stuff I have
forgotten.

Anyway, for cboBlowerCap query for dropdown list I added a concatenated
field to put [BlowerCap] and [Btus] together. e.g BC_Btus:=[BlowerCap] &
"-" & [Btus]
This will give me what I need and then I can pull the values apart with
Left() and Right() Str functions in my Append Query.

I'll check in and watch for reply if anyone has the knowledge how to acheive
this using the aforementioned method.

Thanks,
wally


Stuart McCall said:
wallymeister said:
Hello Access Guru's,
I now have a problem that's just wierd. See if I can explain.
I have form w/ 4 controls (2 combo boxes, 2 textboxes).
The 1st combo box has a query that filters for all [TypeDesc] and
[TypePrefix].
The cboTypeDesc dropdown holds [TypeDesc] field and a txtbox next to it
has
controlsource = cboTypeDesc.column(1) which is [TypePrefix] that goes with
[TypeDesc] selected.
This is working fine!

Now the other 2 controls another combobox and another textbox.

This [cboBlowerCap] runs a query that filters for all possible BlowerCaps
and Btus for the TypeDesc presently selected in the 1st combobox. This
combobox displays both fields [BlowerCap] & [Btus] in dropdown. Like the
other textbox in above example, this one has a controlsource =
cboBlowerCap.column(1) to hold the value that is linked to BlowerCap
chosen.
The problem is that when I select any combination of data in this combobox
the textbox to display the Btus will always display the top btu of the
BlowerCap choices.

e.g.

BlowerCap Btus
024 040
024 060
036 *060*
036 080
<036 100>
042 080
048 080
048 100
< > Selected

So, using the above data, if I select the combination 036 - 100, the 036
displays in cboBlowerCap fine but the value displayed in txtBtus will be
the
matching value of the first appearance of 036, which will be 060 (* *)

The purpose of this form is to use the values selected to build an
appendquery.
Both of the textboxes are locked to the user.

I would appreciate if anyone can shed light on what might be happening
here.
I am virtually doing the same thing here that I did with the other two
controls but it is acting different.

Thanks much,
wally
Click to expand...

Not sure I'm on the right track here, but you could try this:

=cboBlowerCap.column(1, cboBlowerCap.ListIndex)

(in other words provide the row index too)
Click to expand...
 
wallymeister said:
Stuart,
Thanks for the reply! I think you're on to something. Your suggestion is
bringing back memories of when I used to get along pretty well in VBA and
control properties. I've used ListIndex a few times but for the life of
me I
can't remember exactly how this works. I'm trying to search this forum for
more on this property and how it works. Meanwhile, I'll wait and see if
anyone has any suggestions.
When I just used listindex to return a number I got exactly what I was
getting before. The list index # would only change with the changes in
column 1. It would be the right number chronologically but just wouldn't
change with each row.

e.g. using =cboBlowerCap.ListIndex
BlowerCap Btus ListIndex Returned
024 040 0
024 060 0
036 *060* 2
036 080 2
<036 100> 2
042 080 5
048 080 6
048 100 6
< > Selected

Thanks again for your input.

wally


Stuart McCall said:
wallymeister said:
Hello Access Guru's,
I now have a problem that's just wierd. See if I can explain.
I have form w/ 4 controls (2 combo boxes, 2 textboxes).
The 1st combo box has a query that filters for all [TypeDesc] and
[TypePrefix].
The cboTypeDesc dropdown holds [TypeDesc] field and a txtbox next to it
has
controlsource = cboTypeDesc.column(1) which is [TypePrefix] that goes
with
[TypeDesc] selected.
This is working fine!

Now the other 2 controls another combobox and another textbox.

This [cboBlowerCap] runs a query that filters for all possible
BlowerCaps
and Btus for the TypeDesc presently selected in the 1st combobox. This
combobox displays both fields [BlowerCap] & [Btus] in dropdown. Like
the
other textbox in above example, this one has a controlsource =
cboBlowerCap.column(1) to hold the value that is linked to BlowerCap
chosen.
The problem is that when I select any combination of data in this
combobox
the textbox to display the Btus will always display the top btu of the
BlowerCap choices.

e.g.

BlowerCap Btus
024 040
024 060
036 *060*
036 080
<036 100>
042 080
048 080
048 100
Click to expand...
Click to expand...
Click to expand...

Well ListIndex returns the (zero based) row that is currently selected. So
in your above example, it should return 4. So:

cboBlowerCap.column(1, cboBlowerCap.ListIndex)

would get you 100. Is this not what you're looking for?
 
I have moved on with still another method I have found and it's working
perfectly. It turns out I do a concatenate [BlowerCap] & [Btus] but that is
just for the user to select an existing record instead of being able to
create something that doesn't exist. I am now using ModelID that is linked
to users selection of [BlowerCap] & [Btus] combination to query table. I did
change quite a few things and maybe something I changed fixed the problem I
had earlier but in the meantime I think I found a better way. (or at least as
good)

Thanks so much for your help, the code you are suggesting looks right to me
but wasn't working probably because of some other logic mess up.

wally

Stuart McCall said:
wallymeister said:
Stuart,
Thanks for the reply! I think you're on to something. Your suggestion is
bringing back memories of when I used to get along pretty well in VBA and
control properties. I've used ListIndex a few times but for the life of
me I
can't remember exactly how this works. I'm trying to search this forum for
more on this property and how it works. Meanwhile, I'll wait and see if
anyone has any suggestions.
When I just used listindex to return a number I got exactly what I was
getting before. The list index # would only change with the changes in
column 1. It would be the right number chronologically but just wouldn't
change with each row.

e.g. using =cboBlowerCap.ListIndex
BlowerCap Btus ListIndex Returned
024 040 0
024 060 0
036 *060* 2
036 080 2
<036 100> 2
042 080 5
048 080 6
048 100 6
< > Selected

Thanks again for your input.

wally


Stuart McCall said:
Hello Access Guru's,
I now have a problem that's just wierd. See if I can explain.
I have form w/ 4 controls (2 combo boxes, 2 textboxes).
The 1st combo box has a query that filters for all [TypeDesc] and
[TypePrefix].
The cboTypeDesc dropdown holds [TypeDesc] field and a txtbox next to it
has
controlsource = cboTypeDesc.column(1) which is [TypePrefix] that goes
with
[TypeDesc] selected.
This is working fine!

Now the other 2 controls another combobox and another textbox.

This [cboBlowerCap] runs a query that filters for all possible
BlowerCaps
and Btus for the TypeDesc presently selected in the 1st combobox. This
combobox displays both fields [BlowerCap] & [Btus] in dropdown. Like
the
other textbox in above example, this one has a controlsource =
cboBlowerCap.column(1) to hold the value that is linked to BlowerCap
chosen.
The problem is that when I select any combination of data in this
combobox
the textbox to display the Btus will always display the top btu of the
BlowerCap choices.

e.g.

BlowerCap Btus
024 040
024 060
036 *060*
036 080
<036 100>
042 080
048 080
048 100
Click to expand...
Click to expand...

Well ListIndex returns the (zero based) row that is currently selected. So
in your above example, it should return 4. So:

cboBlowerCap.column(1, cboBlowerCap.ListIndex)

would get you 100. Is this not what you're looking for?
Click to expand...
 
Back
Top