[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Set the record  source of a form from another form

Posted on 2011-03-09
8
Medium Priority
?
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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