If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Removing choices from combobox query but stilling showing for older entries
I have a form, frmMain that lists staff names in a combobox from a query
into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and ShowInViews. The RowSource Query for the combobox is: SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE (((lkpStaff.ShowInViews)=True)); The combobox ControlSource is the field Staff in tblMain which is populated from frmMain. By unchecking ShowInViews for staff that are no longer employed I can remove them from the combo's dropdown. However, of course, it also removes those staff names from older entries. I want to have my cake and eat it too, i.e., to remove the staff person as a choice going forward, while still retaining them in older records. I hope that makes sense and thanks in advance. Doug __________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
#2
|
|||
|
|||
Removing choices from combobox query but stilling showing for older entries
It sound like you should be able to have your cake and eat it.
However, you haven't made clear what you mean by "older entries". If you have another form or report using the same SELECT query, all you need to do is drop the WHERE clause. If you have a different SELECT query, omit ShowInViews altogether. -- Len __________________________________________________ ____ remove nothing for valid email address. "Doug Glancy" wrote in message ... |I have a form, frmMain that lists staff names in a combobox from a query | into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and | ShowInViews. The RowSource Query for the combobox is: | | SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE | (((lkpStaff.ShowInViews)=True)); | | The combobox ControlSource is the field Staff in tblMain which is populated | from frmMain. | | By unchecking ShowInViews for staff that are no longer employed I can remove | them from the combo's dropdown. However, of course, it also removes those | staff names from older entries. I want to have my cake and eat it too, | i.e., to remove the staff person as a choice going forward, while still | retaining them in older records. | | I hope that makes sense and thanks in advance. | | Doug | | | __________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________ | | The message was checked by ESET NOD32 Antivirus. | | http://www.eset.com | | | |
#3
|
|||
|
|||
Removing choices from combobox query but stilling showing for older entries
Len,
Len, Thanks for your time. I was afraid it wouldn't be clear. It's all the same combobox in the same form. Basically, I want to not show a name as a choice now, while still displaying that name for previous records. This is just on the form. The underlying data, as you suggest, is fine. I just want the database users to still see the name "John" for a record they entered on the form last year when John was an employee, but not be able to pick "John" as a choice today. Doug ".Len B" wrote in message ... It sound like you should be able to have your cake and eat it. However, you haven't made clear what you mean by "older entries". If you have another form or report using the same SELECT query, all you need to do is drop the WHERE clause. If you have a different SELECT query, omit ShowInViews altogether. -- Len __________________________________________________ ____ remove nothing for valid email address. "Doug Glancy" wrote in message ... |I have a form, frmMain that lists staff names in a combobox from a query | into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and | ShowInViews. The RowSource Query for the combobox is: | | SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE | (((lkpStaff.ShowInViews)=True)); | | The combobox ControlSource is the field Staff in tblMain which is populated | from frmMain. | | By unchecking ShowInViews for staff that are no longer employed I can remove | them from the combo's dropdown. However, of course, it also removes those | staff names from older entries. I want to have my cake and eat it too, | i.e., to remove the staff person as a choice going forward, while still | retaining them in older records. | | I hope that makes sense and thanks in advance. | | Doug | | | __________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________ | | The message was checked by ESET NOD32 Antivirus. | | http://www.eset.com | | | __________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
#4
|
|||
|
|||
Removing choices from combobox query but stilling showing for older entries
Hi Doug,
Clearly, if John isn't in the list he cannot be displayed by the combo. Perhaps you could create a textbox the same location, size and shape as the existing combo and set it to use DLookUp to display John (or anyone in lkpStaff). You could use the OnCurrent event to make either the combo or the txtbox visible depending on ShowInViews. Maybe you would prefer to allow the combo box to show all employees and to write code for its AfterUpdate event to advise the user if a former employee is chosen and undo the choice. -- Len __________________________________________________ ____ remove nothing for valid email address. "Doug Glancy" wrote in message ... | Len, | | Len, | | Thanks for your time. I was afraid it wouldn't be clear. | | It's all the same combobox in the same form. Basically, I want to not show | a name as a choice now, while still displaying that name for previous | records. This is just on the form. The underlying data, as you suggest, | is fine. I just want the database users to still see the name "John" for a | record they entered on the form last year when John was an employee, but not | be able to pick "John" as a choice today. | | Doug | | ".Len B" wrote in message | ... | It sound like you should be able to have your cake and eat it. | However, you haven't made clear what you mean by "older entries". | | If you have another form or report using the same SELECT query, | all you need to do is drop the WHERE clause. If you have a | different SELECT query, omit ShowInViews altogether. | | -- | Len | __________________________________________________ ____ | remove nothing for valid email address. |
#5
|
|||
|
|||
Removing choices from combobox query but stilling showing for older entries
As you found, if you remove the item from the combo's RowSource, and the
bound field is hidden, the combo has no value to display so shows as blank. One approach is to leave the inactive people in the RowSource, but sort them to the bottom. Typically the RowSource would be something like this: SELECT StaffID, Staff.Surname & ", " + Staff.FirstName & IIf([Inactive], " (inactive)", Null) AS FullName FROM Staff ORDER BY Inactive DESC, Surname, FirstName; If that's not possible, you could use a query as the source for your form, adding the Staff table to the query so you have the FirstName and Surname fields available. Now you can place a text box on top of the combo (a bit narrower so the combo's drop-down still appears at the right), and the text box can show the staff name (even if it's not in the combo's RowSource.) In the text box's GotFocus event, SetFocus to the combo. It still goes blank when the combo gets focus, but at least it shows for all all other rows (even in a continuous form.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Doug Glancy" wrote in message ... I have a form, frmMain that lists staff names in a combobox from a query into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and ShowInViews. The RowSource Query for the combobox is: SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE (((lkpStaff.ShowInViews)=True)); The combobox ControlSource is the field Staff in tblMain which is populated from frmMain. By unchecking ShowInViews for staff that are no longer employed I can remove them from the combo's dropdown. However, of course, it also removes those staff names from older entries. I want to have my cake and eat it too, i.e., to remove the staff person as a choice going forward, while still retaining them in older records. I hope that makes sense and thanks in advance. Doug __________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
#6
|
|||
|
|||
Removing choices from combobox query but stilling showing for older entries
Allen and Len,
Thanks to both of you. I will take a look at using a textbox. Doug "Allen Browne" wrote in message ... As you found, if you remove the item from the combo's RowSource, and the bound field is hidden, the combo has no value to display so shows as blank. One approach is to leave the inactive people in the RowSource, but sort them to the bottom. Typically the RowSource would be something like this: SELECT StaffID, Staff.Surname & ", " + Staff.FirstName & IIf([Inactive], " (inactive)", Null) AS FullName FROM Staff ORDER BY Inactive DESC, Surname, FirstName; If that's not possible, you could use a query as the source for your form, adding the Staff table to the query so you have the FirstName and Surname fields available. Now you can place a text box on top of the combo (a bit narrower so the combo's drop-down still appears at the right), and the text box can show the staff name (even if it's not in the combo's RowSource.) In the text box's GotFocus event, SetFocus to the combo. It still goes blank when the combo gets focus, but at least it shows for all all other rows (even in a continuous form.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Doug Glancy" wrote in message ... I have a form, frmMain that lists staff names in a combobox from a query into a lookup table. The table, lkpStaff has 3 fields, StaffID, Staff and ShowInViews. The RowSource Query for the combobox is: SELECT lkpStaff.ThingID, lkpStaff.Thing FROM lkpStaff WHERE (((lkpStaff.ShowInViews)=True)); The combobox ControlSource is the field Staff in tblMain which is populated from frmMain. By unchecking ShowInViews for staff that are no longer employed I can remove them from the combo's dropdown. However, of course, it also removes those staff names from older entries. I want to have my cake and eat it too, i.e., to remove the staff person as a choice going forward, while still retaining them in older records. I hope that makes sense and thanks in advance. Doug __________ Information from ESET NOD32 Antivirus, version of virus signature database 4852 (20100209) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4854 (20100210) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4854 (20100210) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |
Thread Tools | |
Display Modes | |
|
|