Custom Programming Aid

[Guest]

Hello,

I write most of my code in public modules. All of my queries are transfered
into SQL strings and placed in the public module (often to private modules
also).

When using Access 97 (old days) I used "Autokeys" to hit, for example,
"{F5}" which would run a Macro that would fire "Sendkeys" to automatically
insert the SQL string syntax into the class module :

For Example:
Sendkeys "strsql = strsql & "
Sendkeys "{Down}
Sendkeys "strsql = strsql & "
Sendkeys "{Down}

This save me many many many keystrokes typing: strsql = strsql & "

Since Access 97 I have Access no longer, for some reason, allows you to fire
macros in a class module (Or Private) using Auotkeys. Since then, I have to
type this string manually and have nightmares about typing it over and over
again (strsql = strsql & ")!

Here is my question.

How can a fire macros using autokeys (or any other method) while developing
in a private or class module so that I can automatically insert this string?

PS : I have even tried using IBM via voice (voice recognition software) to
fire via voice macros with some sucess. However this introduced other
problems (ie If I coughed into the microphone, it would initiate viruses!)

Thank you in advance if you have an answer to this one.


Ross

 

[Marshall Barton]

I write most of my code in public modules. All of my queries are transfered
into SQL strings and placed in the public module (often to private modules
also).

When using Access 97 (old days) I used "Autokeys" to hit, for example,
"{F5}" which would run a Macro that would fire "Sendkeys" to automatically
insert the SQL string syntax into the class module :

For Example:
Sendkeys "strsql = strsql & "
Sendkeys "{Down}
Sendkeys "strsql = strsql & "
Sendkeys "{Down}

This save me many many many keystrokes typing: strsql = strsql & "

Since Access 97 I have Access no longer, for some reason, allows you to fire
macros in a class module (Or Private) using Auotkeys. Since then, I have to
type this string manually and have nightmares about typing it over and over
again (strsql = strsql & ")!

Here is my question.

How can a fire macros using autokeys (or any other method) while developing
in a private or class module so that I can automatically insert this string?

Sorry, I can't help you with your question because I do not
use that approach to long concatenations. I strongly prefer
to use the line continuation sequence of space, underscore.
For example

strSQL = "SELECT thisfield, thatfield, otherfield " _
& "FROM mytable " _
& "WHERE thisfield = 123 " _
& "And thatfield = ""abc"" "

 

[Guest]

Hi Marsh

My Question must not be clear:

Example: Here a SQL for a query

SELECT tbl_JE.JE_No, tbl_JE.Report_Order, tbl_JE.JE_Description
FROM tbl_JE_References;

I want to insert the variable Prefix syntax in frot of the sql to convert it
to a string.

strsql = "SELECT tbl_JE.JE_No, tbl_JE.Report_Order, tbl_JE.JE_Description "
strsql = strsql & "FROM tbl_JE_References;"

I don't want to type ( strsql = " ) and ( strsql = strsql & " ), I want to
automatically insert the Prefix using a macro (so I can reduce the
typing/keystrokes that I have to do).

Do this make sense?


Ross

 

[Douglas J Steele]

Marsh is saying that he would use something like the following instead, so
that you don't need "strsql = strsql & ":

strsql = "SELECT tbl_JE.JE_No, tbl_JE.Report_Order, " & _
"tbl_JE.JE_Description " & _
"FROM tbl_JE_References;"

 

[Guest]

Yes, That would save keystrokes but, I would still have to manually insert
that sytax for each line.

Thanks

Ross

 

[RD]

I see what you're saying. I actually don't mind (strsql = strsql & " ) myself.
When I get to the end of the first one I Shift+Home, Ctrl+C, drop to the next
line and paste it in.

You want to set up some kind of macro to insert that string for you.

An alternative would be to hold your SQL strings in a table. Then you could
write a short query to that table to return the longer SQL statement, assign it
to a variable and not worry about line continuation at all.

Check this out:
http://www.mvps.org/access/queries/qry0014.htm

This article deals with running several action queries in sequence but the basic
idea works well for organizing SQL statements if you use a lot of them in code.

Another alternative would be to download and install MZ Tools. MZ Tools allows
you to define code templates that are then available via a right click on the
mouse.

Hope this helps,
RD

 

[Guest]

RD,

That is it! A light when on. Perhaps I could Ctrl+C and enter paste into a
memo field pop up, use code to insert the strings, Cltrl+C the ending
product, and automatically close the pop - up.

I think I could do that!

Great Idea. Thank you RD!

 

[Marshall Barton]

Either way, Paste is your friend ;-)

 

[Tim Ferguson]

I strongly prefer
to use the line continuation sequence of space, underscore.

Perhaps the alternative is to do the thing programmatically:

dim s as new CSQLCommand

s.Clear ' prepare for a new command
s.Add "SELECT This, That, TheOther"
s.Add "FROM SomeWhere"
s.Add "WHERE AField = " & s.SQLString(txtField.Value)
s.Add " AND ADate = " & s.SQLDate(Now())

s.Debug ' could be debug.print or a MsgBox() for example

Set rst = db.OpenRecordset(s.SQL, dbOpenSnapshot, dbForwardOnly)


Of course, the CSQLCommand is left to the reader as an exercise.

Hope that helps


Tim F

 

[RD]

Whoa! Don't credit me! I'm not much for re-inventing things.

Let us know if your wheel rolls.

Regards,
RD

RD,

That is it! A light when on. Perhaps I could Ctrl+C and enter paste into a
memo field pop up, use code to insert the strings, Cltrl+C the ending
product, and automatically close the pop - up.

I think I could do that!

Great Idea. Thank you RD!