• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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?
0
SteveL13
Asked:
SteveL13
  • 3
  • 3
  • 2
2 Solutions
 
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
 
Jeffrey CoachmanCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jeffrey CoachmanCommented:
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 CoachmanCommented:
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now