Missing Records (and Missing Updates)

Hi all

This is an ongoing problem I have had for 4 weeks now.

I have made a a system thats acts like a clock In/clock out Out system.

the structure is somthing like this
ID
Username
tblDailyLog
TimeIn
MorningBreakOut
MorningBreakOut
LunchOut
LunchIn
AfternoonOut
AfternoonIn
TimeOut

All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)

When a user arrives in the morning they make a record which they use for the day

They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.

Everything was going fine until people noticed that every now and again a sign in time dissapeared.

I have hacked myself to death trying to solve this problem but still the updates go Astray.


Now each time a time is updated the process goes somthing like this


1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)

2. User Hits a sign in/out button
3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once)
3. The table is updated with the new time (using some dynamic SQL)
4. The table is repeatadly checked using a DO loop to make sure the the correct time went in.
5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens).
6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other)
7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.


I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.

Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..

None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.

Does anyone have any idea how these updates/inserts can go missing or create #ERRORs.
I've built plenty of Databases in my time and have never come across this.
__________________________________________________ ______________

This is the function I use to add a record to tblBugfixingLog and tblSQLRecord


Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue)
Dim TempSQL As String
TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _
"#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _
"#" & Now & "#," & _
"'" & FieldUpdating & "'," & _
"'" & NewFieldValue & "'," & _
"'" & GetNTUser & "'," & _
"'" & TimesheetNumber & "'," & _
"'" & fOSMachineName & "')"
' MsgBox TempSQL
DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False
'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table
DoCmd.RunSQL TempSQL, False
End Sub

Public Function CleanData(ByVal DataToClean As String)
Dim TempData As String
Dim i As Integer
TempData = ""
For i = 1 To Len(DataToClean)
Select Case Mid(DataToClean, i, 1)
Case "'"
TempData = TempData & "`"
Case """"
TempData = TempData & "`"
Case Else
TempData = TempData & Mid(DataToClean, i, 1)
End Select
Next i
CleanData = TempData
End Function


__________________________________________________ ____


I have no idea how this can create #ERROR lines in the table when it is just added to and nothing else.

Does anyone have any clue to what may be happening here.

(Oh yeah and no matter how hard I try, I can't replicate the problem.... works for me every time no matter how harse I am to it!)

Please save what little hair I have left and give me some hope

Cheers
Homer