I'm using the newest version of ACCESS. I have a (table) list of employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that I want to
schedule using the same list of employees. How do I use this same list so
that if I choose an employee for clerk, that when I fill in the employee for
door - that employee can not be chosen again (i.e. I want to avoid the error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.

You need to use a form for choosing employees and their positions. On your
form use a combobox to select an employee and setup the combobox so that
when an employee us selected and assigned a position, that employee is
removed from the rowsource of the combobox.

Steve
(E-Mail Removed)


"Thanks, Buddy" <Thanks, (E-Mail Removed)> wrote in message
news:A06C8902-024D-499A-9F90-(E-Mail Removed)...
> I'm using the newest version of ACCESS. I have a (table) list of
> employees.
> I have a form with 4 positions (clerk, door, clean-up, charge) that I want
> to
> schedule using the same list of employees. How do I use this same list so
> that if I choose an employee for clerk, that when I fill in the employee
> for
> door - that employee can not be chosen again (i.e. I want to avoid the
> error
> of scheduling the same employee for two or more positions).
>
> Thanks! hope that made sense.

[orange]

Use the Employee table as a source for names in a listbox or combobox on your form. Once you selected the employee for the first position, you would remove that person from "candidates" for the next position by using SQL like
select employeeName from employeeTable where employeename <>me.position1.value

Then Select the employee for position 2, then adjust your SQL to remove both position1 candidate and position2 candidate with SQL such as
select employeename from employeeTable where employeename <> me.position1.value and employeename <> me.position2.value

This is the approach, you'll have to check SQL syntax carefully. You can build the SQL using vba. You willl likely use the afterUpdate events of the Position textboxes

Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share the
same list (my list of employees). So if I choose an employee for field one
(clerk), when I go to field two - door, that employee's name won't appear on
the list any longer? Wow? I looked up combobox in my access book and it
doesn't tell me how to remove that employee from the row source...this is
great news if I can really do this, but can you explain a little bit more?
Is there an option on combobox that I choose to make this happen?

Thanks, Buddy

"Steve" wrote:

> You need to use a form for choosing employees and their positions. On your
> form use a combobox to select an employee and setup the combobox so that
> when an employee us selected and assigned a position, that employee is
> removed from the rowsource of the combobox.
>
> Steve
> (E-Mail Removed)
>
>
> "Thanks, Buddy" <Thanks, (E-Mail Removed)> wrote in message
> news:A06C8902-024D-499A-9F90-(E-Mail Removed)...
> > I'm using the newest version of ACCESS. I have a (table) list of
> > employees.
> > I have a form with 4 positions (clerk, door, clean-up, charge) that I want
> > to
> > schedule using the same list of employees. How do I use this same list so
> > that if I choose an employee for clerk, that when I fill in the employee
> > for
> > door - that employee can not be chosen again (i.e. I want to avoid the
> > error
> > of scheduling the same employee for two or more positions).
> >
> > Thanks! hope that made sense.
>
>
> .
>

It sounds like your tables are not correct. They should be something like:
TblEmployee
EmployeeID
FirstName
LastName
etc

TblPosition
PositionID
Position

TblSchedule
ScheduleID
ScheduleDate

TblScheduleEmployeePosition
ScheduleEmployeePositionID
ScheduleID
EmployeeID
PositionID

There is no built-in way in a combobox to remove that employee from the row
source. You need to make the row source a query and then design the query to
remove all employees previously selected from the row source.

Steve




"Thanks, Buddy" <(E-Mail Removed)> wrote in message
news:08925389-7D59-4A10-8BB1-(E-Mail Removed)...
> Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
> door, clean-up, and charge ( a total of 4 fields) that I want to share the
> same list (my list of employees). So if I choose an employee for field
> one
> (clerk), when I go to field two - door, that employee's name won't appear
> on
> the list any longer? Wow? I looked up combobox in my access book and it
> doesn't tell me how to remove that employee from the row source...this is
> great news if I can really do this, but can you explain a little bit more?
> Is there an option on combobox that I choose to make this happen?
>
> Thanks, Buddy
>
> "Steve" wrote:
>
>> You need to use a form for choosing employees and their positions. On
>> your
>> form use a combobox to select an employee and setup the combobox so that
>> when an employee us selected and assigned a position, that employee is
>> removed from the rowsource of the combobox.
>>
>> Steve
>> (E-Mail Removed)
>>
>>
>> "Thanks, Buddy" <Thanks, (E-Mail Removed)> wrote in
>> message
>> news:A06C8902-024D-499A-9F90-(E-Mail Removed)...
>> > I'm using the newest version of ACCESS. I have a (table) list of
>> > employees.
>> > I have a form with 4 positions (clerk, door, clean-up, charge) that I
>> > want
>> > to
>> > schedule using the same list of employees. How do I use this same list
>> > so
>> > that if I choose an employee for clerk, that when I fill in the
>> > employee
>> > for
>> > door - that employee can not be chosen again (i.e. I want to avoid the
>> > error
>> > of scheduling the same employee for two or more positions).
>> >
>> > Thanks! hope that made sense.
>>
>>
>> .
>>

Buudy,

Please note... *Position* is a Reserved Word and will cause you problems
because it is a problem for Access. For a complete list of Reserved Words
see... http://allenbrowne.com/Ap****ueBadWord.html To avoide such problem
you can prefix the field names with the table names, ie...

tblPosition
pPositionID
pPosition

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Steve" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> It sounds like your tables are not correct. They should be something like:
> TblEmployee
> EmployeeID
> FirstName
> LastName
> etc
>
> TblPosition
> PositionID
> Position
>
> TblSchedule
> ScheduleID
> ScheduleDate
>
> TblScheduleEmployeePosition
> ScheduleEmployeePositionID
> ScheduleID
> EmployeeID
> PositionID
>
> There is no built-in way in a combobox to remove that employee from the
> row source. You need to make the row source a query and then design the
> query to remove all employees previously selected from the row source.
>
> Steve
>
>
>
>
> "Thanks, Buddy" <(E-Mail Removed)> wrote in message
> news:08925389-7D59-4A10-8BB1-(E-Mail Removed)...
>> Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
>> door, clean-up, and charge ( a total of 4 fields) that I want to share
>> the
>> same list (my list of employees). So if I choose an employee for field
>> one
>> (clerk), when I go to field two - door, that employee's name won't appear
>> on
>> the list any longer? Wow? I looked up combobox in my access book and it
>> doesn't tell me how to remove that employee from the row source...this is
>> great news if I can really do this, but can you explain a little bit
>> more?
>> Is there an option on combobox that I choose to make this happen?
>>
>> Thanks, Buddy
>>
>> "Steve" wrote:
>>
>>> You need to use a form for choosing employees and their positions. On
>>> your
>>> form use a combobox to select an employee and setup the combobox so that
>>> when an employee us selected and assigned a position, that employee is
>>> removed from the rowsource of the combobox.
>>>
>>> Steve
>>> (E-Mail Removed)
>>>
>>>
>>> "Thanks, Buddy" <Thanks, (E-Mail Removed)> wrote in
>>> message
>>> news:A06C8902-024D-499A-9F90-(E-Mail Removed)...
>>> > I'm using the newest version of ACCESS. I have a (table) list of
>>> > employees.
>>> > I have a form with 4 positions (clerk, door, clean-up, charge) that I
>>> > want
>>> > to
>>> > schedule using the same list of employees. How do I use this same list
>>> > so
>>> > that if I choose an employee for clerk, that when I fill in the
>>> > employee
>>> > for
>>> > door - that employee can not be chosen again (i.e. I want to avoid the
>>> > error
>>> > of scheduling the same employee for two or more positions).
>>> >
>>> > Thanks! hope that made sense.
>>>
>>>
>>> .
>>>
>
>

Thanks Steve. I'm beginning to think this project is above my ability, but
I'm willing to challenge myself. I just don't get how to 'make the row
source a query and then design the query to remove all employees previously
selected from the row source'. I'll play around with it. I'm also looking
for a template out there for someone else who may have run into the same
problem.

Thanks,
Buddy
"Steve" wrote:

> It sounds like your tables are not correct. They should be something like:
> TblEmployee
> EmployeeID
> FirstName
> LastName
> etc
>
> TblPosition
> PositionID
> Position
>
> TblSchedule
> ScheduleID
> ScheduleDate
>
> TblScheduleEmployeePosition
> ScheduleEmployeePositionID
> ScheduleID
> EmployeeID
> PositionID
>
> There is no built-in way in a combobox to remove that employee from the row
> source. You need to make the row source a query and then design the query to
> remove all employees previously selected from the row source.
>
> Steve
>
>
>
>
> "Thanks, Buddy" <(E-Mail Removed)> wrote in message
> news:08925389-7D59-4A10-8BB1-(E-Mail Removed)...
> > Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
> > door, clean-up, and charge ( a total of 4 fields) that I want to share the
> > same list (my list of employees). So if I choose an employee for field
> > one
> > (clerk), when I go to field two - door, that employee's name won't appear
> > on
> > the list any longer? Wow? I looked up combobox in my access book and it
> > doesn't tell me how to remove that employee from the row source...this is
> > great news if I can really do this, but can you explain a little bit more?
> > Is there an option on combobox that I choose to make this happen?
> >
> > Thanks, Buddy
> >
> > "Steve" wrote:
> >
> >> You need to use a form for choosing employees and their positions. On
> >> your
> >> form use a combobox to select an employee and setup the combobox so that
> >> when an employee us selected and assigned a position, that employee is
> >> removed from the rowsource of the combobox.
> >>
> >> Steve
> >> (E-Mail Removed)
> >>
> >>
> >> "Thanks, Buddy" <Thanks, (E-Mail Removed)> wrote in
> >> message
> >> news:A06C8902-024D-499A-9F90-(E-Mail Removed)...
> >> > I'm using the newest version of ACCESS. I have a (table) list of
> >> > employees.
> >> > I have a form with 4 positions (clerk, door, clean-up, charge) that I
> >> > want
> >> > to
> >> > schedule using the same list of employees. How do I use this same list
> >> > so
> >> > that if I choose an employee for clerk, that when I fill in the
> >> > employee
> >> > for
> >> > door - that employee can not be chosen again (i.e. I want to avoid the
> >> > error
> >> > of scheduling the same employee for two or more positions).
> >> >
> >> > Thanks! hope that made sense.
> >>
> >>
> >> .
> >>
>
>
> .
>

You would use a combobox to enter EmployeeID in the form based on
TblScheduleEmployeePosition. The rowsource of the combobox would be a query.
To create the query, go to the list of queries and select new. One of the
options would be the Unmatched Query Wizard. Use the wizard and select
TblEmployee then in the next step select TblScheduleEmployeePosition. You
want the query to return all EmployeeIDs in TblEmployee not found in
TblScheduleEmployeePosition. In your query, make the first column EmployeeID
and the second column the following expression:
Employee = [LastName] & ", " & [FirstName]
Set the following properties in the combobox:
RowSource NameOfTheQuery
Bound Column 1
Column Count 2
Column Width 0,2

If this project is above your ability, I can help you. I provide fee-based
help with Access, Excel and Word applications. My fee to help you would be
very reasonable. If you want my help, contact me.

Steve
(E-Mail Removed)

"Thanks, Buddy" <(E-Mail Removed)> wrote in message
news:C6EE3905-0320-43CF-AD6C-(E-Mail Removed)...
> Thanks Steve. I'm beginning to think this project is above my ability, but
> I'm willing to challenge myself. I just don't get how to 'make the row
> source a query and then design the query to remove all employees
> previously
> selected from the row source'. I'll play around with it. I'm also
> looking
> for a template out there for someone else who may have run into the same
> problem.
>
> Thanks,
> Buddy
> "Steve" wrote:
>
>> It sounds like your tables are not correct. They should be something
>> like:
>> TblEmployee
>> EmployeeID
>> FirstName
>> LastName
>> etc
>>
>> TblPosition
>> PositionID
>> Position
>>
>> TblSchedule
>> ScheduleID
>> ScheduleDate
>>
>> TblScheduleEmployeePosition
>> ScheduleEmployeePositionID
>> ScheduleID
>> EmployeeID
>> PositionID
>>
>> There is no built-in way in a combobox to remove that employee from the
>> row
>> source. You need to make the row source a query and then design the query
>> to
>> remove all employees previously selected from the row source.
>>
>> Steve
>>
>>
>>
>>
>> "Thanks, Buddy" <(E-Mail Removed)> wrote in message
>> news:08925389-7D59-4A10-8BB1-(E-Mail Removed)...
>> > Thanks, Steve. Let me clarify to make sure. I have one field for
>> > clerk,
>> > door, clean-up, and charge ( a total of 4 fields) that I want to share
>> > the
>> > same list (my list of employees). So if I choose an employee for field
>> > one
>> > (clerk), when I go to field two - door, that employee's name won't
>> > appear
>> > on
>> > the list any longer? Wow? I looked up combobox in my access book and
>> > it
>> > doesn't tell me how to remove that employee from the row source...this
>> > is
>> > great news if I can really do this, but can you explain a little bit
>> > more?
>> > Is there an option on combobox that I choose to make this happen?
>> >
>> > Thanks, Buddy
>> >
>> > "Steve" wrote:
>> >
>> >> You need to use a form for choosing employees and their positions. On
>> >> your
>> >> form use a combobox to select an employee and setup the combobox so
>> >> that
>> >> when an employee us selected and assigned a position, that employee is
>> >> removed from the rowsource of the combobox.
>> >>
>> >> Steve
>> >> (E-Mail Removed)
>> >>
>> >>
>> >> "Thanks, Buddy" <Thanks, (E-Mail Removed)> wrote in
>> >> message
>> >> news:A06C8902-024D-499A-9F90-(E-Mail Removed)...
>> >> > I'm using the newest version of ACCESS. I have a (table) list of
>> >> > employees.
>> >> > I have a form with 4 positions (clerk, door, clean-up, charge) that
>> >> > I
>> >> > want
>> >> > to
>> >> > schedule using the same list of employees. How do I use this same
>> >> > list
>> >> > so
>> >> > that if I choose an employee for clerk, that when I fill in the
>> >> > employee
>> >> > for
>> >> > door - that employee can not be chosen again (i.e. I want to avoid
>> >> > the
>> >> > error
>> >> > of scheduling the same employee for two or more positions).
>> >> >
>> >> > Thanks! hope that made sense.
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

Buddy,

See Bruce's reply he gives you a way to do what you want... for FREE.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Thanks, Buddy" <(E-Mail Removed)> wrote in message
news:C6EE3905-0320-43CF-AD6C-(E-Mail Removed)...
> Thanks Steve. I'm beginning to think this project is above my ability, but
> I'm willing to challenge myself. I just don't get how to 'make the row
> source a query and then design the query to remove all employees
> previously
> selected from the row source'. I'll play around with it. I'm also
> looking
> for a template out there for someone else who may have run into the same
> problem.
>
> Thanks,
> Buddy
> "Steve" wrote:
>
>> It sounds like your tables are not correct. They should be something
>> like:
>> TblEmployee
>> EmployeeID
>> FirstName
>> LastName
>> etc
>>
>> TblPosition
>> PositionID
>> Position
>>
>> TblSchedule
>> ScheduleID
>> ScheduleDate
>>
>> TblScheduleEmployeePosition
>> ScheduleEmployeePositionID
>> ScheduleID
>> EmployeeID
>> PositionID
>>
>> There is no built-in way in a combobox to remove that employee from the
>> row
>> source. You need to make the row source a query and then design the query
>> to
>> remove all employees previously selected from the row source.
>>
>> Steve
>>
>>
>>
>>
>> "Thanks, Buddy" <(E-Mail Removed)> wrote in message
>> news:08925389-7D59-4A10-8BB1-(E-Mail Removed)...
>> > Thanks, Steve. Let me clarify to make sure. I have one field for
>> > clerk,
>> > door, clean-up, and charge ( a total of 4 fields) that I want to share
>> > the
>> > same list (my list of employees). So if I choose an employee for field
>> > one
>> > (clerk), when I go to field two - door, that employee's name won't
>> > appear
>> > on
>> > the list any longer? Wow? I looked up combobox in my access book and
>> > it
>> > doesn't tell me how to remove that employee from the row source...this
>> > is
>> > great news if I can really do this, but can you explain a little bit
>> > more?
>> > Is there an option on combobox that I choose to make this happen?
>> >
>> > Thanks, Buddy
>> >
>> > "Steve" wrote:
>> >
>> >> You need to use a form for choosing employees and their positions. On
>> >> your
>> >> form use a combobox to select an employee and setup the combobox so
>> >> that
>> >> when an employee us selected and assigned a position, that employee is
>> >> removed from the rowsource of the combobox.
>> >>
>> >> Steve
>> >> (E-Mail Removed)
>> >>
>> >>
>> >> "Thanks, Buddy" <Thanks, (E-Mail Removed)> wrote in
>> >> message
>> >> news:A06C8902-024D-499A-9F90-(E-Mail Removed)...
>> >> > I'm using the newest version of ACCESS. I have a (table) list of
>> >> > employees.
>> >> > I have a form with 4 positions (clerk, door, clean-up, charge) that
>> >> > I
>> >> > want
>> >> > to
>> >> > schedule using the same list of employees. How do I use this same
>> >> > list
>> >> > so
>> >> > that if I choose an employee for clerk, that when I fill in the
>> >> > employee
>> >> > for
>> >> > door - that employee can not be chosen again (i.e. I want to avoid
>> >> > the
>> >> > error
>> >> > of scheduling the same employee for two or more positions).
>> >> >
>> >> > Thanks! hope that made sense.
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

"Steve" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> If this project is above your ability, I can help you. I provide fee-based
> help with Access, Excel and Word applications. My fee to help you would be
> very reasonable. If you want my help, contact me.
>
> Steve
> (E-Mail Removed)


This project is definitely beyond stevie's abilities.


These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past Christmas
period and a few gems from the Access newsgroups to show Stevie's
"expertise".


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Steve wrote:
> Yes, you are right but a database is the correct tool to use not a
> spreadsheet.


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...


Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve



Sept 9, 2009
"Steve" <(E-Mail Removed)> wrote in message
> you can then return all the characters in front of it with the Left()
> fumction. Would look like:
> Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)
>
> Steve

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.



Sept 22,2009
Sorry Steve, even I can see that this is a useless answer. I made it pretty
clear that "CW259" is just ONE possible value for the control.

"Steve" wrote:

> Hello David,
>
> Open your report in design view and select txtOrderID. Open properties and
> go to the Data tab. Put the following expression in the Control Source
> property:
>
> =IIF([chkActive],"CW259","(CW259)")
>
> Steve


John... Visio MVP