?
Solved

ShowAllRecords in Access 2007 VBA does not work

Posted on 2010-09-14
6
Medium Priority
?
1,043 Views
Last Modified: 2012-05-10
I am developing an app in Access 2007.

In a form, I have a subform that shows records from a table. I have a button that will filter this display reflecting a field in the displayed table that I have highlighted: code A. . Works a treat.
 I have another button that just does docmd.showallrecords (code B) - that does nothing apart from redisplaying according to the filetr I earlier specififed, it does not return to displaying the entire table. The Toggle filter button in the ribbon, however, does what it should and toggles between the latest filter and no filter.

This code works in Access 2002 (XP).

I repaired and compacted the app and that makes no difference...

Any ideas?
'code A
Private Sub Command9_Click()
'select similar sessions...

On Error GoTo Err_Command9_Click

    ' look at what was selected in the existing display
    Screen.PreviousControl.SetFocus
    ' fileter the table by this criterion
    DoCmd.RunCommand acCmdFilterBySelection

Exit_Command9_Click:
    Exit Sub

Err_Command9_Click:
    MsgBox err.description
    Resume Exit_Command9_Click
    
End Sub

'code B
Private Sub Command10_Click()
' show all sessions...

On Error GoTo Err_Command10_Click

    DoCmd.ShowAllRecords
    
Exit_Command10_Click:
    Exit Sub

Err_Command10_Click:
    MsgBox err.description
    Resume Exit_Command10_Click
    
End Sub

Open in new window

0
Comment
Question by:Peborgh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 33674037
Hi,

instead of "ShowAllRecords" simply set the Filter to an empty string:

Me.Filter=""
Me.FilterOn=False

Cheers,

Christian
0
 

Author Comment

by:Peborgh
ID: 33682089
Sorry, as your defined it, nothing still hweppened.
I need an example of the syntax:
Say the form is "fred" and the subform "bill" lives in a control in fred calld jim, how do I set the filter for that inner form?

many thaks,

peter
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 33684744
Hi Peter,

depends on where you want to execute the function. If you want to execute it in the subform then you can use the syntax above (in the click event of the button which should remove the filter).
If you have the button in the main form then you can use this instead:

Me.jim.Form.Filter=""
Me.jim.Form.FilterOn=False

The names of the main form and the sub form isn't important, you only need to know the name of the subform control (jim in your example).
A subform control can only have one form as child object so you get the reference to the child form with the "jim.Form" reference.

The "DoCmd" commands are in many cases only the functions which would be executed if you would have defined the same thing in a macro. And in many cases they work only if the form is activated where they should work on. So if you would use "Me.jim.Form.SetFocus" before the "DoCmd.ShowAllRecords" it should work, too. But in any case where you can avoid the "DoCmd" you should better use the methods and properties of the objects - in this case the "Filter" and "FilterOn" properties which any form object does have.

Cheers,

Christian
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Peborgh
ID: 33689608
Christian, you're a very nice man! Your latest suggestion for a button in the main form worked!

And thanks for the dire warning re DoCmd...

Many thanks.

peter
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 33690081
Hi Peter,

you're welcome.

Here's a little demo I wrote earlier to show you how to work with filters for a search form. With this you can filter any column of an endless form individually, very simple to implement:

http://www.office-loesung.de/ftopic13271_15_0_asc.php#

(You will find it at the end of the page, named "FilterDemo3.zip" to download.)

Cheers,

Christian
0
 

Author Comment

by:Peborgh
ID: 33699740
Renewed thanks. Won't look at this immediately because of dealine pressures.

peter
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 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