- Joined
- Dec 10, 2008
- Messages
- 5
- Reaction score
- 0
I have a form, frmPersonnelAddChange, that users can launch to add info on a new professional (such as a Case Manager) associated with a client. There will be two ways to access it: either from a command button on a switchboard, or from a command in a Client Information form that has comprehensive client data of different types on many tab pages of a tab control. The latter is what is set up so far, and what I am working with.
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.
In the header, under the title label, the frmPersonnelAddChange has a select-record combo box cboFindPRecord. Right now this combo lists ALL the professionals of all types in the database (from tblPersonnel, which includes a TID or personnel-type primary key field for each professional listed). What I want to do, is dynamically change the Criteria for the cboFindPRecord RowSource to list only the type of professional currently named in the hidden txtTID text 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. The idea is that the latter option would be 'on' when launching frmPersonnelAddChange from a Switchboard (otherwise the form would launch showing the professional-type last-invoked by a user, such as Case Managers only or Behavioral Therapists only).
Right now I need to solve how to dynamically change the criteria for cboFindPRecord when launched from the subform command button on, for example, the Case Manager tab control page. 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 (professional listings) will be returned if the user wants to view them.
This is my second day trying to find something on the net to help me figure this out, but the closest I could get have been discussions of setting combo criteria from user selection of a second combo, or setting various criteria variations using a text box -- the latter didn't show me how to allow for showing all records (theoretically, by writing code for the on-Click event of a ckShowAll checkbox). Trying to write an expression stating the the TID could be an Integer >= 0 didn't work. I tried conditionally changing the RowSource SQL statement in VBS, but the SQL syntax throws compilation errors if copied directly from the combo box RowSource property into VBS, and I don't know enough about VBS to rewrite the syntax so it works and still creates the textbox correctly with the concatenations & sorts I need.
fyi, the SQL syntax including criteria limiting the combo to the TID currently in txtTID is:
SELECT tblPersonnel.PID, [Lastname] & (" "+[Suffix]) & ", " & ([Nickname]+" ") & [FirstName] AS [Personnel Name], [LastName] & (" "+[Suffix]) AS NameSort1, [FirstName] & ("'" & [Nickname] & "'") AS NameSort2, tblPersonnel.TID FROM tblPersonnel WHERE (((tblPersonnel.TID)=Forms!frmPersonnelAddChange.txtTID)) ORDER BY [LastName] & (" "+[Suffix]), [FirstName] & ("'" & [Nickname] & "'");
I have attached some screencaps of the upper part of the form; one screencap with the combo box showing only Case Managers, another with the combo showing All types of profession. the usually-hidden txtTID textbox has been made Visible for the screencaps (green colored textbox & label, in the lower right). I've also attached a screencap of the RowSource query grid when the criteria is included that limits the combo to the type currently showing in txtTID. I'm not including possible VBS examples as they were all fairly dodgy guesses to begin with.
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?
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.
In the header, under the title label, the frmPersonnelAddChange has a select-record combo box cboFindPRecord. Right now this combo lists ALL the professionals of all types in the database (from tblPersonnel, which includes a TID or personnel-type primary key field for each professional listed). What I want to do, is dynamically change the Criteria for the cboFindPRecord RowSource to list only the type of professional currently named in the hidden txtTID text 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. The idea is that the latter option would be 'on' when launching frmPersonnelAddChange from a Switchboard (otherwise the form would launch showing the professional-type last-invoked by a user, such as Case Managers only or Behavioral Therapists only).
Right now I need to solve how to dynamically change the criteria for cboFindPRecord when launched from the subform command button on, for example, the Case Manager tab control page. 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 (professional listings) will be returned if the user wants to view them.
This is my second day trying to find something on the net to help me figure this out, but the closest I could get have been discussions of setting combo criteria from user selection of a second combo, or setting various criteria variations using a text box -- the latter didn't show me how to allow for showing all records (theoretically, by writing code for the on-Click event of a ckShowAll checkbox). Trying to write an expression stating the the TID could be an Integer >= 0 didn't work. I tried conditionally changing the RowSource SQL statement in VBS, but the SQL syntax throws compilation errors if copied directly from the combo box RowSource property into VBS, and I don't know enough about VBS to rewrite the syntax so it works and still creates the textbox correctly with the concatenations & sorts I need.
fyi, the SQL syntax including criteria limiting the combo to the TID currently in txtTID is:
SELECT tblPersonnel.PID, [Lastname] & (" "+[Suffix]) & ", " & ([Nickname]+" ") & [FirstName] AS [Personnel Name], [LastName] & (" "+[Suffix]) AS NameSort1, [FirstName] & ("'" & [Nickname] & "'") AS NameSort2, tblPersonnel.TID FROM tblPersonnel WHERE (((tblPersonnel.TID)=Forms!frmPersonnelAddChange.txtTID)) ORDER BY [LastName] & (" "+[Suffix]), [FirstName] & ("'" & [Nickname] & "'");
I have attached some screencaps of the upper part of the form; one screencap with the combo box showing only Case Managers, another with the combo showing All types of profession. the usually-hidden txtTID textbox has been made Visible for the screencaps (green colored textbox & label, in the lower right). I've also attached a screencap of the RowSource query grid when the criteria is included that limits the combo to the type currently showing in txtTID. I'm not including possible VBS examples as they were all fairly dodgy guesses to begin with.
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?
Attachments
Last edited: