Strange Null recordset problem on subform (1 Viewer)

[PeterW]

Hi,

Im not even sure how to describe the issue Ive got so please feel free to ask me to clarify anything.

I have a form which contains 3 subforms (in pages). Each subform allows a user to enter details of their daily working times.

Subform1 (Planned): What hours they plan to do on any one day (completed week before)
Subform2 (Actual): What they actually did (completed generally the next day)
Subform3 (Downtime): Any exceptions to Subform2 eg if they were in a meeting for an hour etc.

All 3 subforms are displayed as Datasheet views with the ability to add new records directly.

So for any one day a user may have:

Subform1: Date: 18/08/2014, Start Time: 07:00, Finish Time: 15:00, Lunch: 30mins
Subform2: Date: 18/08/2014, Hours:6, Mins: 30
Subform3: Date: 18/08/2104, Reason: Meeting, Hours: 1, Mins: 30

When you enter a subform it displays their times for the last 7 days, it does this using a Record Source of (for example in Subform 1)

SELECT Hours_Planned.user_name, Hours_Planned.when, Hours_Planned.Start_Time, Hours_Planned.End_Time, Hours_Planned.Lunch_hrs, Hours_Planned.Lunch_mins, Hours_Planned.totMins FROM Hours_Planned WHERE Hours_Planned.when>=NewDate('day',-7,Date())  ORDER BY Hours_Planned.when;

It then applies a Filter to only display the records relating to the user.

eg

[user_name]='Joe Bloggs'

All seemed to be working fine however some users have reported that on occassions it appears to display records for AN OTHER user but with their name attached. In one case a user saw these incorrect records, closed the form, opened it again and they were gone. I was unable to replicate the problem and put it down to user error.

I have now found however that there is a problem, it only appears to happen when a user has returned to work after time off and therefore there are no records for them in the last 7 days ie a Null recordset should be returned.

Instead of a blank recordset however it seems to be pulling the last record for 7 days ago and then (and this is really weird) pulling the corresponding records for subforms 2 and 3....

eg

Joe Bloggs has no entries in last 7 days, when he opens the form today (18 August 2014) it displays a single entry in Subform1 relating to 11 August 2014 from Mary Smith however it has amended the name from Mary to Joe (both on screen and in the tables behind)

If you then go into subform2 it will display Marys entry for her Actual times on the 11th, subform3 will likewise show Marys Downtime entries, again for the 11th. Subforms 2 and 3 entries however are not necessarily the last ones for that day, they are always however the same persons as subform1.


So in the case above it would take Marys records and amend the name to Joe, if however Bill then logs in (for the 1st time in a week) and opens the form he will get Joes new records ie Marys old ones and these will then change to Bills name.......

Im lost as to why its not just simply returning nothing ready for the user to enter new data in?

Any help would be appreciated.


Regards,

Peter

 

[JHB]

Is the database split in Front- & Backend?
Explain a little how your database and forms are set up?

 

[PeterW]

Is the database split in Front- & Backend?
Explain a little how your database and forms are set up?

Yes, each user has a Front End with all linking to a central back end.

Apologies, Im not certain what you're looking for from the 2nd part but the tables involved in the issue are as follows:

Hours_Planned (used in Subform1)

id (autonumber, Primary key)
user_name (Text)
when (Short Date)
start_Time (Short Time)
end_Time (short time)
Lunch_hrs (Integer)
Lunch_mins (Integer)
totMins (Integer)

Hours_Worked (used in subform2)

id (autonumber, Primary key)
user_name (Text)
when (Short Date)
theHours (Integer)
theMins (Integer)
totMins (Integer)

Downtime (used in subform3)

id (autonumber, Primary key)
user_name (Text)
when (Short Date)
theHours (Integer)
theMins (Integer)
Reason (Text)
totMins (Integer)

Theres one additional table which is used to look up the persons name based on the id theyve logged into the PC with.

tblConstants
which contains a lot of data on the person but specifically....

PID (text)
name (Text)
Team (Integer)

 

[JHB]

I was just thinking of, why do you not put in the user name in Where Clause in your query, then you don't need a filter?

 

[PeterW]

I was just thinking of, why do you not put in the user name in Where Clause in your query, then you don't need a filter?

Great minds, I made that change this morning and its working. Im not sure why the code was written that way at the time, I think it was around whether it was easy to reference another form (containing the users name) within the subform and the easy (and still technically not incorrect) option was taken.

Id still be interested to know a reason why the logic of applying a filter to a full recordset containing no records matching the filter would cause it to display an incorrect record?

 

[JHB]

..
Id still be interested to know a reason why the logic of applying a filter to a full recordset containing no records matching the filter would cause it to display an incorrect record?

It is hard to say, not having the database here, mostly it is a little tiny thing, no one is thinking of!