Repost - I really need help here, as I have several forms that need combo boxes filtered like this!

[Mike Matheny]

I am trying to populate a combo box with filtered names. Here is the deal -
I have a table named CustEquipment, that has a field named CtagID and one
named CID. This table has lots of equipment listed, all differentiated by
the CID (company ID) I am trying to get the combo box to only give the
equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer Info,
Hardware, Software, etc.On the main form, I have a Customer Name combo box
that selects all the customer names. I have placed a CID field on all the
subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated with
the correct info for that company via the CID field. On one of the subforms
on the tab control (actually I haven't tried this on the other ones yet -
this is the one I am working on right now) I have a combo box that I want to
populate with only the CtagIDs of the currently selected CID. The CID field
is being populated correctly with the CID of the currently selected company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID! If I enter the
correct CID, the combo box is populated correctly, but it never again
prompts me for the CID even when I change Company Names. I have other fields
on other subforms that are date fields, and when I double click on them and
pop up a calendar, I am able to populate the date field with the contents of
the calendar control by using Me.LastDate or Me.DueDate.

I'm sure it is me, so where am I going wrong?

 

[tina]

try
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Forms!frmMain!SubformControlName!CID

 

[Mike Matheny]

That just brings up a blank combo box. It looks like it should work, but it
isn't! Any other ideas? (BTW, thanx for this one!)

 

[Graham Mandeno]

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the Customers
table, and also has a CID control somewhere. Right? This means that you
want the rowsource for the combo on the subform to change when the main form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & Me!CID & "'"

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

 

[Mike Matheny]

The selection criteria is not changing - I am still in the same CID as I
have been - I just want to populate the CtagID combo box with CtagIDs that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the Customers
table, and also has a CID control somewhere. Right? This means that you
want the rowsource for the combo on the subform to change when the main form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the deal -
I have a table named CustEquipment, that has a field named CtagID and one
named CID. This table has lots of equipment listed, all differentiated by
the CID (company ID) I am trying to get the combo box to only give the
equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer Info,
Hardware, Software, etc.On the main form, I have a Customer Name combo box
that selects all the customer names. I have placed a CID field on all the
subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated with
the correct info for that company via the CID field. On one of the subforms
on the tab control (actually I haven't tried this on the other ones yet -
this is the one I am working on right now) I have a combo box that I

want

to
populate with only the CtagIDs of the currently selected CID. The CID field
is being populated correctly with the CID of the currently selected company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID! If I enter the
correct CID, the combo box is populated correctly, but it never again
prompts me for the CID even when I change Company Names. I have other fields
on other subforms that are date fields, and when I double click on them and
pop up a calendar, I am able to populate the date field with the

contents

of
the calendar control by using Me.LastDate or Me.DueDate.

I'm sure it is me, so where am I going wrong?

 

[Neil]

Mike,

Whenever you change the rowsource of a combobox, you should requery to
update the changes. Put the following line of code in the AfterUpdate event
of any combo box that is changing the rowsource of another:

Me.ComboBoxName.Requery

Note that ComboBoxName is the combobox that you are trying to change (and
you may need to update more than 1).

HTH,

Neil.

The selection criteria is not changing - I am still in the same CID as I
have been - I just want to populate the CtagID combo box with CtagIDs that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the Customers
table, and also has a CID control somewhere. Right? This means that you
want the rowsource for the combo on the subform to change when the main form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the deal -
I have a table named CustEquipment, that has a field named CtagID and one
named CID. This table has lots of equipment listed, all differentiated by
the CID (company ID) I am trying to get the combo box to only give the
equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer Info,
Hardware, Software, etc.On the main form, I have a Customer Name combo box
that selects all the customer names. I have placed a CID field on all the
subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated with
the correct info for that company via the CID field. On one of the subforms
on the tab control (actually I haven't tried this on the other ones yet -
this is the one I am working on right now) I have a combo box that I

want

to
populate with only the CtagIDs of the currently selected CID. The CID field
is being populated correctly with the CID of the currently selected company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID! If I

enter
the

correct CID, the combo box is populated correctly, but it never again
prompts me for the CID even when I change Company Names. I have other fields
on other subforms that are date fields, and when I double click on

them
and

pop up a calendar, I am able to populate the date field with the

contents

of
the calendar control by using Me.LastDate or Me.DueDate.

I'm sure it is me, so where am I going wrong?

 

[Mike Matheny]

This is still not working. I get a blank combo box. Just to reiterate, I
have a table, CustMaster, that contains customer contact info, and a primary
key named CID that is indexed and does not allow dupes.
I have another table, CustEquipment, that contains all the customer
hardware, distinguished by the CID.
I have a subform, subfrmPMCustEquip, that has a combo box that when I pull
it down, I want it to retreive ONLY the customer equipment for the CID of
the current customer I am working with. The subfrmPMCustEquip has a text box
named CID that contains the correct CID I want to filter with. I have not
been able to use the data in the CID text box to create a query yet.

Here is the path of the CID box I want to use in the query
frmMain -> subfrmPMCustEquip -> CID
Likewise, here is the combobox I am wanting to do the filtered selection in
frmMain -> subfrmPMCustEquip -> CtagID
--

Mike Matheny

Mike,

Whenever you change the rowsource of a combobox, you should requery to
update the changes. Put the following line of code in the AfterUpdate event
of any combo box that is changing the rowsource of another:

Me.ComboBoxName.Requery

Note that ComboBoxName is the combobox that you are trying to change (and
you may need to update more than 1).

HTH,

Neil.

The selection criteria is not changing - I am still in the same CID as I
have been - I just want to populate the CtagID combo box with CtagIDs that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the Customers
table, and also has a CID control somewhere. Right? This means that you
want the rowsource for the combo on the subform to change when the

main
form

record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the
deal -
I have a table named CustEquipment, that has a field named CtagID

and
one

named CID. This table has lots of equipment listed, all

differentiated
by

the CID (company ID) I am trying to get the combo box to only give the
equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer Info,
Hardware, Software, etc.On the main form, I have a Customer Name

combo
box

that selects all the customer names. I have placed a CID field on

all
the

subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated with
the correct info for that company via the CID field. On one of the
subforms
on the tab control (actually I haven't tried this on the other ones yet -
this is the one I am working on right now) I have a combo box that I want
to
populate with only the CtagIDs of the currently selected CID. The CID
field
is being populated correctly with the CID of the currently selected
company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID! If I enter
the
correct CID, the combo box is populated correctly, but it never again
prompts me for the CID even when I change Company Names. I have other
fields
on other subforms that are date fields, and when I double click on them
and
pop up a calendar, I am able to populate the date field with the contents
of
the calendar control by using Me.LastDate or Me.DueDate.

I'm sure it is me, so where am I going wrong?

 

[Neil]

Goto the properties of the combobox on the subform. In the RowSource (under
the 'Data' tab), click the 3 dots on the right hand side to open the "SQL
Statement : Query Builder". Add the table where you are getting the
information from (should be the subform table) and drop the required fields
from the table into the fields section (Put the CTagID field as the first
column and then the name that you want to display as the second column). In
the Criteria row for the CTagID (as far as I can see this is the field you
use in the subform to link to the main form) enter
Forms!MainFormName!CIDName. Close down the query builder (and save the
changes). Once again, in the properties of the combobox, goto the 'Format'
tab end enter the number of columns you added into the RowSource (2 if it
was only the ID field and a name field) into the Column Count property. Then
set the Column Widths property making sure the first one is 0 (e.g.
0cm;5cm). This should hopefully display only the names of the customer
equipment but everything will be linked together so you only have to requery
the combobox when you need to refresh the list.

The above assumes that you have a textbox in the main form that has it's
control source to be the CID field in CustMaster (if not add one, you can
always set it's visible property to be false if you do nat want the user to
see this information) and CIDName is the name you give to this textbox.

HTH,

Neil.

This is still not working. I get a blank combo box. Just to reiterate, I
have a table, CustMaster, that contains customer contact info, and a primary
key named CID that is indexed and does not allow dupes.
I have another table, CustEquipment, that contains all the customer
hardware, distinguished by the CID.
I have a subform, subfrmPMCustEquip, that has a combo box that when I pull
it down, I want it to retreive ONLY the customer equipment for the CID of
the current customer I am working with. The subfrmPMCustEquip has a text box
named CID that contains the correct CID I want to filter with. I have not
been able to use the data in the CID text box to create a query yet.

Here is the path of the CID box I want to use in the query
frmMain -> subfrmPMCustEquip -> CID
Likewise, here is the combobox I am wanting to do the filtered selection in
frmMain -> subfrmPMCustEquip -> CtagID
--

Mike Matheny

Mike,

Whenever you change the rowsource of a combobox, you should requery to
update the changes. Put the following line of code in the AfterUpdate event
of any combo box that is changing the rowsource of another:

Me.ComboBoxName.Requery

Note that ComboBoxName is the combobox that you are trying to change (and
you may need to update more than 1).

HTH,

Neil.

The selection criteria is not changing - I am still in the same CID as I
have been - I just want to populate the CtagID combo box with CtagIDs that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the
Customers
table, and also has a CID control somewhere. Right? This means

that
you

want the rowsource for the combo on the subform to change when the main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the
deal -
I have a table named CustEquipment, that has a field named CtagID and
one
named CID. This table has lots of equipment listed, all differentiated
by
the CID (company ID) I am trying to get the combo box to only give the
equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer Info,
Hardware, Software, etc.On the main form, I have a Customer Name combo
box
that selects all the customer names. I have placed a CID field on all
the
subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated
with
the correct info for that company via the CID field. On one of the
subforms
on the tab control (actually I haven't tried this on the other ones
yet -
this is the one I am working on right now) I have a combo box that I
want
to
populate with only the CtagIDs of the currently selected CID. The CID
field
is being populated correctly with the CID of the currently selected
company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID! If I enter
the
correct CID, the combo box is populated correctly, but it never again
prompts me for the CID even when I change Company Names. I have other
fields
on other subforms that are date fields, and when I double click on them
and
pop up a calendar, I am able to populate the date field with the
contents
of
the calendar control by using Me.LastDate or Me.DueDate.

I'm sure it is me, so where am I going wrong?

 

[Mike Matheny]

Tried that and it doesn't work - blank combo box. Here is the SQL statement
it built, which again looks like it would work:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
(((CustEquipment.CID)=Forms!frmMain!CID));

I added a text box to the frmMain and set it's control source to CID, and it
updates with the correct CID for the company I select via another combo box.
It just ins't working, and as dumb as I am, it certainly looks like it
should!

--

Mike Matheny

Goto the properties of the combobox on the subform. In the RowSource (under
the 'Data' tab), click the 3 dots on the right hand side to open the "SQL
Statement : Query Builder". Add the table where you are getting the
information from (should be the subform table) and drop the required fields
from the table into the fields section (Put the CTagID field as the first
column and then the name that you want to display as the second column). In
the Criteria row for the CTagID (as far as I can see this is the field you
use in the subform to link to the main form) enter
Forms!MainFormName!CIDName. Close down the query builder (and save the
changes). Once again, in the properties of the combobox, goto the 'Format'
tab end enter the number of columns you added into the RowSource (2 if it
was only the ID field and a name field) into the Column Count property. Then
set the Column Widths property making sure the first one is 0 (e.g.
0cm;5cm). This should hopefully display only the names of the customer
equipment but everything will be linked together so you only have to requery
the combobox when you need to refresh the list.

The above assumes that you have a textbox in the main form that has it's
control source to be the CID field in CustMaster (if not add one, you can
always set it's visible property to be false if you do nat want the user to
see this information) and CIDName is the name you give to this textbox.

HTH,

Neil.

This is still not working. I get a blank combo box. Just to reiterate, I
have a table, CustMaster, that contains customer contact info, and a primary
key named CID that is indexed and does not allow dupes.
I have another table, CustEquipment, that contains all the customer
hardware, distinguished by the CID.
I have a subform, subfrmPMCustEquip, that has a combo box that when I pull
it down, I want it to retreive ONLY the customer equipment for the CID of
the current customer I am working with. The subfrmPMCustEquip has a text box
named CID that contains the correct CID I want to filter with. I have not
been able to use the data in the CID text box to create a query yet.

Here is the path of the CID box I want to use in the query
frmMain -> subfrmPMCustEquip -> CID
Likewise, here is the combobox I am wanting to do the filtered selection in
frmMain -> subfrmPMCustEquip -> CtagID

as

I

have been - I just want to populate the CtagID combo box with

CtagIDs
that

have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the
Customers
table, and also has a CID control somewhere. Right? This means that
you
want the rowsource for the combo on the subform to change when the main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the
deal -
I have a table named CustEquipment, that has a field named

CtagID
and

one
named CID. This table has lots of equipment listed, all differentiated
by
the CID (company ID) I am trying to get the combo box to only

give
the

equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer
Info,
Hardware, Software, etc.On the main form, I have a Customer Name combo
box
that selects all the customer names. I have placed a CID field

on
all

the
subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated
with
the correct info for that company via the CID field. On one of the
subforms
on the tab control (actually I haven't tried this on the other ones
yet -
this is the one I am working on right now) I have a combo box

that

I The
CID

 

[Graham Mandeno]

But the selection criterion *is* changing. As you navigate from one
customer record to another the value of the CID changes, so the filtered
recordset for your combo changes also.

Have you tried what I suggested?

Basically you have a choice between (1) using the method I've given you and
respecifying the rowsource when the selection filter value changes, and (2)
specifying the form control as the selection filter value. With option (2)
you still need to requery the combo (as Neil pointed out) when the form
control value changes, because the rowsource query has no way of knowing
that the change has occurred. So you *still* need special code in (usually)
your Form_Current procedure. If you add to that the difficulty associated
with referring to controls on subforms in a way that SQL understands (and
particularly if your subform is used on more than one main form), option (1)
is the winner for me every time.

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

The selection criteria is not changing - I am still in the same CID as I
have been - I just want to populate the CtagID combo box with CtagIDs that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the Customers
table, and also has a CID control somewhere. Right? This means that you
want the rowsource for the combo on the subform to change when the main form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the deal -
I have a table named CustEquipment, that has a field named CtagID and one
named CID. This table has lots of equipment listed, all differentiated by
the CID (company ID) I am trying to get the combo box to only give the
equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer Info,
Hardware, Software, etc.On the main form, I have a Customer Name combo box
that selects all the customer names. I have placed a CID field on all the
subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated with
the correct info for that company via the CID field. On one of the subforms
on the tab control (actually I haven't tried this on the other ones yet -
this is the one I am working on right now) I have a combo box that I

want

to
populate with only the CtagIDs of the currently selected CID. The CID field
is being populated correctly with the CID of the currently selected company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID! If I

enter
the

correct CID, the combo box is populated correctly, but it never again
prompts me for the CID even when I change Company Names. I have other fields
on other subforms that are date fields, and when I double click on

them
and

pop up a calendar, I am able to populate the date field with the

contents

of
the calendar control by using Me.LastDate or Me.DueDate.

I'm sure it is me, so where am I going wrong?

 

[Mike Matheny]

I'm sorry - I select the company from a combo box on frmMain. Then the
following code executes after I make the selection:
Private Sub cboCompName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CID] = " & Str(Nz(Me![cboCompName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This updates all the subforms that are on different pages of the tab control
that is on frmMain. So, I fire up the DB and select a company - all info on
all pages of the tab control reflect the info for the company I have
selected. Now, on one of the pages of the tab control, the subform
subfrmPMCustEquip resides. I have several combo boxes on the form that I
pick different equipment from. If I don't put any thing in the rowsource, it
selects all the equipment from the custequip table, regardless of the CID,
which is what I would expect. What I want to do is filter ONLY the equipment
in the custequip table that has matching corresponding CID of the company I
originally selected from the frmMain combo box. This is the sticking point!
Each and every form and subform has a textbox named CID that gets populated
with the CID of the currently selected company, and it is populated
correctly. Just can't figure out how to use the data of that textbox in a
query that will work - either it brings up a blank combo box, or I get a box
asking for the CID, which if I put in the correct number, filters only the
equipment from the currently selected company.


--

Mike Matheny

But the selection criterion *is* changing. As you navigate from one
customer record to another the value of the CID changes, so the filtered
recordset for your combo changes also.

Have you tried what I suggested?

Basically you have a choice between (1) using the method I've given you and
respecifying the rowsource when the selection filter value changes, and (2)
specifying the form control as the selection filter value. With option (2)
you still need to requery the combo (as Neil pointed out) when the form
control value changes, because the rowsource query has no way of knowing
that the change has occurred. So you *still* need special code in (usually)
your Form_Current procedure. If you add to that the difficulty associated
with referring to controls on subforms in a way that SQL understands (and
particularly if your subform is used on more than one main form), option (1)
is the winner for me every time.

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

The selection criteria is not changing - I am still in the same CID as I
have been - I just want to populate the CtagID combo box with CtagIDs that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the Customers
table, and also has a CID control somewhere. Right? This means that you
want the rowsource for the combo on the subform to change when the

main
form

record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the
deal -
I have a table named CustEquipment, that has a field named CtagID

and
one

named CID. This table has lots of equipment listed, all

differentiated
by

the CID (company ID) I am trying to get the combo box to only give the
equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer Info,
Hardware, Software, etc.On the main form, I have a Customer Name

combo
box

that selects all the customer names. I have placed a CID field on

all
the

subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated with
the correct info for that company via the CID field. On one of the
subforms
on the tab control (actually I haven't tried this on the other ones yet -
this is the one I am working on right now) I have a combo box that I want
to
populate with only the CtagIDs of the currently selected CID. The CID
field
is being populated correctly with the CID of the currently selected
company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID! If I enter
the
correct CID, the combo box is populated correctly, but it never again
prompts me for the CID even when I change Company Names. I have other
fields
on other subforms that are date fields, and when I double click on them
and
pop up a calendar, I am able to populate the date field with the contents
of
the calendar control by using Me.LastDate or Me.DueDate.

I'm sure it is me, so where am I going wrong?

 

[tina]

what you're trying to do is pretty common, so yes it
should be doable. i've read all the posts, and everyone
seems to be communicating clearly, but...
if you're still struggling with this, mike, you're welcome
to email me a copy of your db - compacted and zipped to
under 2 MB - if you like; i'll try to fix it up for you
and send it back so you can see how to do it. if you want
to do this, you'll need to use my REAL email address:
(e-mail address removed)
btw, i'm on Access 2k, so if your db is 2002 you'd have to
convert down to 2k before sending; a 97 db i can convert
up myself, and then back down when done.

-----Original Message-----
Tried that and it doesn't work - blank combo box. Here is the SQL statement
it built, which again looks like it would work:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
(((CustEquipment.CID)=Forms!frmMain!CID));

I added a text box to the frmMain and set it's control source to CID, and it
updates with the correct CID for the company I select via another combo box.
It just ins't working, and as dumb as I am, it certainly looks like it
should!

--

Mike Matheny

Goto the properties of the combobox on the subform. In

the RowSource
(under

the 'Data' tab), click the 3 dots on the right hand side to open the "SQL
Statement : Query Builder". Add the table where you are getting the
information from (should be the subform table) and drop

the required
fields

from the table into the fields section (Put the CTagID field as the first
column and then the name that you want to display as

the second column).
In

the Criteria row for the CTagID (as far as I can see this is the field you
use in the subform to link to the main form) enter
Forms!MainFormName!CIDName. Close down the query builder (and save the
changes). Once again, in the properties of the combobox, goto the 'Format'
tab end enter the number of columns you added into the RowSource (2 if it
was only the ID field and a name field) into the Column

Count property.
Then

set the Column Widths property making sure the first one is 0 (e.g.
0cm;5cm). This should hopefully display only the names of the customer
equipment but everything will be linked together so you

only have to
requery

the combobox when you need to refresh the list.

The above assumes that you have a textbox in the main form that has it's
control source to be the CID field in CustMaster (if not add one, you can
always set it's visible property to be false if you do

nat want the user
to

see this information) and CIDName is the name you give to this textbox.

HTH,

Neil.

contact info, and a
primary

box that when I
pull equipment for the CID
of

subfrmPMCustEquip has a text
box

filter with. I have
not

filtered selection
in trying to change
(and

still in the same CID
as

I

combo box with
CtagIDs

that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the
selection
criteria change, rather than relying on a reference back to a form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the
Customers
table, and also has a CID control somewhere.

Right? This means
that

you
want the rowsource for the combo on the subform to change when the
main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text

then you will need
quotes

around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with

filtered names. Here is
the

deal -
I have a table named CustEquipment, that has

a field named
CtagID combo box to only
give subforms, such as
Customer placed a CID field
on

select a customer in
the the subforms is
updated

field. On one of
the

this on the other
ones

have a combo box
that

I

currently selected CID.
The

the currently
selected

me for the CID! If
I when I double click
on

.

 

[Graham Mandeno]

Hi Mike

From your description, [CID] is clearly present in the recordsource of your
main form, so that makes life a bit simpler. You can either:

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

....and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Note that in both cases, "subfrmPMCustEquip" is the name of the control
which *contains* the subform (which is not necessarily the same as the name
of the form object in the database window).

Don't give up - we'll get this one sorted
--
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.

I'm sorry - I select the company from a combo box on frmMain. Then the
following code executes after I make the selection:
Private Sub cboCompName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CID] = " & Str(Nz(Me![cboCompName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This updates all the subforms that are on different pages of the tab control
that is on frmMain. So, I fire up the DB and select a company - all info on
all pages of the tab control reflect the info for the company I have
selected. Now, on one of the pages of the tab control, the subform
subfrmPMCustEquip resides. I have several combo boxes on the form that I
pick different equipment from. If I don't put any thing in the rowsource, it
selects all the equipment from the custequip table, regardless of the CID,
which is what I would expect. What I want to do is filter ONLY the equipment
in the custequip table that has matching corresponding CID of the company I
originally selected from the frmMain combo box. This is the sticking point!
Each and every form and subform has a textbox named CID that gets populated
with the CID of the currently selected company, and it is populated
correctly. Just can't figure out how to use the data of that textbox in a
query that will work - either it brings up a blank combo box, or I get a box
asking for the CID, which if I put in the correct number, filters only the
equipment from the currently selected company.


--

Mike Matheny

But the selection criterion *is* changing. As you navigate from one
customer record to another the value of the CID changes, so the filtered
recordset for your combo changes also.

Have you tried what I suggested?

Basically you have a choice between (1) using the method I've given you and
respecifying the rowsource when the selection filter value changes, and (2)
specifying the form control as the selection filter value. With option (2)
you still need to requery the combo (as Neil pointed out) when the form
control value changes, because the rowsource query has no way of knowing
that the change has occurred. So you *still* need special code in (usually)
your Form_Current procedure. If you add to that the difficulty associated
with referring to controls on subforms in a way that SQL understands (and
particularly if your subform is used on more than one main form), option (1)
is the winner for me every time.

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

The selection criteria is not changing - I am still in the same CID as I
have been - I just want to populate the CtagID combo box with CtagIDs that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the
Customers
table, and also has a CID control somewhere. Right? This means

that
you

want the rowsource for the combo on the subform to change when the main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the
deal -
I have a table named CustEquipment, that has a field named CtagID and
one
named CID. This table has lots of equipment listed, all differentiated
by
the CID (company ID) I am trying to get the combo box to only give the
equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer Info,
Hardware, Software, etc.On the main form, I have a Customer Name combo
box
that selects all the customer names. I have placed a CID field on all
the
subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated
with
the correct info for that company via the CID field. On one of the
subforms
on the tab control (actually I haven't tried this on the other ones
yet -
this is the one I am working on right now) I have a combo box that I
want
to
populate with only the CtagIDs of the currently selected CID. The CID
field
is being populated correctly with the CID of the currently selected
company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID! If I enter
the
correct CID, the combo box is populated correctly, but it never again
prompts me for the CID even when I change Company Names. I have other
fields
on other subforms that are date fields, and when I double click on them
and
pop up a calendar, I am able to populate the date field with the
contents
of
the calendar control by using Me.LastDate or Me.DueDate.

I'm sure it is me, so where am I going wrong?

 

[Mike Matheny]

OK the name of the control that contains the subform (a tab control) is:
TabCtl0

Is that where I am steering people wrong? So, here is the hierarchy:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> CID is the text box that contains the CID
I want to filter with.

Thanx so much for all your comments.
--

Mike Matheny

Hi Mike

From your description, [CID] is clearly present in the recordsource of your
main form, so that makes life a bit simpler. You can either:

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Note that in both cases, "subfrmPMCustEquip" is the name of the control
which *contains* the subform (which is not necessarily the same as the name
of the form object in the database window).

Don't give up - we'll get this one sorted
--
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.

I'm sorry - I select the company from a combo box on frmMain. Then the
following code executes after I make the selection:
Private Sub cboCompName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CID] = " & Str(Nz(Me![cboCompName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This updates all the subforms that are on different pages of the tab control
that is on frmMain. So, I fire up the DB and select a company - all info on
all pages of the tab control reflect the info for the company I have
selected. Now, on one of the pages of the tab control, the subform
subfrmPMCustEquip resides. I have several combo boxes on the form that I
pick different equipment from. If I don't put any thing in the

rowsource,

it
selects all the equipment from the custequip table, regardless of the CID,
which is what I would expect. What I want to do is filter ONLY the equipment
in the custequip table that has matching corresponding CID of the

company

I
originally selected from the frmMain combo box. This is the sticking point!
Each and every form and subform has a textbox named CID that gets populated
with the CID of the currently selected company, and it is populated
correctly. Just can't figure out how to use the data of that textbox in a
query that will work - either it brings up a blank combo box, or I get a box
asking for the CID, which if I put in the correct number, filters only the
equipment from the currently selected company.


--

Mike Matheny

But the selection criterion *is* changing. As you navigate from one
customer record to another the value of the CID changes, so the filtered
recordset for your combo changes also.

Have you tried what I suggested?

Basically you have a choice between (1) using the method I've given

you
and

respecifying the rowsource when the selection filter value changes,

and
(2)

specifying the form control as the selection filter value. With

option
(2)

you still need to requery the combo (as Neil pointed out) when the form
control value changes, because the rowsource query has no way of knowing
that the change has occurred. So you *still* need special code in (usually)
your Form_Current procedure. If you add to that the difficulty associated
with referring to controls on subforms in a way that SQL understands (and
particularly if your subform is used on more than one main form),

option
(1)

is the winner for me every time.

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

The selection criteria is not changing - I am still in the same CID

as

I

have been - I just want to populate the CtagID combo box with

CtagIDs
that

have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the selection
criteria change, rather than relying on a reference back to a form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the
Customers
table, and also has a CID control somewhere. Right? This means that
you
want the rowsource for the combo on the subform to change when the main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here is the
deal -
I have a table named CustEquipment, that has a field named

CtagID
and

one
named CID. This table has lots of equipment listed, all differentiated
by
the CID (company ID) I am trying to get the combo box to only

give
the

equipment that is owned by a particular CID. I have a main form, named
frmMain, with a tab control and several subforms, such as Customer
Info,
Hardware, Software, etc.On the main form, I have a Customer Name combo
box
that selects all the customer names. I have placed a CID field

on
all

the
subforms for debugging purposes, and when I select a customer in the
Customer Name combo box, all the info in all the subforms is updated
with
the correct info for that company via the CID field. On one of the
subforms
on the tab control (actually I haven't tried this on the other ones
yet -
this is the one I am working on right now) I have a combo box

that

I The
CID

 

[Graham Mandeno]

Hi Mike

Thanks your your email - this one had dropped over my horizon

By "the control that contains the subform", I mean the *subform* control,
not the tab control or tab page control.

Subforms are database objects which exist in their own right (as you can see
in the database window) and you can open them as main forms if you want to.
However, when they are used as subforms, these database objects exist within
a container control called a subform control. The subforn control has a
SourceObject property, which is the name of the database object contained
therein.

Often the subform control and its SourceObject have the same name, but (in
the words of Gershwin) it ain't necessarily so. This is where nuch of the
confusion often arised with referring to subforms. You must use the name of
the subform *control*, not the name of the SourceObject.

Tab controls are interesting, because each tab control has a Pages
collection and each Page has a Controls collection, and this hierarchy is
traceable by following the Parent properties of a control on a tab page.
However, controls on on tab pages are also members of the form's Controls
collection, so it's much easier to refer to:
frmMain!CID
instead of:
frmMain!TabCtl0.Pages!pgPMSched.Controls!CID
even though they are the same thing.

Now, my question:
Have you actually tried one or both of the methods I suggested?

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and
requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Actually, looking at your reply below:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box
I want to populate and...

....it looks like your combo is not on a subform at all. Is this correct?

This means you simply requery by saying:
Me.CtagID.Requery

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

OK the name of the control that contains the subform (a tab control) is:
TabCtl0

Is that where I am steering people wrong? So, here is the hierarchy:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> CID is the text box that contains the CID
I want to filter with.

Thanx so much for all your comments.
--

Mike Matheny

Hi Mike

From your description, [CID] is clearly present in the recordsource of your
main form, so that makes life a bit simpler. You can either:

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Note that in both cases, "subfrmPMCustEquip" is the name of the control
which *contains* the subform (which is not necessarily the same as the name
of the form object in the database window).

Don't give up - we'll get this one sorted
--
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.

I'm sorry - I select the company from a combo box on frmMain. Then the
following code executes after I make the selection:
Private Sub cboCompName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CID] = " & Str(Nz(Me![cboCompName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This updates all the subforms that are on different pages of the tab control
that is on frmMain. So, I fire up the DB and select a company - all

info
on

all pages of the tab control reflect the info for the company I have
selected. Now, on one of the pages of the tab control, the subform
subfrmPMCustEquip resides. I have several combo boxes on the form that I
pick different equipment from. If I don't put any thing in the

rowsource,

it
selects all the equipment from the custequip table, regardless of the CID,
which is what I would expect. What I want to do is filter ONLY the equipment
in the custequip table that has matching corresponding CID of the

company

I
originally selected from the frmMain combo box. This is the sticking point!
Each and every form and subform has a textbox named CID that gets populated
with the CID of the currently selected company, and it is populated
correctly. Just can't figure out how to use the data of that textbox

in

a

query that will work - either it brings up a blank combo box, or I get

a
box

asking for the CID, which if I put in the correct number, filters only the
equipment from the currently selected company.


--

Mike Matheny

But the selection criterion *is* changing. As you navigate from one
customer record to another the value of the CID changes, so the filtered
recordset for your combo changes also.

Have you tried what I suggested?

Basically you have a choice between (1) using the method I've given you
and
respecifying the rowsource when the selection filter value changes, and
(2)
specifying the form control as the selection filter value. With option
(2)
you still need to requery the combo (as Neil pointed out) when the form
control value changes, because the rowsource query has no way of knowing
that the change has occurred. So you *still* need special code in
(usually)
your Form_Current procedure. If you add to that the difficulty associated
with referring to controls on subforms in a way that SQL understands (and
particularly if your subform is used on more than one main form), option
(1)
is the winner for me every time.

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

The selection criteria is not changing - I am still in the same

CID

as

I
have been - I just want to populate the CtagID combo box with CtagIDs
that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the
selection
criteria change, rather than relying on a reference back to a form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the
Customers
table, and also has a CID control somewhere. Right? This means that
you
want the rowsource for the combo on the subform to change when the
main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here

is
the

deal -
I have a table named CustEquipment, that has a field named CtagID
and
one
named CID. This table has lots of equipment listed, all
differentiated
by
the CID (company ID) I am trying to get the combo box to only give
the
equipment that is owned by a particular CID. I have a main form,
named
frmMain, with a tab control and several subforms, such as Customer
Info,
Hardware, Software, etc.On the main form, I have a Customer Name
combo
box
that selects all the customer names. I have placed a CID field on
all
the
subforms for debugging purposes, and when I select a customer

in
the

Customer Name combo box, all the info in all the subforms is updated
with
the correct info for that company via the CID field. On one of the
subforms
on the tab control (actually I haven't tried this on the other ones
yet -
this is the one I am working on right now) I have a combo box

that

I
want
to
populate with only the CtagIDs of the currently selected CID. The
CID
field
is being populated correctly with the CID of the currently selected
company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for the CID!

If

I

click

on

 

[Mike Matheny]

Correction:
frmMain -> TabCtl0 -> pgPMSched -> subfrmPMCustEquip -> CtagID
is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> subfrmPMCustEquip -> CID
is the text box that contains the CID I want to filter with.

--

Mike Matheny

Hi Mike

Thanks your your email - this one had dropped over my horizon

By "the control that contains the subform", I mean the *subform* control,
not the tab control or tab page control.

Subforms are database objects which exist in their own right (as you can see
in the database window) and you can open them as main forms if you want to.
However, when they are used as subforms, these database objects exist within
a container control called a subform control. The subforn control has a
SourceObject property, which is the name of the database object contained
therein.

Often the subform control and its SourceObject have the same name, but (in
the words of Gershwin) it ain't necessarily so. This is where nuch of the
confusion often arised with referring to subforms. You must use the name of
the subform *control*, not the name of the SourceObject.

Tab controls are interesting, because each tab control has a Pages
collection and each Page has a Controls collection, and this hierarchy is
traceable by following the Parent properties of a control on a tab page.
However, controls on on tab pages are also members of the form's Controls
collection, so it's much easier to refer to:
frmMain!CID
instead of:
frmMain!TabCtl0.Pages!pgPMSched.Controls!CID
even though they are the same thing.

Now, my question:
Have you actually tried one or both of the methods I suggested?

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and
requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Actually, looking at your reply below:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box
I want to populate and...

...it looks like your combo is not on a subform at all. Is this correct?

This means you simply requery by saying:
Me.CtagID.Requery

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

OK the name of the control that contains the subform (a tab control) is:
TabCtl0

Is that where I am steering people wrong? So, here is the hierarchy:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> CID is the text box that contains the CID
I want to filter with.

Thanx so much for all your comments.
--

Mike Matheny

Hi Mike

From your description, [CID] is clearly present in the recordsource of your
main form, so that makes life a bit simpler. You can either:

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Note that in both cases, "subfrmPMCustEquip" is the name of the control
which *contains* the subform (which is not necessarily the same as the name
of the form object in the database window).

Don't give up - we'll get this one sorted
--
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.

I'm sorry - I select the company from a combo box on frmMain. Then the
following code executes after I make the selection:
Private Sub cboCompName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CID] = " & Str(Nz(Me![cboCompName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This updates all the subforms that are on different pages of the tab
control
that is on frmMain. So, I fire up the DB and select a company - all info
on
all pages of the tab control reflect the info for the company I have
selected. Now, on one of the pages of the tab control, the subform
subfrmPMCustEquip resides. I have several combo boxes on the form

that

I the
CID, in

get

a

box
asking for the CID, which if I put in the correct number, filters

only
the

equipment from the currently selected company.


--

Mike Matheny

But the selection criterion *is* changing. As you navigate from one
customer record to another the value of the CID changes, so the filtered
recordset for your combo changes also.

Have you tried what I suggested?

Basically you have a choice between (1) using the method I've

given
you

and
respecifying the rowsource when the selection filter value

changes,
and

(2)
specifying the form control as the selection filter value. With option
(2)
you still need to requery the combo (as Neil pointed out) when the form
control value changes, because the rowsource query has no way of knowing
that the change has occurred. So you *still* need special code in
(usually)
your Form_Current procedure. If you add to that the difficulty
associated
with referring to controls on subforms in a way that SQL understands
(and
particularly if your subform is used on more than one main form), option
(1)
is the winner for me every time.

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

The selection criteria is not changing - I am still in the same

CID

as
I
have been - I just want to populate the CtagID combo box with CtagIDs
that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the
selection
criteria change, rather than relying on a reference back to a form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to the
Customers
table, and also has a CID control somewhere. Right? This means
that
you
want the rowsource for the combo on the subform to change when the
main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need
quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names.

Here

is only
give field
on

customer

in of
the box
that CID.
The If click

 

[Graham Mandeno]

Mike, I don't believe you have tried my original suggestion. Please do,
before you send me further emails.

Now we have established that both the combo AND the CID filtering field are
on the subform, you simply need the following code in the *subform's*
Form_Current procedure:

CtagID.RowSource = _
"Select CTagID from CustEquipment where CID=" & CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & CID & "'"

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

Correction:
frmMain -> TabCtl0 -> pgPMSched -> subfrmPMCustEquip -> CtagID
is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> subfrmPMCustEquip -> CID
is the text box that contains the CID I want to filter with.

--

Mike Matheny

Hi Mike

Thanks your your email - this one had dropped over my horizon

By "the control that contains the subform", I mean the *subform* control,
not the tab control or tab page control.

Subforms are database objects which exist in their own right (as you can see
in the database window) and you can open them as main forms if you want to.
However, when they are used as subforms, these database objects exist within
a container control called a subform control. The subforn control has a
SourceObject property, which is the name of the database object contained
therein.

Often the subform control and its SourceObject have the same name, but (in
the words of Gershwin) it ain't necessarily so. This is where nuch of the
confusion often arised with referring to subforms. You must use the

name

of
the subform *control*, not the name of the SourceObject.

Tab controls are interesting, because each tab control has a Pages
collection and each Page has a Controls collection, and this hierarchy is
traceable by following the Parent properties of a control on a tab page.
However, controls on on tab pages are also members of the form's Controls
collection, so it's much easier to refer to:
frmMain!CID
instead of:
frmMain!TabCtl0.Pages!pgPMSched.Controls!CID
even though they are the same thing.

Now, my question:
Have you actually tried one or both of the methods I suggested?

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and
requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Actually, looking at your reply below:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box
I want to populate and...

...it looks like your combo is not on a subform at all. Is this correct?

This means you simply requery by saying:
Me.CtagID.Requery

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

OK the name of the control that contains the subform (a tab control) is:
TabCtl0

Is that where I am steering people wrong? So, here is the hierarchy:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> CID is the text box that contains

the
CID

I want to filter with.

Thanx so much for all your comments.
--

Mike Matheny

Hi Mike

From your description, [CID] is clearly present in the recordsource of
your
main form, so that makes life a bit simpler. You can either:

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and
requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Note that in both cases, "subfrmPMCustEquip" is the name of the control
which *contains* the subform (which is not necessarily the same as the
name
of the form object in the database window).

Don't give up - we'll get this one sorted
--
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.

I'm sorry - I select the company from a combo box on frmMain. Then the
following code executes after I make the selection:
Private Sub cboCompName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CID] = " & Str(Nz(Me![cboCompName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This updates all the subforms that are on different pages of the tab
control
that is on frmMain. So, I fire up the DB and select a company -

all
info

on
all pages of the tab control reflect the info for the company I have
selected. Now, on one of the pages of the tab control, the subform
subfrmPMCustEquip resides. I have several combo boxes on the form

that

I
pick different equipment from. If I don't put any thing in the
rowsource,
it
selects all the equipment from the custequip table, regardless of the
CID,
which is what I would expect. What I want to do is filter ONLY the
equipment
in the custequip table that has matching corresponding CID of the
company
I
originally selected from the frmMain combo box. This is the sticking
point!
Each and every form and subform has a textbox named CID that gets
populated
with the CID of the currently selected company, and it is populated
correctly. Just can't figure out how to use the data of that

textbox
in

a
query that will work - either it brings up a blank combo box, or I

get

a
box
asking for the CID, which if I put in the correct number, filters only
the
equipment from the currently selected company.


--

Mike Matheny

But the selection criterion *is* changing. As you navigate from one
customer record to another the value of the CID changes, so the
filtered
recordset for your combo changes also.

Have you tried what I suggested?

Basically you have a choice between (1) using the method I've given
you
and
respecifying the rowsource when the selection filter value changes,
and
(2)
specifying the form control as the selection filter value. With
option
(2)
you still need to requery the combo (as Neil pointed out) when the
form
control value changes, because the rowsource query has no way of
knowing
that the change has occurred. So you *still* need special code in
(usually)
your Form_Current procedure. If you add to that the difficulty
associated
with referring to controls on subforms in a way that SQL understands
(and
particularly if your subform is used on more than one main form),
option
(1)
is the winner for me every time.

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

The selection criteria is not changing - I am still in the

same
CID

as
I
have been - I just want to populate the CtagID combo box with
CtagIDs
that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the
selection
criteria change, rather than relying on a reference back to

a
form

control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound

to
the

Customers
table, and also has a CID control somewhere. Right? This means
that
you
want the rowsource for the combo on the subform to change

when
the

main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need
quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names.

Here

is
the
deal -
I have a table named CustEquipment, that has a field named
CtagID
and
one
named CID. This table has lots of equipment listed, all
differentiated
by
the CID (company ID) I am trying to get the combo box to only
give
the
equipment that is owned by a particular CID. I have a main form,
named
frmMain, with a tab control and several subforms, such as
Customer
Info,
Hardware, Software, etc.On the main form, I have a

Customer
Name

combo
box
that selects all the customer names. I have placed a CID field
on
all
the
subforms for debugging purposes, and when I select a

customer

in
the
Customer Name combo box, all the info in all the subforms is
updated
with
the correct info for that company via the CID field. On

one

of CID!
If with
the

 

[Mike Matheny]

Sorry, I was just correcting the earlier info - you stated that it didn't
appear that the combo box was on a subform, and when I saw what I had said
earlier, I realized that I had erred in my statement. I will try and report
back.
Again, thanx much.

--

Mike Matheny

Mike, I don't believe you have tried my original suggestion. Please do,
before you send me further emails.

Now we have established that both the combo AND the CID filtering field are
on the subform, you simply need the following code in the *subform's*
Form_Current procedure:

CtagID.RowSource = _
"Select CTagID from CustEquipment where CID=" & CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & CID & "'"

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

Correction:
frmMain -> TabCtl0 -> pgPMSched -> subfrmPMCustEquip -> CtagID
is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> subfrmPMCustEquip -> CID
is the text box that contains the CID I want to filter with.

--

Mike Matheny

Hi Mike

Thanks your your email - this one had dropped over my horizon

By "the control that contains the subform", I mean the *subform* control,
not the tab control or tab page control.

Subforms are database objects which exist in their own right (as you

can
see

in the database window) and you can open them as main forms if you

want
to.

However, when they are used as subforms, these database objects exist within
a container control called a subform control. The subforn control has a
SourceObject property, which is the name of the database object contained
therein.

Often the subform control and its SourceObject have the same name, but (in
the words of Gershwin) it ain't necessarily so. This is where nuch of the
confusion often arised with referring to subforms. You must use the

name

of
the subform *control*, not the name of the SourceObject.

Tab controls are interesting, because each tab control has a Pages
collection and each Page has a Controls collection, and this hierarchy is
traceable by following the Parent properties of a control on a tab page.
However, controls on on tab pages are also members of the form's Controls
collection, so it's much easier to refer to:
frmMain!CID
instead of:
frmMain!TabCtl0.Pages!pgPMSched.Controls!CID
even though they are the same thing.

Now, my question:
Have you actually tried one or both of the methods I suggested?
1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and
requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Actually, looking at your reply below:
frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box
I want to populate and...
...it looks like your combo is not on a subform at all. Is this correct?

This means you simply requery by saying:
Me.CtagID.Requery

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

OK the name of the control that contains the subform (a tab control) is:
TabCtl0

Is that where I am steering people wrong? So, here is the hierarchy:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> CID is the text box that contains the
CID
I want to filter with.

Thanx so much for all your comments.
--

Mike Matheny

Hi Mike

From your description, [CID] is clearly present in the

recordsource

of

your
main form, so that makes life a bit simpler. You can either:

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and
requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Note that in both cases, "subfrmPMCustEquip" is the name of the control
which *contains* the subform (which is not necessarily the same as the
name
of the form object in the database window).

Don't give up - we'll get this one sorted
--
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.

I'm sorry - I select the company from a combo box on frmMain.

Then
the

following code executes after I make the selection:
Private Sub cboCompName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CID] = " & Str(Nz(Me![cboCompName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This updates all the subforms that are on different pages of the tab
control
that is on frmMain. So, I fire up the DB and select a company - all
info
on
all pages of the tab control reflect the info for the company I have
selected. Now, on one of the pages of the tab control, the subform
subfrmPMCustEquip resides. I have several combo boxes on the

form
that

I
pick different equipment from. If I don't put any thing in the
rowsource,
it
selects all the equipment from the custequip table, regardless

of
the

CID,
which is what I would expect. What I want to do is filter ONLY the
equipment
in the custequip table that has matching corresponding CID of the
company
I
originally selected from the frmMain combo box. This is the sticking
point!
Each and every form and subform has a textbox named CID that gets
populated
with the CID of the currently selected company, and it is populated
correctly. Just can't figure out how to use the data of that textbox
in
a
query that will work - either it brings up a blank combo box, or

I
get

a
box
asking for the CID, which if I put in the correct number,

filters
only

the
equipment from the currently selected company.


--

Mike Matheny

But the selection criterion *is* changing. As you navigate

from
one

customer record to another the value of the CID changes, so the
filtered
recordset for your combo changes also.

Have you tried what I suggested?

Basically you have a choice between (1) using the method I've given
you
and
respecifying the rowsource when the selection filter value changes,
and
(2)
specifying the form control as the selection filter value. With
option
(2)
you still need to requery the combo (as Neil pointed out) when the
form
control value changes, because the rowsource query has no way of
knowing
that the change has occurred. So you *still* need special

code

in

(usually)
your Form_Current procedure. If you add to that the difficulty
associated
with referring to controls on subforms in a way that SQL understands
(and
particularly if your subform is used on more than one main form),
option
(1)
is the winner for me every time.

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

The selection criteria is not changing - I am still in the same
CID
as
I
have been - I just want to populate the CtagID combo box with
CtagIDs
that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource when the
selection
criteria change, rather than relying on a reference back

to

a

form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is bound to
the
Customers
table, and also has a CID control somewhere. Right? This means
that
you
want the rowsource for the combo on the subform to change when
the
main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you will need
quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names. Here
is
the
deal -
I have a table named CustEquipment, that has a field named
CtagID
and
one
named CID. This table has lots of equipment listed, all
differentiated
by
the CID (company ID) I am trying to get the combo box to only
give
the
equipment that is owned by a particular CID. I have a main
form,
named
frmMain, with a tab control and several subforms, such as
Customer
Info,
Hardware, Software, etc.On the main form, I have a Customer
Name
combo
box
that selects all the customer names. I have placed a CID field
on
all
the
subforms for debugging purposes, and when I select a customer
in
the
Customer Name combo box, all the info in all the

subforms

is one combo
box

 

[Mike Matheny]

Thanx, that worked, but why do I need to code it into the form_current
event, rather than placing it in the record source in the data properties of
the combo box?

--

Mike Matheny

Sorry, I was just correcting the earlier info - you stated that it didn't
appear that the combo box was on a subform, and when I saw what I had said
earlier, I realized that I had erred in my statement. I will try and report
back.
Again, thanx much.

--

Mike Matheny

Mike, I don't believe you have tried my original suggestion. Please do,
before you send me further emails.

Now we have established that both the combo AND the CID filtering field are
on the subform, you simply need the following code in the *subform's*
Form_Current procedure:

CtagID.RowSource = _
"Select CTagID from CustEquipment where CID=" & CID

This assumes CID is numeric. If it is text then you will need quotes around
it:
"Select ... where CID='" & CID & "'"

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

Correction:
frmMain -> TabCtl0 -> pgPMSched -> subfrmPMCustEquip -> CtagID
is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> subfrmPMCustEquip -> CID
is the text box that contains the CID I want to filter with.

--

Mike Matheny

Hi Mike

Thanks your your email - this one had dropped over my horizon

By "the control that contains the subform", I mean the *subform* control,
not the tab control or tab page control.

Subforms are database objects which exist in their own right (as you can
see
in the database window) and you can open them as main forms if you want
to.
However, when they are used as subforms, these database objects exist
within
a container control called a subform control. The subforn control

has

a

SourceObject property, which is the name of the database object contained
therein.

Often the subform control and its SourceObject have the same name,

but
(in

the words of Gershwin) it ain't necessarily so. This is where nuch

of
the

confusion often arised with referring to subforms. You must use the name
of
the subform *control*, not the name of the SourceObject.

Tab controls are interesting, because each tab control has a Pages
collection and each Page has a Controls collection, and this

hierarchy
is

traceable by following the Parent properties of a control on a tab page.
However, controls on on tab pages are also members of the form's Controls
collection, so it's much easier to refer to:
frmMain!CID
instead of:
frmMain!TabCtl0.Pages!pgPMSched.Controls!CID
even though they are the same thing.

Now, my question:
Have you actually tried one or both of the methods I suggested?
1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and
requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Actually, looking at your reply below:
frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box
I want to populate and...
...it looks like your combo is not on a subform at all. Is this correct?

This means you simply requery by saying:
Me.CtagID.Requery

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

OK the name of the control that contains the subform (a tab

control)
is:

TabCtl0

Is that where I am steering people wrong? So, here is the hierarchy:

frmMain -> TabCtl0 -> pgPMSched -> CtagID is the combo box I want to
populate and
frmMain -> TabCtl0 -> pgPMSched -> CID is the text box that

contains
the

CID
I want to filter with.

Thanx so much for all your comments.
--

Mike Matheny

Hi Mike

From your description, [CID] is clearly present in the

recordsource

of
your
main form, so that makes life a bit simpler. You can either:

1) Respecify the RowSource in Form_Current for the main form:

Dim sSql as string
sSql = "Select CTagID from CustEquipment where CID=" & Me.CID
Me.subfrmPMCustEquip.Form.[name of combo].RowSource = sSql

or:
2) Refer the combo rowsource to the CID control on the main form and
requery
it in Form_Current:

RowSource: Select CTagID from CustEquipment where
CID=Forms!frmMain!CID

...and in Form_Current:
Me.subfrmPMCustEquip.Form.[name of combo].Requery

Note that in both cases, "subfrmPMCustEquip" is the name of the
control
which *contains* the subform (which is not necessarily the same

as
the

name
of the form object in the database window).

Don't give up - we'll get this one sorted
--
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.

I'm sorry - I select the company from a combo box on frmMain. Then
the
following code executes after I make the selection:
Private Sub cboCompName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CID] = " & Str(Nz(Me![cboCompName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This updates all the subforms that are on different pages of

the
tab

control
that is on frmMain. So, I fire up the DB and select a

company -
all

info
on
all pages of the tab control reflect the info for the company

I
have

selected. Now, on one of the pages of the tab control, the subform
subfrmPMCustEquip resides. I have several combo boxes on the form
that
I
pick different equipment from. If I don't put any thing in the
rowsource,
it
selects all the equipment from the custequip table, regardless of
the
CID,
which is what I would expect. What I want to do is filter ONLY the
equipment
in the custequip table that has matching corresponding CID of the
company
I
originally selected from the frmMain combo box. This is the sticking
point!
Each and every form and subform has a textbox named CID that gets
populated
with the CID of the currently selected company, and it is populated
correctly. Just can't figure out how to use the data of that textbox
in
a
query that will work - either it brings up a blank combo box,

or

I when
the

way

of

knowing
that the change has occurred. So you *still* need special

code

in
(usually)
your Form_Current procedure. If you add to that the difficulty
associated
with referring to controls on subforms in a way that SQL
understands
(and
particularly if your subform is used on more than one main form),
option
(1)
is the winner for me every time.

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

The selection criteria is not changing - I am still in the same
CID
as
I
have been - I just want to populate the CtagID combo box with
CtagIDs
that
have the same CID as the current CID I am working with.

--

Mike Matheny

Hi Mike

It's often better to respecify the combo's rowsource

when
the

selection
criteria change, rather than relying on a reference back

to

a
form
control.
This is especially so if the control is buried on a subform.

I gather from your description that the main form is

bound
to

the
Customers
table, and also has a CID control somewhere. Right? This
means
that
you
want the rowsource for the combo on the subform to

change
when

the
main
form
record changes. So, try this:

In Form_Current for the main form:

[Subform Control Name].Form![Combo Name].RowSource = _
"Select CTagID from CustEquipment where CID=" & Me!CID

This assumes CID is numeric. If it is text then you

will
need

quotes
around
it:
"Select ... where CID='" & Me!CID & "'"

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

I am trying to populate a combo box with filtered names.
Here
is
the
deal -
I have a table named CustEquipment, that has a field named
CtagID
and
one
named CID. This table has lots of equipment listed, all
differentiated
by
the CID (company ID) I am trying to get the combo box to
only
give
the
equipment that is owned by a particular CID. I have a main
form,
named
frmMain, with a tab control and several subforms, such as
Customer
Info,
Hardware, Software, etc.On the main form, I have a Customer
Name
combo
box
that selects all the customer names. I have placed a CID
field
on
all
the
subforms for debugging purposes, and when I select a
customer
in
the
Customer Name combo box, all the info in all the

subforms

is
updated
with
the correct info for that company via the CID field.

On
one

of
the
subforms
on the tab control (actually I haven't tried this on the
other
ones
yet -
this is the one I am working on right now) I have a combo
box
that
I
want
to
populate with only the CtagIDs of the currently selected
CID.
The
CID
field
is being populated correctly with the CID of the currently
selected
company.
The rowsource entry for the CtagID is:
SELECT CustEquipment.CtagID FROM CustEquipment WHERE
CustEquipment.CID=Me.CID;
Every time I click the combo box, it prompts me for

the
CID!

If
I
enter
the
correct CID, the combo box is populated correctly, but it
never
again
prompts me for the CID even when I change Company

Names.

I field
with

 

[Graham Mandeno]

Hi Mike

Thanx, that worked, but why do I need to code it into the form_current
event, rather than placing it in the record source in the data properties of
the combo box?

Because as you move from one record to the next, the CID value changes, and
so does the selection criterion for the combo rowsource. Therefore you must
requery the combo in the Current event procedure to refresh the list.