ACCESS FEED: Re: [MS_AccessPros] How do I write a public procedure that 2 different forms can call.

Minggu, 01 Juni 2014

Re: [MS_AccessPros] How do I write a public procedure that 2 different forms can call.

Most likely that error is here

.FindFirst "[" & SearchField & "] = " & SearchValue

Often this is a typo or a logici issue. If your field is called ProdID, and you pass instead ProductID, you will get that error because there is no field ProductID in the fields collection. Same if you have a control called txtBxA and you do me.forms.controls("textBoxA"). There is no item in the controls collection with a named index of textBoxA

On Fri, May 30, 2014 at 9:51 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Good morning Mr. Mandeno

I first would like to say thank you. It's been quite long since… I finally have got a chance to get back to play with the Public Function that you were helping me with.

Now I've got the function working pretty good and it can be called from different forms. However, I am not sure if my code is efficient enough or the statements (such as MoveFirst, MoveLast, check for EOF, Requery, Goto …) is redundant or unnecessary. I've also tried to create a Sub (resides in the main form) which deletes records in the sub form. However that created a Run-time error '3265' Item not found in this collection. What part of my code caused that error? Below is my code.

Thank again Mr. Mandeno

Phucon

'Delete record in the main form, this sub's working OK

Private Sub cmdDelOrder_Click()

Call FindFormRecord(Me, "pkeyOrderID", Me.txtOrderID) 'passing Number data type

End Sub

'Delete record in the sub form, this created 'error 3265 item not found in this collection'

Private Sub cmdDelProduct_Click()

Call FindFormRecord(Me, "fkeyProductID", Me!subSubTotal.Form!cboProductID)

End Sub

Public Function FindFormRecord(

    SearchForm As Access.Form, _

    SearchField As String, _

    SearchValue As Variant, _

    Optional NoMatchMessage As String = "Record not found" _

    ) As Boolean



On Error GoTo ErrorHandler

With SearchForm.RecordsetClone

    Select Case .Fields(SearchField).Type

        Case dbText

             SearchValue = Chr$(34) & SearchValue & Chr$(34)

        Case dbLong

             SearchValue = SearchValue

    End Select



    .MoveFirst

    .MoveLast



If Not .EOF Then

         .FindFirst "[" & SearchField & "] = " & SearchValue

            If Not .NoMatch Then    'if found then delete the record.

                If MsgBox("Deleting " & SearchValue & ". Proceed to delete?", vbInformation + vbYesNo, "Delete record.") = vbYes Then

                    .Delete

                    .Requery

                End If

            Else

                MsgBox NoMatchMessage

                GoTo ExitcboFindSetup

            End If

    Else

        GoTo ExitcboFindSetup

    End If

End With



ExitcboFindSetup:

    Exit Function



ErrorHandler:

        MsgBox Err.Number & vbCrLf & Err.Description

        Resume ExitcboFindSetup

End Function