Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2007
    Posts
    26

    Unanswered: Recordset from a SQL statement

    This is a ADO connection and the strsql works until I get to set rs.
    What code do I need to create the rs (recordset)
    Ado connection…….
    Dim strsql As String
    Dim rs As Recordset

    strsql = "SELECT pro_propertyID,"
    strsql = strsql & "pro_custid, "
    strsql = strsql & "pro_Address, "
    strsql = strsql & "pro_town, "
    strsql = strsql & "pro_city, "
    strsql = strsql & "pro_postcode, "
    strsql = strsql & "pro_state, "
    strsql = strsql & "pro_Memo "
    strsql = strsql & "FROM property "
    strsql = strsql & "WHERE (((pro_propertyID)= getpropertyID()));"
    objConn.Execute (strsql)

    I need a recordset to populate the form fields

    rs.Open (strsql) ???? what code would I use to create a recordset

    but I get “The error is run-time error ‘91’: Object variable or with block variable not set”


    Me![proAddress] = rs.Fields("pro_Address")
    Me![protown] = rs.Fields("pro_Town")
    Me![procity] = rs.pro_city


    Casey

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Difficult to know everything that might be wrong since you have omitted some code but....

    You can execute the string using the connection execute method, which returns a recordset - you set your rs variable to the return of this method.

    Or you can create a New recordset (Dim rs as New ADODB.Recordset), assign the conenction as the active connection and then use you code as is.

    This is all from memory - I'm not 100% certain on some of my terminology above.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think you have to open the recordset on the open connection
    Code:
    rs.Open (strsql, objConn)
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2007
    Posts
    26
    pootle flump

    would you explain a little more on you set your rs variable to the return of this method
    what would the code look like

    and

    Dim rs as New ADODB.Recordset
    again what code would I use after the strsql statement

    Casey

  5. #5
    Join Date
    Dec 2007
    Posts
    26
    Georgev thanks for your reply

    objConn.Execute (strsql)

    rs.Open (strsql, objConn)
    on typing the code it then showed compile error expected =

    and a syntax error message

    Casey

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tell you what - let's stick with one technique

    We'll go for the second.

    1) Make sure your connection is Open (we can't tell from your code if it is).
    2) Spot on with tne New declaration
    3) Use Geroge's code
    4) I've just noticed this line:
    Code:
     objConn.Execute (strsql)
    This is the method I mentioned for option one. It would be:
    Code:
     Set rs = objConn.Execute (strsql)
    But we'll ignore that cause we are going for option two. As such, comment out the objConn.Execute (strsql) line. See how you get on. Any probs, copy and paste what ya got back here

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Simultaneous post - your problem is covered in my reply.

  8. #8
    Join Date
    Dec 2007
    Posts
    26
    pootle flump thanks for your help

    I tried option one "Set rs = objConn.Execute (strsql)" and it worked
    it proves that the strSQL statement and the connection work ok

    I have commented out the “objConn.Execute (strsql)” now and need the code for option 2

    Even though option one works I’m really interested to know what code you would use for option 2
    ADO connection...................
    strsql = "SELECT pro_propertyID,"
    strsql = strsql & "pro_custid, "
    strsql = strsql & "pro_Address, "
    strsql = strsql & "pro_town, "
    strsql = strsql & "pro_city, "
    strsql = strsql & "pro_postcode, "
    strsql = strsql & "pro_state, "
    strsql = strsql & "pro_Memo "
    strsql = strsql & "FROM property "
    strsql = strsql & "WHERE (((pro_propertyID)= getpropertyID()));"

    ‘objConn.Execute (strsql) ‘Original code commented out
    ‘Set rs = objConn.Execute(strsql) ‘ option one code commented out

    OPTION 2 CODE NEEDED

    Me![PropertyID] = GetPropertyID()
    Me![customerid].Value = GetCustomerID()
    Me![proAddress].Value = rs.Fields("pro_Address")
    Me![protown].Value = rs.Fields("pro_Town")
    Me![procity].Value = rs.Fields("pro_city")
    Me![prostate] = rs.Fields("pro_postcode")
    Me![propostcode] = rs.Fields("pro_state")
    Me![proMemo] = rs.Fields("pro_memo")

    Casey

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok.

    Code:
     ADO connection...................
    'Make sure ADO connection is open
    
    Dim rs As New ADODB.Recordset 'NOTE - "New" keyword.
    
    'Blah blah blah doing the SQL statement
    
    rs.Open (strsql, objConn)'George's code copied and pasted
    There are even more methods. I would recommend:
    • You get the value of getpropertyID() into a variable and hard code it into your string
    • Read up on ADO in Access VBA help - the ADO section is excellent.
    HTH

  10. #10
    Join Date
    Dec 2007
    Posts
    26
    Georgev code works but had to take away the brackets

    rs.Open (strsql, objConn) does not work
    rs.Open strsql, objConn works great

    Thanks heaps Georgev and pootle flump. I feel I have made a significant change for how to program from now on using ADO

    Thanks again

    Casey

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You are right Casey. The rule (that I forgot myself) is that you use brackets for functions not statements.... unless you call a statement using the Call keyword.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •