combo box source

[Dan]

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?

 

[Allen Browne]

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.

 

[Dan]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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?

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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?