Referring to range in access

[ped]

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

[pbaldy]

Any number of ways. In what situation are you trying to do it?

[ped]

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

[pbaldy]

In code? You'd open a recordset on an SQL statement that got that record only:

SELECT * FROM TableName WHERE IDField = 27

[ped]

In code? You'd open a recordset on an SQL statement that got that record only:

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.

[pbaldy]

What does your existing looping code look like? I usually use DAO, which might look like:

  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

[ped]

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

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

[pbaldy]

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.

[ped]

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?

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.

[pbaldy]

You can execute SQL

INSERT INTO TargetTable(Field1, Field2...)
SELECT Field1, Field2...
FROM SourceTable
WHERE IDField = 3

[ped]

Thanks again ...very very much!!! i've learn alot from this small interaction

Good day!

Thanks again, pbaldy

[pbaldy]

No problemo, have a great day!