Brandon Garnett
asked on
MS Access Continuous Form Detail Section does not refresh properly
Hello,
I have a Continous Form. in the Form Header, I have a Combo box where the user selects criteria.
In the After Update Event of the Combo Box I make the Filter for the form apply based on what the user selected, refresh the form and make the Detail section visible to show the records (Detail section is set to Visible=False by default)
If I select one item from the combo box, everything appears ok. Then when i change my selection to another item, The first record from the original selection still appears but the rest of the records are for the new selection. The detail section should only be showing the records for the new selection.
How do i fix my issue? If you need more explanation, I'll do my best. Code for the AfterUpdate event is below. Thanks
I have a Continous Form. in the Form Header, I have a Combo box where the user selects criteria.
In the After Update Event of the Combo Box I make the Filter for the form apply based on what the user selected, refresh the form and make the Detail section visible to show the records (Detail section is set to Visible=False by default)
If I select one item from the combo box, everything appears ok. Then when i change my selection to another item, The first record from the original selection still appears but the rest of the records are for the new selection. The detail section should only be showing the records for the new selection.
How do i fix my issue? If you need more explanation, I'll do my best. Code for the AfterUpdate event is below. Thanks
Private Sub cboProjects_AfterUpdate()
On Error GoTo Err_cboProjects_AfterUpdate
' Apply Filter based on Users Selection
If Me.cboProjects.Value > "" Then
Me.Detail.Visible = False
Me.Filter = "ProjectID = " & Me.cboProjects.Value
Me.FilterOn = True
Me.Refresh
Else
Me.Detail.Visible = False
Exit Sub
End If
' Count the Number of Notes for this Contact on thie Project
Dim rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo Err_cboProjects_AfterUpdate
Me.txtRecordCount.Value = rst.RecordCount
' If record count is 0, Display message to User that there are no notes to edit.
' If record count is not 0, Show the detail section of the form to show the notes.
If Me.txtRecordCount.Value = 0 Then
Me.Detail.Visible = False
MsgBox "There are no notes for this Contact on the Selected Project/Prospect." & vbCr & vbCr & "Please Choose a different Project/Prospect"
Else
Me.Detail.Visible = True
End If
Exit_cboProjects_AfterUpdate:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub
Err_cboProjects_AfterUpdate:
MsgBox "An Error Occurred in the cboProjects AfterUpdate Event; Form: " & Me.Name & vbCr & vbCr & "Please take a Screen shot of this Error message and send to tech@healthcapital.com" & _
vbCr & vbCr & "Error Number: " & Err.Number & vbCr & vbCr & "Error Desc: " & Err.Description, vbInformation, "PLEASE TAKE A SCREENSHOT OF THIS ERROR!"
Resume Exit_cboProjects_AfterUpdate
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Thanks for all the suggestions.
I tried going about this in another direction where instead of applying a filter, I just changed the forms RecordSource to what i need. However, it did not resolve my issue.
So what i did was i replaced lines 8 and 9 with a string holding a SQL statement, then set the form's RecordSource to the string.
Would changeing Me.Refresh to Me.ReQuery help?
Thanks for all the suggestions.
I tried going about this in another direction where instead of applying a filter, I just changed the forms RecordSource to what i need. However, it did not resolve my issue.
So what i did was i replaced lines 8 and 9 with a string holding a SQL statement, then set the form's RecordSource to the string.
Would changeing Me.Refresh to Me.ReQuery help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found my problem, The combo box where the above code is in the AfterUpdate event had its control source set, so everytime i changed records in the AfterUpdate event, it would result in the Record in my database to get update to the new controlsource value.
Removed the ControlSource and all works well.
Thanks all for your time and efforts, I will award each of you points for it.
Removed the ControlSource and all works well.
Thanks all for your time and efforts, I will award each of you points for it.
You really don't need to run through a recordset to get a count of the records...
You can do something like this:
me.txtRecordCount=Dcount("