Solved

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

Posted on 2015-01-21
8
292 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

789 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