Solved

Set the record  source of a form from another form

Posted on 2011-03-09
8
274 Views
Last Modified: 2012-06-21
I'd like to set the record source of a form from a "triggering" form.

Can I and if so what is the syntax for setting the record source of ResultsForm from my SearchForm?

Thanks
0
Comment
Question by:epuglise
  • 4
  • 3
8 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility


Forms!YourOtherFormName.Form.RecordSource = "SomeQueryName"
That Form needs to be open first.

mx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
OR ...

Dim sSQL As String
sSQL = "your SQL String here"

Forms!YourOtherFormName.Form.RecordSource = sSQL

mx
0
 

Author Comment

by:epuglise
Comment Utility
so the last bit of code in my search button click is the docmd.openform of the form whose record source i'm  trying to set. I'm currently passing a filter via the OpenArgs attribute.

Could I pass the filter and the data source via OpenArgs? otherwise I'm not sure how to have the results form open when i tell it what its recordsource is.

I hope that made sense. :/
thanks
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"Could I pass the filter and the data source via OpenArgs?"
Sure ... but you will have to parse out the two items in the Form Load event.  Either pass the SQL or the Query name ... with this Filter information ... if that is what you mean ?

You can also open the Form with a WHERE clause in the Open command ... which can be applied to the existing Recordsource .... using this syntax

DoCmd.OpenForm "YourFormName",acFormDS ,, <WHERE CLAUSE HERE>

mx
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:epuglise
Comment Utility
Ok, here are the gory details.

my query includes data that I need to search on, but that results in duplicate values of stuff i only want to see once in the results form datasheet.

qAnimal =

Animal | Location | Attribute
Giraffe | Africa | Yellow
Giraffe | Africa | Tall
Giraffe | Africa | Herbivore
Lion | Africa | Yellow
Lion | Africa | Short
Lion | Africa | Carnivore

This "Select Distinct" query has everything I might need to search on based on what the user enters in various search boxes. I've tried hiding the Attribute column in the query, but then the filter can't find the attribute data to search on.

My results Subform has for its datasource, this query, qAnimal. The code passes it a where clause via OpenArgs.

If the where clause is "attribute like Yellow" I'll get exactly two results. Which is what I want
If the where clause is "location like Africa" I'll get six results. Which I don't want. I want two results.

So I thought that one way around this would be to have two queries, one with attributes and one without.  Then, if the user enters an attribute search term, I'd use qAnimal, but if not, then I'd have them use qAnimalNoAttribute (which would be the same as qAnimal, just without the attribute column) .

There's probably a way more elegant way of doing this.  I'm all (elephant) ears... :)
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
OK

I do this from time to time.
You can pass in any string as your OpenArgs.
The trick is to pass it in, in a delimited format, split it and then do some logic with it

If you pass in "Location,Africa" to the following code you should get just two results.
If your report detail code has

if me.attribute = "" then
    me.attribute.visible = false
else
    me.attribute.visible = true
end if

The attribute textbox, or control would be hidden
Pass in "Attribute,Yellow" and all three fields should show with two records displayed

***Note that you used 'LIKE' as your comparator.
You may require some wildcards  -- *Africa* or *Yellow*
Private Sub Report_Open(Cancel As Integer)
Dim MyArgs() As String
Dim myDesiredField as string
Dim MyDesiredValue as string
Dim SQL as string

If Nz(Reports!rptMyReport.OpenArgs, "") <> "" Then
    MyArgs() = Split(Reports!rptMyReport.OpenArgs, ",")
    myDesiredField = Chr(34) & MyArgs(0) & Chr(34)
    MyDesiredValue = Chr(34) & MyArgs(1) & Chr(34)
end if

Select case true
    Case myDesiredField = "Location"
        SQL = "Select Distinct Animal, Location, "" as Attribute from tblMyTable where location like " & MyDesiredValue
    Case myDesiredField = "Attribute"
        SQL = "Select Distinct Animal, Location, Attribute from tblMyTable where Attribute like " & MyDesiredValue
    Case else
        Cancel = true
        Exit sub
End select

me.RecordSource = SQL

end sub

Open in new window

0
 

Author Comment

by:epuglise
Comment Utility
Ok Nick, let me give this a try today.  Note that I'm dealing entirely with forms, but I think I can figure out how to convert your code to form-ish from report-ish :)
0
 

Author Comment

by:epuglise
Comment Utility
ok I got it working using your case method and passing the entire recordsource via open args.  Thanks!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now