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
|
|||
|
|||
combo box source
I have an emplyee database with tbl_employees, fields for name address phone,
etc... I have another table for locker assignments, lockernumber, and a assigned to field combo box based on: SELECT tbl_employees.EmployeeID, [LastName] & ", " & [FirstName] AS Contact, tbl_employees.LastName, tbl_employees.FirstName, tbl_employees.Active FROM tbl_employees WHERE (((tbl_employees.Active)=Yes)) ORDER BY tbl_employees.LastName, tbl_employees.FirstName; this allows me to only assign lockers to active employees. this part works well. my issue is when a employee leaves, I change the status to inactive. in the form for Lockers the name goes blank, which is what I wanted, however when I check the actual table the combo box shows the employees number. How do I make the field go blank, when a emplyee becomes inactive? -- Dan |
#2
|
|||
|
|||
combo box source
Good question, Dan. I think everyone faces this one sooner or later.
My personal response is not to exclude the inactive people, but to sort them to the bottom of the list. That way the names don't go blank (implying unassigned), but Access does not use the inactive people's names when it autocompletes an entry either. SELECT tbl_employees.EmployeeID, [LastName] & ", " + [FirstName] & IIf(Active, Null, " (inactive)") AS Contact, FROM tbl_employees ORDER BY tbl_employees.Active DESC, tbl_employees.LastName, tbl_employees.FirstName; FWIW, I always you a saved query as the RowSource (rather than a SQL statement.) That way there is only one place you have to make the change for something like this, and all the employee combos in your application stay consistent. (I actually use a different prefix for these - lq for lookup query, rather than qry for query - so they all sort together. -- 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. "Dan" wrote in message ... I have an emplyee database with tbl_employees, fields for name address phone, etc... I have another table for locker assignments, lockernumber, and a assigned to field combo box based on: SELECT tbl_employees.EmployeeID, [LastName] & ", " & [FirstName] AS Contact, tbl_employees.LastName, tbl_employees.FirstName, tbl_employees.Active FROM tbl_employees WHERE (((tbl_employees.Active)=Yes)) ORDER BY tbl_employees.LastName, tbl_employees.FirstName; this allows me to only assign lockers to active employees. this part works well. my issue is when a employee leaves, I change the status to inactive. in the form for Lockers the name goes blank, which is what I wanted, however when I check the actual table the combo box shows the employees number. How do I make the field go blank, when a emplyee becomes inactive? -- Dan |
#3
|
|||
|
|||
combo box source
Thanks Allen, your Posts and website have been very helpful.
What you say makes sense, however I get an error with you code "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" I dont see any spelling errors, so I wonder if something missing. I am too new to code to fix it. thanks again -- Dan "Allen Browne" wrote: Good question, Dan. I think everyone faces this one sooner or later. My personal response is not to exclude the inactive people, but to sort them to the bottom of the list. That way the names don't go blank (implying unassigned), but Access does not use the inactive people's names when it autocompletes an entry either. SELECT tbl_employees.EmployeeID, [LastName] & ", " + [FirstName] & IIf(Active, Null, " (inactive)") AS Contact, FROM tbl_employees ORDER BY tbl_employees.Active DESC, tbl_employees.LastName, tbl_employees.FirstName; FWIW, I always you a saved query as the RowSource (rather than a SQL statement.) That way there is only one place you have to make the change for something like this, and all the employee combos in your application stay consistent. (I actually use a different prefix for these - lq for lookup query, rather than qry for query - so they all sort together. -- 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. "Dan" wrote in message ... I have an emplyee database with tbl_employees, fields for name address phone, etc... I have another table for locker assignments, lockernumber, and a assigned to field combo box based on: SELECT tbl_employees.EmployeeID, [LastName] & ", " & [FirstName] AS Contact, tbl_employees.LastName, tbl_employees.FirstName, tbl_employees.Active FROM tbl_employees WHERE (((tbl_employees.Active)=Yes)) ORDER BY tbl_employees.LastName, tbl_employees.FirstName; this allows me to only assign lockers to active employees. this part works well. my issue is when a employee leaves, I change the status to inactive. in the form for Lockers the name goes blank, which is what I wanted, however when I check the actual table the combo box shows the employees number. How do I make the field go blank, when a emplyee becomes inactive? -- Dan |
#4
|
|||
|
|||
combo box source
Revert to the SQL statement you had in the combo's RowSource property.
Click in the property, and click the Build button (...) beside this. This will open the query design window, where you can work graphically rather than with code. BTW, you don't need the DESC. (I use that because my field is called Inactive, reversed from yours.) -- 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. "Dan" wrote in message ... Thanks Allen, your Posts and website have been very helpful. What you say makes sense, however I get an error with you code "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" I dont see any spelling errors, so I wonder if something missing. I am too new to code to fix it. thanks again -- Dan "Allen Browne" wrote: Good question, Dan. I think everyone faces this one sooner or later. My personal response is not to exclude the inactive people, but to sort them to the bottom of the list. That way the names don't go blank (implying unassigned), but Access does not use the inactive people's names when it autocompletes an entry either. SELECT tbl_employees.EmployeeID, [LastName] & ", " + [FirstName] & IIf(Active, Null, " (inactive)") AS Contact, FROM tbl_employees ORDER BY tbl_employees.Active DESC, tbl_employees.LastName, tbl_employees.FirstName; FWIW, I always you a saved query as the RowSource (rather than a SQL statement.) That way there is only one place you have to make the change for something like this, and all the employee combos in your application stay consistent. (I actually use a different prefix for these - lq for lookup query, rather than qry for query - so they all sort together. -- 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. "Dan" wrote in message ... I have an emplyee database with tbl_employees, fields for name address phone, etc... I have another table for locker assignments, lockernumber, and a assigned to field combo box based on: SELECT tbl_employees.EmployeeID, [LastName] & ", " & [FirstName] AS Contact, tbl_employees.LastName, tbl_employees.FirstName, tbl_employees.Active FROM tbl_employees WHERE (((tbl_employees.Active)=Yes)) ORDER BY tbl_employees.LastName, tbl_employees.FirstName; this allows me to only assign lockers to active employees. this part works well. my issue is when a employee leaves, I change the status to inactive. in the form for Lockers the name goes blank, which is what I wanted, however when I check the actual table the combo box shows the employees number. How do I make the field go blank, when a emplyee becomes inactive? -- Dan |
#5
|
|||
|
|||
combo box source
seems to be working now. I also made it a seperate query, and based the row
source on the query. I use an emplyee combobox in a few spots, only having to update 1 query makes alot more sense and sorting all the inactive to the bottom makes it easier too. thanks again -- Dan "Allen Browne" wrote: Revert to the SQL statement you had in the combo's RowSource property. Click in the property, and click the Build button (...) beside this. This will open the query design window, where you can work graphically rather than with code. BTW, you don't need the DESC. (I use that because my field is called Inactive, reversed from yours.) -- 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. "Dan" wrote in message ... Thanks Allen, your Posts and website have been very helpful. What you say makes sense, however I get an error with you code "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" I dont see any spelling errors, so I wonder if something missing. I am too new to code to fix it. thanks again -- Dan "Allen Browne" wrote: Good question, Dan. I think everyone faces this one sooner or later. My personal response is not to exclude the inactive people, but to sort them to the bottom of the list. That way the names don't go blank (implying unassigned), but Access does not use the inactive people's names when it autocompletes an entry either. SELECT tbl_employees.EmployeeID, [LastName] & ", " + [FirstName] & IIf(Active, Null, " (inactive)") AS Contact, FROM tbl_employees ORDER BY tbl_employees.Active DESC, tbl_employees.LastName, tbl_employees.FirstName; FWIW, I always you a saved query as the RowSource (rather than a SQL statement.) That way there is only one place you have to make the change for something like this, and all the employee combos in your application stay consistent. (I actually use a different prefix for these - lq for lookup query, rather than qry for query - so they all sort together. -- 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. "Dan" wrote in message ... I have an emplyee database with tbl_employees, fields for name address phone, etc... I have another table for locker assignments, lockernumber, and a assigned to field combo box based on: SELECT tbl_employees.EmployeeID, [LastName] & ", " & [FirstName] AS Contact, tbl_employees.LastName, tbl_employees.FirstName, tbl_employees.Active FROM tbl_employees WHERE (((tbl_employees.Active)=Yes)) ORDER BY tbl_employees.LastName, tbl_employees.FirstName; this allows me to only assign lockers to active employees. this part works well. my issue is when a employee leaves, I change the status to inactive. in the form for Lockers the name goes blank, which is what I wanted, however when I check the actual table the combo box shows the employees number. How do I make the field go blank, when a emplyee becomes inactive? -- Dan |
Thread Tools | |
Display Modes | |
|
|