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

asked 2014-09-26 20:58:44 +0100

Steve R.
84 ●10 ●14 ●19

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 + "'

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 11:47:27.689210

add a comment

0

answered 2014-09-27 12:10:42 +0100

JPL
288 ●2 ●3

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

edit flag offensive delete link

add a comment

0

answered 2014-09-28 00:34:52 +0100

Steve R.
84 ●10 ●14 ●19

updated 2014-09-28 00:50:53 +0100

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

edit flag offensive delete link

Comments

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

JPL ( 2014-09-29 13:12:39 +0100 )edit

Hey!! Thanks,

Steve R. ( 2014-10-20 14:51:48 +0100 )edit

add a comment

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

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 11:47:27.689210

2 Answers

Comments

Question Tools

Stats

Related questions

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

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-03-04 11:47:27.689210

2 Answers

Comments

Question Tools

Stats

Related questions

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

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 11:47:27.689210