Set the record source of a form from another form

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
epugliseAsked:
Who is Participating?
 
Nick67Connect With a Mentor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:


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

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OR ...

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

Forms!YourOtherFormName.Form.RecordSource = sSQL

mx
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
epugliseAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
 
epugliseAuthor Commented:
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
 
epugliseAuthor Commented:
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
 
epugliseAuthor Commented:
ok I got it working using your case method and passing the entire recordsource via open args.  Thanks!
0
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.

All Courses

From novice to tech pro — start learning today.