I have a data input from that contains the fields. [Surnme] and [Forename] and [Dob]. There are 7 other fields on the input form and the report.
Several of the fields, including those above have AfterUpdate scripts to refresh the record.
I have even added a button to refresh the data right before clicking Print Preview.
When I do hit print preview, the report shows up with all of the fields correctly filled in except the ones above. They show the details of a previous record. I then have to go back to the input form, update the data again then I get the fields to update.
There are two tables in the record, relationships have been established.
On your Data Input Form when you add data and then Click a Command Button to Open the Report, then you
need a line of code to Save the data you have just entered. ie
You say you have code in the AfterUpdate event procedures of the controls in the form. I assume that this code writes the data in the controls to one or more tables. I'd suggest that you place breakpoints at the head of these procedures to enable you to step into the code when the procedures are executed. This will enable you to determine whether or not the code is doing what you expect. It will also enable you to examine the data in the tables before opening the report to determine that the relevant rows in the tables have been correctly updated.
Calling the Refresh method causes a form to show any changes to the data in a bound form's recordset to be reflected in the values shown in a form. To save a bound form's current record before opening a report by means of a button on the form the code in the button's Click event procedure should start with:
Code:
Me.Dirty = False
A bound form's Dirty property has a value of True if the data in the bound controls in the form has not yet been saved to the table to which the form is bound. Setting the property to False therefore saves the record to the table. Doing this before code opens a report ensures that the report will use the saved record, whereas, if the record has not been saved in this way, the report will use whatever were the values in the record, if any, before the data was entered or edited in the form. This is what’s happening in your case.
When inserting data into two related tables in a form, the form will normally be bound to the referenced table in the relationship, and will contain a linked subform bound to the referencing table. An Orders form with an OrderDetails subform, linked on OrderID, would be an example. In this case the parent form's current record is automatically saved when focus is moved to the subform. However, it would be possible to enter data in the parent form and then insert rows in the subform, and then return to the parent form and edit its data. Consequently a button in the parent form to open a report should still have Me.Dirty = False as the first line in its Click event procedure.
While bound forms will normally be used to enter or edit data, there might be occasions when an unbound form is used, or where a bound form contains both bound and unbound controls. In this scenario the data in any bound controls will be saved in the usual way, so again a button to open a report should still have Me.Dirty = False as the first line in its Click event procedure. Data in the unbound controls would need to be saved by executing code to write the data to the relevant table or tables. This might be done in each unbound control's AfterUpdate event procedure, or by other code in the form's module. However it's done, this code should be executed before any code which opens a report to ensure that the saved data is used by the report. The following code is taken from one of my demo files, and is for the AfterUpdate event procedure of a multi-select list box in a Students form in which courses taken by the current student can be selected or deselected. The code inserts or deletes rows for the StudentCourses table on the basis of whether a course in the list has been selected or not. A separate button opens a report showing which course each student is taking. As the student's course data, if changed in the form, will already have been saved by the following code, the report will always be up to date.
Code:
Private Sub lstCourses_AfterUpdate()
Dim n As Integer
Dim strCriteria As String
Dim strSQL As String
Const MESSAGE_TEXT = "A student must be entered before selecting any courses."
If IsNull(Me.StudentID) Then
MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
' loop through all items in list
' and deselect each one
With Me.lstCourses
For n = 0 To .ListCount - 1
.Selected(n) = False
Next n
End With
Exit Sub
End If
With Me.lstCourses
For n = .ListCount - 1 To 0 Step -1
strCriteria = "StudentID = " & Nz(Me.StudentID, 0) & " And CourseID = " & .ItemData(n)
If .Selected(n) = False Then
' if item has been deselected then delete row from table
If Not IsNull(DLookup("StudentID", "StudentCourses", strCriteria)) Then
strSQL = "DELETE * FROM StudentCourses WHERE " & strCriteria
CurrentDb.Execute strSQL, dbFailOnError
End If
Else
' if item has been selected then insert row into table
If IsNull(DLookup("StudentID", "StudentCourses", strCriteria)) Then
strSQL = "INSERT INTO StudentCourses (StudentID, CourseID, StatusID) " & _
"VALUES(" & Me.StudentID & "," & .ItemData(n) & ",1)"
Me.Dirty = False
CurrentDb.Execute strSQL, dbFailOnError
End If
End If
Next n
End With
End Sub
I'm going to be a bit dense here, but... what you do to a field during an AfterUpdate event is a change that hasn't been saved yet. You need ANOTHER data save to cause the underlying record to be updated. And you described that in fact, a second save does exactly that. But if the AfterUpdate event is in that form you just updated, you have created an event loop based on a never-ended update cycle. Your described behavior of the fields updated in the AfterUpdate event makes perfect sense for updating the record in the same form that fired the AfterUpdate event.
NEVER make changes to a record from an AfterUpdate event. The action that would have saved those changes has already occurred. Make changes in a BeforeUpdate event. Before you get confused on that... an update event from a form doesn't change any fields on the form. (It changes them in the underlying record.) So whatever you were going to save in the AfterUpdate event should already be ripe for saving in the BeforeUpdate event unless I have totally read this first post entirely wrong.
And "refresh the record" also doesn't seem to make an sense because at the moment of an Update (the event within Access), the form and the record should be identical. A Me.Refresh will be a non-event because after a save, the record WAS refreshed - by the SaveRecord action. So the Me.Refresh would just pick up what you wrote during the Update and load them to the controls. As I recall, you get a Form_Current event after a successful update, so the Me.Refresh is automatically implied.
If I have totally misunderstood this, OK. But something just doesn't seem quite right here.
