Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    May 2004
    Posts
    21

    Unanswered: Form/Subform Problem

    I have an ADP connected to a SQL Server database. I want to connect a combo box in the main form that selects an employee from a table (by hidden employee id) to assign that employee to a certain press release that is displayed in the form. When this combo box selects an employee, I would like the subform to display contact information from the same employee table which contains other information including phone #, fax #, and e-mail. The information in the subform should change when a different employee is selected in the combo box. Any help would be greatly appreciated.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Have the subform created and saved. In the main form, use the Wizard to create the subform, which will walk you through it.

    To use the wizard, display the toolbox and make sure the icon that looks like a magic wand is highlighted, then press the subform button.

    Basically, to use Parent/Child relationships, both the Form and Subform need to be linked to underlying tables. The property sheet for the subform contains the settings for Parent/Child relations, which you can set to your combo box. Then, when the combo box value changes, the subform refilters to match. The requirement is that the data type of the matched fields is the same.

    Note: my environment is Access 97 - newer versions may be slightly different.

    Have fun

  3. #3
    Join Date
    May 2004
    Posts
    21
    The parent-child relationship is set in the subform. Both the parent and child are set to the same attribute, however it still does not work. Does the attribute name need to be more specific, such as listing the form it comes from? (By the way, the wizard set these properties.) Or is something else wrong with the form/subform?

  4. #4
    Join Date
    May 2004
    Posts
    21
    I have also noticed that when the form first opens, the subform correctly selects the proper information for the employee; however, if you change the employee in the combo box, the subform does not update appropriately.

  5. #5
    Join Date
    May 2004
    Posts
    65

    Use the Afterupdate on your Listbox

    Try this:

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[EmployeeNo] = '" & [Listbox] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    ' where EmployeeNo is your employee identifier and Listbox is the name of your control

  6. #6
    Join Date
    May 2004
    Posts
    21
    I added this code to my form and I receive an error message when I click a new item in the combo box. The error reads:

    Run-time error '438'

    Object doesn't support this property or method.

    However, right before this error message appears, the subform changes the information to the correct information. All I need now is to clear this error and it will work properly.

    Thanks for your help so far.

  7. #7
    Join Date
    May 2004
    Posts
    21
    Also, the debugger points to the rs.FindFirst line of code.

  8. #8
    Join Date
    May 2004
    Posts
    65

    Can you Post the whole sub code

    Can you post the code before and after where the error occurs.

    What access are u using

  9. #9
    Join Date
    May 2004
    Posts
    21
    Code:
    Private Sub cboMedia_Employee_AfterUpdate()
        Dim rs As Object
        
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[MEDIA_EMPLOYEE_ID] = '" & [cboMedia_Employee] & "'"
        If Not rs.EOF Then
            Me.Bookmark = rs.Bookmark
        End If
    End Sub
    I am using Access 2002.

    Error occurs on this line:
    rs.FindFirst "[MEDIA_EMPLOYEE_ID] = '" & [cboMedia_Employee] & "'"

    Thanks.
    Last edited by scoot241; 05-21-04 at 12:07.

  10. #10
    Join Date
    May 2004
    Posts
    65
    Try
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[MEDIA_EMPLOYEE_ID] = '" & me![cboMedia_Employee] & "'"
    If Not rs.EOF Then
    Me.Bookmark = rs.Bookmark
    End If

  11. #11
    Join Date
    May 2004
    Posts
    21
    Same error.

  12. #12
    Join Date
    May 2004
    Posts
    65
    does the main form have the datasource set to the table [media_employee_ID] is the key to. and if so is it set to indexed (no duplicates)?

  13. #13
    Join Date
    May 2004
    Posts
    21
    No, the main form stores a foreign key value in this combo box. The subform contains the primary key, and is a lookup table for the combo box. The user of the database would like to be able to view the contact information when he/she selects an employee to assign to a certain press release (the main form's information).

  14. #14
    Join Date
    May 2004
    Posts
    65
    As a last resort. Delete the listbox from the form. Recreate it using the toolbox icon for listbox and set the wizard to on. When you drop it on the form it will ask you if you would like to go to a specified record based on the selection. It will automatically write the code for you.

    I would also assume the listbox is on the main form. Not the subform.

  15. #15
    Join Date
    May 2004
    Posts
    21
    My wizard doesn't ask me if I would like to go to a record when I select one in that box. It first asks if I want a table or query to look up the values in, then asks which table, then asks what attributes I want in my combo box, then asks how i would like my attributes displayed in the combo box, then it asks whether I want to store the value or remember the value for later use. Here I choose that I want the value stored in the main form's MEDIA_EMPLOYEE_ID attribute. Then it asks for a name and it is finished.

    Combo box is on the main form, with its attribute being the foreign key MEDIA_EMPLOYEE_ID. Subform contains the employee information and its primary key field is MEDIA_EMPLOYEE_ID.

Posting Permissions

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