Results 1 to 12 of 12
  1. #1
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51

    Referring to range in access


    Hi, i have question on how to refer to range in access?
    Suppose i want to edit ID no 107 instead of looping how can i go and directly refer to that record and edit/delete....

    Thanks everyone

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Any number of ways. In what situation are you trying to do it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    Quote Originally Posted by pbaldy View Post
    Any number of ways. In what situation are you trying to do it?



    i want to refer record id 27 directly without looping...and edit it

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    In code? You'd open a recordset on an SQL statement that got that record only:

    SELECT * FROM TableName WHERE IDField = 27
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    Quote Originally Posted by pbaldy View Post
    In code? You'd open a recordset on an SQL statement that got that record only:
    Quote Originally Posted by pbaldy View Post

    SELECT * FROM TableName WHERE IDField = 27


    pbaldy, thanks for quick reply...
    Can you please show me in more detail ['m begginer in access]
    I want to know how to open connection >> open req table >> edit then close back connection...

    Thanks again.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    What does your existing looping code look like? I usually use DAO, which might look like:

    Code:
      Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
      Set db = CurrentDb()
      
      strSQL = "SELECT * FROM Tablename WHERE IDField = " & Me.IDField
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
      'do your thing, perhaps testing for EOF to make sure a record was returned
    
      set rs = nothing
      set db = nothing
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    Wow that's really cool...i tried this way and works perfect!!!...
    So this is directly opening the location i want to edit? right?


    If i want this kinda code with out loop to select and copy data into different table, how would the code look like in a very simple way?



    Thanks again very much



    Code:
    Option Compare Database
    Option Explicit
    Sub FGHJJHDFJSH()
    Dim strSQL  As String
     Dim db      As DAO.Database
     Dim rs      As DAO.Recordset
     Set db = CurrentDb()
     
     strSQL = "SELECT * FROM Emps WHERE ID = " & 3 'Me.IDField
     Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
     
     Debug.Print rs.Fields("ID")
     
     rs.Edit
      rs!Names = "NAmeCh"
     rs.Update
     'do your thing, perhaps testing for EOF to make sure a record was returned
     Set rs = Nothing
     Set db = Nothing
     
    End Sub

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Your code would be getting the record with 3 in the ID field, and only that record. More commonly you'd get the ID value from a form or someplace rather than hard-coding it.

    To add a record to another table, you'd open a second recordset on that table, and use the AddNew method instead of the Edit method.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    thanks again pbaldy, so there is no way to move the data in bulk meeting certain critiria instead of looping till EOF and checking several critiria? and then moving it?


    Quote Originally Posted by pbaldy View Post
    Your code would be getting the record with 3 in the ID field, and only that record. More commonly you'd get the ID value from a form or someplace rather than hard-coding it.
    Quote Originally Posted by pbaldy View Post

    To add a record to another table, you'd open a second recordset on that table, and use the AddNew method instead of the Edit method.


  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    You can execute SQL

    INSERT INTO TargetTable(Field1, Field2...)
    SELECT Field1, Field2...
    FROM SourceTable
    WHERE IDField = 3
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    Thanks again ...very very much!!! i've learn alot from this small interaction

    Good day!

    Thanks again, pbaldy

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    No problemo, have a great day!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Referring to another table for a text box
    By hawkins in forum Reports
    Replies: 5
    Last Post: 07-05-2011, 04:14 PM
  2. Replies: 2
    Last Post: 11-26-2010, 04:20 AM
  3. Referring to objects
    By stubbly in forum Programming
    Replies: 1
    Last Post: 10-14-2009, 09:36 AM
  4. Simple Question about Referring to tables
    By KIDRoach in forum Access
    Replies: 4
    Last Post: 06-19-2009, 07:34 AM
  5. Referring to fields in tab controls
    By AndrewAfresh in forum Forms
    Replies: 1
    Last Post: 06-03-2006, 05:10 PM

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