Link to home
Start Free TrialLog in
Avatar of Brandon Garnett
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

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

Open in new window

SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<aside>
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("ProjectID","YourProjectsTable","ProjectID=" & Me.cboProjects.Value)
Avatar of Brandon Garnett
Brandon Garnett

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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.