Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2009
    Posts
    185

    Question Unanswered: Help reading a single record control file

    I have one record control file table. I store various values that I want to read in Form Load event of a form. Can you direct me to some sample code for Access.

    Basically
    Open"controlfile"
    Get record 1
    read fields that I want.
    Close file.

    I have some code to add records to a file that I am trying to convert but am not getting far.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you just want to get one value, use DLookup. If several, open a recordset on the table.
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    DFirst is better.

    ="My setting for MyField is " & DFirst("MyField","MyTable")
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by StarTrekker
    DFirst is better.
    Your reasoning? There is no speed difference in my testing.
    Paul

  5. #5
    Join Date
    Jul 2009
    Posts
    185
    dlookup worked great!!!
    To bad there is not a dUpdate function
    I guess I could write my own function or just use the following I think it about right. But it gives me a Expected end of statement error.But it is 4:40am so time for bed.

    strSQL="Update Control set [sUsername]="bob" where acid=1"
    DoCmd.SetWarnings False
    DoCmd****nSQL strSQL
    DoCmd.SetWarnings True

  6. #6
    Join Date
    Jul 2009
    Posts
    185
    docmd****nSQL "Update Control set [sUsername]='Bob' where acid=1"
    I think this works Now I go to bed happy.

  7. #7
    Join Date
    Jul 2009
    Posts
    185
    Now I need to do a recordset to read multiple fields but it looks a lot more complicated. Any suggests for sample code.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by pbaldy
    Your reasoning? There is no speed difference in my testing.
    No criteria needed Just seems more logical to me when you know there is and always will be only one record in the table.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Code:
    Dim rs as RecordSet
    set rs = CurrentDB.OpenRecordset ("SELECT * FROM TABLE;")
    If rs.RecordCount > 0 Then
       rs.MoveFirst
       Do
          MsgBox rs!Field1
          MsgBox rs!Field2
          MsgBox rs!Field3
          rs.MoveNext
       Loop Until rs.EOF
    End If
    You wouldn't need the loop or the movenext if you have only one record though.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Searching on recordset should have turned up numerous examples

    Code:
      Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
      Set db = CurrentDb()
      
      strSQL = "SELECT * FROM TableName"
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
      Whatever = rs!FieldName
    
      set rs = nothing
      set db = nothing
    ps: DLookup doesn't require a criteria.
    Paul

  11. #11
    Join Date
    Jul 2009
    Posts
    185
    tried the following but got errors
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    strSQL = "SELECT * FROM Control"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    Debug.Print rs!sUsername
    Debug.Print rs!City

    Set rs = Nothing
    Set db = Nothing

    So I tried the following out of a Access 2007 vb programming book
    Dim strSQL As String
    Dim cnn1 As adodb.Connection
    Set cnn1 = CurrentProject.Connection
    Dim rs As New adodb.Recordset
    myrs.ActiveConnection = cnn1 ' I get a object required error here

    strSQL = "SELECT * FROM Control"
    myrs.Open (strSQL)

    Debug.Print rs!sUsername
    Debug.Print rs!City

    Set rs = Nothing
    Set db = Nothing

    I am at a lost of what to try next.

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What were the errors with the DAO method, and on what line? Has the DAO reference been checked in Tools/References?
    Paul

  13. #13
    Join Date
    Jul 2009
    Posts
    185

    Question

    Sorry I miss my in myrs in a couple places.
    I am interest why your code did not work. I might try it again to get the error messages again.

    Dim strSQL As String
    Dim cnn1 As adodb.Connection
    Set cnn1 = CurrentProject.Connection
    Dim myrs As New adodb.Recordset
    myrs.ActiveConnection = cnn1

    strSQL = "SELECT * FROM Control"
    myrs.Open (strSQL)

    Debug.Print myrs!sUsername
    Debug.Print myrs!sCity

    Set rs = Nothing
    Set db = Nothing

  14. #14
    Join Date
    Jul 2009
    Posts
    185
    I get "User-defined type not defined"
    dim db as DAO.Database

  15. #15
    Join Date
    Jul 2009
    Posts
    185
    Quote Originally Posted by pbaldy
    What were the errors with the DAO method, and on what line? Has the DAO reference been checked in Tools/References?
    It is now all so easy when you know what you are doing. I am learniing.
    Is that something I will have to set on each machine I run my db on or is it tried to my RcLog mdb file?

    Thanks so ever so much for your help. Being able to read a table under my control is very important to me though I am finding it is needed less in Access than Clarion a progran generator I am very used to. I am starting to warm up to Access.

Posting Permissions

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