?
Solved

Set the record  source of a form from another form

Posted on 2011-03-09
8
Medium Priority
?
331 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
ID: 35087834


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

mx
0
 
LVL 75
ID: 35087850
OR ...

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

Forms!YourOtherFormName.Form.RecordSource = sSQL

mx
0
 

Author Comment

by:epuglise
ID: 35087906
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75
ID: 35087948
"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
 

Author Comment

by:epuglise
ID: 35088152
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 2000 total points
ID: 35089768
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
ID: 35098295
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
ID: 35113104
ok I got it working using your case method and passing the entire recordsource via open args.  Thanks!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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