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
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 |
#7
|
|||
|
|||
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. . . |
#9
|
|||
|
|||
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. . . |
#10
|
|||
|
|||
Challenge: One table associated with several fields - but avo
The OP is not clear about what type of record he is creating in which the
four positions are filled. I see now that I made an assumption that it was some sort of scheduling thing, and that a new record would be created periodically. That assumption may not be warranted, but neither is your assumption about the structure, as the database purpose is not clearly defined. What is most definitely not warranted is your continually mistaken belief that these newsgroups are for anything other than free peer-to-peer support. When you told the employee the design was wrong, then posted a sketchy table structure, I wondered if you were heading in the direction of a solicitiation. I will repeat: these newsgroups are not your personal advertising forum. Commercial solicitations are inappropriate. If you haven't learned that by now there is no reason to think you can handle Access. Steve wrote: 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 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 [quoted text clipped - 89 lines] . -- Message posted via http://www.accessmonster.com |
|
Thread Tools | |
Display Modes | |
|
|