Goto record in subform

[weavind]

Hi,

I have a main form and a subform which both have show records from the same stocklist table. The main form has a summary of one record while the subform is unlinked and shows a list view of all records. On the main form i have an unbound control box that a stock number can be typed into and the corresponding stock number record is displayed on the main form. I however would like the subform to jump or highlight the corresponding stock number record in the list. For example, if i want to go to stock number 130, the main form will show the details of this stock number and the subform will show the 20 records before this and the 20 records after this stock number.

Set rst = db.OpenRecordset("tblStockList")          
Me!subfrmStockInfo.SetFocus
Me!subfrmStockInfo!txtSRN.SetFocus
rst.FindFirst "SRN = " & Form_frmStockInfo.cboSRN
'MsgBox "SRN: " & rst!SRN 'This is just to confirm the right number is found
DoCmd.GoToRecord acDataForm, "SRN", acGoTo, rst.AbsolutePosition + 1

The above code does find the right stock number and it does find the absolute position. It however gives the error "The object 'SRN' isn't open.
I've tried many references to the field SRN but i just can't get it to jump to this record.

 

[arnelgp]

Set rs= me.subfrmStockInfo.form.recordset
Me!subfrmStockInfo!txtSRN.SetFocus
rst.FindFirst "SRN = " & Form_frmStockInfo.cboSRN
'MsgBox "SRN: " & rst!SRN 'This is just to confirm the right number
Set rs=nothing

 

[weavind]

When i change the recordset to

Set rs= me.subfrmStockInfo.form.recordset

, then the subform just changes to #name when i enter a stocknumber. However if i put the

DoCmd.GoToRecord acDataForm, "SRN", acGoTo, rst.AbsolutePosition + 1

line in, then i do notice that the subform now jumps to the correct record. I however do still get the error "Runtime 2489 - The object "SRN" isn't open.

 

[arnelgp]

Sorry, should be:

Set rst=...

And not


Set rs=...

 

[JHB]

.. I however do still get the error "Runtime 2489 - The object "SRN" isn't open.

Do you've a form with the name "SRN"?
Below is a link to a explanation of DoCmd.GoToRecord.
https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-gotorecord-method-access

 

[weavind]

Just wanted to say thanks. Got it working now.

Below code works 100%

Set rst = Me.subfrmStockInfo.Form.Recordset
Me!subfrmStockInfo.SetFocus
Me!subfrmStockInfo.Form!txtSRN.SetFocus
rst.FindFirst "SRN = " & Form_frmStockInfo.cboSRN
Set rst = Nothing