Refer to SQL statements globally
in [Forms]
|
Prev: Append Query to Mainform and Subform
Next: we hoping dat you kan give up 100$ to roshankaiburns@hotmail.com
From: Kurt Heisler on 26 May 2010 17:11 I have two large SQL statements that I need to use across several forms, reports, etc. Rather than repeat them in each form, I'm trying to store and reference them globally. For example, I'd like to store strSQL1 and strSQL2 in a global module, so I can use either like this in a form: Random form: Me!lstResults.RowSource = strSQL1 Random report: Me.Report.RecordSource = strSQL2 Would I just store the SQL statements in a global module like: Public Function SQLSource() As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "SELECT blah blah" strSQL2 = "SELECT blah blah" End Function If so, how would I refer to it elsewhere. Like: Me.Report.RecordSource = strSQL2 'need to call the function first (SQLSource), and then pick the correct SQL Thanks.
From: Douglas J. Steele on 26 May 2010 17:17 Why not store them in a table, and look them up using DLookup when you need them? If that's not sufficient, no, what you're proposing won't work. What you can try is create a new module (not a class module or a module associated with a form or report) and put the following in it: Public Const strSQL1 As String = "SELECT blah blah" Public Const strSQL2 As String = "SELECT blah blah" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/djsteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "Kurt Heisler" <heislerkurt(a)gmail.com> wrote in message news:df0ab2e2-c734-4a22-b3ae-9a6d93f6cfdc(a)q36g2000prg.googlegroups.com... >I have two large SQL statements that I need to use across several > forms, reports, etc. > > Rather than repeat them in each form, I'm trying to store and > reference them globally. > > For example, I'd like to store strSQL1 and strSQL2 in a global module, > so I can use either like this in a form: > > Random form: > > Me!lstResults.RowSource = strSQL1 > > Random report: > > Me.Report.RecordSource = strSQL2 > > Would I just store the SQL statements in a global module like: > > Public Function SQLSource() As String > > Dim strSQL1 As String > Dim strSQL2 As String > > strSQL1 = "SELECT blah blah" > strSQL2 = "SELECT blah blah" > > End Function > > If so, how would I refer to it elsewhere. Like: > > Me.Report.RecordSource = strSQL2 'need to call the function first > (SQLSource), and then pick the correct SQL > > Thanks. > >
From: david on 26 May 2010 23:59
Store the SQL as a query. That makes it easy to test, find, develop and use. For example, store SQL1 as query1, then use: Me!lstResuts.RowSource = "Query1" Sometimes you wish to modify the sql stored in a query. You can get it like this: strSQL1 = codedb.querydefs("Query1").SQL (david) "Kurt Heisler" <heislerkurt(a)gmail.com> wrote in message news:df0ab2e2-c734-4a22-b3ae-9a6d93f6cfdc(a)q36g2000prg.googlegroups.com... >I have two large SQL statements that I need to use across several > forms, reports, etc. > > Rather than repeat them in each form, I'm trying to store and > reference them globally. > > For example, I'd like to store strSQL1 and strSQL2 in a global module, > so I can use either like this in a form: > > Random form: > > Me!lstResults.RowSource = strSQL1 > > Random report: > > Me.Report.RecordSource = strSQL2 > > Would I just store the SQL statements in a global module like: > > Public Function SQLSource() As String > > Dim strSQL1 As String > Dim strSQL2 As String > > strSQL1 = "SELECT blah blah" > strSQL2 = "SELECT blah blah" > > End Function > > If so, how would I refer to it elsewhere. Like: > > Me.Report.RecordSource = strSQL2 'need to call the function first > (SQLSource), and then pick the correct SQL > > Thanks. > > |