docmd.findrecord error 2162

[RAG2007]

Hi

Having some problems with docmd.findrecord, getting runtime error 2162, in an adp, sql server back end.

On my main form, I have a continuous view subform giving a list of subrecords within the main record. Double clicking on the subrecord changes the subform to a form that gives more detailed information on that specific subrecord. I've been trying to use docmd.findrecord to pull up the info, but I'm getting nowhere.
Here's my code:

 
iID = Me.ID.Value
Forms!frmInitiative.CommView.SourceObject = "frmComm"
Set ctl = Forms!frmInitiative!CommView.Form!ID
DoCmd.GoToControl ctl.Name
DoCmd.FindRecord iID, acEntire, , acSearchAll, , acCurrent

The error is coming in on the docmd.findrecord part.
I also tried DoCmd.GoToRecord acActiveDataObject, , acGoTo, iID
To no avail.

Any suggestions out there?

[Minion]

You may have more luck using a filter instead of trying to goto a specific record. Unfortunately, I'm not entirely sure what event is launching your code or from which form. Still the following should work for you in narrowing the records to show just that one record. I'm assuming the field name that contains the ID is called ID.

dim iID 
 
iID = me.ID.Value
'If the recordset is attached to the form that launches the code then the following will work, otherwise change it to point at the other form.
me.Filter = "ID = '" & iID & "'"
me.FilterOn = True
 

To go back to being able to see the entire recordset just add code to the reverse event setting FilterOn to False.

Hope this helps.

- Minion -

Hi

Having some problems with docmd.findrecord, getting runtime error 2162, in an adp, sql server back end.

On my main form, I have a continuous view subform giving a list of subrecords within the main record. Double clicking on the subrecord changes the subform to a form that gives more detailed information on that specific subrecord. I've been trying to use docmd.findrecord to pull up the info, but I'm getting nowhere.
Here's my code:

 
iID = Me.ID.Value
Forms!frmInitiative.CommView.SourceObject = "frmComm"
Set ctl = Forms!frmInitiative!CommView.Form!ID
DoCmd.GoToControl ctl.Name
DoCmd.FindRecord iID, acEntire, , acSearchAll, , acCurrent

The error is coming in on the docmd.findrecord part.
I also tried DoCmd.GoToRecord acActiveDataObject, , acGoTo, iID
To no avail.

Any suggestions out there?

[jaxjagfan]

Hi

Having some problems with docmd.findrecord, getting runtime error 2162, in an adp, sql server back end.

On my main form, I have a continuous view subform giving a list of subrecords within the main record. Double clicking on the subrecord changes the subform to a form that gives more detailed information on that specific subrecord. I've been trying to use docmd.findrecord to pull up the info, but I'm getting nowhere.
Here's my code:

 
iID = Me.ID.Value
Forms!frmInitiative.CommView.SourceObject = "frmComm"
Set ctl = Forms!frmInitiative!CommView.Form!ID
DoCmd.GoToControl ctl.Name
DoCmd.FindRecord iID, acEntire, , acSearchAll, , acCurrent

The error is coming in on the docmd.findrecord part.
I also tried DoCmd.GoToRecord acActiveDataObject, , acGoTo, iID
To no avail.

Any suggestions out there?

If I am reading this correctly you want a form to open up that will display the record details when you doubleclick the subform

Private Sub Form_DblClick(Cancel As Integer)
Dim strCrit as String
strCrit = "[ID] = " & Me.ID
Docmd.OpenForm "frmInitiative",,,strCrit
End Sub
 

This will open a form in normal view based on the current record at the time of your doubleclick (assuming the name of the field or control is "ID" in "frmInitiative") and display just the record(s) with the same ID as the one you Doubleclicked on.

[RAG2007]

Minion got it right, I'm changing the subform sourceobject to a different form. The main form stays the same.

I had tried .Filter and couldn't get it to work for the subform. Here's what I've done:
Forms!frmInitiative.CommView.Forms.Filter = "ID = " & iID & ""

(CommView is the subform name; iID is an integer, so I left out the single quotes). I get:
Error 438: Object doesn't support property or method.

Alternatively, I could change the recordsource of the subform, but, again, I can't get that to work using this:
Forms!frmInitiative.CommView.Forms.RecordSource = "SELECT...

Am I missing something here?

[RAG2007]

OK, I found a workaround. On my main form I added an unbound field to hold my ID, so when you select the first record in the subform it's ID get's dumped in there. When the new subfrom opens, it has no recordset, but in the Tag I put a stored procedure. In the On Load event, I call the procedure using the ID from the unbound field on the main form, and then on my subform set me.recordsource to me.tag.

Docmd.findrecord has always been very sensitive for me, seeming to work only when it wants to.