Combo Box Filter

  • Thread starter Thread starter Pam Hisaw
  • Start date Start date
P

Pam Hisaw

I have a combo box (Combo59) on a main form that I want to enter a
manufacturer name to and have it filter only those part numbers to another
combo box (PartIDNumber) on a subreport.

Part ID Number combo box has this statement in the Row Source:

SELECT qComboQuotePartsSubformParts.PartIDNumber,
qComboQuotePartsSubformParts.ManufacturerPartNumber,
qComboQuotePartsSubformParts.Manufacturer,
qComboQuotePartsSubformParts.Item, qComboQuotePartsSubformParts.ListPrice
FROM qComboQuotePartsSubformParts WHERE
Manufacturer=Forms!fQuoteMainForm!Combo59;

Combo59 has in the After Update an event procedure to requery the
PartIDNumber combo box.

It seems to be working in a strange way. When I go to this form, a dialog
box pops up asking for a parameter of "Forms!fQuoteMainForm!Combo59". I can
enter a manufacturer in this box and it does what I want. If I go to
Combo59 and enter a different manufacturer, there are no changes.

I also don't want to use a pop up dialog box because I may go into this form
many times for different reasons other than selecting part numbers.

I know this is lengthy, but I wanted to make it as clear as possible. Any
help would be greatly appreciated.

Thanks,
Pam
 
Hi Pam

What you are doing seems to be correct. It the query parameter dialog pops
up, then it means that Access can't resolve the reference to
Forms!fQuoteMainForm!Combo59. You should double-check that:
1) the name of the form is indeed "fQuoteMainForm"
2) the name of the combo is indeed "Combo59" (incidentally, I suggest
renaming it to something meaningful like cboSelectPartManufacturer)

If it all seems OK, then while the form is open, press Ctrl-G to show the
debug window and type:
?Forms!fQuoteMainForm!Combo59<enter>

It should display the current manufacturer number. If not, it might give a
clue as to what is wrong.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Pam Hisaw said:
I have a combo box (Combo59) on a main form that I want to enter a
manufacturer name to and have it filter only those part numbers to
another combo box (PartIDNumber) on a subreport.

Part ID Number combo box has this statement in the Row Source:

SELECT qComboQuotePartsSubformParts.PartIDNumber,
qComboQuotePartsSubformParts.ManufacturerPartNumber,
qComboQuotePartsSubformParts.Manufacturer,
qComboQuotePartsSubformParts.Item,
qComboQuotePartsSubformParts.ListPrice FROM
qComboQuotePartsSubformParts WHERE
Manufacturer=Forms!fQuoteMainForm!Combo59;

Combo59 has in the After Update an event procedure to requery the
PartIDNumber combo box.

It seems to be working in a strange way. When I go to this form, a
dialog box pops up asking for a parameter of
"Forms!fQuoteMainForm!Combo59". I can enter a manufacturer in this
box and it does what I want. If I go to Combo59 and enter a
different manufacturer, there are no changes.

I also don't want to use a pop up dialog box because I may go into
this form many times for different reasons other than selecting part
numbers.

I know this is lengthy, but I wanted to make it as clear as possible.
Any help would be greatly appreciated.

Thanks,
Pam
Click to expand...

Hmm. In principle, your setup seems to work for me without the problems
you report. Double-check the name of the main form ("fQuoteMainForm"?)
and of the combo box ("Combo59"?) on the main form.
 
Graham,

Thanks for the help and speedy reply. I had checked and rechecked all names
and just thought it was something in the expressions used. I not good with
expressions. Anyway, after you confirmed expression should work, I did more
research and the main form is actually a subform. After the correct
expression for a subform, it worked!!

Now I have one more problem. When this works, it takes out all the part
numbers for all my previous jobs. Is there a way to make this work without
affecting previous jobs and only work on new jobs?

Again your help is greatly appreciated!!

Thanks,
Pam


Graham Mandeno said:
Hi Pam

What you are doing seems to be correct. It the query parameter dialog pops
up, then it means that Access can't resolve the reference to
Forms!fQuoteMainForm!Combo59. You should double-check that:
1) the name of the form is indeed "fQuoteMainForm"
2) the name of the combo is indeed "Combo59" (incidentally, I suggest
renaming it to something meaningful like cboSelectPartManufacturer)

If it all seems OK, then while the form is open, press Ctrl-G to show the
debug window and type:
?Forms!fQuoteMainForm!Combo59<enter>

It should display the current manufacturer number. If not, it might give a
clue as to what is wrong.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.


Pam Hisaw said:
I have a combo box (Combo59) on a main form that I want to enter a
manufacturer name to and have it filter only those part numbers to another
combo box (PartIDNumber) on a subreport.

Part ID Number combo box has this statement in the Row Source:

SELECT qComboQuotePartsSubformParts.PartIDNumber,
qComboQuotePartsSubformParts.ManufacturerPartNumber,
qComboQuotePartsSubformParts.Manufacturer,
qComboQuotePartsSubformParts.Item, qComboQuotePartsSubformParts.ListPrice
FROM qComboQuotePartsSubformParts WHERE
Manufacturer=Forms!fQuoteMainForm!Combo59;

Combo59 has in the After Update an event procedure to requery the
PartIDNumber combo box.

It seems to be working in a strange way. When I go to this form, a dialog
box pops up asking for a parameter of "Forms!fQuoteMainForm!Combo59". I can
enter a manufacturer in this box and it does what I want. If I go to
Combo59 and enter a different manufacturer, there are no changes.

I also don't want to use a pop up dialog box because I may go into this form
many times for different reasons other than selecting part numbers.

I know this is lengthy, but I wanted to make it as clear as possible. Any
help would be greatly appreciated.

Thanks,
Pam
Click to expand...
Click to expand...
 
Hi Pam

So this is a continuous form?

You can do this by using a trick with two controls - a combo box and a text
box:
1) add to your recordsource query the table which contains the part
information, joined of course by PartIDNumber
2) make the join a "left join" (including all records on the left side, but
only those Part table records where there is a match)
3) add to your subform a textbox, bound to the text you want to display
(PartNumber?)
4) set the textbox properties: Enabled:No, Locked:Yes
5) position the textbox over the text part of the combo box and in fromt of
the combo

Now, when the combo does not have the focus, the textbox will show the part
number/description, but as soon as the combo gets the focus it will appear
in front and allow you to choose a part from those listed for the selected
manufacturer.

Another suggestion:

Since the combo is on a subform, I suggest you respecify the rowsource when
the manufacturer changes, instead of just requerying the combo. This gets
around the problem of referring to a control on a subform, and is especially
important if the subform appears on more than one form.

Create a saved query something like this:

SELECT PartIDNumber, ManufacturerPartNumber,
Manufacturer from PartsTable order by ManufacturerPartNumber;

Save it as qryListPartsByManufacturer.

Now, in your manufacturer combo's AfterUpdate procedure, instead of saying
PartIDNumber.Requery, use this code:
PartIDNumber.RowSource = "Select * from " _
& "qryListPartsByManufacturer where Manufacturer=" _
& cboSelectPartManufacturer

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.


Pam Hisaw said:
Graham,

Thanks for the help and speedy reply. I had checked and rechecked all names
and just thought it was something in the expressions used. I not good with
expressions. Anyway, after you confirmed expression should work, I did more
research and the main form is actually a subform. After the correct
expression for a subform, it worked!!

Now I have one more problem. When this works, it takes out all the part
numbers for all my previous jobs. Is there a way to make this work without
affecting previous jobs and only work on new jobs?

Again your help is greatly appreciated!!

Thanks,
Pam


Graham Mandeno said:
Hi Pam

What you are doing seems to be correct. It the query parameter dialog pops
up, then it means that Access can't resolve the reference to
Forms!fQuoteMainForm!Combo59. You should double-check that:
1) the name of the form is indeed "fQuoteMainForm"
2) the name of the combo is indeed "Combo59" (incidentally, I suggest
renaming it to something meaningful like cboSelectPartManufacturer)

If it all seems OK, then while the form is open, press Ctrl-G to show the
debug window and type:
?Forms!fQuoteMainForm!Combo59<enter>

It should display the current manufacturer number. If not, it might
Click to expand...
give
a
clue as to what is wrong.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.


Pam Hisaw said:
I have a combo box (Combo59) on a main form that I want to enter a
manufacturer name to and have it filter only those part numbers to another
combo box (PartIDNumber) on a subreport.

Part ID Number combo box has this statement in the Row Source:

SELECT qComboQuotePartsSubformParts.PartIDNumber,
qComboQuotePartsSubformParts.ManufacturerPartNumber,
qComboQuotePartsSubformParts.Manufacturer,
qComboQuotePartsSubformParts.Item, qComboQuotePartsSubformParts.ListPrice
FROM qComboQuotePartsSubformParts WHERE
Manufacturer=Forms!fQuoteMainForm!Combo59;

Combo59 has in the After Update an event procedure to requery the
PartIDNumber combo box.

It seems to be working in a strange way. When I go to this form, a dialog
box pops up asking for a parameter of "Forms!fQuoteMainForm!Combo59".
Click to expand...
I
can
enter a manufacturer in this box and it does what I want. If I go to
Combo59 and enter a different manufacturer, there are no changes.

I also don't want to use a pop up dialog box because I may go into
Click to expand...
this
form
many times for different reasons other than selecting part numbers.

I know this is lengthy, but I wanted to make it as clear as possible. Any
help would be greatly appreciated.

Thanks,
Pam
Click to expand...
Click to expand...
Click to expand...
 
Back
Top