A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

combo box source



 
 
Thread Tools Display Modes
  #1  
Old October 25th, 2008, 03:16 AM posted to microsoft.public.access.tablesdbdesign
dan
external usenet poster
 
Posts: 1,408
Default 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  
Old October 25th, 2008, 07:17 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old October 26th, 2008, 05:47 AM posted to microsoft.public.access.tablesdbdesign
dan
external usenet poster
 
Posts: 1,408
Default 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  
Old October 26th, 2008, 05:52 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old October 26th, 2008, 06:22 AM posted to microsoft.public.access.tablesdbdesign
dan
external usenet poster
 
Posts: 1,408
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.