MS Access Continuous Form Detail Section does not refresh properly

Posted on 2013-08-30
Medium Priority
Last Modified: 2013-09-03

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.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
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"
    Me.Detail.Visible = True
End If

    On Error Resume Next
    Set rst = Nothing
    Exit Sub

    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

Question by:Brandon Garnett
LVL 24

Assisted Solution

Bitsqueezer earned 668 total points
ID: 39454637

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).


LVL 50

Assisted Solution

by:Dale Fye
Dale Fye earned 664 total points
ID: 39454899
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
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39455097
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)
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Author Comment

by:Brandon Garnett
ID: 39460475

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?
LVL 74

Accepted Solution

Jeffrey Coachman earned 668 total points
ID: 39460574
<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...

Author Comment

by:Brandon Garnett
ID: 39460828
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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question