Erratic procedure that misbehaves

[theDBguy]

Yes I had a look at the code. I do not understand it well. I need an explanation on how to put it in practice to solve my problems.

Hi. We won't have any idea you needed more help implementing it if you didn't tell us. Okay, so try the following:

Copy and paste the function from the website into a Standard Module. Save the module and just leave the name as Module1 for now.

Then, wherever you had either DoCmd.RunSQL strSQL or CurrentDb.Execute strSQL, replace them with this:

fExecuteQuery strSQL

And let us know what happens.

 

[buhal004]

I am more than happy to tell you that the code now worked like a song.

I will now have to wait for some time to see whether the modified code executes its purpose consistently.

I am very appreciate to theDBguy. I thank you. How do I thank you officially ?

 

[buhal004]

Are any of the workstations hosting the FE using a WiFi connection to the network?

Yes. Some are using WIFI

 

[isladogs]

Thanks for answering my question.
Access does not work well with Wi-Fi. If anyone is editing data and the wireless connection is dropped, even for a short time, corruption will occur … and potentially code may not run reliably. Strongly recommend everyone uses a wired connection. If that's not possible then it would be better to use SQL Server for the BE database as that is able to handle dropped connections better

 

[theDBguy]

I am more than happy to tell you that the code now worked like a song.

I will now have to wait for some time to see whether the modified code executes its purpose consistently.

I am very appreciate to theDBguy. I thank you. How do I thank you officially ?

You just did. Glad to hear you got it to work. Good luck with your project.

 

[buhal004]

Yes I had a look at the code. I do not understand it well. I need an explanation on how to put it in practice to solve my problems.

Thanks for answering my question.
Access does not work well with Wi-Fi. If anyone is editing data and the wireless connection is dropped, even for a short time, corruption will occur … and potentially code may not run reliably. Strongly recommend everyone uses a wired connection. If that's not possible then it would be better to use SQL Server for the BE database as that is able to handle dropped connections better

Thanks for this useful information. The thing is that if it were a WIFI issue the procedure would be interrupted. In fact one would not realize that the code did not run well. Only later one finds that part of the code ran and that part I mentioned did not as the relevant records did not update.

I will now wait to see whether I solve this problem with CurrentDB.execute instead of with DoCmd. RunSQL

Can theDBGuy please explain the code referred to in 11 for my education. Thanks.

 

[theDBguy]

Can theDBGuy please explain the code referred to in 11 for my education. Thanks.

Hi. My understanding was you used the function I mentioned and the error went away. Is that correct? If so, that function was a way to handle the parameters you have in your query. There are other ways to handle it, but that function sorts of an easy way to handle multiple situations. Take a look at the code within the function and let us know which line you need us to explain further. Cheers!

 

[bastanu]

Did you try what I posted in #7:

StrSQL = "UPDATE tblservicelog SET tblservicelog.invoiceno =" & [Forms]![frmBilling]![paste_invoiceno] & ", tblservicelog.invoiced = true, tblservicelog.paid = true" _

& " WHERE (((tblservicelog.invoiced)=false) AND ((tblservicelog.hospno)='" & lista & "')AND ((tblservicelog.include)=True));"

 

[buhal004]

Hi. My understanding was you used the function I mentioned and the error went away. Is that correct? If so, that function was a way to handle the parameters you have in your query. There are other ways to handle it, but that function sorts of an easy way to handle multiple situations. Take a look at the code within the function and let us know which line you need us to explain further. Cheers!

Thanks for your interest. The original problem is still outstanding. I had a long code which works well most of the time. I posted it in my first post. However at times a specific part of this code ( an update SQL statement) did not execute. The whole procedure ran to its termination and one only realizes that the data did not update, later. This is episodic and unpredictable.

I was advised to change DoCmd. RunSQL to CurrentDB.execute in that particular part of the code. I found difficulty with getting this to run. But with your help I got it going. Now I have to see whether this solves the original issue.

I thank you.

 

[Pat Hartman]

I wasn't suggesting that code was bad (I write it all the time), just that you should not code things that Access does automatically and that applies especially to bound forms. Even in a bound form, you still need validation code in the Form's BeforeUpdate event to ensure that you are not saving bad data.