Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

form /subform

Discussion in 'Information Technology' started by Tammy, Jul 28, 2006.

  1. Tammy

    Tammy
    Expand Collapse
    Guest

    Hi - I hope someone can help me please.
    I'm creating a database where customer service agents can enter & track
    customer complaints. Ok - I've got it set up where they pick their customer
    name from a combo box. Ok that works fine and it stores picked customer in
    the "main" table. My problem is that the customer service agent has to be
    able to see and/or input/edit the name of the person who works for their
    customer. So I thought a subform would work. Well, I created the subform
    from the "customers" table. What I want to be able to happen is when the CSA
    selects their customer at the top of the form - that the subform will
    autopopulate as well. Its not working quite right. It will only work if I
    scroll to the next record, then come back to the record I'm working on and
    the name of the person they need to see/edit shows up in the subform. How
    can i make that name pop up automatically when they select a name from the
    customer list? Does that make sense? I hope I wrote there where someone can
    understand. Any help appreciated.
     
  2. Loading...


  3. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    Hi Tammy

    Create an unbound combo box on your main form call this new combo [cboSearch]

    I have assumed that you have a table called tblCustomerDetails
    I have assumed that you have fields called
    CustomerID
    Customer1stName
    CustomerSurname

    ComboBox
    Row Source Type Table/Query
    Row Source
    SELECT [tblCustomerDetails].[ CustomerID], [tblCustomerDetails ].[
    Customer1stName], [tblCustomerDetails ].[ CustomerSurname] FROM
    [tblCustomerDetails ];

    Column Count 3
    Column widths 0cm;2.542cm;2.542cm


    Set the AfterUpdate like this

    Private Sub cboSearch_AfterUpdate()
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustomerID] = " & Str(Nz(Me![cboSearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub


    This will take you to the record you want

    Next – your subform
    You MUST have the [CustomerID] in both the main form and the sub form. You
    can set the visible to NO if you don’t want to show it but it will still be
    there.

    Set the Data Column of the properties box on the subform like this
    Source Object tblCustomerDetails
    Link Child Field CustomerID
    Link Master Field CustomerID
    Enabled Yes
    Locked No

    Populate your subform with fields from tblCustomerDetails

    This will autopopulate your subform AfterUpdate of [cboSearch]


    Hope this helps

    --
    Wayne
    Manchester, England.



    "Tammy" wrote:

    > Hi - I hope someone can help me please.
    > I'm creating a database where customer service agents can enter & track
    > customer complaints. Ok - I've got it set up where they pick their customer
    > name from a combo box. Ok that works fine and it stores picked customer in
    > the "main" table. My problem is that the customer service agent has to be
    > able to see and/or input/edit the name of the person who works for their
    > customer. So I thought a subform would work. Well, I created the subform
    > from the "customers" table. What I want to be able to happen is when the CSA
    > selects their customer at the top of the form - that the subform will
    > autopopulate as well. Its not working quite right. It will only work if I
    > scroll to the next record, then come back to the record I'm working on and
    > the name of the person they need to see/edit shows up in the subform. How
    > can i make that name pop up automatically when they select a name from the
    > customer list? Does that make sense? I hope I wrote there where someone can
    > understand. Any help appreciated.
     
  4. Tammy

    Tammy
    Expand Collapse
    Guest

    Hi Wayne - I have tried it and it just does not work. I created the
    cboSearch and subform on a form titled "Issues" created from the "Issues"
    table. Is that the problem? Or, would you mind terribly if I were to email
    or call you? My yahoo email is motherworks@yahoo.com - thanks for anything
    you could do.

    "Wayne-I-M" wrote:

    > Hi Tammy
    >
    > Create an unbound combo box on your main form call this new combo [cboSearch]
    >
    > I have assumed that you have a table called tblCustomerDetails
    > I have assumed that you have fields called
    > CustomerID
    > Customer1stName
    > CustomerSurname
    >
    > ComboBox
    > Row Source Type Table/Query
    > Row Source
    > SELECT [tblCustomerDetails].[ CustomerID], [tblCustomerDetails ].[
    > Customer1stName], [tblCustomerDetails ].[ CustomerSurname] FROM
    > [tblCustomerDetails ];
    >
    > Column Count 3
    > Column widths 0cm;2.542cm;2.542cm
    >
    >
    > Set the AfterUpdate like this
    >
    > Private Sub cboSearch_AfterUpdate()
    > Dim rs As Object
    >
    > Set rs = Me.Recordset.Clone
    > rs.FindFirst "[CustomerID] = " & Str(Nz(Me![cboSearch], 0))
    > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    > End Sub
    >
    >
    > This will take you to the record you want
    >
    > Next – your subform
    > You MUST have the [CustomerID] in both the main form and the sub form. You
    > can set the visible to NO if you don’t want to show it but it will still be
    > there.
    >
    > Set the Data Column of the properties box on the subform like this
    > Source Object tblCustomerDetails
    > Link Child Field CustomerID
    > Link Master Field CustomerID
    > Enabled Yes
    > Locked No
    >
    > Populate your subform with fields from tblCustomerDetails
    >
    > This will autopopulate your subform AfterUpdate of [cboSearch]
    >
    >
    > Hope this helps
    >
    > --
    > Wayne
    > Manchester, England.
    >
    >
    >
    > "Tammy" wrote:
    >
    > > Hi - I hope someone can help me please.
    > > I'm creating a database where customer service agents can enter & track
    > > customer complaints. Ok - I've got it set up where they pick their customer
    > > name from a combo box. Ok that works fine and it stores picked customer in
    > > the "main" table. My problem is that the customer service agent has to be
    > > able to see and/or input/edit the name of the person who works for their
    > > customer. So I thought a subform would work. Well, I created the subform
    > > from the "customers" table. What I want to be able to happen is when the CSA
    > > selects their customer at the top of the form - that the subform will
    > > autopopulate as well. Its not working quite right. It will only work if I
    > > scroll to the next record, then come back to the record I'm working on and
    > > the name of the person they need to see/edit shows up in the subform. How
    > > can i make that name pop up automatically when they select a name from the
    > > customer list? Does that make sense? I hope I wrote there where someone can
    > > understand. Any help appreciated.
     

Share This Page