I have a an add-new form, frmPersonnelAddChange, that users are to invoke to add a new new professional (such as a Case Manager) associated with a client. Info for different types of professionals is listed in subforms on different tab control pages (e.g. a tab for Case Manager, a tab for Physical Therapist). The type of Personnel (TID, Personnel type table primary key) is set to a different default value in each subform according to which type of professional the tab & subform deal with. Eg, on the Case Manager tab control page, the integer 2 (the TID for Case Manager) is the default value of the textbox bound to TID on the Case Manager subform on that tab control page. When the add-new form frmPersonnelAddChange is launched from the Case Manager subform with the command cmdAddNew, the professional type is sent by code to a hidden textbox txtTID in the add-new form frmPersonnelAddChange.
The frmPersonnelAddChange has a select-record combo box cboFindPRecord. in the header. This combo lists ALL the professionals of all types in the database, from tblPersonnel (which includes a TID or personnel-type field for each professional listed). What I want to do, is dynamically change the Criteria for the RowSource to list only the type of professional currently named in txtTID. This would be the default state of the cboFindPRecord combo box. I would also like to be able to give the user the option to see all the professional types available; upon checking a "See All" checkbox would be one way to do it... if I could figure out how to do it.
My main problem is that while I could limit cboFindPRecord to the current txtTID professional type by adding:
[Forms]![frmPersonnelAddChange].[txtTID]
On the Criteria line of the 0-column-width TID field in the combo's RowSource SQL statement (which I built using the ... elipse, not by figuring out the SQL from scratch) -- I have not been able to come up with a way to remove or change the criteria on command so that all records will present. Ultimately users will be alternatively launching frmPersonnelAddChange without doing it from a different form's tab control subform for a certain type of professional -- that is, launching it directly from a cmd button on a switchboard.
I've spent more than a day trying to find something on the net to help me figure this out, but the closest I could get were discussions of setting combo criteria from setting a second combo, and setting various criteria limitation changes using a text box -- the latter didn't show me how to allow for showing all records. Trying to write an expression making the TID >= 0 didn't work.
Obviously I'm learning VBS applications while trying to make things work on a case-by-case basis -- and not being very knowledgeable, I'm stumped on what direction to take to make what I want, work. Any suggestions?
The frmPersonnelAddChange has a select-record combo box cboFindPRecord. in the header. This combo lists ALL the professionals of all types in the database, from tblPersonnel (which includes a TID or personnel-type field for each professional listed). What I want to do, is dynamically change the Criteria for the RowSource to list only the type of professional currently named in txtTID. This would be the default state of the cboFindPRecord combo box. I would also like to be able to give the user the option to see all the professional types available; upon checking a "See All" checkbox would be one way to do it... if I could figure out how to do it.
My main problem is that while I could limit cboFindPRecord to the current txtTID professional type by adding:
[Forms]![frmPersonnelAddChange].[txtTID]
On the Criteria line of the 0-column-width TID field in the combo's RowSource SQL statement (which I built using the ... elipse, not by figuring out the SQL from scratch) -- I have not been able to come up with a way to remove or change the criteria on command so that all records will present. Ultimately users will be alternatively launching frmPersonnelAddChange without doing it from a different form's tab control subform for a certain type of professional -- that is, launching it directly from a cmd button on a switchboard.
I've spent more than a day trying to find something on the net to help me figure this out, but the closest I could get were discussions of setting combo criteria from setting a second combo, and setting various criteria limitation changes using a text box -- the latter didn't show me how to allow for showing all records. Trying to write an expression making the TID >= 0 didn't work.
Obviously I'm learning VBS applications while trying to make things work on a case-by-case basis -- and not being very knowledgeable, I'm stumped on what direction to take to make what I want, work. Any suggestions?
![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] 