How to Address Rows of a Datasheet in a Subform?

[David Salahi]

I have a subform containing a datasheet and I need to be able to modify
values in some columns of the datasheet based on entries the user makes to
other columns within the datasheet.

I figured out that I can access each column's control through the
datasheet's Controls collection property. But the values contained in each
control correspond to the first row shown in the datasheet. How can I access
any given row of the subform datasheet (for both read and write)?

David Salahi

 

[Graham Mandeno]

Hi David

Modifying the value in a control will change not the *first* row, but the
*current* row.

You can move the current row to a specific record using one of the following
methods:

1. If your RecordSource contains a primary (or other unique) key:

Dim f as form
Set f = Me![YourSubformControlName].Form
With f.RecordsetClone
.FindFirst "[KeyField]=" & KeyValue
If .NoMatch Then
MsgBox "Record not found"
Else
f.Bookmark = .Bookmark
End If
End With

If [KeyField] is a text field the value should be enclosed appropriately
with quotes. For example:
.FindFirst "[KeyField]='" & KeyValue & "'"

2. If you want to move to a specific RowNumber (starting at 1):

Dim f as form
Set f = Me![YourSubformControlName].Form
With f.RecordsetClone
.MoveFirst
If RowNumber > 1 Then .Move RowNumber-1
If .EOF Then
MsgBox "Record not found"
Else
f.Bookmark = .Bookmark
End If
End With

Note that the operation that actually moves the current record is the line
that synchronises the bookmarks.

It is possible to change the values of a fields in the RecordsetClone
without moving the current record by editing the record directly:

With f.RecordsetClone
.MoveFirst
If RowNumber > 1 Then .Move RowNumber-1
If .EOF Then
MsgBox "Record not found"
Else
.Edit
![SomeField] = SomeValue
![SomeOtherField] = SomeOtherValue
.Update
End If
End With

 

[David Salahi]

Graham,
Thanks a ton! That answered my question.
Dave