SQL Query with form elements

[colintis]

Hi guys,

I'm having a problem with a subform, it runs a query that contained an element from the main form (a recordset) used for parameter. When the main form takes a date parameter, the AfterUpdate event only runs the following line of code:

AttendanceSub.Requery

and below is the query AttendanceSub

SELECT Attendance.Date
, Attendance.EmployeeNo
, Attendance.ChristianName
, Attendance.Surname
, Attendance.Area
, Attendance.Shift
, Attendance.Duties
, Attendance.RosterCode
, Attendance.Attendance
, Attendance.ManfHours
, Attendance.WhouseHours
, Attendance.[Other Hrs]
, Attendance.Comments
, Attendance.Observe
, Attendance.absenceHours
FROM Attendance
WHERE 
(
(Attendance.Date)>=[forms]![AddWeekRec]![WSDate] 
And (Attendance.Date)<=DateAdd("d",[forms]![AddWeekRec]![Days]-1,[forms]![AddWeekRec]![WSDate])
)
ORDER BY (Attendance.Date);
 

After it executes, the subform should list the records with the entered date from the main form. The problem is the list won't showed up. It was still working when I use it to test something last month, but now it doesn't including the previous backup versions. The subform also includes "EmployeeNo" in both link child fields and link master fields, where "EmployeeNo" is a field from the main form, source object is the query AttendanceSub.

Can anyone tell me is it the coding error? or some property setting in the form need to be make? Thanks.

[jimatqsi]

Hmm, I don't see anything obvious. A few things you might try:
instead of AttendanceSub.Requery, try me.requery to see if the result is any different.

Copy the query to a new, temporary name and run while that form is open with the changed date and see what the query returns. If it is empty, go into design mode and study what you see there. The answer should show up, or at least an answer that can lead you to another question.

Study filtering forms and consider whether that is a better solution for you. I always seem to run into problems when I try to use form data within a saved query. See these posts for info about that:
http://bytes.com/topic/access/answer...d-filters-form
http://bytes.com/topic/access/insigh...filtering-form

Jim

[colintis]

@jimatqsi
Hi Jim,

A little deeper checking, the subform in the main form is also called AttendanceSub, so I think the code in the main form is calling the subform to do the requery.

The setting in this subform has query attendanceSub as record source, order by the date field in the query. It also set to yes for both allow filters and edits, no locks and fetch defaults.

I tried executing the copied query while the form is opened, it did returns the records within the date range. But when I tried to list a specific person (using EmployeeNo), a pop-up appears with a message : Record is deleted.

Also the 2 links you put at the end said they were removed already...

[colintis]

I found the problem....its actually there's a corruption in the database itself. After I do the Compact and Repair Database everything just work back as normal. :)

Thanks for the advice Jim, for leading me to find the error message which lead to the solution ;D

[jimatqsi]

So how can it be that these records are being deleted? Not knowing anything about your DB or processes, I can't offer anything about that. But it sounds like you are looking for data that is no longer there, so I would ask "should it still be there? If it is, where is it? If it's not, why not? Sounds like you have a lot of digging to do to see what's really going on.

I don't understand what's up with those links ... posts stay on the site for years. Maybe they got moved to an archive area.

Jim

[colintis]

@jimatqsi
The data records are there, i scroll through the table and its there, the question becomes: "Why the error said the record is deleted while the data is still in the table?".

During the time i scroll through the table, i also found a few records' fields are all written as #DELETE, that might be data corruption. There's also a M$ Support page mentioned that when I google the error message. And yeah, after I compacted the database I can then get the required output.

But then another problem has shown up for data type, and that's another topic's problem...

[NeoPa]

Jim,

The problem with the links seems to be that you copied and pasted only the snipped display of previous links. I tried to fix them for you, but found there was nothing in there to show where you'd intended to link to. Sorry.

[NeoPa]

colintis: The data records are there, i scroll through the table and its there, the question becomes: "Why the error said the record is deleted while the data is still in the table?".

I would read that as a corrupted index. Indices get rebuilt from the existing data when a repair is done so you'd expect that to get fixed.