Form with Subform and Combo Box

[jlgray0127]

Good morning, all!
I've been working on this for 3 days and still can not find a solution! I think it's the way I'm asking the question, so, I'm going to try again.
I have a main form, which lists a program we are working on.
Each Program consists of 8 phases (refered to as GW1, GW2, GW3, etc). Within each of of these 8 phases, there are specific tasks associated to that phase, which are to take place.
So, phase one, or GW1, as it's referred to in my tables, has 12 specific tasks, which are identified in another table, called tasks. The structure of this table, is



Program Name, GW1, Activity, and a check box, if that activity is required or not.

I have another table, which lists the status and score for each GW and Activity.

What I need to happen, is when I open my main form,
It list the progam and the program information.

I want a subform, which lists the GW and the activities associated with the GW. I have this much accomplished.

What I need to happen, is a combo box to to be inserted in the subform, that will allow the user to select the status of that activity.
Now, the tricky part. Each GW has an activity and 3 specific tasks for this activity.
When I insert the combo box, it looks at the table, and lists all the activities for every GW and Activity. I want it only to look at the 3 specific status's that are tied to the GW and Activity of the record I'm updating.
I tried to create a query from the data source in the properties box, but this is not working.
Thoughts on how to filter it to look specifically at the GW and Activity I am on?

[ItsMe]

...I tried to create a query from the data source in the properties box, but this is not working...

Not quite sure I follow. If you are referring to a combobox you may want to set the RowSource property to reflect that status of the unique records. Perhaps the query builder can assist you with that. Use the ellipses(...) button next to the RowSource field for the combobox property.

[jlgray0127]

So, below is the query builder I am trying to build. I want the links I placed, as this will show the unique record, but I am still getting all the records from ProgramsGWCriteria. I just need the 3 records, which would be tied to the record I'm looking at. I want the combo box to contain Status, and then a text box to update to the Score. This way I can calculate the percentage based on the status selected.
Much thanks for your assistance!!!!

[ItsMe]

I don't understand why you need so many joins. Is there a way you can have a single join with a Primary Key to a Secondary key? Then at the bottom of the window you can include the fields you need; PK, Description, status, etc. You can set the criteria in the fields/columns after you add them to the SELECT statement, by dragging them to the fields at the bottom of the query builder window.

[jlgray0127]

I have this working, but it's only updating for the first record. When I make the selection on the first record, and go to the next record, the combo box does not update to the current record. Any thoughts on how to get the SQL Query to update to the next record after the previous record is updated?

[ItsMe]

Usually, comboboxes are not dependent on a form's recordset. They work best when they dictate to other controls. It sounds like you want your combobox's RowSource to be dynamic and dependent upon values in controls on a form.

You can concatenate control values into your SQL.

Me.cmbOne.RowSource = "SELECT PK, Field1, Field2, FROM tblMain WHERE PK = " & Me.ControlName.Value

[jlgray0127]

Yes. I want my combo box to reference the record set.
On record one, GW1 and the Activity have 3 specific status's I need to choose from.
The next GW and Activity has 3 different status to choose from. I need it to update for each recordset.

[ItsMe]

I am not sure I follow what you are after. If you are on a record and this record still needs two status verifications of a possible three, you can have your combo reflect that by having its Rowsource consider the three status' fields.

Something Like
"WHERE Status1 = 0 OR Status2 = 0 OR Status3 = 0 "

After a user selects one of the available options, the combo can update the appropriate field. I would use a control button to "Submit" the user's selection. This could run some code to update the appropriate field, the field dependent upon the selection the user made.

Is this close to what you are after?

[ItsMe]

I have this working, but it's only updating for the first record. When I make the selection on the first record, and go to the next record, the combo box does not update to the current record. Any thoughts on how to get the SQL Query to update to the next record after the previous record is updated?

I was reading this post and would like to point something out. When a user adjusts the value of a control, it is usually for the current record. If you want to UPDATE more than one record you can use an event handler to run an action query or to loop through a recordset. In other words, a user typing a value into a textbox control will only update the one record, the current record.

Consider a combobox that has a control source set to a control name on a form. It is the same thing, the user makes a selection with the combo and the control source receives the update. A single record is updated.

You will need to implement some code in order to UPDATE more than one record. An event handler could do this for you.

[jlgray0127]

Thank you!
I think we are on the same page.
My subform has many records and I need to update the status for each record. I created an SQL query for the combo box, to relate the GW and Activity to the table my combo box is using for a list. This way, when I go to GW1 Activity 1, I only get the 3 possible status for the first record. After I make the selection in my combo box, and go to the next record, when I select the combo box, it is still giving me the 3 status's for GW1 and Activity 1, when I need it to give me the results for GW1 and Activity 2. How do I force the combo box to now look at Activity 2, and give me the results for that?
This is my new issue. :P I get what you're saying about a loop, but not sure how to do that. I'd like for it to happen in the afterupdate event on the combo box, if possible, so the user is only tabbing between records. I am planning on only allowing an edit in the combo box, to maintain the data integrity of the tables. The user is not allowed to edit anything, other than select the status of the activity they are working.
Does this make sense? I'm sorry I'm so confusing in my explanations, and appreciate all the help and time you're giving me.

[ItsMe]

...After I make the selection in my combo box, and go to the next record, when I select the combo box, it is still giving me the 3 status's for GW1 and Activity 1, when I need it to give me the results for GW1 and Activity 2. How do I force the combo box to now look at Activity 2, and give me the results for that?...

If you want your subform's recordset to be dependent upon the value of the combobox you can place some code in the subform's current event. Go to the form object in the navigation pane and open it in Design View from there. Open the VBA editor and create a sub procedure for the form's load event.

Me.Recordsource = "SELECT * FROM qryMain WHERE Status = " & Forms!frmMain!SubformContainerName!Combobox.Value

The idea is you need to use the fully qualified name of the combobox so your subform understands the dynamic value of your combobox control.

Side Note: You can not have a circular dependency. If your combo is dependent on the subform's recordset, you can not have the subform dependent on the combo; at least not reliably

[jlgray0127]

That's not what I'm looking for.
Basically, I have a list of activities in my subform. I need to select a status for the activity. The status is listed in another table. There are 3 status for each activity. I need the user to be able to select the status of the activity.
So, for Activity1, there are 3 possible status' that activity can be in. When I select the combo box, it shows me the 3 choices for activity 1, and I select the status, and move to the next record on the subform, Activity 2.
When I get to activity 2, I want the combo box to list the possible status's for activity 2. The combo box, when selected, shows the activities for activity 1. The SQL query created through the datasource on the properties menu, does not recognize that I have switched to the next record.

[ItsMe]

Then I would refer to post # 6 for an example to have your combobox dependent upon the subform.
Me.cmbOne.RowSource = "SELECT PK, Field1, Field2, FROM tblMain WHERE PK = " & Me.ControlName.Value
Hopefully, all you need is to have your combobox's RowSource dynamically updated in the subform's current event. So, you may need to use the combos fully qualified name within the subform's class module. I don't think you will though, Me. shortcut should work...

Something seems circular about your RowSource / Recordsource dependencies. I was going to focus on that in post #11 but decided to offer an example of having the subform dependent.

If one dependency or the other does not work (because it Breaks) then you need to look at how your table relations are set up. It seems you one two functions from one combo.

[jlgray0127]

How would I word this, then? This is what I have right now....

Subform is tied to ProgramsGWActivities and the combo box is tied to ProgramCriteria.

SELECT ProgramCriteria.Activity, ProgramCriteria.GW, ProgramCriteria.Status, ProgramCriteria.Score, ProgramCriteria.Owner, ProgramCriteria.Required, ProgramCriteria.ProjectState
FROM ProgramsGWActivities INNER JOIN ProgramCriteria ON (ProgramsGWActivities.Activity = ProgramCriteria.Activity) AND (ProgramsGWActivities.Owner = ProgramCriteria.Owner) AND (ProgramsGWActivities.GW = ProgramCriteria.GW)
WHERE (((ProgramsGWActivities.Owner)=[Forms]![Programs]![ProgramsGWActivities].[Form]![Owner]) AND ((ProgramsGWActivities.Program)=[Forms]![Programs]![ProgramsGWActivities].[Form]![Program]) AND ((ProgramsGWActivities.GW)=[Forms]![Programs]![ProgramsGWActivities].[Form]![GW]))
GROUP BY ProgramCriteria.Activity, ProgramCriteria.GW, ProgramCriteria.Status, ProgramCriteria.Score, ProgramCriteria.Owner, ProgramCriteria.Required, ProgramCriteria.ProjectState
HAVING (((ProgramCriteria.Activity)=[Forms]![Programs]![ProgramsGWActivities].[Form]![Activity]));



The form looks like this...

[ItsMe]

I see a continuous form and either a combobox or a lookupfield. Unbound controls do not play well within continuous forms. If you have a combobox in a continuous form, you are not going to be able to have its RowSource dependent upon the same continuous form.