missing data Timing Issue? (1 Viewer)

[TomL]

I have a report that, when the report is run from the form it's missing data, yet when I run the report from the IDE the report works fine and the data is present. I'm thinking it might be a timing issue, that is to say, if I step through the code, where the database is updated and the report is run the missing data, which is from the same form that calls the report is on the report. On the other hand if I just run the form and the report runs the data is missing. Is this a bug or is there some why know if the database has been updated so I know the data will be there? Here's the code, which is straight forward, just two database updates and run the report.
UpdateValues
UpdateExpenses

DoCmd.OpenReport "rptCourtForm", acViewPreview

Thanks,
Tom

 

[HiTechCoach]

Are you opening the report from the form?

 

[TomL]

Yes, I have a button on the form, which calls the report.

Tom

 

[HiTechCoach]

Tom,

TIP: Use the forum code tags. Youc an click th # icon.

I do not things this is a bug in Access. I have not experienced it. Might be a bug in your code logic.

UpdateValues
UpdateExpenses

What is the VBA code in these Subs?

 

[TomL]

The UpdateValues and UpdateExpenses are calls to two procedures, the first does editing and loads class values the second, UpdateExpenses is a call to a procedure that updates the database table. As I mentioned if I step through the code and the report is loaded the values are in the report and if I don't go through debug the values don't show in the report. I should mention the values that don't show up in the report are the same values that are updated in the form, which after the update calls the report, which is why I thought there was a timing problem.

Thanks,
Tom

 

[HiTechCoach]

Tom,

It does sound like the code you are using is causing some type of timing issue

Without seeing your code I can't offer any suggestions on how to solve the timing issue.

Are you not able to post the VBA code?

 

[TomL]

Here's the code for the database update. As I mentioned I can step through the code without problem. Upon returning a value of true from the function I call the report.

Public Function UpdateExpenses() As Boolean
Dim sqlStr As String

On Error GoTo ErrorCheck
If Not GetConnection Then
Exit Function
End If

If Not rs Is Nothing Then
If rs.State = 1 Then
rs.Close
End If
End If

sqlStr = "select * from FinancialCase where ID = " & CurrentID
rs.Open sqlStr, Conn, adOpenKeyset, adLockOptimistic
rs("HouseholdNumber") = clsfinancial.HouseholdNumber
rs("MedicalExpenseAmt") = clsfinancial.MedicalExpenseAmt
rs("MedicalExpenseLength") = clsfinancial.MedicalExpenseLength
rs("CourtOrderedAmt") = clsfinancial.CourtOrderedAmt
rs("CourtOrderedLength") = clsfinancial.CourtOrderedLength
rs("ChildCareAmt") = clsfinancial.ChildCareAmt
rs("ChildCareLength") = clsfinancial.ChildCareLength
rs("OtherExpense") = clsfinancial.OtherExpense
rs("OtherExpenseAmt") = clsfinancial.OtherExpenseAmt
rs("PropertyLeanAmt") = clsfinancial.PropertyLeanAmt
rs.Update
UpdateExpenses = True
rs.Close
Set rs = Nothing
Exit Function
ErrorCheck:

MsgBox "Error In Other Income Record. " & Err.Description, vbOKOnly, "Error"
UpdateExpenses = False

End Function

 

[HiTechCoach]

Tom,

Thank you for posting the code. That really helps.

I have some questions for you.

1) What version of Access (exe) are you using?

2) Is the database split?

3) If split or not split: In what format is the database?

4) If split:
a) In what format is the back end?
b) Are you using linked tables?
c) Is the back end located on another computer?

5) Are you using Option Explicit in every module?

6) I noticed in your code that you might be using a record set variable (rs) that is globally defined. Is this correct? Is there a reason why is is not defined (DIM'ed) locally within the function?

I find it "best practice" to keep the scope for variables local whenever possible.

Try changing the scope of the rs variable to local within the function to see if that has any effect on the speed (timing).

 

[vbaInet]

Whilst you're answering Boyd's questions could I get you to change your approach of updating the record from a recordset to using an SQL Update statement. I believe your problem originates from the update not reaching the db on time via the connection.

Two suggestions:

1. Use an UPDATE statement in place of the recordset

OR

2. Add a DoEvents after the two functions.

One thing to note as well, if your report's recordset is based on your form and the record you're updating affects the form's recordset, it would be wise to Requery the form just before opening the report.

 

[TomL]

First, thanks to everyone for all the help and suggestions. The problem was resolved by changing the db update to a sql update statement and adding the doEvents.
I also saw the wisdom of HiTech's suggestion and moved to use a local variable, figuring that not only might it help, but also because it is good practice.
Again, thanks for the help with this problem.

Regards,
Tom

 

[HiTechCoach]

EDIT: Tom, I had starting typing a reply before I your post.

I agree with vbaInet.

I would also recommend using an Update query.

What I like to use instead of DoEvents is to forces a refresh of the cache.

Example:

DBEngine.Idle dbRefreshCach

Note: I only use ADO with NON-Access backends (tables).

My understanding is that when using an Access backend (tables) that ADO adds another layer (ODBC). When JET/ACE get the request in ADO it must translate it back to DAO before executing.

 

[HiTechCoach]

One thing to note as well, if your report's recordset is based on your form and the record you're updating affects the form's recordset, it would be wise to Requery the form just before opening the report.

I would not requery the form but save the data if the form is dirty.

Example for a command button on a form to run the report:

' used to store the returned value from the function
Dim bolResult as Boolean

' save any changes to the record in the form.
If Me.Dirty Then Me.Dirty = false

Call UpdateValues

' since the function returns a value, store it for later use
bolResult = UpdateExpenses()


if bolResult = True then 
  ' only run the report if UpdateExpenses()  was successful 
   DoCmd.OpenReport "rptCourtForm", acViewPreview

End If

I added code to use the function's returned value.

 

[TomL]

Boyd,
Thanks for all the input, I'll give your suggestions a try.

Regards,
Tom

 

[HiTechCoach]

Tom,

You're welcome.

Let us know how it goes.