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.Request ID, 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]

Re: Returning VB Executed SQL String to a Form

On 7 Nov 2003 10:57:31 -0800, Louis wrote:
[color=blue]
> SQLStr = "SELECT Request.Request ID, Request.ReqName , Request.ReqDesc
> FROM Request"
> DoCmd.RunSQL SQLStr
> 'Debug.Print SQLStr[/color]

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!YourFormN ame.RecordSourc e = SQLStr

your current form:
Me.RecordSource = SQLStr

HTH - Peter

--
No mails please.

[Louis]

Re: Returning VB Executed SQL String to a Form

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 <nospam@doering .org> wrote in message news:<bogqhb$1e c2bq$1@ID-204768.news.uni-berlin.de>...[color=blue]
> On 7 Nov 2003 10:57:31 -0800, Louis wrote:
>[color=green]
> > SQLStr = "SELECT Request.Request ID, Request.ReqName , Request.ReqDesc
> > FROM Request"
> > DoCmd.RunSQL SQLStr
> > 'Debug.Print SQLStr[/color]
>
> 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!YourFormN ame.RecordSourc e = SQLStr
>
> your current form:
> Me.RecordSource = SQLStr
>
> HTH - Peter[/color]

[Rick Brandt]

Re: Returning VB Executed SQL String to a Form

"Louis" <louishong@lyco s.com> wrote in message
news:13f35229.0 311071353.4f0b4 413@posting.goo gle.com...[color=blue]
> 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![/color]

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]

Re: Returning VB Executed SQL String to a Form

On 7 Nov 2003 13:53:37 -0800, Louis wrote:
[color=blue]
> 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. ...[/color]

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.Request ID, 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.Query Defs("YourQuery ").SQL
SQLstr = Left(SQLstr, Len(SQLstr) - 3) 'cut-off ; and CrLf
SQLstr = SQLstr & " WHERE " & CONDstr & 2;"

CurrentDb.Query Defs("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:)