make two dependent select fields

[stephane]

i've got two table
ServicesGroups ( id, name ) and
Services ( id, group_id, name )
tables linked on ServicesGroups. id=Services.gro up_id

at first select field i list ServicesGroups table,
so when user select group i need to reload Services select field, and show only services wich linked to selected group ( Where Services.group_ id=ServicesGrou ps.id )

i realize it so

sql_str = "SELECT Services.name, Services.id " & _
          "FROM Services " & _
          "WHERE Services.group_id=" & ServiceGroup.Value
Servicefield.RowSource
Servicefield.Requery

but making Servicefield.Re query we reload all Services selects at form, but we need reload only Services select at current row of the parent table

[stephane]

[NeoPa]

i've got two table
ServicesGroups ( id, name ) and
Services ( id, group_id, name )
tables linked on ServicesGroups. id=Services.gro up_id

at first select field i list ServicesGroups table,
so when user select group i need to reload Services select field, and show only services wich linked to selected group ( Where Services.group_ id=ServicesGrou ps.id )

i realize it so

sql_str = "SELECT Services.name, Services.id " & _
          "FROM Services " & _
          "WHERE Services.group_id=" & ServiceGroup.Value
Servicefield.RowSource
Servicefield.Requery

but making Servicefield.Re query we reload all Services selects at form, but we need reload only Services select at current row of the parent table

Your second line of code :

Servicefield.RowSource

Does not set it to the new SQL code. You need :

Servicefield.RowSource = sql_str

[stephane]

oh
it's a error on posing.
in my real code there are
Servicefield.Ro wSource = sql_str

this code reloads all services select fields

[Michael R]

I'm not sure how what you ask is possible considering the nature of forms, but you can try the following alternative:

(1) Create table Services:

Service_Id (Autonumber (can as well be entered by user), Primary Key)
Group_Id (Number) or (Text)
Service_Name (Text)

each record will have a service name and its apropriate service group

(2) Create table Selection:

Selection_Id (Autonumber (can as well be entered by user), Primary Key)
Selection (Text), you can also make it a Combo Box in Lookup, and make its Row Source:

SELECT Services.Servic e_Name, Services.Group_ Id FROM Services

with Column Count of 2 for looking up for all possible serivices from Services table, but it is not nescasary for the form.

Add all the other fields you need for creating selections in the form, ex:
Field3
Field4


(3) Create query qrySelection:

SELECT Selection.Selec tion, Selection.Field 3, Selection.Field 4, Services.Group_ Id
FROM Selection LEFT JOIN Services ON Selection.Selec tion = Services.Servic e_Name;

Save it.


(4) Create a continuous form myForm based on qrySelection:

On form's header or footer add a Combo Box named cboGroup_Id with the following Row Source:

SELECT DISTINCT Services.Group_ Id FROM Services;

Put Selection field control and Group_Id field control and all the other necessary field controls into detail section. Change Selection control (if it is not already) to Combo Box, with the following Row Source:

SELECT Services.Servic e_Name FROM Services WHERE (((Services.Gro up_Id)=Forms!my Form!cboGroup_I d));

Add

Me.Selection.Requery

to cboGroup_Id On Change event.

---
Now the form should work by user choosing the Group_Id from cboGroup_Id having the appropriate service names on the rows and the service groups according to them.

[Michael R]

Comment on:

I'm not sure how what you ask is possible considering the nature of forms

- What I really mean is, that I'm not entirely sure if what you ask is possible with your current building. Maybe it is possible with some deep ADO programming, but the solution I gave you is pretty simple (well at least for those who aren't expertized in ADO programming)