Solved: Changing Tables with VBA

[MWE]

I have a VBA routine that sequences through the records of an Access table. Thus far I have used it to pull information that was too complicated for normal queries (at least for me).

I now want to modify records under certain circumstances. I get an error whenever I try to do that. I assume that the dataset has been opened as "read-only"; I am using
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TargetTable")

How do I open this dataset for "edit"? I have poked around VBA help, but my version of Help does not have any info on OpenrecordSet. OpenrecordSet is a valid method, but there is just no help available on it.

Thanks

[mdmackillop]

A bit beyond me, but here's an extract from my VB6 help file which might be of use.

[TonyJollans]

Off the top of my head - and it's been a while since I used Access - position the recordset at the record you want to edit and do ...

rst.Edit
' make your changes to individual fields, then
rst.Update

[austenr]

See if this is what you are after. This comes from the Access 2000 black book.
dim rstLocal as ADODB.Recordset

rst.Local.Fields("your field here") = _
rst.Local.Fields("your field here")
rst.Local.Update

[MWE]

Off the top of my head - and it's been a while since I used Access - position the recordset at the record you want to edit and do ...

rst.Edit
' make your changes to individual fields, then
rst.Update
Thanks

Off the top or your head is pretty ... that worked fine.

[geekgirlau]

Generally speaking you would use something like the following:



Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("TargetTable",dbOpenDynaset)

With rst
.Edit
!MyField = "Whatever"
.Update
End With

rst.close
db.close
Set rst = Nothing
Set db = Nothing

[austenr]

Anyone know of a good source for understanding ACCESS VBA and its syntax? Examples, etc. Thanks

[Imdabaum]

I have a form that keeps track of notes for particular projects. The notes are to be kept individually, but displayed in a text field together. To do this I combined all the note attributes into one field with a query. Now I am trying to get all the records that match a projectID and put them into a field called BigMemo. The BigMemo will be displayed in the textbox. This is the code that I used to cycle through.


Private Function BuildMemoField(ByVal ProjectID As String) As String
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT PropertyID, PropertyNumber, NoteDate & ': ' & entryType & '; ' & RER & ' - ' & Memo AS BigMemo " & _
"From tblPropertiesUpdates " & _
"WHERE (((tblPropertiesUpdates.PropertyID) = ProjectID])) " & _
"ORDER BY NoteDate DESC;"

MsgBox strSQL, vbCritical, "SQL String" 'tested to make sure sql statmnt was correct
Set qdf = dbs.CreateQueryDef("qryUpdateMemo", strSQL) 'creates qry each time form is loaded
Dim strMemo As String
Dim temp As Recordset
Set temp = qdf.OpenRecordset ' this is where my error occurs.
'cycles down through records while propertyID is the same
Do Until temp.EOF
strMemo = strMemo & temp.Fields("BigMemo").Value & vbNewLine & vbNewLine
temp.MoveNext
Loop

MsgBox Len(strMemo), vbInformation, "Length of Memo"
BuildMemoField = strMemo
dbs.QueryDefs.Delete "qryUpdateMemo"
Set qdf = Nothing
Set dbs = Nothing
Set temp = Nothing
End Function


I figured it out, the Me![ProjectID] was throwing it off. By passing the value in it automatically updates the query.