Set the record  source of a form from another form

Posted on 2011-03-09
Medium Priority
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?

Question by:epuglise
  • 4
  • 3
LVL 75
ID: 35087834

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

LVL 75
ID: 35087850
OR ...

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

Forms!YourOtherFormName.Form.RecordSource = sSQL


Author Comment

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. :/
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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>


Author Comment

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... :)
LVL 26

Accepted Solution

Nick67 earned 2000 total points
ID: 35089768

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
    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


Author Comment

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 :)

Author Comment

ID: 35113104
ok I got it working using your case method and passing the entire recordsource via open args.  Thanks!

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

624 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