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

Create a dynamic SQL/Query for a sub form

Does anyone know if there is a way to use a SQL statement for a sub form.  Or some way to use a recordset or querydef (that is not a created query object)?  Because you can use objects like queries or tables as your sourceobject for your sub form, I was hoping that I would could in some way use a SQL statement (that is created dynamically).

Any ideas anyone?  Any help would be appreciated.  Thanks.
0
NickPereza
Asked:
NickPereza
  • 2
1 Solution
 
mcallarseCommented:
Can create and/or edit QueryDefs on the fly using VB(A):

Dim db
Dim qd As New QueryDef

Set db = CurrentDb

strSQL = "select * from myTable"

With qd
    .Name = "myTempQuery"
    .SQL = strSQL
End With
db.QueryDefs.Append qd

' Following to illustrate Edit only.

db.QueryDefs("myTempQuery").SQL = strSQL

Set qd = Nothing: Set db = Nothing

' Set SubForm SourceObject

mySubForm.SourceObject = "Query.myTempQuery"

--
0
 
mcallarseCommented:
NOTE: If you will be using a temporary query, would suggest creating a query on main form open, using that query for all changes while the form is open, then deleting the query -- db.querydefs.delete "myTempQuery" -- on main form close. If you have multiple users accessing your database, would suggest this approach, as opposed to a single query used for all updates.
0
 
nexusnationCommented:
Hi NickPereza,
This question has been abandoned and needs to be finalized (112 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
 
1WilliamCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to mcallarse
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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