sub-form dependent combo box

H

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!
 
J

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
 
H

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
 
J

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

Huber57 said:
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 said:
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
Click to expand...
Click to expand...
 
H

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

Jeanette Cunningham said:
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

Huber57 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Search using a combo box and then creating a report. 4
Dependent combo box in Web Database 0
Combo box filtering records in a subfrm 7
combobox population record dependent 4
List Box not Working in Sub Form 1
Dependent combo boxes issue 1
Combo Box Requering 2
make selection of combo box required 0

Top