Trouble with Dynamically updating Combo Boxes

[mtengelh]

Hello All,

I'm looking for some assistance with a DB form that I've created to capture timing information for an operational area. I have two distinct combo boxes (Task Type and Task Sub Type) which have been created and work fine individually. However I would like them to work in combination with each other so that when the Task Type box is entered the Task sub type drop down populates with only those from the prior selected box. I have tried this multiple different ways but have had no luck and now the selections do not populate at all in the second combo box. Currently in the row source for Task Sub Type I have selected all of the necessary fields and have criteria set to pull [Forms]![Timer]![ComboTaskName] and then an after update embedded macro to requery the selection on the Task Type box. Am I missing something? Any help would be appreciated.

[John_G]

You should rename the "Timer" form to something else - "Timer" is the name of a form property, and also a function, so it should be considered a "reserved" word. I don't think that is the source of your problem, though.

You have the right idea here, but the requery of the Task Subtype combo has to be done in the After Update event of the Task Type combo (is that [ComboTaskName]?)

What is the SQL of the Row Source of the sub-type combo, what does the Macro do, and what form event is it activaed by?

[mtengelh]

Thanks for the Quick response John!

I apologize for the misunderstanding, the requery macro was placed on the after update event of the task type combo (yes ComboTaskName)

Here is the info from the Row source of the sub type combo - SELECT Status.ID, Status.TaskSubType, Status.TaskName FROM Status WHERE (((Status.TaskName)=Forms![Timer]!ComboTaskName)) ORDER BY Status.TaskSubType;
There is no macro on the sub type combo...only the one mentioned above. I'm not sure what you mean by which form event is it activated by..I'm thinking this isn't relevant now that the macro is on the other combo?

[John_G]

I don't see anything obvious. The only thing I can think of is that when you said this above:

...embedded macro to requery the selection on the Task Type box

you meant requery the task Sub-type box; does the macro requery the right combo box?

The macro is in the right place, the After Update event of the Task Type combo.

[mtengelh]

The requery occurs on the task sub type box, correct...sorry for the confusion. Could the relationships be messing with this?

[John_G]

Could the relationships be messing with this?

No, I don't think so.

What is the Row source for the first combo box comboTaskName? Do you include an ID column as the first field, but hide it with a width of 0 ? If so, that may be the problem - let us know.

[mtengelh]

The row source is SELECT [TaskNames].[ID], [TaskNames].[Task Name] FROM TaskNames ORDER BY [Task Name];

And yes the width is set to 0. What should I do if this is a potential issue?

[mtengelh]

I just went in and changed the bound column to 2 from 1 and it solved the issue. I appreciate all of your assistance!

[John_G]

Glad we could help. The issue of bound column vs. visible data is a common one.

[mtengelh]

Ok...so I seem to have created a new issue. Now that I have the combo boxes working together, I am not able to capture the first combo value in my table. When I select the first combo value it populates the second box with the correct selections but only the second value is pulling through to my table that I am writing the results to. Any suggestions? Is this possibly due to the bound column issue?

[John_G]

How are you getting the values of the combo boxes into the table? If the two combo boxes are bound to table fields, when you changed the "bound column" setting of the first one it also changed the data that is added to the table field.

[mtengelh]

Thanks for the quick reply John...both of my fields state they are unbound.

State = "INSERT INTO TaskTimings ([TaskType],[UserID],[TaskSubType],[Time],[Comment],[Date]) values ('" & Task & "','" & User & "','" & Status & "','" & Time & "','" & Comment & "','" & Date & "')"
DoCmd.RunSQL State

TaskType is not populating

[John_G]

Isn't the task type combo box called ComboTaskName? Your statement should be:

State = "INSERT INTO TaskTimings ([TaskType],[UserID],[TaskSubType],[Time],[Comment],[Date]) values ('" & ComboTaskName & "','" & User & "','" & Status & "','" & Time & "','" & Comment & "','" & Date & "')"

[mtengelh]

Hi John...sorry...I had updated that previously and it didn't solve the problem

State = "INSERT INTO TaskTimings ([TaskType],[UserID],[TaskSubType],[Time],[Comment],[Date]) values ('" & ComboTaskName & "','" & User & "','" & Status & "','" & Time & "','" & Comment & "','" & Date & "')"

[John_G]

Because you changed the bound column specification in the properties for the combo box, you may now have to explicitly specify which combo box column contains the value you want to enter in to the database.

If it is the first (hidden) column, try this:

State = "INSERT INTO TaskTimings ([TaskType],[UserID],[TaskSubType],[Time],[Comment],[Date]) values ('" & ComboTaskName.column(0) & "','" & User & "','" & Status & "','" & Time & "','" & Comment & "','" & Date & "')"

Combo box column references are zero-based.

Another change would be to use

CurrentDB.Execute State, dbFailonerror instead of DoCmd.RunSQL State

This causes a runtime error to be raised if the SQL statement fails with an error.

John