Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: simple table access question

    I am new at using sql and am trying to open a table to get the data
    I have seen the examples on how to do this but can't quite make it work in real life.
    I want to open a table (tblnotice) and take the field "notice" contents and make that a string. What do I need to make the code work?
    Dim StrSQL As String
    Dim Dbs As Database, RST As Recordset
    Set Dbs = CurrentDb
    Dim STRnotice As String
    StrSQL = "SELECT notice from tblnotice"
    Set RST = Dbs.OpenRecordset(StrSQL)
    strnotice = ?

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    I want to open a table (tblnotice) and take the field "notice" contents and make that a string. What do I need to make the code work?
    Dim StrSQL As String
    Dim Dbs As Database, RST As Recordset
    Set Dbs = CurrentDb
    Dim STRnotice As String
    StrSQL = "SELECT notice from tblnotice"
    Set RST = Dbs.OpenRecordset(StrSQL)
    strnotice = ?
    Code:
    ' You are pretty close
    ' The record set can return several records
    ' I've used a Loop to walk through the records and print the output
    ' Here's an example
    
    Sub TestRST()
    
    Dim StrSQL As String
    Dim Dbs As Database, RST As Recordset
    Set Dbs = CurrentDb
    Dim STRnotice As String
    StrSQL = "SELECT notice from tblnotice"
    Set RST = Dbs.OpenRecordset(StrSQL)
    
    ' Make sure the recordset has records
    If Not RST.RecordCount = 0 Then
    ' Do this to initialize the recordset sometimes not all records are read in until you call the last
        RST.MoveLast
        RST.MoveFirst
    ' Loop until the End of the Recordset
    Do While Not RST.EOF
    ' Use the index number of the field or the name RST.Fields("notice")
        STRnotice = RST.Fields(0)
        ' Print string to the debug window
        Debug.Print STRnotice
        RST.MoveNext
    Loop
        
    ' lets move to the first record and do it another way
    RST.MoveFirst
    Do While Not RST.EOF
        ' Can also use RST.notice see diff between (!)bang and (.)
        STRnotice = RST![notice]
        ' Print string to the debug window
        Debug.Print STRnotice
        RST.MoveNext
    Loop
    End If
    
    ' Good to close the recordset when were done.
    RST.Close
    
    End Sub
    ~

    Bill

  3. #3
    Join Date
    May 2004
    Posts
    159

    Thanks

    Thanks that gave me the insight I needed on how to do this!

Posting Permissions

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