Solved

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

Posted on 2015-01-21
8
301 Views
Last Modified: 2015-01-21
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?
0
Comment
Question by:SteveL13
[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
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 250 total points
ID: 40562999
DoCmd.OpenForm "frmContacts", acNormal, , , acFormAdd, acWindowNormal
Forms!formName.Form.RecordSource = yourqueryname
Forms!formName.Form.Requery


This should be somewhere near it.


Kelvin
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 40563028
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
 

Author Comment

by:SteveL13
ID: 40563044
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40563113
I am sure Kelvin can assist
0
 

Author Closing Comment

by:SteveL13
ID: 40563139
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40563153
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40563372
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40563472
No worries Jeff, we all added to his knowledge.

Happy to share the points, I'm not part of any "points race".
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

726 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