 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / September 2007eliminate a choice from dropdown list once selected
I am trying to put together a FORM where the employee (mgr) schedules cleaning jobs to be done for that day. Each record will be a given DAY. It will include the employees available for that day and then ALL jobs that need to be done. Once the job is scheduled for an employee for that day - I would like that choice to NO longer show up on the drop down list for that Given day. Any ideas on how to not let is show up once selected and they on a new record (new day) all dropdowns for jobs get repopulated and start all over again? Thanks, Barb > I am trying to put together a FORM where the employee (mgr) > schedules cleaning jobs to be done for that day. [quoted text clipped - 10 lines] > Thanks, > Barb use a subquery to filter the employees who have been assigned a job on that day as a NOT IN() criteria. You did not post the table/form names so change mine to suit. The combobox rowsource should be: SELECT empID, empName from Employees where empID not in (SELECT empID from Assignments where DateAssignment = Forms!schedule! txtDateAssignment);  SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Sorry if I wasn't clear- it is not the employee I want eliminated from the dropdown but the job. One employee can do many jobs in a given day but the job only needs to be done once I am trying to make a DAILY Master Schedule of who does what jobs for that day. I didn't realize but still need help on putting the Form/Subform(s) together. I would like Todays date to be the top - Main form and as a subform(maybe-not sure) have an employee and then as another subform(tried with form wizard-didn't work out) what jobs they are assigned for TODAY. Then below that the next employee and what they are assigned for TODAY. AS each job gets assigned for TODAY it gets removed from the dropdown list for each employee subform. Here are some of the tables I have tblCleaning Cleanid(PK) joblocautonum location job date Clockid TblPersonnel Clockid(PK) Firstname Lastname Status Shift Frequency TblJobsAtLoc JoblocAutonum(PK) Job Location Room estTime ProcLink MixingIstr Clockid Not sure where the clockid belongs in all 3 tables??? Or if need to set up a query to base what type of form(s) it would like- see beginning info. Thanks for your help, Barb > > I am trying to put together a FORM where the employee (mgr) > > schedules cleaning jobs to be done for that day. [quoted text clipped - 20 lines] > txtDateAssignment); > > Sorry if I wasn't clear- it is not the employee I want > eliminated from the dropdown but the job. One employee can do > many jobs in a given day but the job only needs to be done > once Understood. But the principle should be the same. > I am trying to make a DAILY Master Schedule of who does what > jobs for that day. I didn't realize but still need help on [quoted text clipped - 3 lines] > form wizard-didn't work out) what jobs they are assigned for > TODAY. Ok, I see this scenario. You have a list for jobs. Say jobs are tied to a schedule, like every tuesday, every weekday, the 15th of each month. You should also indicate which shift will the job be done. I don't see that in the TblJobsAtLoc And you should not need the ClockID in that table. You have a form with a textbox for the date, and one for shift if it's important. You have a listbox of employees.to pick from on the left. You then have a combobox that holds all the (available) jobs for that date(and shift if that's important),.Below that you have a subform that holds the jobs that have been assigned to the employee that have already been assigned for thae date and shift in the form's header. The subfrm's link parent fields are the date textbox, shift textbox and employee listbox. link child fields come from the subform based on tblcleaning. When you select a new job from the combobox, it should add a record to the table in the subform, Then you want to requery the combobox, which should have a rowsource that filters tbljobsatlocation for date or the day of the week, shift and the joblocautonum (what a horrible fieldname) tthat is not in the tblcleaning for that employee and date/shift info. The rowsource I see would be along the lines of SELECT JoblocAutonum, Job, Location, Room FROM TblJobsAtLoc WHERE JoblocAutonum NOT IN (SELECT JoblocAutonum from tblCleaning WHERE Clockid = Forms!myForm!lstEmployees AND [Date] = Forms!myForm!txtDate AND Shift = Forms!myForm!txtShift ) Good luck! BTW date is a reserved name in Access and you may have all sorts of issues using it in a table. > Then below that the next employee and what they are assigned > for TODAY. AS each job gets assigned for TODAY it gets [quoted text clipped - 59 lines] >> Forms!schedule! txtDateAssignment); >> SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Just want to make sure I understand before I redive in. So in the main form the job combo box is just really there for Scheduling purposes - to select to input into subform? for the main form with date and shift and employee and I making a query for that or just getting it from the employee table and not sure where to get the DATE field from - I really wanted ONE Record of main form to be based on 1 DATE - See listed all employees as as subforma nd and then a subform within that of who is assigned to do what jobs. Anyway possible??? Thanks, Barb > > Sorry if I wasn't clear- it is not the employee I want > > eliminated from the dropdown but the job. One employee can do [quoted text clipped - 116 lines] > >> Forms!schedule! txtDateAssignment); > >> > Just want to make sure I understand before I redive in. > > So in the main form the job combo box is just really there for > Scheduling purposes - to select to input into subform? Yes. The main form is not necessarily bound to any table or query. You could create a calendar table with just a date/shift in it. > for the main form with date and shift and employee and I > making a query for that or just getting it from the employee > table and not sure where to get the DATE field from - Date and shift could be unbound textboxes or bound to the calendar table, You could populate the date with a function or let the scheduler type in whatever date he wants. > I really wanted ONE Record of main form to be based on 1 DATE > - See listed all employees as as subforma nd and then a > subform within that of who is assigned to do what jobs. > > Anyway possible??? A subform cannot be placed on a continuous form. so you cannot have a subform on the employees subform, that's why I used a listbox instead. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com I feel like I am getting closer -Please help me see this thru if possible. See below I have a drop downlist in a subform (cleaning jobs) main from is the one side - who is the employee and subform shows what many jobs the boss is assigning them. For the bosses sake I would like the joblocautnum to list ALL JObs at the given locations that have NOT yet been assigned for that given Date. Thought I would create an after update event for the next record- Not sure if this is the correct place to put it - and somewhere else??? that would include all jobs at the location but NOT the ones already assigned for that day. I am sure I am messing up on the syntax somewhere. I get the drop down and select the joblocautonum for the first record in the subform fine when I go to the next record in the subform to assign the next job and hoping that the one I just assigned for that day is not available - I get two errors and then see the drop down list WITH ALL choices even the one previously scheduled. Here are the erros Enter Parameter Value Forms!sbfjncCleaning!cbojoblocautonum Enter Parameter Value Forms!sbfjncCleaning!txtdateassigned I checked all the names of the forms and text boxes and they all match up not sure since a subform if I need something extra???? Also have a feeling something not right with the syntax??? See below Private Sub cbojoblocautonum_AfterUpdate() Dim lsql 'This function set the rowsource of JoblocAutonum,getting rid of what has already been assigned for a given day lsql = "Select joblocautonum, job, location, room " & "FROM tbljobsatlocnew " & "WHERE joblocAutonum NOT IN (Select joblocautonum from tbljnccleaning where [joblocautonum] = forms!sbfjnccleaning!cbojoblocautonum and [dateassigned] = forms!sbfjnccleaning!txtdateassigned)" Me.cbojoblocautonum.RowSource = lsql End Sub Need to get this done soon if possible - Thanks in advance for the help, Barb > > Just want to make sure I understand before I redive in. > > [quoted text clipped - 22 lines] > have a subform on the employees subform, that's why I used a > listbox instead. > I am sure I am messing up on the syntax somewhere. > [quoted text clipped - 10 lines] > Enter Parameter Value > Forms!sbfjncCleaning!txtdateassigned This indicates that you have set the combobox and the date into the subform, not in the mainform. Do you understand that as soon as you requery the combobox in the second record, the text in cbojoblocautonum will disappear from the first record as well? Go back and read the post I wrote yesterday explaining how to set up your form and subform. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com From Your previous posting You have a form with a textbox for the date, and one for shift if it's important. You have a listbox of employees.to pick from on the left. You then have a combobox that holds all the (available) jobs for that date(and shift if that's important),.Below that you have a subform that holds the jobs that have been assigned to the employee that have already been assigned for thae date and shift in the form's header. The subfrm's link parent fields are the date textbox, shift textbox and employee listbox. link child fields come from the subform based on tblcleaning. In the above info I am confused on how to link Mainform with listbox and combobox and date (don't think I need shift for now) with the Subform CleaningAssigned. Can you clarify some more. Thanks, Barb > > I am sure I am messing up on the syntax somewhere. > > [quoted text clipped - 19 lines] > Go back and read the post I wrote yesterday explaining how to > set up your form and subform. > From Your previous posting > [quoted text clipped - 19 lines] > Thanks, > Barb The employee listbox, and the text box for the date sit on the mainform. There is no need to bind those controls to the mainform. The subform contains the data records that you created when you associated an employee with a cleaning job and date. When you choose an employee from the listbox it will show the employee's existing assignments for the date. Change the date, it will show the same employee's assignments for the new date, if any exist. The combobox should show any assignments where no employees have for the given date. Just clicking on the new record row in the subform should create a new record with the default data of employeeID, JobDate and JobID populated. Again, the subform's link parent fields should indicate the names of the listbox control and the date control, the link child fields should be the two fields that will hold the data in the subform. I've just realised that I've forgotten to mention that you need to set the subform's jobID field's default value to the combobox. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com I set up the main form based off of the cleaning table and reinserted the personnel list box using the wizard and the same for the joblocautonum drop down list. along with DateAssigned in Mainform Added the subform for cleaning including all the cleaning fields. In the mainform after I selected the peronnel, joblocautonum and date assigned 1 record is put into the subform. When I go back to same Mainform the select an additional combination it OVERWrited record ONE instead of Adding a new record. Not sure where I am going wrong???????? Thanks for helping, Barb > > I am sure I am messing up on the syntax somewhere. > > [quoted text clipped - 19 lines] > Go back and read the post I wrote yesterday explaining how to > set up your form and subform. > I set up the main form based off of the cleaning table and > reinserted the personnel list box using the wizard and the [quoted text clipped - 11 lines] > Thanks for helping, > Barb Please post the code in the afterupdate event for the three controls. There is probably a DoCmd.GoToRecord , , acNewRec that needs to be set somewhere. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Private Sub cbojoblocautonum_AfterUpdate() Me![joblocautonum] = Forms!frmljncCleaning!cbojoblocautonum End Sub Private Sub LstClockid_AfterUpdate() Me![ClockId] = Forms!frmljncCleaning!LstClockid End Sub Private Sub txtDateAssigned_AfterUpdate() Me![DateAssigned] = Forms!frmljncCleaning!txtDateAssigned End Sub The form acts a noted before with or without the above code putting in what is selected in the Main form into ONLY the 1st record and overwriting it each time a new selection is made. Not what else I may need. Thanks for still helping - I can give you more details or refer to previous tables for help Thanks, Barb > > I set up the main form based off of the cleaning table and > > reinserted the personnel list box using the wizard and the [quoted text clipped - 17 lines] > There is probably a DoCmd.GoToRecord , , acNewRec that needs to > be set somewhere. > Private Sub cbojoblocautonum_AfterUpdate() > Me![joblocautonum] = Forms!frmljncCleaning!cbojoblocautonum [quoted text clipped - 7 lines] > Me![DateAssigned] = Forms!frmljncCleaning!txtDateAssigned > End Sub Those won't do anything unless you have the cleaning table bound to the main form? Check that please. It may be what's causing the overwrite. You should remove the code from all three events. Also remove any code from the subform's events and the controls on the subform's controls' events With that done, changing any of the controls on the mainform should not have any effect on the subform. test that this is the case, and add the following to the cbojoblocautonum_AfterUpdate() event (or to the onClick event of a new command button labeled "Assign job to employee") Dim strSQL as string strSQL = "Insert into tblCleaning "_ strSQL = strSQL & "(DateAssigned, ClockId, joblocautonum) " strSQL = strSQL & " values(#" & me.txtDateAssigned & "#, " strSQL = strSQL & me.LstClockid & ", " strSQL = strSQL & me.cbojoblocautonum & ");" docmd.runSQL strSQL me.frmljncCleaning.Requery > The form acts a noted before with or without the above code > putting in what is selected in the Main form into ONLY the 1st [quoted text clipped - 27 lines] >> There is probably a DoCmd.GoToRecord , , acNewRec that needs >> to be set somewhere. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com so the Main form as I usually know it - has not ties with the subform ( just unbound controls pulling lists from the personnel and jobsatloc tables - then when add code to after update event of the cbojoblocautonum- should populate subform- is that the idea?? Once all code was out and added your code to the afterupdate event of cbojoblocautonum I get a compilation error at the line Me.frmljncCleaning.Requery Method or data error It is the name of the mainform - I check that - still Not working???? Thank you so much for still helping, Barb > > Private Sub cbojoblocautonum_AfterUpdate() > > Me![joblocautonum] = Forms!frmljncCleaning!cbojoblocautonum [quoted text clipped - 67 lines] > >> There is probably a DoCmd.GoToRecord , , acNewRec that needs > >> to be set somewhere. I took away the requery code- think I am a little closer It says will append 1 record but need to close out of form and come back in to see it show up in the subform(prob-what the requery is for???) > > Private Sub cbojoblocautonum_AfterUpdate() > > Me![joblocautonum] = Forms!frmljncCleaning!cbojoblocautonum [quoted text clipped - 67 lines] > >> There is probably a DoCmd.GoToRecord , , acNewRec that needs > >> to be set somewhere. > I took away the requery code- think I am a little closer > > It says will append 1 record but need to close out of form and > come back in to see it show up in the subform(prob-what the > requery is for???) the requery should have the name of the sub-form. Sorry, I was confused. >> > Private Sub cbojoblocautonum_AfterUpdate() >> > Me![joblocautonum] = Forms!frmljncCleaning!cbojoblocautonum [quoted text clipped - 68 lines] >> >> There is probably a DoCmd.GoToRecord , , acNewRec that >> >> needs to be set somewhere. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Got the requery changed and it works - of course there is always a but..... Still have the issue of see below. Since it is now a new day where i am. I really wanted to have Main form move to a new Record for a New Day(the user would have the option to click to a new record and todays date could be input or automatically set to date() and then schedule the jobs for the new day. HOwever the navigation buttons for main form only stay at 1st record so I am overwriting yesterdays Schedule- what am I missing??? So really main form should show 365 records - subform should show what was scheduled for each day. Thanks again for all of you help, Barb > > I took away the requery code- think I am a little closer > > [quoted text clipped - 77 lines] > >> >> There is probably a DoCmd.GoToRecord , , acNewRec that > >> >> needs to be set somewhere. > Got the requery changed and it works - of course there is > always a but..... Still have the issue of see below. [quoted text clipped - 8 lines] > missing??? So really main form should show 365 records - > subform should show what was scheduled for each day. The code I gave you cannot overwrite yesterday's schedule. Since the job is removed from the combobox when you add it to the row, you can never enter the same job again. If you type a new date into the textbox, that also changes the value that is put in the subform's records. If it is overwriting, then you have done something different from what I said. The fact that the navigation buttons don't move is because the form is not bound to a recordsource. > Thanks again for all of you help, > Barb You can go to the trouble of building and maintaining a table with one column, the date to use as a seed for your mainform, or you can simply write some code to add or subtract 1 from the unbound textbox that contains the date. and put that code in command buttons. If you use the unbound method, you can also just type a new date into the textbox and start entering jobs for that date. If you make a table for the dates, you will need to add the date to the table, then requery or reopen the form, then set the form to the correct date. I personally see no need for a table which contains only one column. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com I got the date on the main form fixed - and the subform works for the first date- thanks but now when I go to a new date the subform for the new date shows all of the jobs already scheduled - would like the subform to got back to blank (and reschedule for new day)since need to schedule new jobs and some of the same jobs for new day. Thanks so much for still helping, Barb > > Got the requery changed and it works - of course there is > > always a but..... Still have the issue of see below. [quoted text clipped - 38 lines] > I personally see no need for a table which contains only one > column. > I got the date on the main form fixed - and the subform works > for the first date- thanks but now when I go to a new date the [quoted text clipped - 5 lines] > Thanks so much for still helping, > Barb The link child fields and link parent fields should control this. Make sure that the Employee listbox and the txtdate field are in the parent fields, separated with a comma, and the child fields are the employee and date columns of the subform. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com When I tie the two forms together with the list and combobox as the masterfield and the subform controls as the child fields. I get ONR record only visible - the most recent one added on the subform??? any ideas????? thanks again!!! Barb > > I got the date on the main form fixed - and the subform works > > for the first date- thanks but now when I go to a new date the [quoted text clipped - 10 lines] > the parent fields, separated with a comma, and the child fields are > the employee and date columns of the subform. wondering if I could keep the main and subform still unlinked - still see all records on subform but when move to NEW record(=new date) only show records in subform that date in main form = dateAssigne in subform - not sure what and where to tie this idea of the code to and what code to put in??? thanks again > > I got the date on the main form fixed - and the subform works > > for the first date- thanks but now when I go to a new date the [quoted text clipped - 10 lines] > the parent fields, separated with a comma, and the child fields are > the employee and date columns of the subform. Bob, I got it with putting only the date as the master, child field-makes sense now. Eventually would like to do total hours assigned to each employee per day - not sure best place to do this- each job they are assigned has est. duration - would like to total that up per person in subform and somehow mark them as not able to assign any more jobs to them??? any ideas? thanks, barb > > I got the date on the main form fixed - and the subform works > > for the first date- thanks but now when I go to a new date the [quoted text clipped - 10 lines] > the parent fields, separated with a comma, and the child fields are > the employee and date columns of the subform. Tried posting this as new problem but don't seem to understand that the mainform has unbound control combobox Below is the code I put in and there is an erro Me![Job] = Forms!frmljncCleaning!cbojoblocautonum.Column(1) I am trying to automatically fill in the job(number) in the subform based off of what is selected in the combo box of the main form - the job number is the second field in the combo box. I am sure sytax is wrong somewhere - just not sure where?? Thanks, Barb > > I got the date on the main form fixed - and the subform works > > for the first date- thanks but now when I go to a new date the [quoted text clipped - 10 lines] > the parent fields, separated with a comma, and the child fields are > the employee and date columns of the subform. > Tried posting this as new problem but don't seem to understand > that the mainform has unbound control combobox > > Below is the code I put in and there is an erro Put where? What is the text of the error message. Provide the following data that you will find in the properties box form name subform name subform source object cbojoblocautonum recordsource. > Me![Job] = Forms!frmljncCleaning!cbojoblocautonum.Column(1) > [quoted text clipped - 23 lines] >> the child fields are the employee and date columns of the >> subform. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com The error I am getting is Runtime error 2465 Access can't fin field 'job' refered to in your expression > Put where? I am trying to automatically fill in the job(number) in the subform based off of what is selected in the cbojoblocautonum combo box of the main form - the job number is the second field in the combo box. form name Main form name is frmljncCleaning > subform name - qrycleaninglPersonnel > subform source object(not sure really what that means) - think object name on subform that trying to automatically input- it is JOB > cbojoblocautonum recordsource(on Mainform) - tbljobsatlocnew Thanks for still helping, Barb > > Tried posting this as new problem but don't seem to understand > > that the mainform has unbound control combobox [quoted text clipped - 37 lines] > >> the child fields are the employee and date columns of the > >> subform. > The error I am getting is Runtime error 2465 > Access can't fin field 'job' refered to in your expression [quoted text clipped - 7 lines] > main form - the job number is the second field in the combo > box. Where is the code that gives the error? specifically what event in what form? > form name Main form name is frmljncCleaning >> subform name - qrycleaninglPersonnel >> subform source object(not sure really what that means) - Open frmljncCleaning in design view. there should be a big properties menu, it will say FORM on it and have several tabls named format, data, event other and all on it. If it is not visible, click once on the properties menu in the toolbar. It's the one with a finger pointing to a list. click once on the subform. The properties box will change its heading to subform/subreport: somename that name might be qrycleaninglPersonnel. Tell me that name click the data tab of this panel. the first three entries are 1) Source object: I need to know that. 2) link child fields: 3) link parent fields: >> think object name on subform that trying to automatically >> input- it is JOB cbojoblocautonum recordsource(on Mainform) - [quoted text clipped - 44 lines] >> >> comma, and the child fields are the employee and date >> >> columns of the subform. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com After those bugs from above(still don't have them figured out) Since it is now a new day where i am. I really wanted to have Main form move to a new Record for a New Day and then schedule the jobs for the new day. HOwever the navigation buttons for main form only stay at 1st record so I am overwriting yesterdays Schedule- what am I missing??? So really main form should show 365 records - subform should show what was scheduled for each day. Thanks again for all of you help, Barb > > Private Sub cbojoblocautonum_AfterUpdate() > > Me![joblocautonum] = Forms!frmljncCleaning!cbojoblocautonum [quoted text clipped - 67 lines] > >> There is probably a DoCmd.GoToRecord , , acNewRec that needs > >> to be set somewhere. Hello, I've just fiinished reading through your postings. I cannot help you, but wondering if you can help me. Did you ever get your form to work? I have a need for a similar form (although for a different task). If you did resolve the issues with your form, please let me know how you set it up. SignatureThank you for your time and any info. -Diane |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.