eliminate a choice from dropdown list once selected

[Bob Quintal]

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

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);

 

[babs]

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

 

[babs]

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

 

[Bob Quintal]

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

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

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);

 

[Bob Quintal]

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.

 

[babs]

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

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.

 

[babs]

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

 

[Bob Quintal]

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

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.

 

[babs]

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

 

[Bob Quintal]

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

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.

 

[Bob Quintal]

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

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.

 

[babs]

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

 

[Bob Quintal]

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

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

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.

 

[babs]

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

 

[babs]

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
End Sub

Private Sub LstClockid_AfterUpdate()
Me![ClockId] = Forms!frmljncCleaning!LstClockid
End Sub

Private Sub txtDateAssigned_AfterUpdate()
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

 

[babs]

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
End Sub

Private Sub LstClockid_AfterUpdate()
Me![ClockId] = Forms!frmljncCleaning!LstClockid
End Sub

Private Sub txtDateAssigned_AfterUpdate()
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

 

[babs]

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
End Sub

Private Sub LstClockid_AfterUpdate()
Me![ClockId] = Forms!frmljncCleaning!LstClockid
End Sub

Private Sub txtDateAssigned_AfterUpdate()
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

 

[Bob Quintal]

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
End Sub

Private Sub LstClockid_AfterUpdate()
Me![ClockId] = Forms!frmljncCleaning!LstClockid
End Sub

Private Sub txtDateAssigned_AfterUpdate()
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 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

:

=?Utf-8?B?YmFicw==?= <[email protected]>
wrote in

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

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.

 

[babs]

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

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.

in
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

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

:

=?Utf-8?B?YmFicw==?= <[email protected]>
wrote in

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

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.

 

[Bob Quintal]

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.

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.