Finding record

[jcpelejo]

Hello. I have a subform that I would like to double click on the [ClaimNumber] field to set the record to the value in the textbox. I have tried coding it like this:

Private Sub ClaimNumber_DblClick(Cancel As Integer)
Dim rst As Recordset
Dim strSearchName As String
Dim strClaimNumber As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!ClaimNumber)
strClaimNumber = "[Forms]![Manual Claims System - MR Canada]!Form.ClaimNumber"
rst.Find "strClaimNumber = " & strSearchName
Me.Bookmark = rst.Bookmark
rst.Close

End Sub

Please advise.

 

[rewdee]

Hi

I'm not sure if you have typos or these are just mistakes. First of all you say the field is [ClaimNumber] then the code should be:

rst.FindFirst "[ClaimNumber]=" & CInt(Me.subformname!controlname)
If rst.NoMatch then
    msgbox "No Match Record"
Else
    'Me.whatevertxtboxName=rst!ClaimNumber
    'Or do whatever  
End If

I hope this helps,
Rewdee

 

[jcpelejo]

Hi Rewdee,
I changed my VBA coding to read:

Function FindClaimNumber()
On Error GoTo ClaimNumber_Err

With CodeContextObject
Forms![Manual Claims System - MR Canada]!
[Search Query subform].Form!ClaimNumber.Enabled
= .ClaimNumber
DoCmd.GoToControl "[txtClaimNumber1]"
DoCmd.FindRecord .ClaimNumber, acEntire, False, ,
False, , True
End With

FindClaimNumber_Exit:
Exit Function

ClaimNumber_Err:
MsgBox Error$
Resume mcrSearch_FindClaimNumber_Exit

End Function

It works now. Thank you for your help.

 

[jcpelejo]

Hi Rewdee,
I changed my VBA coding to read:

Function FindClaimNumber()
On Error GoTo ClaimNumber_Err

With CodeContextObject
Forms![Manual Claims System - MR Canada]!
[Search Query subform].Form!ClaimNumber.Enabled
= .ClaimNumber
DoCmd.GoToControl "[txtClaimNumber1]"
DoCmd.FindRecord .ClaimNumber, acEntire, False, ,
False, , True
End With

FindClaimNumber_Exit:
Exit Function

ClaimNumber_Err:
MsgBox Error$
Resume FindClaimNumber_Exit

End Function

It works now. Thank you for your help.