Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding & Deleting Record in Subform (Access 2002)

    I have 2 questions: 1st - Finding a Record in a subform. I have a "master" form with 2 subforms and want a button on the master form to locate and display a particular record on 1 of the subforms. I can create a parameter query joining 2 tables to find the necessary information; however, I'd love to automate it right on the master form. I've tried using a combo box and creating a macro but no matter what combination I try, it's not working, I just keep getting different error messages. I don't necessarily want a combo box.

    2nd question: I'd like to create a button on the subform to delete a particular record on the subform. I have figured out that I can turn "Record Selector" on and use the keyboard to delete a record in the subform without it deleting the entire record from the master form down but I would like to offer the users a button. I do have referential integrity in the relationship of my tables and I'm sure that's factoring in. Do I have to turn that off?

    I would be grateful for any help!!! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    About question 1: What exactly do you want to do? Do you want to find a record in the subform that belongs to the current record in the main form? Or do you want to find a subform record regardless of to which main form record it belongs? This makes a huge difference for the code needed.

    About question 2: The Form View toolbar already contains a Delete Record button.

    What kind of subform do you have? If it is a datasheet subform, any buttons you place on it in design view will be invisible in datasheet view. In a continuous or single form, you can put a command button cmdDelete on the subform with On Click event procedure

    Private Sub cmdDelete_Click()
    On Error GoTo ErrHandler
    RunCommand acCmdDeleteRecord
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    Referential integrity should not be a problem if you're deleting subform records.

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    Re: Question1: I want to find a record in subform regardless of which main form record it belongs. I'm sorry I failed to provide that important info.

    Re: Question 2: I have a continuous form. Thank you!! I rarely look at the Form View toolbar and that does work fine <img src=/S/blush.gif border=0 alt=blush width=15 height=15>. I did want a button on the subform itself so I am going to put the cmdDelete button on the subform just as you provided. Thank you again!!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    Searching through both subform and main form records requires a 2-step process: first find the first main form record whose subform contains the search value, then locate the relevant record in the subform. The easiest way to do so is to put a combo box on the main form that lists the search values. I have attached an example based on the Categories and Products tables from the Northwind database. Here is the After Update code for the combo box:

    Private Sub cboSearchProduct_AfterUpdate()
    Dim rstMain As Object
    Dim rstSub As Object

    On Error GoTo ErrHandler

    If IsNull(Me.cboSearchProduct) Then Exit Sub
    ' Main form recordset
    Set rstMain = Me.RecordsetClone
    ' Try to find category
    rstMain.FindFirst "CategoryID = " & Me.cboSearchProduct.Column(2)
    If rstMain.NoMatch Then
    Beep
    Else
    ' Move to found category
    Me.Bookmark = rstMain.Bookmark
    ' Subform recordset
    Set rstSub = Me.sbfProducts.Form.RecordsetClone
    ' Try to find product
    rstSub.FindFirst "ProductID = " & Me.cboSearchProduct
    If rstSub.NoMatch Then
    Beep
    Else
    ' Move to product
    Me.sbfProducts.Form.Bookmark = rstSub.Bookmark
    Me.sbfProducts.SetFocus
    End If
    End If

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rstSub.Close
    Set rstSub = Nothing
    rstMain.Close
    Set rstMain = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    Once again HansV to my rescue - many thanks! Before trying this on my own database, I decided to attempt this code in the Northwind database and have done as you've recommended creating a combo box on the main form titled "Categories" getting the values from the Products Table and told it to "Remember value for later use" and put the code in; however, I'm not getting the following compile error "Method or data member not found" on the 2nd line of code below (which I believe is line 15 on your code):

    ' Subform recordset
    Set rstSub = Me.sbfProducts.Form.RecordsetClone

    Obviously I'm a beginner with this; however, I don't think Access recognizes "Products" as a form (and clearly it is) - I've double checked the form name and code over and over and can't find what's wrong. I thought maybe that I had typed the code incorrectly so I tried copying/pasting your code directly into Access; however, I'm still getting the same error. Any ideas?

    Very grateful for your help!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    Have you created a form in the Northwind database based on the Categories table, with a subform based on the Products table? If so, open the main form in design view, and click ONCE on the subform to select it. Don't click a second time, for then you select something within the subform. If you clicked a second time by accident, click outside the subform, then try again.
    The Object dropdown list in the Formatting toolbar and the Properties window will display the name of the subform as a control on the main form. This may be different from the name of the subform in the database window (= the value of the Source Object property). You need to use the name of the subform as a control in the code.
    Does that help?

  7. #7
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    I did not actually create a form myself in the Northwind database, I found one that was created called Categories with a subform from the Products table and used that. I'll try creating one. Thanks!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    You can try the existing one too, but take good note of the exact name of the subform as a control on the Categories form.

  9. #9
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    As you noted, I did go back and use the name of the subform (Product List) as the control in the code; however, I am now getting a syntax error. Will you be so kind as to look the code below and tell me the error of my ways? I renamed the subform to 1 word (ProductList) as opposed to 2 words as they had it. I'm trying to get a good understanding of this, because to complicate things on my own database, I want to search in the subform not for a single field like ProductName, but rather for 3 fields, First Name, Middle Name, Last Name.

    Private Sub cboSearchProduct_AfterUpdate()
    Dim rstMain As Object
    Dim rstSub As Object

    On Error GoTo ErrHandler

    If IsNull(Me.cboSearchProduct) Then Exit Sub
    ' Main form recordset
    Set rstMain = Me.RecordsetClone
    ' Try to find category
    rstMain.FindFirst "CategoryID = " & Me.cboSearchProduct.Column(2)
    If rstMain.NoMatch Then
    Beep
    Else
    ' Move to found category
    Me.Bookmark = rstMain.Bookmark
    ' Subform recordset
    Set rstSub = Me.ProductList.Form.RecordsetClone
    ' Try to find product
    rstSub.FindFirst "ProductID = " & Me.cboSearchProduct
    If rstSub.NoMatch Then
    Beep
    Else
    ' Move to product
    Me.ProductList.Form.Bookmark = rstSub.Bookmark
    Me.ProductList.SetFocus
    End If
    End If

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rstSub.Close
    Set rstSub = Nothing
    rstMain.Close
    Set rstMain = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub



    Thank you again for your help!!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    Can you tell us which line of the code is highlighted when you get the syntax error?

  11. #11
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    I just get the syntax error and it doesn't take me into the VB editor. I click [Ok] on the message and it disappears and when I go to the code, there's nothing highlighted. It won't let me go into the code without clearing the syntax error.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    Open the form in design view.
    Switch to the Visual Basic Editor (Alt+F11)
    Select Debug | Compile <projectname>

    Do you get an error then?

  13. #13
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    I don't get an error Debugging.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    OK, try the following: the After Update event procedure contains a line

    On Error GoTo ErrHandler

    Make this into a comment temporarily by inserting an apostrophe ' before it. Switch back to Access, and open the form in form view. When you select an item in the combo box. you should now get a choice End / Debug / Help. Click Debug, and note which line is highlighted. You can inspect the value of variables and properties by hovering the mouse pointer over them. Report back.

  15. #15
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding & Deleting Record in Subform (Access 2002)

    Ah yes thank you. I get this line of code highlighted

    rstMain.FindFirst "CategoryID = " & Me.cboSearchProduct.Column(2)

    When hovering over the last portion it provides quicktip with Me.cboSearchProduct.Column(2) = Null

    I tried changing the Column from 2 to 1 just to see what it would do, and when going into debug it says
    Me.cboSearchProduct.Column(1) = "Perth pasties" (that's the product I was trying to search for) but when I go back and delete the apostrophe on "On Error GoTo ErrHandler" I then get the syntax error again.

    Thanks for your continued patience and assistance!!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •