public string

  • Thread starter Thread starter Bill H.
  • Start date Start date
B

Bill H.

why does this not work:

Option Compare Database
Option Explicit
Public strSQLrecs As String
strSQLrecs = "SELECT [Qry_Union_FamilyMemberAll].[PriKey] " '(etc)

What happens when I click on any command button, I get an error msg. If I
turn the strSQLrecs assignment into a comment, all is fine again.


What I what to do is to have a public string (longish) that on my various
command buttons I can concatenate to another string and then run the
resultant sql string.
 
Bill H. said:
why does this not work:

Option Compare Database
Option Explicit
Public strSQLrecs As String
strSQLrecs = "SELECT [Qry_Union_FamilyMemberAll].[PriKey] " '(etc)

What happens when I click on any command button, I get an error msg.
If I turn the strSQLrecs assignment into a comment, all is fine again.

What I what to do is to have a public string (longish) that on my
various command buttons I can concatenate to another string and then
run the resultant sql string.
Click to expand...

Hi Bill,

I believe you will have to put the "strSQLrecs =" init code somewhere
where it will execute. Maybe in the open or onLoad event of the form.

By the way, if this part of the string remains static you might change
it to CONST. The CONST statement would work right where you have the
init code.

HTH
 
As far as I know, you cannot assign a value to the string variable outside of a
sub or variable.

One way to handle this might be to write a simple wraparound sub that you call
to set the string.

Public Sub SetStrSqlRecs()
strSQLRecs = "SELECT ..."
End Sub

Or you might try makding strSQLRecs a constant and assigning a value to the
constant. T

Public Const strSQLrecs As String = "SELECT xxx FROM TableName WHERE" & " Still
more info " & _
"Still more stuff"
 
HMM. It worked for me. I did test my example code.

Did you paste from the newsgroup reader? Sometimes that causes problems. For
instance my example wrapped onto a new line when it should have all been on one line.

Option Compare Database
Option Explicit
Public Const strSQLRecs as String = "SELECT xxx FROM XXX" & _
" WHERE SomeField = SomeValue" & _
" SomeDateField is Not Null"

Note that you can continue a line using Space Ampersand space underscore.

HOWEVER, you cannot do that within the quotes so you have to close the string
phrase with a quote do the line continuation and then start the next string
phrase with a quote.
 
that does not work. Access turns the code RED.
Click to expand...

Where? It's legal as long as
a) it's in a plain Module (not a form module or a class)

and

b) the "other stuff" does not include any calls that are illegal in a Const
definition (i.e. practically all of them).

Off topic a little bit, but why isn't this just a compiled querydef?

B Wishes


Tim F
 
heck, I even copied the sample text from ms Help, and that also didn't work.

I just put it at the top of a form module, in the General section, after the

Option Compare Database
Option Explicit

lines.

I only want it to apply to to that form.

Guess I need to put it somewhere else.

Not a querydef? Well, it's code that I want to put into the rowsource of a
combo box on the form.

-Bill
 
I just put it at the top of a form module, in the General section,
Click to expand...

Read the instructions. Public Consts are only valid in normal Modules.

Defining a Const in a Class Module (i.e. including Form modules) outside
a procedure will create a value that is visible everywhere in that
module, i.e. all code contained in the form module.
Guess I need to put it somewhere else.


Not a querydef? Well, it's code that I want to put into the rowsource
of a combo box on the form.
Click to expand...

So what? Querydefs are ideal for rowsources (Access creates a hidden and
anonymous querydef whenever you apply a SQL command to one in any case).


HTH


Tim F
 
Back
Top