combobox population record dependent

[jovobas]

I've got a sales database with customer companies, products and
salespersons. These three tables are linked together in 2 joins, join
table S(pecialty): products and salespersons and join table O(rders):
companies and specialties. I have split the Specialty table from the
Orders table because of access rights.

For main purposes I created a form with the companies with an Orders
subform. The subform is in datasheet view for easy overview.
On this subform I have currently 1 combobox with the specialty i.e.
[salesperson Name] & " ("& [product Name] &")". In this combobox all
existing specialties occur. Now I want to redesign the subform to
having two comboboxes: one with the salespersons and one with the
products.
-- They should both be bound to the [Specialty] from the Orders table
(because they must display the correct info)
-- The first combobox should only list the salespersons (distinct?)
-- The second combobox, and this is my biggest problem, should only
contain products that are in the specialty of the salesperson for that
record.
And of course, if I add a new Order, choose a salesperson, this second
combobox should (re)populate accordingly.

In other words: I want a combobox that has a population that's based
on or dependent on the record data.

Thanks for any insights.

 

[Jeanette Cunningham]

Hi jovobas,
the second combo needs a query for its row source.
This query needs to pick the specialties for any particular salesperson.
There are 2 ways to do the query.
One is to write the sql of the query in the code and use a where clause that
specifies the salespersonID.

Something like-->
Private Sub Combo1_AfterUpdate()
Dim strSQL As String
Dim strWhere As String

Const strcStub = "Select tblName.SpecialityID, tblName.Speciality " _
& "From TableName"

If Len(Me.combo1Name & vbNullString) >0 Then
strWhere = "Where SalespersonID = " & Me.combo1Name & ""
strSQL = strcStub & " " & strWhere
Else
strSQL = strcStub
End If
Debug.Print strSQL
Me.Combo2Name.RowSource = strSQL
End Sub

The other way is to create a saved query for the speciality combo's row
source.
In the criteria row for the query, put = Forms![NameOfForm]![NameOfCombm1]

Replace the names of the fields and combos with your names.
The above gives the general idea, you will need an order by clause if you do
the row source in code and you might have one more than 1 table in the
query.
Depending on your table setup, you may need to use the Distinct key word or
do a group by to limit the query to unique valuew.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[jovobas]

Hi Jeanette,
thanks for the prompt reply.
I had explored this possibility and this works great for new records. But I
also want it to work for existing records in datasheet or continuous view (so
without an event trigger).

For example:
Company form
Company X (record 1)
Orders :Continuous subform:
Order 1 ; Salesperson A ; Product C (on dropdown: only products from the
specialty of salesperson A)
Order 2 ; Salesperson A ; Product D (on dropdown: only ...)
Order 3 ; Salesperson B ; Product E (on dropdown: only products form the
specialty of salesperson B)
Order new ; Choose Salesperson ; Products of chosen Salesperson (that's
where your answer comes in)

I have tried your method, but this displays a combobox with products from
the specialty of the first Order, for all the Orders. In this example: In
Order 3 the correct product is shown, but the dropdown list has the products
from Salesperson A's specialty.

So here's my challenge: how to populate a combobox based on record data (for
existing orders) , not on an event.

Thanks for any insights.

Hi jovobas,
the second combo needs a query for its row source.
This query needs to pick the specialties for any particular salesperson.
There are 2 ways to do the query.
One is to write the sql of the query in the code and use a where clause that
specifies the salespersonID.

Something like-->
Private Sub Combo1_AfterUpdate()
Dim strSQL As String
Dim strWhere As String

Const strcStub = "Select tblName.SpecialityID, tblName.Speciality " _
& "From TableName"

If Len(Me.combo1Name & vbNullString) >0 Then
strWhere = "Where SalespersonID = " & Me.combo1Name & ""
strSQL = strcStub & " " & strWhere
Else
strSQL = strcStub
End If
Debug.Print strSQL
Me.Combo2Name.RowSource = strSQL
End Sub

The other way is to create a saved query for the speciality combo's row
source.
In the criteria row for the query, put = Forms![NameOfForm]![NameOfCombm1]

Replace the names of the fields and combos with your names.
The above gives the general idea, you will need an order by clause if you do
the row source in code and you might have one more than 1 table in the
query.
Depending on your table setup, you may need to use the Distinct key word or
do a group by to limit the query to unique valuew.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I've got a sales database with customer companies, products and
salespersons. These three tables are linked together in 2 joins, join
table S(pecialty): products and salespersons and join table O(rders):
companies and specialties. I have split the Specialty table from the
Orders table because of access rights.

For main purposes I created a form with the companies with an Orders
subform. The subform is in datasheet view for easy overview.
On this subform I have currently 1 combobox with the specialty i.e.
[salesperson Name] & " ("& [product Name] &")". In this combobox all
existing specialties occur. Now I want to redesign the subform to
having two comboboxes: one with the salespersons and one with the
products.
-- They should both be bound to the [Specialty] from the Orders table
(because they must display the correct info)
-- The first combobox should only list the salespersons (distinct?)
-- The second combobox, and this is my biggest problem, should only
contain products that are in the specialty of the salesperson for that
record.
And of course, if I add a new Order, choose a salesperson, this second
combobox should (re)populate accordingly.

In other words: I want a combobox that has a population that's based
on or dependent on the record data.

Thanks for any insights.

 

[Jeanette Cunningham]

Yes, this is what happens with a continuous form.
Access does not bother to requery the combo for each separate record on the
continuous form.
You can usually get a better result by putting a textbox over the combo to
cover the combo.
The combo has tab stop set to no.
The textbox is bound to the name of the product field.
The on got focus of the textbox sets the focus to the combo and drops it
down.
When the user clicks the textbox, the combo comes to the front. When the
user moves to another field, the textbox comes to the front.
This usually has the effect of showing the chosen product for each row of
the continuous form.
There are many postings about this on the discussion groups.
Do a search and you will find the variations for different ways of setting
up the continuous form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,
thanks for the prompt reply.
I had explored this possibility and this works great for new records. But
I
also want it to work for existing records in datasheet or continuous view
(so
without an event trigger).

For example:
Company form
Company X (record 1)
Orders :Continuous subform:
Order 1 ; Salesperson A ; Product C (on dropdown: only products from
the
specialty of salesperson A)
Order 2 ; Salesperson A ; Product D (on dropdown: only ...)
Order 3 ; Salesperson B ; Product E (on dropdown: only products form
the
specialty of salesperson B)
Order new ; Choose Salesperson ; Products of chosen Salesperson (that's
where your answer comes in)

I have tried your method, but this displays a combobox with products from
the specialty of the first Order, for all the Orders. In this example: In
Order 3 the correct product is shown, but the dropdown list has the
products
from Salesperson A's specialty.

So here's my challenge: how to populate a combobox based on record data
(for
existing orders) , not on an event.

Thanks for any insights.

Hi jovobas,
the second combo needs a query for its row source.
This query needs to pick the specialties for any particular salesperson.
There are 2 ways to do the query.
One is to write the sql of the query in the code and use a where clause
that
specifies the salespersonID.

Something like-->
Private Sub Combo1_AfterUpdate()
Dim strSQL As String
Dim strWhere As String

Const strcStub = "Select tblName.SpecialityID, tblName.Speciality " _
& "From TableName"

If Len(Me.combo1Name & vbNullString) >0 Then
strWhere = "Where SalespersonID = " & Me.combo1Name & ""
strSQL = strcStub & " " & strWhere
Else
strSQL = strcStub
End If
Debug.Print strSQL
Me.Combo2Name.RowSource = strSQL
End Sub

The other way is to create a saved query for the speciality combo's row
source.
In the criteria row for the query, put =
Forms![NameOfForm]![NameOfCombm1]

Replace the names of the fields and combos with your names.
The above gives the general idea, you will need an order by clause if you
do
the row source in code and you might have one more than 1 table in the
query.
Depending on your table setup, you may need to use the Distinct key word
or
do a group by to limit the query to unique valuew.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I've got a sales database with customer companies, products and
salespersons. These three tables are linked together in 2 joins, join
table S(pecialty): products and salespersons and join table O(rders):
companies and specialties. I have split the Specialty table from the
Orders table because of access rights.

For main purposes I created a form with the companies with an Orders
subform. The subform is in datasheet view for easy overview.
On this subform I have currently 1 combobox with the specialty i.e.
[salesperson Name] & " ("& [product Name] &")". In this combobox all
existing specialties occur. Now I want to redesign the subform to
having two comboboxes: one with the salespersons and one with the
products.
-- They should both be bound to the [Specialty] from the Orders table
(because they must display the correct info)
-- The first combobox should only list the salespersons (distinct?)
-- The second combobox, and this is my biggest problem, should only
contain products that are in the specialty of the salesperson for that
record.
And of course, if I add a new Order, choose a salesperson, this second
combobox should (re)populate accordingly.

In other words: I want a combobox that has a population that's based
on or dependent on the record data.

Thanks for any insights.

 

[jovobas]

Hi Jeanette,

again, thank you for the fast reply. It was very helpful.

My current solution (in continuous form view):
I put these controls in the details section of the subform
1. a combobox cmbSpec with the SpecID, SalespersonName, SalespersonID,
ProductName and ProductID
2. 2 txtboxes (txtSP and txtProd) with =cmbSpec.column(1) and
=cmbSpec.column(3) so they display name of salesperson and product
3. 2 comboboxes (cmbSP and cmbProd) that requery on the onCurrent event
4. a combobox that looks up the specs on afterupdate cmbSP or cmbProd and
saves the returned spec in the order.
5. some extra code to meet my other needs.

Thanks a lot !

Yes, this is what happens with a continuous form.
Access does not bother to requery the combo for each separate record on the
continuous form.
You can usually get a better result by putting a textbox over the combo to
cover the combo.
The combo has tab stop set to no.
The textbox is bound to the name of the product field.
The on got focus of the textbox sets the focus to the combo and drops it
down.
When the user clicks the textbox, the combo comes to the front. When the
user moves to another field, the textbox comes to the front.
This usually has the effect of showing the chosen product for each row of
the continuous form.
There are many postings about this on the discussion groups.
Do a search and you will find the variations for different ways of setting
up the continuous form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,
thanks for the prompt reply.
I had explored this possibility and this works great for new records. But
I
also want it to work for existing records in datasheet or continuous view
(so
without an event trigger).

For example:
Company form
Company X (record 1)
Orders :Continuous subform:
Order 1 ; Salesperson A ; Product C (on dropdown: only products from
the
specialty of salesperson A)
Order 2 ; Salesperson A ; Product D (on dropdown: only ...)
Order 3 ; Salesperson B ; Product E (on dropdown: only products form
the
specialty of salesperson B)
Order new ; Choose Salesperson ; Products of chosen Salesperson (that's
where your answer comes in)

I have tried your method, but this displays a combobox with products from
the specialty of the first Order, for all the Orders. In this example: In
Order 3 the correct product is shown, but the dropdown list has the
products
from Salesperson A's specialty.

So here's my challenge: how to populate a combobox based on record data
(for
existing orders) , not on an event.

Thanks for any insights.

Hi jovobas,
the second combo needs a query for its row source.
This query needs to pick the specialties for any particular salesperson.
There are 2 ways to do the query.
One is to write the sql of the query in the code and use a where clause
that
specifies the salespersonID.

Something like-->
Private Sub Combo1_AfterUpdate()
Dim strSQL As String
Dim strWhere As String

Const strcStub = "Select tblName.SpecialityID, tblName.Speciality " _
& "From TableName"

If Len(Me.combo1Name & vbNullString) >0 Then
strWhere = "Where SalespersonID = " & Me.combo1Name & ""
strSQL = strcStub & " " & strWhere
Else
strSQL = strcStub
End If
Debug.Print strSQL
Me.Combo2Name.RowSource = strSQL
End Sub

The other way is to create a saved query for the speciality combo's row
source.
In the criteria row for the query, put =
Forms![NameOfForm]![NameOfCombm1]

Replace the names of the fields and combos with your names.
The above gives the general idea, you will need an order by clause if you
do
the row source in code and you might have one more than 1 table in the
query.
Depending on your table setup, you may need to use the Distinct key word
or
do a group by to limit the query to unique valuew.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia






I've got a sales database with customer companies, products and
salespersons. These three tables are linked together in 2 joins, join
table S(pecialty): products and salespersons and join table O(rders):
companies and specialties. I have split the Specialty table from the
Orders table because of access rights.

For main purposes I created a form with the companies with an Orders
subform. The subform is in datasheet view for easy overview.
On this subform I have currently 1 combobox with the specialty i.e.
[salesperson Name] & " ("& [product Name] &")". In this combobox all
existing specialties occur. Now I want to redesign the subform to
having two comboboxes: one with the salespersons and one with the
products.
-- They should both be bound to the [Specialty] from the Orders table
(because they must display the correct info)
-- The first combobox should only list the salespersons (distinct?)
-- The second combobox, and this is my biggest problem, should only
contain products that are in the specialty of the salesperson for that
record.
And of course, if I add a new Order, choose a salesperson, this second
combobox should (re)populate accordingly.

In other words: I want a combobox that has a population that's based
on or dependent on the record data.

Thanks for any insights.