Solved

How open form using query rather than table

Posted on 2014-01-21
8
334 Views
Last Modified: 2014-01-22
I have a form which has it's record source as a table.

But I want to be able to open the form using a query sometimes instead.  Can this be done?

I'm trying to do it with:

DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, acWindowNormal

But don't know how to make it use the query.

Or do I need to use open arguments?  There is two criteria:

ID = [Forms]![frmSelectNameAndDate]![cboName]
and
DateWorked = [Forms]![frmSelectNameAndDate]![txtDate]

--Steve
0
Comment
Question by:SteveL13
  • 3
  • 3
  • 2
8 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39797906
set the recordsource to the query instead of the table. add params to the query if necessary
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39797913
Assuming that the query has the same fields as the table, you can use the Open event to change the forms RecordSource property:

me.RecordSource = "qryYourQueryName"
0
 

Author Comment

by:SteveL13
ID: 39797922
But sometimes I want the record source to be the table and other times the query.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39797966
populate a global variable and set the form recordsource on load event.
e.g.
on load
 if var then me.recordsource = query else me.recordsource = table.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39797978
Then consider using the OpenArgs argument of the OpenForm method to pass the form what you want to use as the recordsource if not the table.  Something like:

DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, acWindowNormal, "qryYourQuery"

Then use the Open event of the form to change the recordsouce appropriately:

Private sub Form_Open

     If IsNull(me.OpenArgs) = False then me.RecordSource = me.OpenArgs

End Sub
0
 

Author Comment

by:SteveL13
ID: 39797990
So this goes in the onopen event of the form:

If IsNull(me.OpenArgs) = False then me.RecordSource = me.OpenArgs

But where does this go?

DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, acWindowNormal, "qryYourQuery"
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39797996
"But where does this go?"

That is how you open the form, usually from the click event of a command button on another form, or something like that.
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 500 total points
ID: 39798001
in calling form
populate a global variable
DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, acWindowNormal


in loading form
e.g.
on load
 if var then me.recordsource = query else me.recordsource = table.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

831 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