Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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. OP
    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. OP
    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.
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page