Open a form with a different query applied

[kattatonic1]

Have frmPositions working great. Its Record Source is qryPositions, working great.



I have created a parameter query, qryPositionsFindPos, that prompts for a Positon#, and produces the right record. I would like to apply this query to frmPositions now. Scratching head. I copied the form and made the duplicate's record source qryPositionsFindPos and it works great. Having duplicate forms is not a great idea. I would rather apply the second query whenever I want to the one form.

Tried Macro to open query from button and it opens the query itself, doesn't apply the query to the form.

Tried Docmd.OpenQuery and it returns an error message.

I have not coded for eons. Using very little code, some macros. Simple database.

Any help appreciated, particularly because once I have it working in the Positions world of my db, I'd like to apply the technique to more than one form/record source actually.

Kay from Toronto

[ItsMe]

In your form's load event you could have some code that would run or not, depending on an Open Args value

You can adjust your form's recordset with VBA. This example uses open args and to determine the form's recordsource. You can pass open args in a Docmd.OpenForm statement.


untested...
if Me.OpenArgs <> "" then
Me.RecordSource= "SELECT * FROM qryPositions WHERE [SomeField] = " Me.OpenArgs
else
Me.RecordSource= "SELECT * FROM qryPositions"
end IF

The WHERE clause in the SQL can replace your parameterized query.

[June7]

Another option:

DoCmd.OpenForm "formname", , , "[SomeField]=" & Me.somecontrolname

Also eliminates the dynamic parameter (I never use them) in query.

[ItsMe]

...

DoCmd.OpenForm "formname", , , "[SomeField]=" & Me.somecontrolname

.

June,

Have you noticed that using Docmd and the Where Criteria applies the criteria to the form's "filter"? I have used this Docmd with the Where criteria for ever and then noticed while working in 2010 that the form's filter was applied. The Where criteria did not get applied to the form's RecordSource. Just wondering if my previous data bases are getting the filter applied when I thought it was not.

[June7]

Yes, it sets the form filter property. Just don't let the form get saved with the setting.

[ItsMe]

Just don't let the form get saved with the setting.

I was just thinking about that...

[ipisors]

I was just thinking about that...

Just another 2 cents, I never use the form's filter property and not really ever the Where either....Just dynamically set the recordsource either by using a vba/sql statement or by using a querydef whose .sql property gets manipulated. I agree, messing with the filter gets thorny.

Plus I'm using listboxes and comboboxes to dynamically restrict the form's recordsource (i.e., Select * from tbl_something where ID=" & me.Listbox, etc) anyway, that filters would soon be lost and irrelevant, or worse yet, saved into design like you mentioned.

It's kind of the 'balance' in between the two theories of those who have grown to loathe bound forms, vs. those who use nothing but bound forms, in between because I like bound forms but as long as I'm continually able to change the 'binding'.

[ItsMe]

Plus I'm using listboxes and comboboxes to dynamically restrict the form's recordsource (i.e., Select * from tbl_something where ID=" & me.Listbox, etc) anyway, that filters would soon be lost and irrelevant, or worse yet, saved into design like you mentioned.

This is typically my approach too. The only thing is, I have been unwittingly setting the filter property. For what ever reason, it did not become apparent to me until after I starting using 2010. Now I need to go back over production DB's and reinforce constraints. I am of the mindset that the filter tool is for public interface. Now I just need to make sure I am practicing what I preach.

[June7]

I've never had issue with using the WHERE CONDITION argument for forms and reports. I have only 1 case where I change the RecordSource property of a report.

[John_G]

Hi all -

Yes, the "where" on opening a form will set the "Filter" property, and if you go in to form design view you will see it.

But, unless you have VBA code or a macro that also sets the FilterOn property to True (this property is not in the property sheet), or runs DoCmd.Applyfilter, the filter will not be applied when the form is opened.

You can also turn the filter on or off while the form is open.

John

[kattatonic1]

Just so you know what I ended up using. I created a macro with just the OpenForm command. In the Filter condition I put the name of the parameter query. It works beautifully. I know some of you seasoned developers are not huge fans of macros but this one needs no other info or commands in the macro, and I can apply it in many places.

For those who suggest other things: all techniques appreciated! I am learning so much. I end up applying the ideas somewhere sometime. Where I have to, I am stumbling through the code to make it work and getting better at it, making notes, etc.

Many thanks!

Kay
Toronto
Access 2010