How to Use a Variable in an SQL String in the Base Form's Data Tab

[SteveR]

I have a Base form where the “content type = sql command” under the data tab. The code below uses the global variable intStoryNUM. However, it does not work.
Can a variable be used? If so, how would the code below get corrected.
(Note: Base is the front-end, MySQL is the back-end.)

SELECT “tblLinkedList”.“LinkStoryNUM”,
“tblLinkedList”.“LinkAuthorNUM”,
“tblAuthorList”.“AuthorIDNUM”,
“tblAuthorList”.“AuthorLast” FROM
“TEST_sfmags”.“tblLinkedList”
“tblLinkedList”,
“TEST_sfmags”.“tblAuthorList”
“tblAuthorList” WHERE
“tblLinkedList”.“LinkAuthorNUM” =
“tblAuthorList”.“AuthorIDNUM” AND
“tblLinkedList”.“LinkStoryNUM” = ‘" +
intStoryNUM + "’

[JPLED]

Using a [Basic] Global variable in the SQL statement stored in a Base form is indeed impossible.

The only mean I see is by modifying the original SQL statement of the form by a macro, typically by filtering the data when the form gets opened.
As an example, if you use the Access2Base macro library, there exists in the API the OpenForm action which has a Filter argument.

JPL

[SteveR]

That is what I (grumble) suspected. My workaround, usings Access2Base is shown below.

Sub OpenAddAuthorForm
Openform strAddAuthorForm, , , “”“StoryIDNUM”"= ‘" + intStoryNUM + "’", acFormReadOnly ,

Forms(strAddAuthorForm).Height = 600

Forms(strAddAuthorForm).Width = 1000
Forms(strAddAuthorForm).Controls(“fmtStoryIDNUM”).value= intStoryNUM
Forms(strAddAuthorForm).Controls(“AddTextBox01”).value=strStoryName
exit sub

end sub

[JPLED]

100% equivalent but, maybe, a bit more elegant would be:

Sub OpenAddAuthorForm

Dim oF As Object

Set oF = Openform strAddAuthorForm, , , “”“StoryIDNUM”"= ‘" + intStoryNUM + "’", acFormReadOnly ,

oF.Height = 600

oF.Width = 1000

oF.Controls(“fmtStoryIDNUM”).value= intStoryNUM

oF.Controls(“AddTextBox01”).value=strStoryName

exit sub

end sub

JPL

[SteveR]

Hey!! Thanks,