Results 1 to 5 of 5
  1. #1
    rivereridanus is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    71

    open recordset with variable SQL

    Hello!

    I am trying to write a code to allow a user (learning SQL) to type in a simple SELECT FROM WHERE etc... statement into a textbox txtrunsql, and upon on_click event open that recordset.

    Private Sub cmdrunsql_Click()
    Dim dbsMenuStudy As DAO.Database


    Dim rstadhoc As DAO.Recordset
    Dim strSQL As String

    Set dbsMenuStudy = Currentdb
    strSQL = Me.txtrunsql
    Set rstadhoc = dbsMenuStudy.OpenRecordset(strSQL)
    End Sub


    When I click the button, nothing happens.
    Any suggestions would be wonderful!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi
    What do you expext to happen?
    You've created the recordset but you're not doing anything with it, as far as I can see.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by rivereridanus View Post
    Hello!

    I am trying to write a code to allow a user (learning SQL) to type in a simple SELECT FROM WHERE etc... statement into a textbox txtrunsql, and upon on_click event open that recordset.

    Private Sub cmdrunsql_Click()
    Dim dbsMenuStudy As DAO.Database
    Dim rstadhoc As DAO.Recordset
    Dim strSQL As String

    Set dbsMenuStudy = Currentdb
    strSQL = Me.txtrunsql
    Set rstadhoc = dbsMenuStudy.OpenRecordset(strSQL)
    End Sub


    When I click the button, nothing happens.
    Any suggestions would be wonderful!
    The Access Web has an example mdb for a search form where you can select the fields you want to have searched. The page is:

    http://access.mvps.org/access/forms/frm0045.htm

    (FindRecord2k.Zip)

  4. #4
    rivereridanus is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2011
    Posts
    71
    Quote Originally Posted by Bob Fitz View Post
    Hi
    What do you expext to happen?
    You've created the recordset but you're not doing anything with it, as far as I can see.
    I am hoping to open it for viewing (not editing) like I might a query. I thought
    Openrecordset would do that, but I guess not?

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi
    I am hoping to open it for viewing (not editing) like I might a query. I thought
    Openrecordset would do that, but I guess not?
    I'm afraid not, but you can use a report or a form to view the data with using the SQL string you created as the "Record Source". I you create a form and display it in "datasheet" view it will look very much like a query. You could set it's properties to stop the data being edited.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-20-2013, 10:12 AM
  2. ADO Recordset.Open (SQL) does nothing
    By workindan in forum Programming
    Replies: 3
    Last Post: 06-23-2010, 02:07 PM
  3. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 PM
  4. Replies: 3
    Last Post: 02-16-2010, 10:43 PM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums