RecordSet Clone Problem with code

[rlsublime]

I am currently getting an error with the following code. The error is "mothod or data member not found" and all fields are defined correctly as far as i know. Any ideas on what I am doing wrong? Thanks

Private Sub InvestorLoanNumber_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim SID1 As String
Dim stLinkCriteria1 As String
Dim rsc1 As DAO.Recordset
Set rsc = Me.RecordsetClone
Set rsc1 = Me.RecordsetClone
If Not IsNull(Me.InvestorLoanNumber) And Not IsNull(Me.HLoanNumber) Then
SID = Me.Investor_Loan_Number.Value
SID1 = Me.H_Loan_Number.Value
stLinkCriteria = "[InvestorLoanNumber]=" & "'" & SID & "'"
stLinkCriteria1 = "[HLoanNumber]=" & "'" & SID1 & "'"

    If DCount("InvestorLoanNumber", "Repurchase", stLinkCriteria) > 0 And DCount("HLoanNumber", "Repurchase", stLinkCriteria1) > 0 Then
    
        Me.Undo
        MsgBox "H Loan Number " _
        & SID1 & " and Investor Loan Number " _
        & SID & " has already been entered." _
        & vbCr & vbCr & "You will now been taken to the record.", vbInformation _
        , "Duplicate Information"
    
 
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    
    
        rsc1.FindFirst stLinkCriteria1
        Me.Bookmark = rsc1.Bookmark
    End If
Set rsc = Nothing
Set rsc1 = Nothing
End If
On Error GoTo Err_H
Err_H:
Exit Sub
End Sub

[help_me_with_access]

line num?....

[rlsublime]

Line 11 "SID = Me.Investor_Loan_Number.Value"

the Investor_Loan_Number is in another form. Thanks

[RuralGuy]

Here's a good link on the syntax for using the Forms collection to reference a control on another form.
http://access.mvps.org/access/forms/frm0031.htm

[rlsublime]

so if i am trying to look within a table called Investor_request, would it be me!investor_request.investor_loan_number.value.

I think I tried this but it did not work. Any ideas? Thanks

[help_me_with_access]

see RuralGuy's linkage.

[rlsublime]

I did but I am not understanding it since I am new to access and programming. I am trying to reference table: Investor_Request and look within the field: Investor_Loan_Number for that value.

Thanks

[RuralGuy]

You can use a Domain function to look inside a table if you want (DLookup, DCount, etc). The link I provided was the syntax for using the Forms collection to look at a control in another open form.

[ssanfu]

Info on how to refer to controls on a form using the "ME" property. (from A2K3 Help)
---------------------------------------------------

Me Property (of forms & reports)


You can use the Me property in Visual Basic to refer to a form, report, (or to the form or report associated with a subform or subreport), or class module where Visual Basic code is currently running.

Setting

The Me property is available only by using Visual Basic and is read-only in all views.

Remarks

The Me property contains an object reference to the current form or report and is faster than a fully qualified object reference.
For example, the following two code fragments refer to the value of the LastName control for the current record on the Employees form:

strLastName = Forms!Employees.LastName
strLastName = Me!LastName

In most cases, the form or report referred to by the Me property is the same form or report referred to by the ActiveForm or ActiveReport properties of the Screen object. However, these properties refer to the form or report with the forcus, whereas the Me property always refers to the form or report in which code is running.

-------------------------------------------------------

So if the control that has the data is on a different form, you cannot refer to it using the "Me" keyword. AND the form has to be open. The syntax to refer to a control on a different form is

Forms!FormName.ControlName

(Note: you do not have to type ".Value" because the value property is the default property.)



--------------------------------------------------------------------------------------------
I also noticed some errors in the code.

You have these three lines:

If Not IsNull(Me.InvestorLoanNumber) And Not IsNull(Me.HLoanNumber) Then 

SID = Me.Investor_Loan_Number.Value 
SID1 = Me.H_Loan_Number.Value

Why does (in red/blue) does one reference have underscores and the other one doesn't??


I modified your code a little ....

Private Sub InvestorLoanNumber_BeforeUpdate(Cancel As Integer)
   On Error GoTo Err_H

   Dim rsc As DAO.Recordset
   Dim rsc1 As DAO.Recordset
   Dim SID As String
   Dim SID1 As String
   Dim stLinkCriteria As String
   Dim stLinkCriteria1 As String

   Set rsc = Me.RecordsetClone
   Set rsc1 = Me.RecordsetClone


  '   If Not IsNull(Me.InvestorLoanNumber) And Not IsNull(Me.HLoanNumber) Then
  '   SID = Me.Investor_Loan_Number
  '   SID1 = Me.H_Loan_Number

'change form1 to your form name
   SID = Forms!form1.Investor_Loan_Number
   SID1 = Forms!form1.H_Loan_Number

   If Not IsNull(SID) And Not IsNull(SID1) Then

      stLinkCriteria = "[InvestorLoanNumber] = '" & SID & "'"
      stLinkCriteria1 = "[HLoanNumber] = '" & SID1 & "'"

      If DCount("InvestorLoanNumber", "Repurchase", stLinkCriteria) > 0 And DCount("HLoanNumber", "Repurchase", stLinkCriteria1) > 0 Then

         Me.Undo
         MsgBox "H Loan Number " & SID1 _
                & " and Investor Loan Number " & SID _
                & " has already been entered." _
                & vbNewLine & vbNewLine & "You will now been taken to the record.", vbInformation _
                                                                                      , "Duplicate Information"


         rsc.FindFirst stLinkCriteria
         Me.Bookmark = rsc.Bookmark

         ' There is only ONE "Me.Bookmark"
         ' Which record do you want to go to??
         '         rsc1.FindFirst stLinkCriteria1
         '         Me.Bookmark = rsc1.Bookmark

      End If
      Set rsc = Nothing
      Set rsc1 = Nothing
   End If

Err_Exit:
   Exit Sub

Err_H:
   MsgBox Err.Description, vbInformation

End Sub