sub-form dependent combo box

[Huber57]

To all:

I have a subform that has two fields (comboboxes). The first is a list of
services. The second is a list of consultants who provide those services.
Through the posts on this forum and others (thanks much!) made the list of
consultants dependent on the type of service offered.

BUT ... Once I open up the form (with the subform in it), the first combobox
works fine, but the second one asks me to "Enter Parameter Value" and then
gives the Criteria that the second combobox uses to reference the first one.

Any thoughts?

Thanks in advance!

 

[Jeanette Cunningham]

Hi,
It would help if you post some details.
Are you using code in the after update event of the first combo or in the
enter event of the second combo?
Does the first have a hidden column with the ID of each service?
Does the second combo have a row source something like this?
SELECT tblConsultants.ID, tblConsultants.ConName
FROM tblConsultants INNER JOIN tblServices
ON tblServices.ServicesID = tblConsultants.ServicesID
WHERE tblConsultants.ServicesID = Me.FirstComboName

Jeanette Cunningham

 

[Huber57]

Jeanette:

The first Combobox (ServiceCombo)
Row Source:

SELECT Service_Table.Service
FROM Service_Table
GROUP BY Service_Table.Service;

AfterUpdate Event Procedure:

Private Sub ServiceCombo_AfterUpdate()
Me.ContractorCombo = ""
Me.ContractorCombo.Requery
End Sub

ContractorCombo Details:
Row Source

SELECT ServiceContractorQuery.ContractorName
FROM ServiceContractorQuery
WHERE
(((ServiceContractorQuery.Service)=[Forms]![ServiceContractorDate_Subform]![ServiceCombo]))
ORDER BY ServiceContractorQuery.ContractorName;

The subform is linked to the form via the ProjectName Field. This is listed
in both the "Link Child Fields" and "Link Master Fields".

It works perfectly when I just have the subform open. If the
ContractorCombo fails when I have the entire form open.

Let me know if you need anything else.

Thanks for your help.


Doug

 

[Jeanette Cunningham]

Delete the query from the row source for the Contractor combo on the
property dialog
Put this code in the after update for the first Combobox (ServiceCombo)

Private Sub ServiceCombo_AfterUpdate()
Dim strSQL as String

If Not IsNull(Me.ServiceCombo) Then
Me.ContractorCombo = ""
strSQL = "SELECT ServiceContractorQuery.ContractorName " _
& "FROM ServiceContractorQuery " _
& "WHERE " _
& "ServiceContractorQuery.Service='"" & Me.ServiceCombo & """ " _
& "ORDER BY ServiceContractorQuery.ContractorName"
Debug.Print SQL
Me.ContractorCombo.RowSource = strSQL
End If
End Sub

Jeanette Cunningham

Jeanette:

The first Combobox (ServiceCombo)
Row Source:

SELECT Service_Table.Service
FROM Service_Table
GROUP BY Service_Table.Service;

AfterUpdate Event Procedure:

Private Sub ServiceCombo_AfterUpdate()
Me.ContractorCombo = ""
Me.ContractorCombo.Requery
End Sub

ContractorCombo Details:
Row Source

SELECT ServiceContractorQuery.ContractorName
FROM ServiceContractorQuery
WHERE
(((ServiceContractorQuery.Service)=[Forms]![ServiceContractorDate_Subform]![ServiceCombo]))
ORDER BY ServiceContractorQuery.ContractorName;

The subform is linked to the form via the ProjectName Field. This is
listed
in both the "Link Child Fields" and "Link Master Fields".

It works perfectly when I just have the subform open. If the
ContractorCombo fails when I have the entire form open.

Let me know if you need anything else.

Thanks for your help.


Doug

Hi,
It would help if you post some details.
Are you using code in the after update event of the first combo or in the
enter event of the second combo?
Does the first have a hidden column with the ID of each service?
Does the second combo have a row source something like this?
SELECT tblConsultants.ID, tblConsultants.ConName
FROM tblConsultants INNER JOIN tblServices
ON tblServices.ServicesID = tblConsultants.ServicesID
WHERE tblConsultants.ServicesID = Me.FirstComboName

Jeanette Cunningham

 

[Huber57]

Jeanette,

That did not work. When I try to run it, it gives me a syntax error. Thne
the VB window opens with the first line of the code highlighted in yellow.
The following lines of text appear in red:
strSQL = "SELECT ServiceContractorQuery.ContractorName " _
& "FROM ServiceContractorQuery " _
& "WHERE " _
& "ServiceContractorQuery.Service='"" & Me.ServiceCombo & """ " _
& "ORDER BY ServiceContractorQuery.ContractorName"

Also, it doesn't work as a stand alone subform now, either. I appreciate
the help!

Sincerely,

Doug

Delete the query from the row source for the Contractor combo on the
property dialog
Put this code in the after update for the first Combobox (ServiceCombo)

Private Sub ServiceCombo_AfterUpdate()
Dim strSQL as String

If Not IsNull(Me.ServiceCombo) Then
Me.ContractorCombo = ""
strSQL = "SELECT ServiceContractorQuery.ContractorName " _
& "FROM ServiceContractorQuery " _
& "WHERE " _
& "ServiceContractorQuery.Service='"" & Me.ServiceCombo & """ " _
& "ORDER BY ServiceContractorQuery.ContractorName"
Debug.Print SQL
Me.ContractorCombo.RowSource = strSQL
End If
End Sub

Jeanette Cunningham

Jeanette:

The first Combobox (ServiceCombo)
Row Source:

SELECT Service_Table.Service
FROM Service_Table
GROUP BY Service_Table.Service;

AfterUpdate Event Procedure:

Private Sub ServiceCombo_AfterUpdate()
Me.ContractorCombo = ""
Me.ContractorCombo.Requery
End Sub

ContractorCombo Details:
Row Source

SELECT ServiceContractorQuery.ContractorName
FROM ServiceContractorQuery
WHERE
(((ServiceContractorQuery.Service)=[Forms]![ServiceContractorDate_Subform]![ServiceCombo]))
ORDER BY ServiceContractorQuery.ContractorName;

The subform is linked to the form via the ProjectName Field. This is
listed
in both the "Link Child Fields" and "Link Master Fields".

It works perfectly when I just have the subform open. If the
ContractorCombo fails when I have the entire form open.

Let me know if you need anything else.

Thanks for your help.


Doug

Hi,
It would help if you post some details.
Are you using code in the after update event of the first combo or in the
enter event of the second combo?
Does the first have a hidden column with the ID of each service?
Does the second combo have a row source something like this?
SELECT tblConsultants.ID, tblConsultants.ConName
FROM tblConsultants INNER JOIN tblServices
ON tblServices.ServicesID = tblConsultants.ServicesID
WHERE tblConsultants.ServicesID = Me.FirstComboName

Jeanette Cunningham


To all:

I have a subform that has two fields (comboboxes). The first is a list
of
services. The second is a list of consultants who provide those
services.
Through the posts on this forum and others (thanks much!) made the list
of
consultants dependent on the type of service offered.

BUT ... Once I open up the form (with the subform in it), the first
combobox
works fine, but the second one asks me to "Enter Parameter Value" and
then
gives the Criteria that the second combobox uses to reference the first
one.

Any thoughts?

Thanks in advance!

 

[jversiz via AccessMonster.com]

Hi Huber,

I was having the same problem myself. I hope you found the answer by now,
but if not, check this link out:

http://support.microsoft.com/kb/209099

What was happening to me is that I was referring to the object in the
independent combo as if it were on a main form. What you have to remember is
that a subform is an object within the mainform, not a form itself. Here are
the two examples:

Main form dependent combo...blah blah blah = Forms![Form Name]![Control Name]

Subform dependent combo...blah = [Forms]![Main Form Name]![Subform Name].Form!
Control Name

Best Regards,

James C.
EDI Analyst