Refer to SQL statements globally

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.


0
Kurt
5/26/2010 9:11:42 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
1280 Views

Similar Articles

[PageSpeed] 55

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@gmail.com> wrote in message 
news:df0ab2e2-c734-4a22-b3ae-9a6d93f6cfdc@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.
>
> 

0
Douglas
5/26/2010 9:17:53 PM
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@gmail.com> wrote in message 
news:df0ab2e2-c734-4a22-b3ae-9a6d93f6cfdc@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.
>
> 


0
david
5/27/2010 3:59:21 AM
Reply:

Similar Artilces:

multiple if statements #3
Hi all Thanks for help in the past I have in cell A1 either an "L" or an "S" If cell A1= "L" then I require the following formula (which refers to other cells) in cell A2 =IF(D9<E10,1,IF(D9<E11,2,IF(D9>E11,3))) but if cell A1="S" then I need a slightly different formula =IF(D9>E10,1,IF D9>E11,2,IF D9<E11,3))) in cell A2 Many thanks in advance Alex Allowing for D9=E10=E11, try =IF(A1="S",IF(D9<E10,1,IF(D9<E11,2,IF(D9>E11,3,"eq"))),IF(A1="L",IF(D9>E10,1,IF(D9>E11,2,IF(D9<E11,3,...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Ms sql server 2000 access
Hi. Does anyone know how to connect to MS SQL server 2000 using VC++ 2003? Thanks. ...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

exchange server &sql server
can setup exchange server and sql server on one computer? On Wed, 3 Mar 2004 05:05:52 -0800, "koshi" <anonymous@discussions.microsoft.com> wrote: >can setup exchange server and sql server on one computer? yes...of course you can ...

PowerShell Query Help, WMI, Select Statement
Here is where I have started: #Get Physical Memory function getwmiinfo ($svr) { gwmi -query "select * from Win32_PhysicalMemory" -computername $svr | select [$svr], DeviceLocator } $Servers = get-content -path "C:\test.txt" foreach($Servers in $Servers) { getwmiinfo $Servers } I get this: [risk] DeviceLocator ------ ------------- DIMM0 DIMM1 What I want is this: ServerName DeviceLocator ---------- ...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Can't Add Data on SQL Server 2005 Table in Access 2002 FE
Greetings: I have an application in which I recently upsized the tables (only) to SQL Server 2005. I can edit most of the tables directly, or on the application's forms. Two of the child tables are locked, though. The subforms in which their data appears are locked, as are the tables themselves. I did some research and found that tables without primary keys can't be edited, so i added an identity field to one of the tables to test it. I relinked in Access to refresh the table definition. The new column shows in datasheet view and it contains the values I expected. I still can't a...

Reference
Is it possible to create a reference to another object such as: int & x = i; but using an image list, and referencing different objects depending on a condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben You're better off using pointers for this. I believe, depending on the class, that what you're trying to will make a copy of the object instead of referencing it. "Ben Williamson" <oakdaleclose@googlemail.com> wrote in message news:43ee4603...

SQL Help with changing ILC
My client has 644 Matrix items that are listed with ILC APE######## and AP#######. She wants to change the ILC's for both items to be API########. Can someone please help with a SQL statement - or lead me in the right direction? Any help will be appreciated. ...

Macro to Reference Column Next to Current Reference
Hi, I'd like to automate the following procedure using a macro. Please help. 1) i have 2 sheets: Sheet1 & Sheet2 2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2! F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly. 3) Every month when i do my work, i'd have to manually rekey the formulas to reference the subsequent columns, ie, Cell B2,D2,F2 will change from above to "=Sheet2! G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following month, I have to change manually again to "=Sheet2! H2", "=Sheet2!M2&qu...

Sheet Reference
Is there a formula for listing the current Sheet. I know about the filename cell("filename",A1) which returns the entire path, but I just want the Sheet name to appear. John, You still use the CELL("filename") function, but you need to trim the result a bit. Like this: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99) -- Ture Magnusson Microsoft MVP - Excel Karlstad, Sweden "John" <anonymous@discussions.microsoft.com> wrote in message news:0c6001c3a856$291c9a20$a501280a@phx.gbl... > Is there a formula for listing...

R1C1 reference #2
Excel keeps changing to the R1C1 reference style, but only in one of my workbooks (I believe in just one of the worksheets). I keep changing it back (Tools - Options - General - R1C1 Reference Style), but when I move or copy cells in the workbook, it changes back to R1C1. Anyone have an idea? Look at the TOOLS / OPTIONS / SETTINGS is the 'R1C1 References Style' box checked? If not, check it then save the workbook. Not sure if this is your issue but it's worth a try. Good Luck, -- Gary Brown gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com "Tony S" wrote: > Ex...

External db in sql FROM
I need help with some sql. What is the proper syntax for the following? I'm in one database, but trying to get data from an external another one. FROM tblProducts IN C:\2009Cat\ABC.mdb INNER JOIN tblSales IN C:\2009Cat\ABC.mdb ON tblProducts.ProdID = tblSales.ProdID The way I've shown it doesn't work. thank you Becky On Sat, 13 Feb 2010 09:21:01 -0800, Becky <Becky@discussions.microsoft.com> wrote: Much simpler to first link those tables. -Tom. Microsoft Access MVP >I need help with some sql. What is the proper syntax...

How do I Shorten Object References?
ThisWorkbook.Sheets("Vessel") I'm always referring to this Sheet and Sheet "Operations" I've seen code where people shorten the objects... How exactly do I do that? Dim ws as worksheet set ws = ThisWorkbook.Sheets("Vessel") ws.Range("A1").value = "Shorten" "Benjamin" wrote: > ThisWorkbook.Sheets("Vessel") > I'm always referring to this Sheet and Sheet "Operations" > I've seen code where people shorten the objects... > How exactly do I do that? Benjamin, Here's ...

reference to circular reference bug
I just discovered something that looks like a new excel bug: to reproduce behaviour: 1. open a new workbook; 2. activate circular references; 3. enter those formulas: B4 <- formula is =B5 B5 <- formula is =1+SE(B6;0;1) B6 <- formala is =(B5=2) B7 <- formula is =B5 (same as B4!!!) (NB: SE() this is the standard IIF() function in the italian version of excel;) What's wrong: B4 and B7, will display different results even if they contain the same formula. The result displayed depends on the position of the cell conataining the formula: if it is on the left or above B5 the result...

SmartList Not Using Global Masking
In GP 10, we created a global phone mask using Modifier. Everything seems to be working as designed except for the SmartLists. It appears that the default Smartlists (e.g. Customer Address) are using some sort of defaut masking/formatting on the phone fields instead of our modified format. Does anyone know how to get SmartList to recognize our mask? Thanks You need to change the resource string in modifier See KB 921632, "How to change the telephone format for SmartList" Specifically Step 2. I change the format to twenty X's to cover the full length of the field availabl...

Getting the cell reference
How can I automatically/ continously get the cell reference to the last cell in a particular column, that contains text? Any help would be appreciated! *Thanks. :) * -- DuncanG ------------------------------------------------------------------------ DuncanG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16398 View this thread: http://www.excelforum.com/showthread.php?threadid=277691 Hi see for various methods depending on your data structure and performance requirements: http://www.xldynamic.com/source/xld.LastValue.html "DuncanG" wrote: >...

Using SQL Query Code in Form Module to Return Record Count
Hi Team, I am trying to call the following SQL Query from within the Code to return the count of records that meet a criteria where a field value in the Table would be equal to the Value of a Control that is within a Subform: Dim rst As Recordset Dim SQL As String SQL = "SELECT Count(Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskResponsiblePersonRecordID) AS COUNTER " & _ "FROM Tbl_Risk_LocalProfiles_Subform INNER JOIN Tbl_Risk_LocalProfiles_ResponsiblePersons ON Tbl_Risk_LocalProfiles_Subform.ProfileUpdate_ID = Tbl_Risk_LocalProfiles_ResponsibleP...

References
Hello All I have a split Access2K mdb, with a backend on a server and a frontend on each of 5 PCs. I routinely work on a 'master' version of the frontend, which also lives on the server and which is copied to each PC when it is booted up in the morning. The frontend includes a 3rd party add-in application, which requires some .ini files to be in place on the local workstation, and some references to be set. Everything works fine, except that some of the PCs don't seem to retain these references: immediately after each boot-up these references are either unticked, o...

Global address book changes
We need to update info on most of our users in our Global address book. Is there any way to do this without actually being on the Exchange Server? Is there a remote client of some sort that can do this? Or any way that we could make these changes other computers (at remote facilities perhaps)? thanks, Mike If you install the Windows 2000 or Windows 2003 admin pack then install the Exchange Mgmt Tools on your workstation, you can administer user accounts and mailboxes from your workstation. "Mike" <anonymous@discussions.microsoft.com> wrote in message news:2975301c4...