Ask Your Question

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. gravatar image

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

2 Answers

Sort by » oldest newest most voted

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

JPL gravatar image

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.


edit flag offensive delete link more

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

Steve R. gravatar image

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 more


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


exit sub

end sub

:) JPL

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

Hey!! Thanks,

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

Question Tools

1 follower


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

Seen: 1,634 times

Last updated: Sep 28 '14