Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-21
8
Medium Priority
?
308 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 1000 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 1000 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
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 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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