Returning VB Executed SQL String to a Form

[Louis]

I'm running the following code but it keeps failing because Select
isn't a part of the action query method:

Private Sub Command2_Click()
Dim SQLStr As String

SQLStr = "SELECT Request.RequestID, Request.ReqName, Request.ReqDesc
FROM Request"
DoCmd.RunSQL SQLStr
'Debug.Print SQLStr

End Sub

How does one execute a SQL select statement through VB that's linked
to a button and return the results to a form (that the button reside
on)? I'm assuming that this is a varily simple procudure but i'm
really stumped! Thanks in advance.
Louis

[Peter Doering]

On 7 Nov 2003 10:57:31 -0800, Louis wrote:

SQLStr = "SELECT Request.RequestID, Request.ReqName, Request.ReqDesc
FROM Request"
DoCmd.RunSQL SQLStr
'Debug.Print SQLStr

DoCmd.RunSQL is used for action queries, starting with UPDATE, DELETE etc.
You need to assign the SQL statement to the form's record source:

another form:
Forms!YourFormName.RecordSource = SQLStr

your current form:
Me.RecordSource = SQLStr

HTH - Peter

--
No mails please.

[Louis]

I knew if i posted a short question it wouldn't work. My fault.
Here's what i'm trying to do.

I have multiple drop down filters and based on the combination of the
drop down I want to pass that information to a parameter query and
when <<Find>> button is pressed the filtered Select record sets return
to the form. Due to the number of fields and the complexity of the
drop down selection the query is well over 255 characters long so I
cannot enter the SQL statment to the RecordSource field in the form's
data property page.

So, how does one execute a parameter select query via VB and return
the result to the form that's passing the parameter? Is it possible?
Thanks!

Peter Doering <no****@doering.org> wrote in message news:<bo*************@ID-204768.news.uni-berlin.de>...

On 7 Nov 2003 10:57:31 -0800, Louis wrote:

SQLStr = "SELECT Request.RequestID, Request.ReqName, Request.ReqDesc
FROM Request"
DoCmd.RunSQL SQLStr
'Debug.Print SQLStr

DoCmd.RunSQL is used for action queries, starting with UPDATE, DELETE etc.
You need to assign the SQL statement to the form's record source:

another form:
Forms!YourFormName.RecordSource = SQLStr

your current form:
Me.RecordSource = SQLStr

HTH - Peter

[Rick Brandt]

"Louis" <lo*******@lycos.com> wrote in message
news:13**************************@posting.google.c om...

I knew if i posted a short question it wouldn't work. My fault.
Here's what i'm trying to do.

I have multiple drop down filters and based on the combination of the
drop down I want to pass that information to a parameter query and
when <<Find>> button is pressed the filtered Select record sets return
to the form. Due to the number of fields and the complexity of the
drop down selection the query is well over 255 characters long so I
cannot enter the SQL statment to the RecordSource field in the form's
data property page.

So, how does one execute a parameter select query via VB and return
the result to the form that's passing the parameter? Is it possible?
Thanks!

I don't know the answer to your question, but I "think" that it is possible
in Access 2000 and higher to do this using ADO. However; I did want to
point out that the RecordSource property of a form does not have a 255
character limit so you should be able to just assign the SQL string to that
property.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Peter Doering]

On 7 Nov 2003 13:53:37 -0800, Louis wrote:

I knew if i posted a short question it wouldn't work. My fault.
Here's what i'm trying to do.

I have multiple drop down filters and based on the combination of the
drop down I want to pass that information to a parameter query and
when <<Find>> button is pressed the filtered Select record sets return
to the form. Due to the number of fields and the complexity of the
drop down selection the query is well over 255 characters long so I
cannot enter the SQL statment to the RecordSource field in the form's
data property page. ...

As Rick has pointed out already:

Max RecordSource/RowSource lengths on ...
A97: 2,048 char's
A2002: 32,750 char's

In case this is not sufficient you can always use a stored query, e.g. your
example:

"SELECT Request.RequestID, Request.ReqName, Request.ReqDesc FROM Request;"

and add the criteria like ...

Me.RecordSource = ""
CONDstr = "[MyField1]='" & Me!fldCond1 & "' " & _
" AND [MyField2]='" & Me!fldCond2 '... and so on

SQLstr = CurrentDb.QueryDefs("YourQuery").SQL
SQLstr = Left(SQLstr, Len(SQLstr) - 3) 'cut-off ; and CrLf
SQLstr = SQLstr & " WHERE " & CONDstr & 2;"

CurrentDb.QueryDefs("YourQuery").SQL = SQLstr
Me.RecordSource = "YourQuery"

(not tested)

HTH - Peter

--
No mails please.

[Shawn_09]

I was just having a similar problem:
Run Time error reported that string exceeded max length for recordsource property.

I was building the string while assigning it to recordsource:
Me.RecordSource = sqlSELECTString & sqlWHEREstring

Fixed by changing it to two statements and assiging a single string to recordsource.

RSstring = sqlSELECTString & sqlWHEREstring
Me.RecordSource = RSstring

Hope this helps others passing this way.
Cheers,
S:)