RecordsetClone On Subform

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

I have a form with a listbox that I would like the sub form to move to the
selected record with a click event for the list.

I have not been able to make it work.

It goes to the first record in the link field of the recordset not to the
exact record.

Here is my code:

Private Sub lstContacts_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim lngCACLID As Long

Me.fsubContact_Details.SourceObject = "fsubContact_Details"
Me.fsubContact_Details.Visible = True

lngCACLID = "[CACLID] = " & Me.lstContacts.Column(0)
Me.fsubContact_Details.Form.Requery
Me.fsubContact_Details.Form.RecordsetClone.FindFirst lngCACLID
Me.fsubContact_Details.Form.Bookmark =
Me.fsubContact_Details.Form.RecordsetClone.Bookmark

ErrorHandlerExit:
Exit Sub

ErrorHandler:
Err.Clear
Resume ErrorHandlerExit
End Sub

What is wrong with the code?
How can I make this work?

-Bryan
 
See corrections embedded.
Private Sub lstContacts_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Click to expand...

Dim strCACLID As String
Dim Rst As Recordset
Me.fsubContact_Details.SourceObject = "fsubContact_Details"
Me.fsubContact_Details.Visible = True
Click to expand...

strCACLID = "[CACLID] = " & Format(Me.lstContacts.Column(0))
Me.fsubContact_Details.Form.Requery
Click to expand...

Set Rst = Me.fsubContact_Details.Form.RecordsetClone
Rst.FindFirst strCACLID
If Rst.NoMatch Then
MsgBox "Not found"
Else
Me.fsubContact_Details.Form.Bookmark = Rst.Bookmark
End If
Rst.Close
ErrorHandlerExit:
Exit Sub
Click to expand...
ErrorHandler:
Err.Clear
Resume ErrorHandlerExit
End Sub
Click to expand...

(not tested)

HTH - Peter
 
Thank you, Peter!

It works exactly taht I wanted it to!

One question, what is the purpose of using Format?


Thanks again.
-Bryan


Peter Doering said:
See corrections embedded.
Private Sub lstContacts_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
Click to expand...

Dim strCACLID As String
Dim Rst As Recordset
Me.fsubContact_Details.SourceObject = "fsubContact_Details"
Me.fsubContact_Details.Visible = True
Click to expand...

strCACLID = "[CACLID] = " & Format(Me.lstContacts.Column(0))
Me.fsubContact_Details.Form.Requery
Click to expand...

Set Rst = Me.fsubContact_Details.Form.RecordsetClone
Rst.FindFirst strCACLID
If Rst.NoMatch Then
MsgBox "Not found"
Else
Me.fsubContact_Details.Form.Bookmark = Rst.Bookmark
End If
Rst.Close
ErrorHandlerExit:
Exit Sub
Click to expand...
ErrorHandler:
Err.Clear
Resume ErrorHandlerExit
End Sub
Click to expand...

(not tested)

HTH - Peter
Click to expand...
 
Bryan,
One question, what is the purpose of using Format?
Click to expand...

When you concatenate literals and numeric values using "+" ("text " +
numVar) you receive runtime error 13. With "&", i.e. "text " & numVar you
don't, neither with "text " + Format(numVar) or "text " + Str(numVar).

That I use "text " & Format(numVar) ... treat it as a (bad) habit ;-)

I don't know enough about the internals to tell which way's the right way.
Not to use Format is probably faster.

Peter
 
Back
Top