Erratic procedure that misbehaves (1 Viewer)

[buhal004]

I have a large medical database. Several computers with front ends on each. One back end on a sever. The procedure has several action SQR statements.

My problem is that on occasion, apparently at whim, one SQL statement is not executed.

I know that this description may lack clues, but I wonder if there are any inkling out there on what could be the issue.

Thanks for any ideas.

Louis

 

[jdraw]

More info required. Network type? Sample of code that doesn't execute with some contextual info and/or code.

 

[theDBguy]

I have a large medical database. Several computers with front ends on each. One back end on a sever. The procedure has several action SQR statements.

My problem is that on occasion, apparently at whim, one SQL statement is not executed.

I know that this description may lack clues, but I wonder if there are any inkling out there on what could be the issue.

Thanks for any ideas.

Louis

Hi Louis. Welcome to AWF!

 

[isladogs]

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

 

[buhal004]

I have a large medical database. Several computers with front ends on each. One back end on a sever. The procedure has several action SQR statements.

My problem is that on occasion, apparently at whim, one SQL statement is not executed.

I know that this description may lack clues, but I wonder if there are any inkling out there on what could be the issue.

Thanks for any ideas.

Louis

The following is the code. As I have said most of the time it works fine. Sometimes, I don’t know when and why, it fails to execute the part of the code marked in block letters. I suspect it is because of the connection with the server. This is slow at times.





Private Sub Command20_Click()

If IsNull(Me.Frame22) Then

MsgBox "Please indicate a choice from the list!"

Exit Sub

Else

End If

'Set mydb = CurrentDb()

'Set rst1 = mydb.OpenRecordset("tblInvoices", dbOpenDynaset)



'Set mydb = CurrentDb()

'Set rst1 = mydb.OpenRecordset("tblInvoices", dbOpenDynaset)

'rst1.AddNew

'rst1.InvAmount = Forms!frmBilling!Text12

'rst1.InvDate = Date

'rst1.Invoiced = True

'rst1.PayDate = Date

'rst1.CltID = Forms!frmBilling!List0.Column(0)

'rst1.UserNameInv = Forms!FrmMain!TxtIdentifier

'rst1.Paid = True

'rst1.methodofpayment = Me.Frame22

'Forms!frmBilling!paste_invoiceno = rst1!InvoiceNo

'rst1.Update

'rst1.Close



'Set rst1 = Nothing

'Set mydb = Nothing



'the above code was how it was before.



DoCmd.SetWarnings False

StrSQL = "INSERT INTO tblInvoices ( InvAmount, InvDate, Invoiced, PayDate, CltID, UserNameInv, Paid, methodofpayment )" _

& "SELECT [Forms]![frmBilling]![Text12] AS Expr1, Date() AS Expr2, True AS Expr3, Date() AS Expr4, [Forms]![frmBilling]![List0] AS Expr5, [Forms]![FrmMain]![TxtIdentifier] AS Expr6, True AS Expr7, [Forms]![FrmMethodofPaymentB]![Frame22] AS Expr8;"

DoCmd.RunSQL StrSQL

Forms!frmBilling!paste_invoiceno = DMax("InvoiceNo", "tblInvoices")



Dim lista As String

lista = Forms!frmBilling!List0.Column(0) 'takes up the idcard number



Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "Do you want to print this invoice ?" & vbCrLf & " Please check that you have a letterhead in the printer!"

Style = vbYesNo + vbCritical + vbDefaultButton2

Title = "MsgBox Demonstration"

Help = "DEMO.HLP"

Ctxt = 1000

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbNo Then ' User chose no.

Else

DoCmd.OpenReport "RptInvoicefromfrmBilling", acViewNormal, , , acWindowNormal

'DoCmd.OpenReport "RptInvoicefromfrmBilling", acViewNormal, , , acWindowNormal 'second copy

DoCmd.Close acReport, "RptInvoicefromfrmBilling", acSaveNo

End If

DoCmd.Close acForm, "FrmMethodofPaymentB", acSaveNo



'apparently one should not reference controls from reports. it took me ages to fix these SQL statements. I had to reference on the forms rather than the reports.

' i do not know how the ones in dbSerena worked. In dbserena it worked!



'marks the tblservicelog as invoiced and adds invoice no

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));"

DoCmd.RunSQL StrSQL



'marks the tblHospitalisations as invoiced and add invoice no

StrSQL = "UPDATE tblHospitalisations SET tblHospitalisations.invoiced = Yes, tblHospitalisations.paid = yes, tblHospitalisations.InvoiceNo = [Forms]![frmBilling]![paste_invoiceno]" _

& " WHERE (((tblHospitalisations.cltIdcard)= '" & lista & "') AND (Not (tblHospitalisations.DateDischarge) Is Null) AND ((tblHospitalisations.invoiced)=No));"

DoCmd.RunSQL StrSQL

'marks those investigations that are to be billed with invoice no, invoiced and paid.

StrSQL = "UPDATE tblTest INNER JOIN tblClinPict ON tblTest.IDClinPict = tblClinPict.ID SET tblTest.Invoiceno = [Forms]![frmBilling]![paste_invoiceno], tblTest.Invoiced = true, tblTest.paid = true" _

& " WHERE (((tblTest.Invoiced)=False) AND ((tblTest.Status)='collected') AND ((tblClinPict.CltID)='" & lista & "') AND ((tblTest.tobebilled)=True)) OR (((tblTest.Invoiced)=False) AND ((tblTest.Status)='Printed') AND ((tblClinPict.CltID)='" & lista & "') AND ((tblTest.tobebilled)=True));"

DoCmd.RunSQL StrSQL

'marks those investigations that are NOT to be billed with invoice no, not invoiced and not paid.

StrSQL = "UPDATE tblTest INNER JOIN tblClinPict ON tblTest.IDClinPict = tblClinPict.ID SET tblTest.Invoiceno = [Forms]![frmBilling]![paste_invoiceno], tblTest.Invoiced = false, tblTest.paid = false" _

& " WHERE (((tblTest.Invoiced)=False) AND ((tblTest.Status)='collected') AND ((tblClinPict.CltID)='" & lista & "') AND ((tblTest.tobebilled)=True)) OR (((tblTest.Invoiced)=False) AND ((tblTest.Status)='Printed') AND ((tblClinPict.CltID)='" & lista & "') AND ((tblTest.tobebilled)=false));"

DoCmd.RunSQL StrSQL



'marks the tblConsumables to be billed as invoiced, paid and adds invoicno

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

& " WHERE (((tblConsumables.invoiceno)=0) AND ((tblConsumables.IDcardNo)='" & lista & "') AND ((tblConsumables.tobebilled)=Yes));"

'DoCmd.RunSQL StrSQL



'marks the tblConsumables which are not to be billed as NOT invoiced, NOT paid but with the inv no.

StrSQL = "UPDATE tblConsumables SET tblConsumables.invoiceno = [Forms]![frmBilling]![paste_invoiceno], tblConsumables.invoiced = false, tblConsumables.paid = false" _

& " WHERE (((tblConsumables.invoiceno)=0) AND ((tblConsumables.IDcardNo)='" & lista & "') AND ((tblConsumables.tobebilled)=No));"

'DoCmd.RunSQL StrSQL







'------------------------------------------------------------------------------------------------------------

'strSQL = "SELECT * FROM Orders WHERE ShipCity = """ & Me!txtShipCity & """"

' the above how to insert a text reference into a SQL statment

'SQL = "select partno, description from Inventory where partno = '" & part & "'"

'-----------------------------------------------------------------------------------------------------------------

'how to include variables in an SQL statement

'String variables would take the form:

'SQL = "select partno, description from Inventory where partno = '" & part & "'"

'where the single quote begins the string value, then the sql variable is stopped and the variable containing the value is appended, then the final single quote is appended to the sql variable.



'For numeric variables just leave off the single quotes:

'SQL = "select partno, description from Inventory where partno = " & part & "



'For Microsoft Access Date types replace the single quote with a # (hash) mark.



'For substituting the values in RFgen prompts without declaring variables:

'SQL = "select partno, description from Inventory where partno = '%part'"



'RFgen will search and replace the text %part and replace it with the value in that prompt's textbox. Again, if it should be numeric, just leave off the single quotes.



'To reference a prompt by number instead of name (in case of a loop structure):

'SQL = "select partno, description from Inventory where partno = '%3'"

'===========================================================================================================

DoCmd.SetWarnings True



DoCmd.Close acForm, "frmBilling", acSaveYes ' requery not working

DoCmd.OpenForm "frmBilling", acNormal

Forms!frmBilling.List0 = Null

End Sub

 

[vba_php]

buhal,

posting an extremely long, unorganized code routine like you just did will probably not get an answer. you need to use <code> tags, first of all. second, the guys here would get a headache from trying to sift through all that.

first thought of Mine. have you tried:

currentdb.execute()

instead of:

docmd.runsql()

???

It might not make a difference, but because VBA is not very good, nor is it's compiler, I wouldn't be surprised if there was a difference between those two. secondly, your code says this at one point:

'SQL = "select partno, description from Inventory where partno = '%part'"

I'm not sure what you're talking about there, but the percent sign (%) is not used in Access, as far as I know, as a comparison operator. Access's is the asterisk (*). That is used in Oracle databases and ASP.NET indicator encapsulation code. furthermore, access does not use single quote marks very often.

 

[bastanu]

buhal004,

I don't see how any of the SQL statements would run as the form variables are not being properly evaluated

'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));"

'should be
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));"

Same idea with all the others....there are instructions in those comments at the bottom of the sub you posted on how to do it for various data types.

Cheers,
Vlad

 

[Pat Hartman]

It is way too hard to isolate the commented out code from the active code. Please post the actual, working code without all the garbage. Code that isn't being used is garbage. get rid of it unless you are testing variations and don't know which you will keep.

How do you know the "query" isn't running? Which query are you talking about? I'd assume the append query but I don't know.

Why are you using an unbound form? If you bind the form, your problem could disappear.

 

[buhal004]

It is way too hard to isolate the commented out code from the active code. Please post the actual, working code without all the garbage. Code that isn't being used is garbage. get rid of it unless you are testing variations and don't know which you will keep.

How do you know the "query" isn't running? Which query are you talking about? I'd assume the append query but I don't know.

Why are you using an unbound form? If you bind the form, your problem could disappear.

 

[buhal004]

Many thanks for your replies. I am trying to change
DoCmd.RunSQL StrSQL

And instead use

CurrentDB.execute, StrSQL

But even here I am encountering a problem. I’m getting “Too few parameters. Expected 4. Run-time error ‘3061’. I’ve never used this piece of code. Please give me some directions.

 

[theDBguy]

Many thanks for your replies. I am trying to change
DoCmd.RunSQL StrSQL

And instead use

CurrentDB.execute, StrSQL

But even here I am encountering a problem. I’m getting “Too few parameters. Expected 4. Run-time error ‘3061’. I’ve never used this piece of code. Please give me some directions.

No promises, but try using this function instead.

Code Snippets

 

[buhal004]

It is way too hard to isolate the commented out code from the active code. Please post the actual, working code without all the garbage. Code that isn't being used is garbage. get rid of it unless you are testing variations and don't know which you will keep.

How do you know the "query" isn't running? Which query are you talking about? I'd assume the append query but I don't know.

Why are you using an unbound form? If you bind the form, your problem could disappear.

Forgive me for asking for further clarification but which is the inbound form ? I must tell you I’m self taught in VBA and learn by asking about issues like this. Thank you.

 

[buhal004]

Forgive me for asking for further clarification but which is the inbound form ? I must tell you I’m self taught in VBA and learn by asking about issues like this. Thank you.

I know that sometimes the query does not work because SOMETIMES the procedure executed a new record in invoicelog is created but the table servicelog is not updated.

 

[zeroaccess]

Forgive me for asking for further clarification but which is the inbound form ? I must tell you I’m self taught in VBA and learn by asking about issues like this. Thank you.

"Bound" means that your controls are bound to fields in a table or query. It means they have a Control Source:

 

[buhal004]

"Bound" means that your controls are bound to fields in a table or query. It means they have a Control Source:

View attachment 79941

Thank you. I appreciate that. But what I meant was how do you know from the code which form is unbound.

 

[vba_php]

Thank you. I appreciate that. But what I meant was how do you know from the code which form is unbound.

your code has nothing to do with binding and unbinding of forms. I think you are confused. if you look back at Pat Hartman's post in this link, she was telling you that if you bind your form to a table or query object, you would never have to run all these sql statements that you are running ever again. Do you follow? zeroAccess gave you a screenshot of what "bound" means, in terms of looking at the property sheet of a control on a form. this has nothing to do with a form itself, but the idea is the same.

 

[Pat Hartman]

Access is a Rapid Application Development (RAD) tool. It does lots of stuff for you automatically. You can essentially build a functioning application without writing a single line of code or creating a macro. Access automatically fills your forms with data and saves that data when you change something.

Your code is running append queries. That implies that your data is not being saved automatically. Is this intentional? There are lots of Access templates available. You can see some when you open the Access application. If you choose a template, Access will download it and open it for your. They all use bound forms. I don't like most of these templates. They are too simplistic, incomplete, and in some cases even use bad practices. But, any one of them will be using bound forms. Open any form in design view. Look at the RecordSource property. It will contain the name of a table or query or might even include a query string. Then each control on the bound form will have a ControlSource and that will include the Name of one of the columns selected by the form's RecordSource. Access completely manages fetching and saving everything bound. When you get into app building, you will almost certainly want to write code to validate data entered in your form but the fetching and saving is handled by Access.

 

[buhal004]

I have been using access for some years now. I am self taught but use access-programmers forum for some intuition. The code that I have written does a lot of things to my data and I doubt whether one can do all that without writing code.

I could be wrong of course and look forward to have expert direction.

My access program has been running well for years and I am quite satisfied. My issue is that part of the code fails to execute, sometimes. Most of the time it goes on well.

The part that does not work at time is the following;

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));"
DoCmd.RunSQL StrSQL

I am taking the advise of one advisor on this forum who suggested changing "DoCmd.RunSQL StrSQL" to "CurrentDB.execute"

I get an error saying Too few parameters. Apparently it is not as simple as it looks. Can someone show me, please, who to convert the code so that it works with "CurrentDB.execute.

thanks for your help.

 

[theDBguy]

I have been using access for some years now. I am self taught but use access-programmers forum for some intuition. The code that I have written does a lot of things to my data and I doubt whether one can do all that without writing code.

I could be wrong of course and look forward to have expert direction.

My access program has been running well for years and I am quite satisfied. My issue is that part of the code fails to execute, sometimes. Most of the time it goes on well.

The part that does not work at time is the following;

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));"
DoCmd.RunSQL StrSQL

I am taking the advise of one advisor on this forum who suggested changing "DoCmd.RunSQL StrSQL" to "CurrentDB.execute"

I get an error saying Too few parameters. Apparently it is not as simple as it looks. Can someone show me, please, who to convert the code so that it works with "CurrentDB.execute.

thanks for your help.

Hi. Did you try the link I posted in Post #11?

 

[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.