Open form either with table as record source or as query as record source

I have a form with two command buttons.  One of them is set to open a form via:  (opens the form for a new record)

DoCmd.OpenForm "frmContacts", acNormal, , , acFormAdd, acWindowNormal

But I also want to be able to open the same form with a query as it's record source.  When the form opens it will display all of the records that meet the query criteria.  

How can I do the 2nd option?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
DoCmd.OpenForm "frmContacts", acNormal, , , acFormAdd, acWindowNormal
Forms!formName.Form.RecordSource = yourqueryname
Forms!formName.Form.Requery


This should be somewhere near it.


Kelvin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Then I would just filter the form, ..skipping the query altogether:
examples.:
DoCmd.OpenForm "frmContacts", acNormal, , "YourID=" & Me.txtYourID
Or
DoCmd.OpenForm "frmContacts", acNormal, , "CustomerID=" & Me.txtCustID
Or:
DoCmd.OpenForm "YourForm", acNormal, , "Yourdate=" & "#" & Me.txtDate & "#"

You can make the "Where Criteria" argument as complicated and you need...
Unless, building a complex Where Condition", ...is really not worth the trouble, ...and the query already exists...
In which case, ...I understand fully.
;-)
Then wait for an expert to post a solution to your Q as posted...
Just another option to consider.

Finally,
If you use a query (IMHO) it is too much work swapping recordsources.
Also, if the query is inadvertently deleted (or modified) you may lose the ability to reliably run this query.

With Filtering, only a minimal amount of additional code is needed.
;-)

Jeff
0
SteveL13Author Commented:
The query is already written.  And solution #1 works but I do not want the form when opened with the query as it's record source to allow for new records to be entered.  How can I prevent that?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jeffrey CoachmanMIS LiasonCommented:
I am sure Kelvin can assist
0
SteveL13Author Commented:
I got it by accident.

Forms!frmContacts.Form.AllowAdditions = False

Thank you to both of you.  Kelvin for the solution that worked because the query was already written.  Jeffrey for you excellent insight as to how I should do it in the future.
0
Kelvin SparksCommented:
That's what EE is about. Lots of ideas and different ways to achieve things. No real right or wrong answer, but mostly solutions you can choose from.


Kelvin
0
Jeffrey CoachmanMIS LiasonCommented:
SteveL13,
As you know...
There are always alternate methods...

What's funny here is that, as I was re-reading your question, I realized how much trouble a "complex" query would be to recreate as a Where criteria.
Believe me, ...sometimes it is a lot simpler to just make a query than it is to screw around with all the crazy single and double quotes, and the SQL syntax.
;-)

You could have just selected Kelvins post, as he answered you post directly.

Jeff
0
Kelvin SparksCommented:
No worries Jeff, we all added to his knowledge.

Happy to share the points, I'm not part of any "points race".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.