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

Brandon GarnettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
Hi,

although I would slightly change some things in the code (like declaring rst as DAO.Recordset and not as Object by assuming that you are using DAO) there is no issue in the code as far as I can see.

So what you described seems more to be coming from the query you are using in the RecordSource. It would be good if you could create a demo database, import the form with the problem, the needed tables and queries, delete the most data and anonymize the rest and upload it here, so we can take a look at it. Sometimes the issues came from a completely other source, like lookup fields, bad formatting or anything like that (in general, not especially in your case).

Cheers,

Christian
0
Dale FyeCommented:
I agree with Christian.  I don't see anything that would cause that to occur.

You don't really need line 10 (me.Refresh) unless this is a multi-user application and another user might have modified data since you loaded your form.  And if that is the case, you might want to consider using me.requery to account for records that might have been added by others.  If you choose to keep that line, or change it to requery, I would probably put the .Filter and .FilterOn lines below that line, but this should not be a show stopper.

You should be also able to delete lines 17-21 and change line 22 to read:

Me.txtRecordCount.Value = me.recordsetclone.recordcount
0
Jeffrey CoachmanMIS LiasonCommented:
<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)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Brandon GarnettAuthor Commented:
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?
0
Jeffrey CoachmanMIS LiasonCommented:
<Would changeing Me.Refresh to Me.ReQuery help? >
Give it a try and see...

Typically neither Refresh or Requery should be needed if you do: Me.FilterOn=True
...But I don't know all the details of your environment, nor do I know how toggling the visibility of the detail section might effect this...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brandon GarnettAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.