Method or data member not found

S

slndn

Registered User.
Local time
Today, 00:06
Joined
May 15, 2013
Messages
19
I am receiving the Method or data member not found error message when I attempt to add a new record in form view. I have been working in an old 2002-2003 version of Access until last week when it was converted to 2007. The old version allowed for additions just fine but now after the conversion something is wrong. The code is below. I underlined the line throwing the error message (FindFirst). Any help is very much appreciated. Code is not my strong suit.

'Open form to new record
gstrCallingForm = Me.Name
strDocName = "frmNewApplicant"
strLinkCriteria = "ApplicantID = " & NewApplicantKey()
Me.Visible = False
DoCmd.OpenForm FormName:=strDocName, _
wherecondition:=strLinkCriteria, WindowMode:=acDialog
'Requery index form to pick up the new record, then
'set the bookmark to this new record.
Me.Requery
Set rst = Me.RecordsetClone
strLinkCriteria = "tblApplicants01.ApplicantKey = " & NewApplicantKey()
If Me.RecordsetClone.RecordCount > 0 Then
'If first new record was cancelled, would fail.
rst.FindFirst strLinkCriteria
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
cmdNewRecord.Enabled = False
cmdDetail.Enabled = False


Exit_Procedure:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub
Error_Handler:
 
strLinkCriteria = "tblApplicants01.ApplicantKey = " & NewApplicantKey()

Shouldn't this be ApplicantID?
 
slndn, have you declared the rst as DAO.Recordset? If so have you added DAO reference? Is the function NewApplicantKey returning a correct value?
 
Should the entire strLinkCriteria be changed to ApplicantID or only tblApplicants01.ApplicantKey? I'm assuming you are referring to the third line above the FindFirst line. Thanks so much!
 
pr2-eugin - I don't know how to do what you are asking. Is this in the properties of the form? My recordset type currently says Dynaset.
 
What exactly does ApplicantId refer to?

strLinkCriteria = "ApplicantID = " & NewApplicantKey()
Click to expand...

Is that the name of the field in your Table?
 
No no, not in the Form properties.. In your VBA Code.. What have you Dim -ed the rst as.. Is it something like..
Code: 
Dim rst As DAO.Recordset
 
ApplicantID is a field in the form that populates the ApplicantKey field in my table titled tblApplications.
 
This is the only reference I can find to Dim

Dim rstAppl As Recordset
Dim strDocName As String
Dim strLinkCriteria As String
 
So the declaration is rstAppl but you have used rst.FindFirst..

Why? Change the declaration to.. Dim rst As DAO. Recordset instead of Dim rstAppl As Recordset
 
Okay, I changed it and it is still getting stuck on FindFirst. Here is the code. I really appreciate your help!

Private Sub cmdNewRecord_Click()
On Error GoTo Error_Handler

Dim rst As Recordset
Dim strDocName As String
Dim strLinkCriteria As String

'Open form to new record
gstrCallingForm = Me.Name
strDocName = "frmNewApplicant"
strLinkCriteria = "ApplicantID = " & NewApplicantKey()
Me.Visible = False
DoCmd.OpenForm FormName:=strDocName, _
wherecondition:=strLinkCriteria, WindowMode:=acDialog
'Requery index form to pick up the new record, then
'set the bookmark to this new record.
Me.Requery
Set rst = Me.RecordsetClone
strLinkCriteria = "tblApplicants01.ApplicantKey = " & NewApplicantKey()
If Me.RecordsetClone.RecordCount > 0 Then
'If first new record was cancelled, would fail.
rst.FindFirst strLinkCriteria
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
End If
cmdNewRecord.Enabled = False
cmdDetail.Enabled = False


Exit_Procedure:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Sub
Error_Handler:
MsgBox "An error has occurred in this application. " _
& "Please contact your technical support person and tell" _
& " them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _
Err.Description, Buttons:=vbCritical, title:="My Application"
Resume Exit_Procedure
Resume

End Sub
 
Try this, and see if it helps..
Code: 
Private Sub cmdNewRecord_Click()
On Error GoTo Error_Handler
    Dim rst As DAO.Recordset
    Dim strDocName As String
    Dim strLinkCriteria As String

    'Open form to new record
    gstrCallingForm = Me.Name
    strDocName = "frmNewApplicant"
    strLinkCriteria = "ApplicantID = " & NewApplicantKey()
    Me.Visible = False
    DoCmd.OpenForm FormName:=strDocName, WhereCondition:=strLinkCriteria, WindowMode:=acDialog
    'Requery index form to pick up the new record, then
    'set the bookmark to this new record.
    Me.Requery
    
    Set rst = Me.RecordsetClone
    If rst.RecordCount > 0 Then
        'If first new record was cancelled, would fail.
        rst.FindFirst strLinkCriteria
        If Not rst.EOF Then
            Me.Bookmark = rst.Bookmark
        End If
    End If
    cmdNewRecord.Enabled = False
    cmdDetail.Enabled = False
Exit_Procedure:
    rst.Close
    Set rst = Nothing
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and tell" _
    & " them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _
    Err.Description, Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
End Sub
 
That did work thank you!!! But now the same error in the code below. It is the FindFirst issue again. I believe this is the only new record command left that could cause the issue.

I'm sorry this is such a mess. I'm not sure how this happened.

Private Sub cmdNewRecord_Click()
On Error GoTo Error_Handler

Dim rstMISOrg As Recordset
Dim strDocName As String
Dim strLinkCriteria As String

'Open form to new record
gstrCallingForm = Me.Name
strDocName = "frmMISOrganization"
strLinkCriteria = "tblMISOrganizations.OrganizationKey = " & NewMISOrganizationKey()
Me.Visible = False
DoCmd.OpenForm FormName:=strDocName, _
WhereCondition:=strLinkCriteria, WindowMode:=acDialog
'Requery index form to pick up the new record, then
'set the bookmark to this new record.
Me.Requery
Set rstMISOrg = Me.RecordsetClone
If Me.RecordsetClone.RecordCount > 0 Then
'If first new record was cancelled, would fail.
rstMISOrg.FindFirst strLinkCriteria
If Not rstMISOrg.EOF Then
Me.Bookmark = rstMISOrg.Bookmark
End If
End If
Exit_Procedure:
On Error Resume Next
rstMISOrg.Close
Set rstMISOrg = Nothing
Exit Sub
Error_Handler:
MsgBox "An error has occurred in this application. " _
& "Please contact your technical support person and tell" _
& " them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _
Err.Description, Buttons:=vbCritical, title:="My Application"
Resume Exit_Procedure
Resume

End Sub
 
Okay, that seemed to work for that line of code. When I compiled it ran and found the following error in another section. It says that cboApplicationStatus is not defined. Does this have anything to do with the changes we just made?

Code: 
Public Sub SelectRecords()
On Error GoTo Error_Handler
    Dim varWhereClause As Variant
    Dim strAND As String
    varWhereClause = Null
    strAND = " AND "
    
    If cboApplicationStatus & "" <> "<all>" Then
        varWhereClause = (varWhereClause + strAND) & _
            "tblApplications.ApplicationStatus = """ & _
            cboApplicationStatus & """"
    End If
    
    'If Not IsNull(txtPaymentAmt) Then
    '    varWhereClause = (varWhereClause + strAND) & _
    '        "tblBusiness.BusinessKey IN (" & _
    '        "Select BusinessKey From tblPayment Where" & _
    '        " PaymentAmount = " & Me!txtPaymentAmt & ")"
    'End If
    
    varWhereClause = " WHERE " + varWhereClause
    
    Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)
    Me.Requery
    EnableDisableControls
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application.  " _
    & "Please contact your technical support person and tell them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & Err.Description, _
    Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume
    
End Sub
 
No that has nothing to do with the changes we have made.. But you either might have misspelt the ComboBox name.. or that does not exists.. To make sure you are spelling them right use the intensile tool.. after the If type Me. and it will pull out all the Controls and property associated with the Form, so when you start typing the control you wish to add, it will be highlighted.. So you can use it.. If it does not come up then you are referring to control that does not exist on the Form..
 
Okay, thank you for that explanation. I used the *Me. tool and realized that ApplicationStatus is not in the list of controls for the MIS Form. This combo box exists in my Applications Form.

I'm guessing that the original designer wanted the Application Status to trigger some sort of action behind the scenes. Is there a way to add it to the control list so the code will recognize it or possibly remove the code all together. I can send you the entire code list for this form if you want to see it?

This is the record source in the MIS form properties area. Can I simply add the applications table (where Application Status is located) for this to work?

SELECT tblMISOrganizations.OrganizationKey, tblApplicants01.ApplicantName, tblMISOrganizations.MISActivationDate, tblMISOrganizations.MISInactivationDate, tblMISOrganizations.OrganizationStatus, tblMISOrganizations.OrganizationType, tblMISOrganizations.NameChange, * FROM tblApplicants01 INNER JOIN tblMISOrganizations ON tblApplicants01.ApplicantKey=tblMISOrganizations.ApplicantKey

Here is the first set of code where ApplicationStatus is referred too.

Code: 
 Dim mstrCallingForm As String  'local variable for form daisy-chaining
Private Sub cboApplicationStatus_AfterUpdate()
On Error GoTo Error_Handler
    SelectRecords
    'cboApplicationStatus.Requery
    
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application.  " _
    & "Please contact your technical support person and tell them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & Err.Description, _
    Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume
End Sub

And then this is the code where it is failing:

Code: 
Public Sub SelectRecords()
On Error GoTo Error_Handler
    Dim varWhereClause As Variant
    Dim strAND As String
    varWhereClause = Null
    strAND = " AND "
    
    If cboApplicationStatus & "" <> "<all>" Then
        varWhereClause = (varWhereClause + strAND) & _
            "tblApplications.cboApplicationStatus = """ & _
            cboApplicationStatus & """"
    End If
    
    'If Not IsNull(txtPaymentAmt) Then
    '    varWhereClause = (varWhereClause + strAND) & _
    '        "tblBusiness.BusinessKey IN (" & _
    '        "Select BusinessKey From tblPayment Where" & _
    '        " PaymentAmount = " & Me!txtPaymentAmt & ")"
    'End If
    
    varWhereClause = " WHERE " + varWhereClause
    
    Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)
    Me.Requery
    EnableDisableControls
Exit_Procedure:
    On Error Resume Next
    Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application.  " _
    & "Please contact your technical support person and tell them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & Err.Description, _
    Buttons:=vbCritical, title:="My Application"
    Resume Exit_Procedure
    Resume
    
End Sub
 
You must log in or register to reply here.

Similar threads

C
Form doesn't show new record
Replies
12
Views
600
MarkK
MarkK
R
Method or data member not found on setfocus
Replies
3
Views
402
arnelgp
arnelgp
M
Solved Form Filter and RecordSource Don't Query New Record
Replies
13
Views
673
Pat Hartman
P
M
Solved F11 - Disable or not.
2
Replies
32
Views
2,985
Marshall Brooks
M
S
Solved Subform Record Source - Method or data member not found
Replies
10
Views
789
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom