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
|
|||
|
|||
Challenge: One table associated with several fields - but avoidin
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. |
#2
|
|||
|
|||
Challenge: One table associated with several fields - but avoidin
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 "Thanks, Buddy" Thanks, wrote in message ... 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. |
#3
|
|||
|
|||
Challenge: One table associated with several fields - but avo
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 "Thanks, Buddy" Thanks, wrote in message ... 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. . |
#4
|
|||
|
|||
Challenge: One table associated with several fields - but avo
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" wrote in message ... 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 "Thanks, Buddy" Thanks, wrote in message ... 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. . |
#5
|
|||
|
|||
Challenge: One table associated with several fields - but avo
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" wrote in message ... 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" wrote in message ... 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 "Thanks, Buddy" Thanks, wrote in message ... 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. . |
#6
|
|||
|
|||
Challenge: One table associated with several fields - but avo
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" wrote in message ... 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 "Thanks, Buddy" Thanks, wrote in message ... 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. . . |
#8
|
|||
|
|||
Challenge: One table associated with several fields - but avo
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" wrote in message ... 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" wrote in message ... 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 "Thanks, Buddy" Thanks, wrote in message ... 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. . . |
#9
|
|||
|
|||
Challenge: One table associated with several fields - but avo
You can prevent the same employee from being chosen twice by applying a
unique index to each of the fields Clerk, Door, Clean-up, and Charge. The employee's name would still appear in the combo box, but you wouldn't be allowed to select the name. You don't say (that I can see) if you are storing an EmployeeID number rather than the name, but you should be storing an unchanging number in Clerk, Door, etc., using something like the table structure for tblEmployee that Steve suggested. The following assumes you are storing an employeeID number in the four fields. One possibility for a new record is to enable only one combo box (e.g. Clerk) so that it must be selected first. To do this, in the form's Current event: Me.cboDoor.Enabled = Not Me.NewRecord Me.cboCleanUp.Enabled = Not Me.NewRecord Me.cboCharge.Enabled = Not Me.NewRecord If it is a new record, NewRecord is True, so Not Me.NewRecord is the same as False. The combo box cboClerk is always enabled. Its Row Source is whatever you are using now. In its After Update event: Dim strSQL as String Dim lngClerk as Long lngClerk = Me.cboClerk strSQL = "SELECT [EmployeeID], [FirstName],[LastName] " & _ "FROM tblEmployee " & _ "WHERE [EmployeeID] " & lngClerk & _ " ORDER BY [LastName], [FirstName]" Me.cboDoor.RowSource = strSQL Me.cboDoor.Enabled = True In the After Update for cboDoor you need to add another variable: Dim strSQL as String Dim lngClerk as Long, lngDoor as Long lngClerk = Me.cboClerk lngDoor = Me.cboDoor strSQL = "SELECT [EmployeeID], [FirstName],[LastName] " & _ "FROM tblEmployee " & _ "WHERE [EmployeeID] " & lngClerk & _ " AND [EmployeeID] " & lngDoor & _ " ORDER BY [LastName], [FirstName]" Me.cboCleanUp.RowSource = strSQL Me.cboCleanUp.Enabled = True This would not change the Row Source for an existing record, so if you want to go back to a record and change a name it would be different coding. It seems to me it would be more difficult then, in that if you want to swap names (Clerk and Door, for instance) you would first need to clear cboClerk, then change the name in cboDoor, then update cboClerk. Also, if you change the RowSource you need to change it to something else when you go to another record, or the list will remain as you set it. It depends on what you want to see in the list when you go to an existing record. If you want the list exclude the selected people in that record you would need to set the Row Source in the form's Current event. However, then you would not be able to swap two of the names as described above unless you make allowance for that in the coding. It is manageable enough for a new record, but for an existing record you need to make a number of choices about how to proceed. There may be a cleaner approach than this, but this is what I came up with. Thanks, Buddy wrote: 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 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 [quoted text clipped - 18 lines] . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200912/1 |
Thread Tools | |
Display Modes | |
|
|