Write Conflict Error

[billheath]

I am getting the dreaded "Write Conflict" error when the code reaches the "Me.Refresh" statement. This just started. I have not changed the code lately. This error popped up yesterday.

I have to refresh the information to make the THours calculate properly. I have read many posts about this message but I can't find one that seems to fit.

Thanks, Bill


Dim Interval As Date
Dim strOutput, StrMsg As String
Dim j As Integer


'Determine lenth of time worked on project:
Interval = (Me.Stop.Value - Me.Start.Value)
If Interval < 0 Then
StrMsg = "Stop time must be later than start time. Please Re-enter!"
j = MsgBox(StrMsg, vbOKOnly, "Error")
'Me.Start.SetFocus
Exit Sub
End If

'Convert hour:min to minutes:
strOutput = Int(CSng(Interval * 24)) & "." & 100 * (Format(Interval, "nn")) / 60


Me.WorkedDate.Value = Val(strOutput)
'Check for personal time:
If Me.[ProjectNo] = 0 Then Me.Personal.Value = Val(strOutput)
If Me.[ProjectNo] > 0 Then Me.Personal.Value = 0


Me.Refresh

Dim TheDate, i As Integer
TheDate = DatePart("w", FilterDate)

'Calculate total hours worked during day
Me.THours = DSum("WorkedDate - Personal", "tblTimeSheet", "tblTimeSheet!DateWkd = #" & Me.[FilterDate] & "# ")

'Determine daily hours worked and total for week period
If TheDate <> 1 Then
i = TheDate - 2
End If

NewDate = Me.FilterDate - i
Me.WHours = DSum("WorkedDate - Personal", "tblTimeSheet", "tblTimeSheet!DateWkd >=#" & NewDate & "# And DateWkd <=#" & NewDate & "# + 6 ")

 

[lameid]

What have you tried to fix it?

Generally when something works then doesn't you should back everything up and then compact and repair the front end and backend.

After that you can try using the decomplie switch.

Lastly, you can try importing everything into a new file.

Copying the procedure to NOTEPAD, saving it, reopening the file and copying and pasting back over the original could also work short of decompiling and importing everything.

 

[billheath]

Thanks lameid, I did all of your steps. However, I am still having the same problem

 

[kjv1611]

What if you try Me.Requery instead of Me.Refresh?

Also, is this for something that could be a long-term tool, or is it a quick fix? If it's something that could be long term, you might want to look into standardizing your code - that would also make it a little easier to read.

For instance, check into the Reddick Naming Convention:

http://www.xoc.net/standards/

and

http://www.mvps.org/access/general/gen0012.htm

And you want to put any Dim statements at the top of the procedure. I moved it a round a little, but didn't mess with your names in this example:

Sub DoTheDeal
	Dim Interval As Date
	Dim strOutput, StrMsg As String
	Dim j As Integer
	Dim TheDate As Integer
	Dim i As Integer

	'Determine lenth of time worked on project:
	Interval = (Me.Stop.Value - Me.Start.Value)
	If Interval < 0 Then
		StrMsg = "Stop time must be later than start time. Please Re-enter!"
		j = MsgBox(StrMsg, vbOKOnly, "Error")
		'Me.Start.SetFocus
		Exit Sub
	End If

	'Convert hour:min to minutes:
	strOutput = Int(CSng(Interval * 24)) & "." & 100 * (Format(Interval, "nn")) / 60

	Me.WorkedDate.Value = Val(strOutput)
	'Check for personal time:
	If Me.[ProjectNo] = 0 Then Me.Personal.Value = Val(strOutput)
	If Me.[ProjectNo] > 0 Then Me.Personal.Value = 0

	Me.Refresh

	TheDate = DatePart("w", FilterDate)

	'Calculate total hours worked during day
	Me.THours = DSum("WorkedDate - Personal", "tblTimeSheet", "tblTimeSheet!DateWkd = #" & Me.[FilterDate] & "# ")

	'Determine daily hours worked and total for week period
	If TheDate <> 1 Then
	i = TheDate - 2
	End If

	NewDate = Me.FilterDate - i
	Me.WHours = DSum("WorkedDate - Personal", "tblTimeSheet", "tblTimeSheet!DateWkd >=#" & NewDate & "# And DateWkd <=#" & NewDate & "# + 6 ")
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

Maybe the record is not saved before you refresh it?

Perhaps you want to force the save of the record...

docmd.runcommand acCMDsaverecord

While I generally agree with kjv1611 and I think you probably want a requery, refresh may work and I expect it will error too. Maybe not. It would be an easy fix if it works.

 

[billheath]

Thanks KJV1611. Using Me.Requery in place of me.Refresh seems to have worked.

Lameid; Thanks for your help. I didn't try your suggestion because tghe requery worked and I want to leave well enough alone!!

 

[lameid]

I would stick with Requery too... I was just surprised there is an error with refresh.

 

[billheath]

Now there is another problem with Requery. Once the query is finished, the active field is the first field of the first record. How do I get it to return to the specific record and field?

 

[genomon]

billheath I may be worthless here; I don't do this much, but I seem to remember the way to do this is with a Recordset Clone or Bookmark. No time to look for code samples - maybe you can look at Access help topics or Google?

Cogito eggo sum – I think, therefore I am a waffle.

 

[lameid]

Two thoughts about the record.

First you could filter your form down to the record before the requery. This would mean you were on the right record.

Alternately, you could go with a solution like genomon is recommending and use a recordset clone to find the same record (you'd have to save the criteria before the requery). Next find that record and then set the form's recordset's bookmark property equal to the bookmark property of the recordset clone.

There is a solution for the control too but i don't know that one... There maybe a hasfocus property or something you can use to determine the control with the focus. With this and the setfoucus method you can put the cursor in the right place.