Challenge: One table associated with several fields - but avoidin

T

Thanks, Buddy

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.
 
S

Steve

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 address removed)
 
O

orange

Joined
Dec 17, 2007
Messages
57
Reaction score
0
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
 
Last edited:
T

Thanks, Buddy

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
 
S

Steve

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
 
G

Gina Whipp

T

Thanks, Buddy

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
 
S

Steve

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 address removed)
 
J

John... Visio MVP

Steve said:
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 address removed)
Click to expand...


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)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


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 said:
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
Click to expand...

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 said:
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
Click to expand...


John... Visio MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PLEASE help me with parameter queries & table relationships!!! 1
Listing Data with Certain Qualifications 6
Please help me with Naturalizing table 1
Table Challenge 1
Need 'entered' value added to list 8
Drop Down Lists - Shrinking the List 5
Autonum, but not? :S 6
Training Database 5

Top