Running a docmd.showallrecords command

[huv123]

Hi! Ive create a form (lets called it "OriginalForm") which has a popup menu which has a option that causes a search form ("SearchForm") to popup. {* Each option on the popup menu has a code attached to it so it can carry out some action). When the search button on the search form is clicked, the filtered records are displayed in Original Form. I want to add an option on the popup menu in the Original Form that will return it to the original state so that all records are now visible.

I tried to create a option on the popup menu which would run this code:

Public Function showAll(strForm As String)

DoCmd.showAllRecords

End Function


but its done nothing for me...

I know Im missing something i.e. form name ie. ....allrecords "frmOriginal Form" etc, but when I try to add this in I get an error. Im too new to this to work out how to do it myself and I cant seem to find answers on own.

If someone could please help it would be much appreciated. =)

 

[TheAceMan1]

How are ya huv123 . . . . .

[blue]   Forms!OrigionalFormName.FilterOn = False[/blue]

See Ya! . . . . . .

 

[huv123]

G'Day!

I tried to integrate that command but I couldnt get it to work, I think unfortunately I need to be walked through this!.

Just to give you an idea what I am dealing with:

I created a popup menu using these instructions:

http://www.databasejournal.com/features/msaccess/article.php/10895_3437731_2

but he didnt know how to attach code to the options, only macros, but I found a solution here:

http://www.jamiessoftware.tk/articles/menubars.html

so I have integrated these two ideas.

I have a number of commands include search records --> causes search window to popup, close form, add record (which takes me to a blank record), delete record (self-explanatory). The only two options I cant get to work is:

1. The show all records buttons, which I basically want the form to show all records, if go back to start..independant of filter etc...

2. Save new record that I added using my add record button and refresh the form so that n(existing number of records) + 1(the new one) is now showing in the window and can be navigated using the navigating buttons in edit view.


I hope this helped you understand what is going on. I see next to my name it says technical user, but the truth is that they just didnt have "slow to catch on newbie" option.

Thanks for all your help.

Othwewise enjoying life in sunny australia.

 

[ZmrAbdulla]

TheAceman1's suggestion will do it.
See how it starts...
Here is an example of filtering report using a popup form
may be useful

http://www.fontstuff.com/access/acctut19.htm

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[TheAceMan1]

Roger That huv123 . . . . .

I wasn't sure if you were talking a [blue]Popup Form[/blue] with menu or a [blue]Shortcut Menu[/blue]. From the links you provided I'll assume to know how to get to the button in question in the Shortcut Menu (for now we'll just handle the Show All button).

In a module in the modules window, copy/paste the following code ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Sub enShowAll()
   Dim Show As Boolean
   
   If Forms("[purple][b]OrigionalFormName[/b][/purple]").FilterOn Then Show = True
   CommandBars("[purple][b]ShortcutMenuName[/b][/purple]").Controls("[purple][b]ButtonName[/b][/purple]").Enabled = Show
   
End Sub

Public Function ShowAllOrig()
   Forms("[purple][b]OrigionalFormName[/b][/purple]").FilterOn = False
   Call enShowAll
End Function[/blue]

[ol][li]The routine [blue]enShowAll[/blue] enables the button if the form is filtered, disables otherwise.[/li]
[li]The function [blue]ShowAllOrig[/blue] is the function that runs for the [blue]Show All[/blue] button.[/li][/ol]
Now add the following line . . .

[blue]   Call enShowAll[/blue]

. . . to the following places:
[ol][li]The end of the [blue]Load[/blue] event of the [blue]OrigionalForm[/blue].[/li]
[li]The end of the code in your button on the [blue]Search Form[/blue].[/li][/ol]

Finally . . . in the Show All button of your Shortcut Menu - Properties - On Action, enter the following:

[blue]=ShowAllOrig()[/blue]

[purple]Thats it . . . give it a whirl and report any problems . . .[/purple]

See Ya! . . . . . .

 

[huv123]

http://us.f2.yahoofs.com/bc/4323812a_a89e/bc/My+Documents/TekTip.doc?bfPX4IDBYNrIWjZ3

Here is a word document that will show you screen shots of what it looks like =) and some of the code.

THe SHow all records option has "disappeared" but wont returned after Ive searched the records. I think because your code asks FilterOn=False, where no actual filter command originally exists if you know what I mean.

Im sorry for this hassle but its driving me crazy.

 

[TheAceMan1]

huv123 . . . . .

Disappointingly the link fails.

For now:

[blue][purple]Change:[/purple]
   CommandBars("ShortcutMenuName").Controls("ButtonName").Enabled = Show
[purple]To:[/purple]
   CommandBars("ShortcutMenuName").Controls("ButtonName").Enabled = [purple][b]True[/b][/purple][/blue]

This will put the [blue]Show All[/blue] button back on board.

Now . . . is the search form [blue]filtering[/blue] or [blue]manipulating the RecordSource[/blue] of the Origional Form?

See Ya! . . . . . .

 

[huv123]

I believe it is manipulating the original source...since there is no "Filter" command in the code.

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter frmRhinitis based on search criteria
        Form_frmRhinitis.RecordSource = "select * from tblBaseline where " & GCriteria
        Form_frmRhinitis.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

 

[TheAceMan1]

OK huv123 . . . .

Sorry about the wasted time thinking the form was filtered. Just gotta manipulate the code a little. [blue]The whole Idea is to simply restore the origional SQL for frmRhinitis.[/blue]

[ol][li]In the [blue]Declaration Section[/blue] of the module where you stored [blue]Public Sub enShowAll()[/blue], copy paste the following line:

[blue]Public OrigSrc As String[/blue]

This variable will [blue]hold the origional RecordSource[/blue] of frmRhinitis when the form is open.[/li]
[li]Replace the sub [blue]enShowAll()[/blue] and the function [blue]ShowAllOrig()[/blue] with the following:

[blue]Public Sub enShowAll()
   Dim Show As Boolean
   
   Show = (Forms!frmRhinitis.RecordSource <> OrigSrc)
   CommandBars("mnuReports").Controls("Show All").Enabled = Show
   
End Sub

Public Function ShowAllOrig()
   Forms!frmRhinitis.RecordSource = OrigSrc
   Call enShowAll
End Function[/blue]

Note in [blue]ShowAllOrig()[/blue] how the origional SQL is restored.[/li]
[li]Replace the code in the [blue]Load[/blue] event of frmRhinitis with this:

[blue]   OrigSrc = Me.RecordSource [green]'Hold Origional SQL[/green]
   Call enShowAll[/blue]

[/li]
[li]At the end of your [blue]cmdSearch_Click()[/blue] just before [blue]End Sub[/blue], you should have:

[blue]   Call enShowAll[/blue]

If not . . . make it so.[/li]
[li]The [blue]Action[/blue] property of your [blue]Show All button[/blue] should still be:

[blue]=ShowAllOrig()[/blue]

[/li][/ol]

Note: The Show All button is [blue]enabled as long as the recordsource of frmRhinitis and the variable OrigSrc are not the same.[/blue]

See Ya! . . . . . .

 

[huv123]

This works! However the report button which produced a report of the filtered no longer produces filtered results, it produces a report with all 50 records. Any ideas?

I had a simple generate report command i.e.

Private Sub Report_Click()

    'Close Search Form
    DoCmd.Close
    
    'Open report
    DoCmd.OpenReport "rptSubjects", acViewPreview, Maximize, GCriteria
     
End Sub

Maybe I need to change the options in the rptSubjects? I dont know it was working before...?! Its two steps foward, I step back!

 

[huv123]

It definitely relates to the change in code because when I put the old code back it works..!

I forgot there is also code in module 1 which relates to this:

[/code]
Option Compare Database

'Global variable used to store search criteria
Global GCriteria As String

[/code]

 

[TheAceMan1]

huv123 . . . .

This is because in the replacement code for [blue]cmdSearch_Click()[/blue] I provided in you other thread thread702-1120777, I didn't see a declaration for [blue]GCriteria[/blue], so I combined to make one SQL string. To allow for [blue]GCriteria[/blue] in that code:

[blue][purple]Replace:[/purple]
      SQL = "SELECT * FROM tblBaseline " & _
            "WHERE " & cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

[purple]With:[/purple]
      [b]GCriteria[/b] = "WHERE " & cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
      SQL = "SELECT * FROM tblBaseline " & [b]GCriteria[/b][/blue]

[purple]Note: this is what can happen when your trying to work with more than one thread and one modifys the other![/purple]

See Ya! . . . . . .

 

[TheAceMan1]

Woops . . . the above is not right either . . . should be like your origional:

[blue]      GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
      SQL = "SELECT * FROM tblBaseline Where " & GCriteria[/blue]

See Ya! . . . . . .

 

[huv123]

Yay it worked..! I got an error the first time I tried so I put the old code back in again and it worked..

a quick question - is the reason the report wont open in fron of the form screen this:

http://www.mvps.org/access/reports/rpt0003.htm

I didnt think my form was modal? Under properties, it says Modal:No? because the form doesnt have popup options etc.. I dont know how I can bring it in to focus..?

 

[huv123]

Actually I worked it out myself (see i am learning ) by writing code to set the form window invisible when the report window is open and making it visible again when it is closed. Thank you everyone for all your help!