Select record using VBA in datasheet view

[Guest]

I have a subform on a main form. The subform is in datasheet view.

Can someone please post generic example VBA code that:

1) Selects the entire currently active record;

2) How to select another record based on the contents of a field in the
subform;

Can these things even be done?

Thanks much in advance.

 

[Marshall Barton]

I have a subform on a main form. The subform is in datasheet view.

Can someone please post generic example VBA code that:

1) Selects the entire currently active record;

2) How to select another record based on the contents of a field in the
subform;

To select the entire current record, use:
DoCmd.RunCommand acCmdSelectRecord

What are you going to do once the record is selected?

To navigate to the record idendified in another control, use
the Find (ADO) or FindFirst (DAO) methods. E.g. if
[somefield] is a numeric type of field in the from's record
source table/query:

Me.Recordset.FindFirst "somefield=" _
& me.subform.Form.somecontrol

 

[Guest]

Hi Marshall,

I'm sorry, I'm having a brain blockage today; regarding FIND:

How do I open a recordset on my subform (ADO)?
Then, how do I move the record selector to that record in the subform?

I'm retrieving a value in a variable (lLink) from a field called
LINKED_RECORD like so:

lLink = Nz(Me.sfrmApprovalMain.Form![LINKED_RECORD].Value, 0)
IF lLink = 0 Then Exit Sub

I then need to move the record selector in the subform to the record where
RCD_ID = lLink

How can I do that?

I have a subform on a main form. The subform is in datasheet view.

Can someone please post generic example VBA code that:

1) Selects the entire currently active record;

2) How to select another record based on the contents of a field in the
subform;

To select the entire current record, use:
DoCmd.RunCommand acCmdSelectRecord

What are you going to do once the record is selected?

To navigate to the record idendified in another control, use
the Find (ADO) or FindFirst (DAO) methods. E.g. if
[somefield] is a numeric type of field in the from's record
source table/query:

Me.Recordset.FindFirst "somefield=" _
& me.subform.Form.somecontrol

 

[Marshall Barton]

I'm sorry, I'm having a brain blockage today; regarding FIND:

How do I open a recordset on my subform (ADO)?
Then, how do I move the record selector to that record in the subform?

I'm retrieving a value in a variable (lLink) from a field called
LINKED_RECORD like so:

lLink = Nz(Me.sfrmApprovalMain.Form![LINKED_RECORD].Value, 0)
IF lLink = 0 Then Exit Sub

I then need to move the record selector in the subform to the record where
RCD_ID = lLink

How can I do that?

You don't open a recordset on a form. Bound forms have a
recordset consisting of the records in their record source
table/query. The form's recordset is either an ADO or DAO
recordset, depending on how you have set up your database.
If you don't know what you've done in this regard, most
likely you are using DAO.

You don't move the record selector, you probably mean to
just navigate to the specified record. If you are
displaying the record selector, it will indicate which
record is current.

I think you want code like:

With Me.sfrmApprovalMain.Form
If IsNull(.LINKED_RECORD) Then Exit Sub
.Recordset.FindFirst "RCD_ID=" & .LINKED_RECORD
End With

If you get an invalid Method compile error, try using Find
instead of FindFirst.

 

[Guest]

Thanks a lot it works perfectly...

Now when the user wants to see a related record, the record selector will
advance to that record and select the entire row; nice.

Thanks again.

I'm sorry, I'm having a brain blockage today; regarding FIND:

How do I open a recordset on my subform (ADO)?
Then, how do I move the record selector to that record in the subform?

I'm retrieving a value in a variable (lLink) from a field called
LINKED_RECORD like so:

lLink = Nz(Me.sfrmApprovalMain.Form![LINKED_RECORD].Value, 0)
IF lLink = 0 Then Exit Sub

I then need to move the record selector in the subform to the record where
RCD_ID = lLink

How can I do that?

You don't open a recordset on a form. Bound forms have a
recordset consisting of the records in their record source
table/query. The form's recordset is either an ADO or DAO
recordset, depending on how you have set up your database.
If you don't know what you've done in this regard, most
likely you are using DAO.

You don't move the record selector, you probably mean to
just navigate to the specified record. If you are
displaying the record selector, it will indicate which
record is current.

I think you want code like:

With Me.sfrmApprovalMain.Form
If IsNull(.LINKED_RECORD) Then Exit Sub
.Recordset.FindFirst "RCD_ID=" & .LINKED_RECORD
End With

If you get an invalid Method compile error, try using Find
instead of FindFirst.