Update data on opening a report

[Anneg]

Hello
I've tried looking for previous posts to find an answer. I'm sure this has
been asked many times so apologies for asking it again.
I have a form through which records are added and edited. This form has a
button which opens a report. If the report is opened immediately after
making an edit to a relevant record, the report does not reflect this edit.
How can I automatically have this report reflect the update. I know how to
get into the code area of the database if this is relevant. I would just
need somebody to write me the code so that I can copy and paste it in.
Many thanks.
Anne

 

[boblarson]

If your form is BOUND then adding a record WILL show up in the report right
away, but you have to understand WHEN Access adds the data to the table. It
will write it if you force a save (say using a save button with the command:
DoCmd.RunCommand acCmdSaveRecord in it) OR if you move to another record. If
you enter data and stay on that record, it really hasn't written to the table
yet so your report isn't going to reflect the data.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Jeanette Cunningham]

Hi Anne,
the record that was edited needs to be saved by forcing the form to do a
save before the report is opened. When the button is clicked to open the
report, the first bit of code needs to tell the form to save the record.
If you can get into the code behind this form, can you post back a copy and
paste of all the code for the click event of the button that opens the
report. Then we can give the complete code which will save the edit before
the report opens.

Jeanette Cunningham

 

[Anneg]

Hello Bob
Yes, I see that that's the case. All I needed to do was to move to another
record. Derrrrrr.
Very many thanks for explaining.
Anne

 

[Anneg]

Hello Jeanette
Many thanks for this. Here is the code:

Private Sub Women___19_to_29_Click()
On Error GoTo Err_Women___19_to_29_Click

Dim stDocName As String

stDocName = "Women 20 - 29"
DoCmd.OpenReport stDocName, acPreview

Exit_Women___19_to_29_Click:
Exit Sub

Err_Women___19_to_29_Click:
MsgBox Err.Description
Resume Exit_Women___19_to_29_Click

End Sub

Thanks again.
Anne

 

[boblarson]

A quick thing to add before the Open report code would be:

If Me.Dirty Then Me.Dirty = False

and that will force a save of the record before opening the report.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Anneg]

Hello Bob and Jeanette
Thanks for this code Bob. I'm not exactly sure where in the code this
should go. My previous post included the code behind the button. Where in
that code should it go?
Many thanks again.
Anne

 

[Jeanette Cunningham]

Anne, try it with this code.

Private Sub Women___19_to_29_Click()
On Error GoTo Err_Women___19_to_29_Click

Dim stDocName As String

stDocName = "Women 20 - 29"

If Me.Dirty = True Then
Me.Dirty = False
End If

DoCmd.OpenReport stDocName, acPreview

Exit_Women___19_to_29_Click:
Exit Sub

Err_Women___19_to_29_Click:
MsgBox Err.Description
Resume Exit_Women___19_to_29_Click

End Sub


Jeanette Cunningham

 

[boblarson]

Private Sub Women___19_to_29_Click()
On Error GoTo Err_Women___19_to_29_Click

Dim stDocName As String
If Me.Dirty Then Me.Dirty = False
stDocName = "Women 20 - 29"
DoCmd.OpenReport stDocName, acPreview

Exit_Women___19_to_29_Click:
Exit Sub

Err_Women___19_to_29_Click:
MsgBox Err.Description
Resume Exit_Women___19_to_29_Click

End Sub

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Anneg]

Hello again
Thanks so much. I copied and pasted the whole code. When I click on the
button I get this message:

"You entered an expression that has an invalid reference to the property
Dirty."

Never mind. I'm a bit new at this and I'm bound to be doing something wrong.
If you're unable to offer any more suggestions I'll perservere.

Thanks so much to you and Jeanette.
Best wishes
Anne

 

[Anneg]

Hi Jeanette

I get the same message as with Bob's code. Any other suggestions? I'll
keep persevering!

Thanks for your efforts.
Anne

 

[Jeanette Cunningham]

Anne,
I have seen this error before, but I can't remember exactly what to do.

Could you do some debugging like this?
Replace the code we gave you with this code below.
After you paste this code, run your from with some new data and open the
report.
Press Ctl + G
In the immediate window (usually down the bottom), find what is written
after Dirty property:
and post it back.

Private Sub Women___19_to_29_Click()
On Error GoTo Err_Women___19_to_29_Click

Dim stDocName As String

stDocName = "Women 20 - 29"

Debug.Print "Dirty property: " & Me.Dirty

DoCmd.OpenReport stDocName, acPreview

Exit_Women___19_to_29_Click:
Exit Sub

Err_Women___19_to_29_Click:
MsgBox Err.Description
Resume Exit_Women___19_to_29_Click

End Sub

Jeanette Cunningham

 

[Jeanette Cunningham]

Anne,
I looked up the info for this error. Here is the info:
ACC2000: Dirty Property Not Recognized on Unbound Forms
(208908) - When you reference the Dirty property of an unbound form (a
form that is not based on a table or query), you may receive the following
error message: Run-time error '2455': You entered an expression that has an
invalid reference to the property Dirty
http://support.microsoft.com/kb/208908/en-us

Is the button to open the report on an unbound form? I assumed it was
a bound form when you said you were entering data into it.

Jeanette Cunningham

 

[Mike Revis]

I'm not sure if this is relevant but the way I save the record before moving
to something else using a command button is to put this in the got focus
event of the command button. Using Access 2000.



Private Sub cmdOpenrptYourReportName_GotFocus()
Refresh
End Sub

Mike

 

[boblarson]

Anne:

What version of Access are you using?
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Anneg]

Hi Jeanette, Bob and Mike
Jeanette, I think you might have hit the nail on the head. I failed to
explain that my report button is actually on another "form" which is unbound.
On this "form" I only have buttons to preview reports. My actual data is
entered on another form which is of course bound.

Forgive my ignorance. I suppose this isn't the best thing to do. Is there a
better way? Maybe a course in Access 2003 might be of assistance you might
say.

I did try your cut and paste and Ctrl+G but nothing showed in the window.

Bob, I'm using Access 2003.

Mike, thaks for that suggestion. I have tried it previously to no avail. I
think the fact that my form is unbound has a lot to do with it.

Geeez, talk about rank amateur. I'm doing all this for a swim club
competition to be held shortly.

I really appreciate everyone's input.

Best
Anne

 

[John Spencer]

SO you could try to force a save on all the open forms.

The code snippet below will attempt to save the data on all open forms. The
problem is that if the data can't be saved (incomplete entry - data required
in certain fields) then you would get an error.

Private Sub yourSubNameHere()

Dim I As Long

On Error GoTo ERROR_fSaveAll

For I = 0 To Forms.Count - 1
If Forms(I).Dirty = True Then Forms(I).Dirty = False
Next I

'======== Your print code here ============

Exit sub
ERROR_fSaveAll
msgbox "Save Error on " & Forms(I).Name & " " & vbcrlf & _
Err.Number & " " & Err.Description

End Sub
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Anneg]

Hi John

Yeeeeeeaaaaahhh! Thanks so much. The key line is:

"If Forms(I).Dirty = True Then Forms(I).Dirty = False"

Which I have copied into all my report button codes, i.e.:

Private Sub YourReportName()
On Error GoTo Err_YourReportName_Click

Dim stDocName As String

stDocName = "YourReportName"
If Forms(I).Dirty = True Then Forms(I).Dirty = False
DoCmd.OpenReport stDocName, acPreview

Exit_YourReportName_Click:
Exit Sub

Err_YourReportName_Click:
MsgBox Err.Description
Resume Exit_YourReportName_Click

End Sub

So I now to use this code for any unbound forms.

Many thanks John and to everyone else.

Best wishes
Anne

 

[John Spencer]

You really need to do the loop since what you are entering is going to
update just ONE form and that form is the first form that is opened in the
current collection of forms.

What you have MAY work as long as the form you want to save the data for is
the first form you have opened in the forms that are open.
It seems to be treating I as a zero value (first form). I'm not sure why
it is doing that


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Anneg]

Thanks very much John. I will work on this.
Best
Anne