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
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
Any number of ways. In what situation are you trying to do it?
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.
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
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
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.
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?
You can execute SQL
INSERT INTO TargetTable(Field1, Field2...)
SELECT Field1, Field2...
FROM SourceTable
WHERE IDField = 3
Thanks again ...very very much!!! i've learn alot from this small interaction
Good day!
Thanks again, pbaldy
No problemo, have a great day!