VBA runs well manually but fails when running alone !

[LOUISBUHAGIAR54]

Hi everyone on the weekend.

I have the following code:

Private Sub Command5_DblClick(Cancel As Integer)
If IsNull(Me.List0) Then
MsgBox "Choose a client from the list first !"
Exit Sub
Else
DoCmd.OpenReport "RptInvoice", acViewPreview, , , acWindowNormal
Reports!RptInvoice.Filter = " [cltIdcard] = """ & Me.List0.Column(0) & """"
Reports!RptInvoice.FilterOn = True
End If
Set mydb = CurrentDb()
Set rst1 = mydb.OpenRecordset("tblInvoices", dbOpenDynaset)

'filling up a new record from tblInvoices.

rst1.AddNew
rst1!InvDate = Date
rst1!Invoiced = True
rst1!CltID = Me.List0.Column(0)
rst1!UserNameInv = Forms!FrmMain!TxtIdentifier
rst1!Paid = False
rst1!InvAmount = Reports!RptInvoice!TotalSum 'the problem seems to be coming from here although the information passes well when run manually
Me.paste_invoiceno = rst1!InvoiceNo
rst1.Update
rst1.Close
Set rst1 = Nothing
Set mydb = Nothing

'print the report as now you have its invoiceno
DoCmd.OpenReport "RptInvoice", acViewPreview, , , acDialog


Dim StrSQL As String

'mark the tblHospitalisations as invoiced and add invoice no

StrSQL = " UPDATE tblHospitalisations SET tblHospitalisations.InvoiceNo = [Forms]![frmBilling]![paste_invoiceno], tblHospitalisations.invoiced = true" _
& " WHERE (((tblHospitalisations.cltIdcard)=[Reports]![RptInvoice]![cltIdcard]) AND ((tblHospitalisations.invoiced)=false));"
DoCmd.RunSQL StrSQL

'mark the tblservicelog as invoiced and add invoice no
StrSQL = "UPDATE tblservicelog SET tblservicelog.invoiceno =[Forms]![frmBilling]![paste_invoiceno] , tblservicelog.invoiced = true" _
& " WHERE (((tblservicelog.invoiced)=false) AND ((tblservicelog.hospno)=[Reports]![RptInvoice]![cltIdcard]));"
DoCmd.RunSQL StrSQL


'mark the tblinvestigations as invoiced and add invoiceno
StrSQL = "UPDATE tblTest INNER JOIN tblClinPict ON tblTest.IDClinPict = tblClinPict.ID SET tblTest.Invoiceno = [Forms]![frmBilling]![paste_invoiceno], tblTest.Invoiced = true" _
& " WHERE (((tblTest.Invoiced)=false) AND ((tblClinPict.CltID)=[Reports]![RptInvoice]![cltIdcard]));"
DoCmd.RunSQL StrSQL

Me.paste_invoiceno.Value = 0
Me.Requery
End Sub

When I run it with a 'toggle breakpoint' and using F8, it runs well and the InVAmount field in the table tblInvoices fills well according to the amount on the report field (TotalSum).

When the vba code is running alone without a breakpoint the InVAmount on tblInvoices always fills up with '0'.

Is there an explanation for this ?

Many thanks.


LouisB

 

[LOUISBUHAGIAR54]

Hello,

There has not been a reply to this problem yet, so I presume I have not explained myself well. Whether this vba code works or not seems to depend on whether it is run automatically or in step wise fashion using debugging and F8.

Automatically the field [InVAmount] in the rst1 recordset does not fill up properly from Reports!RptInvoice!TotalSum and the first StrSQL (SQL string) does not work either.

Manually using F8 both work fine.

It appears to me that Access is not having enough time to figure out the code when run rapidly automatically. Is this the reason and if so (?? ) how can it be fixed. I have other instances of similar codes and I have not had similar problems even with more complex codes and SQL statements.

Many thanks for any help as i need it badly.


LouisB

 

[vbaInet]

Why are you trying to retrieve a value from a report?
Also, you shouldn't be saving calculated values.

 

[LOUISBUHAGIAR54]

Good questions.

The answers are the following.

The report I am talking about is an invoice. The report is RptInvoice and it contains three subreports. TotalSum is a field on the page of the report which calculates totals on the three subreports. This whole code is intended to issue the invoice and save various bits of information into a table called tblInvoice. One of the fields of this table is the sum total on the invoice. Although i could use the DSum function in place of this field I though it would make things more complicated. In fact the TotalSum field on the report is the sum total of the functions using DSum on the three subreports.


In fact I have another similar database which is designed with a similar concept. I have worked with it for the last six years with no problems.

LouisB.

 

[vbaInet]

And the reason why you're needing to save calculated values is?

Yes you may have been using it for a long time but it's really not the right way to work with Reports. Does the report have to be open before you run this code? I'll take that as a Yes unless the code will fail otherwise. Whatever you're calculating in the report can be done much quicker in a recordset or using one of the domain aggregate functions (as you already know).

 

[LOUISBUHAGIAR54]

Hello vbaInet.

As you surmised the answer is yes; the report has to be open so that TotalSum can pass on to the tblInvoices.

The thing is the client for which the invoice is made is chosen from a list on a form called "frmBilling".

I do not know how to make set up a recordset so that the idcardno of the client is permanently recorded so that an aggregate function can be stored permanently. I do not consider myself and advanced programmer and would like to learn more on this.

This however still leaves the question on why the SQL statement I referred to still does not work if the code is run automatically.

Many thanks for your expert help. I would really like to learn how to work with recordsets as you suggest.


LouisB

 

[Simon_MT]

You really should save information form the frmbilling into tblInvoices and then proceed from there with the Printed Invoice.

Simon