Good day to all,

I have a continuous form with 3 combo box controls allowing the user to select:
Project Task Type Task

I have a sort of "cascading" control update setup where once a Project is selected, then only certain task types are then available for selection. After a Task Type is selected, then only certain Tasks are available for choosing. I have a Requery method set on the AfterUpdate events for the Project and Task Type controls which then requery the Task Type and Task controls, respectively to give them their updated (and correct) values to choose from. The problem is, if there is more than one record showing in the form, and I select a Project, it requeries ALL of the records in the form, each getting the same result from the underlying query. This, of course, messes up all of the records except the current one.

How can I requery just the record I'm working on in the continuous form and not have it affect the other records which may be showing? Would the answer be similar to this recent/ongoing discussion ? It seems similar, but am having a hard time seeing how I could alter it to fit my needs. It feels like a recordset positional thing.

Thanks for any help!
Jeff

Do you just want to requery the combo boxes? or the whole record?

Just the combo boxes and just the combo box for the record I'm currently in. The AfterUpdate event of the Project control currently contains the code: Me.cboTaskType_ID.Requery. This is referring to the combo box for the Task Type, but it updates the combo boxes of all the records in the continuous form.

Thanks!
Jeff

When you set a new record-source for a combo in continuous forms, you set it for all combos in the recordset you are showing. I'm afraid there is no way around that. the question is, if it changes the actual value of the field bound to the combo-boxes. I'm not sure, but I don't think it does.

If it doesn't (and that's an IF), you don't have a problem, because you can reset the rowsource of your combos On_Current.

Jock

Edit: Just fidgeted around with it, and actual values don't change. If you limit the row-source of a bound combo, you can only see the values that are in the row-source on the other records too, but values remain unchanged. So even if values "disapear" they are only gone untill you jump to the next record, if you reset your combo rowsource to encompas all values on_Current. So, it's an inconvenience, but unfortunately unavoidable, unless you want to have unbound combos (selectabel values) combined with a bound textbox (actual values).


Edited by: prebez on Fri Mar 16 18:31:28 EDT 2007.

See if Roger Carlson's samples help:

* CascadingComboBoxes
* CascadingComboInSubform but make sure you read the Caveat.

I use a different technique that involves using 2 ComboBoxes (1 unfiltered and 1 filtered) for each of the cascade level, i.e. in your case, 2 ComboBoxes for [Task Type] and 2 ComboBoxes for [Task].

Thank you for help on this! I played around with a selectable unbound combo box, but don't really have the screen real estate to spare for an unbound and a bound control (especially 3 pairs).

I downloaded the CascadingComboInsubform db and that is actually going to give me what I want, even with the caveat. I've been trying to implement the code from the example db but keep getting the run time error of "...can't find the form 'frmMain' referred to in a macro expression or Visual Basic code." The line of code the debugger takes me to is:

[Forms]![frmMain]![sfTimeRecord].[Form]!cboTaskType_ID.RowSource = strsql

This line of code appears in the LostFocus event of the 2nd combo box of the subform (sfTimeRecord). I'm having trouble tracking down the source of this error since the name of the main form is frmMain and is open at the time the event is fired. I've double-checked control and form names and spellings. Any ideas where I'm going wrong? I've attached my db in case you have a moment to take a look. The form and subform names are frmMain and frmTimeRecord, respectively. In frmMain, I've named the embedded subform sfTimeRecord.

Thanks again!
Jeff

It's a bøg! I just ran into it the other day. I had a form that I KNEW was open. Try refering to the class object:

Form_frmMain![sfTimeRecord].[Form]!cboTaskType_ID.RowSource = strsql

Or try the whole nine yards (collection reference):

Forms.Item("frmMain").Controls.Item("sfTimeRecord").Form.Controls.Item("cboTaskType_ID").RowSource = strsql

Hope it helps

Jock

P.S. If you know the number of your collection item (if it always opens as the first) you can replace your form name in quotation marks with that number

Hi Jock,

Thank so much for the response. I've swapped out the frmMain to the class object Form_frmMain as you've suggested. The error has gone away, but unfortunately, the combo box still does nothing. I then tried using the collection reference, but then the error appeared again. Do I need to change the reference to frmMain in the WHERE clause of the sql string to the class object reference also?

Dim strsql As String
strsql = "SELECT tblTaskTypes.TaskType_ID, tblTaskTypes.Task_Type, [tblJOIN-Projects_TaskTypes].fkProject_ID " & _
" FROM tblTaskTypes INNER JOIN [tblJOIN-Projects_TaskTypes] ON tblTaskTypes.TaskType_ID = [tblJOIN-Projects_TaskTypes].fkTaskType_ID " & _
" WHERE ((([tblJOIN-Projects_TaskTypes].fkProject_ID)= [Forms]![frmMain].[sfTimeRecord].[Form].[cboProject_ID]));"

I really appreciate the help,
Jeff

The idea is that each pair of the unfiltered and filtered ComboBoxes occupy the same screen space so that only one of them is visible at any given time. You need to size the 2 ComboBoxes to be exactly the same, place then at the same location on the Form and "Bring to Front" the unfiltered ComboBox. In addition, set the Locked to True for the bound ComboBox. Use the FilteredComboBox_GotFocus Event to send the Focus to the unfiltered ComboBox.

With this method, normally the unfiltered ComboBox is visible (and show correct Text in the ComboBox) but when the user enters the unfiltered ComboBox, the code will make the filtered ComboBox active so that the user can make selection from the appropriate (filtered) list of choices. In the rows other than the current/active row, the unfiltered ComboBox is shown correctly also.

Is the Reference you posted in the Open Event of the SubForm? If this is the canse, you will get the posted error as the Subform is opened before the main Form.

Hi Van,

Thank you for your help. I'm still having trouble, however. I've done everything to find these filtered/unfiltered combo box pairs that are on top of one another and can't seem to get to the one that is underneath. In the form design view, I've selected one of the combo boxes and chose "Send to Back" but no other combo box appears. I've also tried resizing the one that is selected and still no luck. I'm very sorry to keep bothering you with this.

To answer your question about where the reference is posted, it isn't in the OnOpen event of the subform, so that at least, can be ruled out.

Thanks,
Jeff

Suggest you post a sample copy of your database (compact first then zip to < 500 KB before posting) so that others can have a look of your database set-up ...

I believe I have achieved success!! Since being unable to figure out how the sample db worked (ie, could not get to the bound/unbound combo box pairs overlaying one another), I did some more research and have come up with a similar solution that now works. The sub form's data is now a query which includes not only the table where key values are to be stored, but the lookup tables also. Instead of 2 combo boxes on top of one another, the top control is a text box (with the text from the lookup table) and the bottom control is a combo box (which selects and stores the numeric key in the join table). The text box is slightly shorter to allow the drop-down arrow to show. The text box's data is locked and disabled and its Tab Stop property is set to 'no' so it cannot get focus. The code behind the scenes is amazingly simple also. If you're interested in seeing how it works in my db, I have attached it. Open frmMain and you can select a project, a task type and a task. The latter 2 being constrained by a previous combo box choice. The embedded subform is called frmTimeRecord-TEST. Check out the code in the sub form's OnCurrent event and the AfterUpdate events of the TaskType and Task combo boxes. And yes, the key values are being stored appropriately in the join table.

I want to thank you again for all of your help. I wouldn't have made it this far without it!! If you would like to see the other sample db from where this solution came, let me know and I will post it too.

Tschüß!
Jeff

Well-done!

Yes, paired ComboBox + TextBox is another method that I am aware of but I mainly use 2 ComboBoxes (1 filtered + 1 unfiltered).

Glad to help ...

You're welcom jheiland. Spread the word about the "Access 2000 forgetting name of open form"-bug.

Glad you got it working

Jock

>> Access 2000 forgetting name of open form bug <<

--- I do not think this is a bug .... As Van stated:

>> Is the Reference you posted in the Open Event of the SubForm? If this is the canse, you will get the posted error as the Subform is opened before the main Form. <<

The key is that the sub-form is opened before the main form, so any code that runs in the sub-form upon opening will error out if you refer to the main form using the Forms collection. The code will NOT error out when using the class of the form because using the Class of the form does not rely on the Forms collection. As a matter of fact, you can actually open a form WITH OUT adding it to the Forms collection. Check out this thread.

Thanks for the clarification Brent. And please excuse my using the b-word

I just naturally assumed that it was the same problem that I've had with one of my forms, that was not a subform (se my thread on "an academic question"). But it is obviously not the case as you point out.

Sorry about the confusion.

Jock

Jock,

No problem ... I personally still sort of think of the phenomenon of opening the form as a class and it NOT being added to the Forms collection as a "Bug" even though there is probably some technical reason why its not ... I also think of the sub-form before main form thing as an Oddity as well (just shy of a bug) ... To me it seems a little weird to load all the sub-forms first, which by nature SHOULD be dependant upon the Main form ... so wouldn't it make sense to load the Main form first? ... But ... It seems the loading of the subforms takes place before the Main form COMPLETES it loading and before ANY events are fired ... so ... it seems the Main form is clicked, MS Access loads the definition of the main form and "sees" that sub-forms are needed, it then COMPLETELY loads the sub-forms, then continues to the Main forms OnOpen and OnLoad events ... I guess that makes sense too ...but it is just not intuitive!

I will often create code in a sub-form that IS dependant upon the main form, like in the sub-forms OnCurrent event ... but ... when I save the sub-form, I set the OnCurrent property to a ZLS (instead of "[event procedure]") ... note the CODE is still there behind the sub-form ... THEN ... the MAIN forms OPEN event has code in it that looks like this:



Modifying the OnCurrent (or other event PROPERTIES) allows me to control whether or not a particular event executes the code or not. I find this technique quite useful

An other technique I use is to force MS Access to load the Sub-Form AFTER the main form loads. I do this by creating my sub-form control on the Main form as an UNBOUND control, then in the Main forms OnCurrent or OnLoad, I set the sub-forms "Source Object" property ...



PS> Did you ever get your "project is already open" problem worked out?

Jock,

Your thread An Acedemic Question ... is very close in relation to the one I linked to earlier in the thread ( here it is again) ... Great minds think alike!!

Brent wrote:
"PS> Did you ever get your "project is already open" problem worked out?"
No. I asked around and everybody (including two 250$ an hour Microsoft specialists that we occasionally employ) says it can't be done, unless you are positively sure that the Access application in question is the only application open.

I decided to make a change of procedure for the users in stead "Close your application before using my automated worksheet, or accept that you will have two instances running."

The thread you link to is most interesting. I'll keep it bookmarked for future multiple-form work.

Brent Wrote:
"Great minds think alike!!"
Thanks, but I like to think of my mind as: generally adequate and occasionally inspired (heh!)

Jock