Random Record Duplicate appearing from Table to Form?

[djspod]

Hi ,
I have a Rota table which feeds a Form Rota page.

The Key record driver is the W/C Sunday date, As you change the Combo Sun Commencing Date the whole Rota changes to that week for all staff and displays what Rota they are working.

For some bizarre reason a rogue record/Staff name is appearing twice?

If I check any Date in the table, The Staff name only appears once but twice in the form?

I have also started with the Sun Field on the form, changed the Text box to a Combo, this feeds from a field/Table - Holiday, Sick, Off, Over time etc.



So basically the plan is as follows, the Staff Rota for the week is pulled into the form from the Table for any given week selection, from here the user can then amend the week by using the CBO dropdown selection if needed, I will be adding this to all of the day fields at some stage. Mon, Tuesday, etc..

Can anyone help please?



Thanks

[ranman256]

Is the query behind the combo box set to UNIQUE VALUES = TRUE?

[djspod]

Yes the Combo box is fed from a table with one field name - Shift Drop down so I guess the answer to your question would be true?

Attachment 27637Attachment 27638

[ranman256]

Then your query can't be correct. It will not make duplicates.

[djspod]

The Rota form was originally also made from a query but I was unable to amend or make changes to the Form so I made the Query result into a table tblRota.

This form is based on that table.

Are your referring to the Date Combo box on the top left or the shift drop down on Sun Field?

The DB is attached on the first post if you need to look at it and check the query.

The Query that mad the Table which feeds the form, please see below for the screenshots.

Thanks for your help

Attachment 27641Attachment 27642Attachment 27643

[aytee111]

If you look at tblRota for week 1/8 Chris Smith appears twice, is this what you are referring to?

[djspod]

Hi Aytee,

Had a look I did not get twice in the table but he was in at 15/01/2017 twice

Tried the following:

Deleted sub form and re added again

Ran the Ajax make table rota again but still the same for Chris Smith, as the form first loads, Chris Smith is in once, when you select another week, he appears twice?

Attachment 27652Attachment 27653

[djspod]

Sunday	ShiftName	FullName	WeekNum	SUN	MON	TUE	WED	THU	FRI	SAT
01/01/2017	dd	Chris Smith	1	06:00-18:00	06:00-18:00	Off	Off	Off	06:00-18:00	06:00-18:00
08/01/2017	dd	Chris Smith	2	06:00-18:00	06:00-18:00	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00
15/01/2017	dd	Chris Smith	3	06:00-18:00	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00	Off
22/01/2017	dd	Chris Smith	4	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00	Off	Off
29/01/2017	dd	Chris Smith	5	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00	06:00-18:00	Off
05/02/2017	dd	Chris Smith	6	Off	06:00-18:00	06:00-18:00	06:00-18:00	06:00-18:00	Off	Off
12/02/2017	dd	Chris Smith	7	06:00-18:00	06:00-18:00	06:00-18:00	Off	Off	06:00-18:00	06:00-18:00
19/02/2017	dd	Chris Smith	1	06:00-18:00	06:00-18:00	Off	Off	Off	06:00-18:00	06:00-18:00
26/02/2017	dd	Chris Smith	2	06:00-18:00	06:00-18:00	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00
05/03/2017	dd	Chris Smith	3	06:00-18:00	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00	Off
12/03/2017	dd	Chris Smith	4	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00	Off	Off
19/03/2017	dd	Chris Smith	5	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00	06:00-18:00	Off
26/03/2017	dd	Chris Smith	6	Off	06:00-18:00	06:00-18:00	06:00-18:00	06:00-18:00	Off	Off
02/04/2017	dd	Chris Smith	7	06:00-18:00	06:00-18:00	06:00-18:00	Off	Off	06:00-18:00	06:00-18:00
09/04/2017	dd	Chris Smith	1	06:00-18:00	06:00-18:00	Off	Off	Off	06:00-18:00	06:00-18:00
16/04/2017	dd	Chris Smith	2	06:00-18:00	06:00-18:00	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00
23/04/2017	dd	Chris Smith	3	06:00-18:00	Off	Off	06:00-18:00	06:00-18:00	06:00-18:00	Off

[aytee111]

Finally I got it! Because your main form combobox is bound to the table, it keeps changing the data. Comboboxes must never be bound fields! NEVER!! You are changing the value of whatever record you are on at the time. Remove tblRota from the main form.

[djspod]

Okay I get your logic, what would you suggest to make the combo work?

Basically I want the user to chose a week from the Combo box - TblSundays

This then changes the rota below which reflects who is off and on that week and on each day.

From what is loaded I plan to have a Combo box on each day of the week which can be changed from the default loaded value and allow the user to change to hol, overtime etc.

Once they are happy with the changes they then print off an A4 report to display for all staff to view (Not built this yet)

I want similar to what I had but without the Chris Smith duplication error.

Any help you can provide would be appreciated.

thanks

[aytee111]

Remove record source from main form, remove control source from combobox, change the master link on your subform to reflect the combobox name. That is all you need to do.

[djspod]

Hi Aytee,

Please forgive me but I have tried your instructions but I am still missing a link somewhere.

Can you help me further please.

Decided to build a blank form (Unbound)

Add a sub form container (frmRota)

Added a combo to the main blank unbound form. Combo2 is unbound other than the Sun dates field from tblSundays

I have tried various methods but I am getting it all wrong?

Just tried Me.[subFormName].Form.Requery on the Combo2 afterupdate thinking that may crack it.

Attachment 27659Attachment 27660

[aytee111]

I presume I should be looking at Form1?

1 - the main form is a bound form - remove the record source
2 - the subform is not linked to the main form
3 - Sunday on the subform must come from the table

[djspod]

I have altered on Form1

1 - the main form is a bound form - remove the record source (Now Removed)
2 - the subform is not linked to the main form
3 - Sunday on the subform must come from the table (now Added)


I get an error message when I try and link the 2 forms together? How do I get around this?

Do I add the Sunday Field from the Rota table to the Form1 to make a link?

What's the process please?

Attachment 27662

[aytee111]

Type it in manually in the property sheet.