form /subform

[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.

 

[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

 

[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 (e-mail address removed) - thanks for anything
you could do.

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.

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.