filtering main form via selecting record in subform

J

Jeff @ CI

I have a main form that displays detail on a client. I have a subform
(unlinked) that displays the entire client list. Using Access 2000

I want to set the main form to display the client detail filtered based upon
selecting a record in the subform. I am trying to filter based on the field
[ClientID].

Currently, I have the control source on the main form[ClientID] set to
=[subform].Form![ClientID]. However, when I select the client in the
subform, only the main form [ClientID] changes. All the remaining detail
remains the same.

any help is appreciated

Jeff
 
D

Damon Heron

How about using a combobox instead of a subform for the client list? Then
it is a trivial issue to show details on the form based on the selection of
the combobox.

Damon
 
J

Jeff @ CI

Damon, thanks. I actually liked the way the subform looked and made the
search a little easier. The combobox works for now but not at the desired
level of satisfaction I wanted. Personal taste.

Again, thank you!

Damon Heron said:
How about using a combobox instead of a subform for the client list? Then
it is a trivial issue to show details on the form based on the selection of
the combobox.

Damon

Jeff @ CI said:
I have a main form that displays detail on a client. I have a subform
(unlinked) that displays the entire client list. Using Access 2000

I want to set the main form to display the client detail filtered based
upon
selecting a record in the subform. I am trying to filter based on the
field
[ClientID].

Currently, I have the control source on the main form[ClientID] set to
=[subform].Form![ClientID]. However, when I select the client in the
subform, only the main form [ClientID] changes. All the remaining detail
remains the same.

any help is appreciated

Jeff
 
D

Damon Heron

You will have to "re-write" the record source of the main form on the click
event of the subform, sort of like this:

Private Sub Form_Click()
Dim strsql As String
Dim varx As Variant
varx = ClientID 'this is the ID in the row of the subform you click
on....
strsql = "SELECT tblClients.ClientID, tblClient.CName, tblClient.Address,
tblClient.Cty" & _
" FROM tblClient Where ClientID= " & [varx] & ""
Me.Parent.RecordSource = strsql
End Sub

Change names to your fields....

Damon





Jeff @ CI said:
Damon, thanks. I actually liked the way the subform looked and made the
search a little easier. The combobox works for now but not at the desired
level of satisfaction I wanted. Personal taste.

Again, thank you!

Damon Heron said:
How about using a combobox instead of a subform for the client list?
Then
it is a trivial issue to show details on the form based on the selection
of
the combobox.

Damon

Jeff @ CI said:
I have a main form that displays detail on a client. I have a subform
(unlinked) that displays the entire client list. Using Access 2000

I want to set the main form to display the client detail filtered based
upon
selecting a record in the subform. I am trying to filter based on the
field
[ClientID].

Currently, I have the control source on the main form[ClientID] set to
=[subform].Form![ClientID]. However, when I select the client in the
subform, only the main form [ClientID] changes. All the remaining
detail
remains the same.

any help is appreciated

Jeff
 
R

Rick Brandt

Jeff said:
I have a main form that displays detail on a client. I have a subform
(unlinked) that displays the entire client list. Using Access 2000

I want to set the main form to display the client detail filtered
based upon selecting a record in the subform. I am trying to filter
based on the field [ClientID].

Currently, I have the control source on the main form[ClientID] set to
=[subform].Form![ClientID]. However, when I select the client in the
subform, only the main form [ClientID] changes. All the remaining
detail remains the same.

any help is appreciated

Flip it around. Continuous main form for clients with client details in a
subform in the footer of the continuous main form. Then you are using the
MasterLink and ChildLink properties as they naturally work.
 
J

Jeff @ CI

Damon, Rick - many thanks.

Rick, I knew how to do your solution and have done it often enough that I
should have known better. I just didn't take the time to look at the other
side of the coin.

Damon, I appreciate the persistence to make things work for me. Please
don't be offended if I go for simple - my brain is fried.

Rick Brandt said:
Jeff said:
I have a main form that displays detail on a client. I have a subform
(unlinked) that displays the entire client list. Using Access 2000

I want to set the main form to display the client detail filtered
based upon selecting a record in the subform. I am trying to filter
based on the field [ClientID].

Currently, I have the control source on the main form[ClientID] set to
=[subform].Form![ClientID]. However, when I select the client in the
subform, only the main form [ClientID] changes. All the remaining
detail remains the same.

any help is appreciated

Flip it around. Continuous main form for clients with client details in a
subform in the footer of the continuous main form. Then you are using the
MasterLink and ChildLink properties as they naturally work.
 
J

Jeff @ CI

Damon,

With your code sample, I have a couple of questions.

#1 - If I have a second subform on the parent that are linked, will I need
to specify which subform to use?

#2 - Following your guidence, I created on the parent form, an OnClick event
procedure using the following code:

Private Sub Form_Click()

Dim strsql As String
Dim varx As Variant
varx = ClientID 'this is the ID in the row of the subform you click on....
strsql = "SELECT CombineNameClientIDQuery.ClientID,
CombineNameClientIDQuery.ClientIDFull" & " FROM CombineNameClientIDQuery
Where ClientID= " & [varx] & ""
Me.Parent.RecordSource = strsql

End Sub

When I go to test the functionality, I get a 'repaint' type flash and
nothing else. I don't get the filtering I desire. The subform is based on a
query that not only combines the ClientID, FirstName, LastName into one
string, but also tests to see if the client is active (Y/N).

Am I missing something here?

Also, while Rick's option will work, the one thing it doesn't allow due to
"form real estate" is to have a larger list displaying for the user to choose
from (the CEO no less).

May I please plead for more help on this headache of mine - and anything to
help make the form work right would also be greatly appreciated.

Jeff


Damon Heron said:
You will have to "re-write" the record source of the main form on the click
event of the subform, sort of like this:

Private Sub Form_Click()
Dim strsql As String
Dim varx As Variant
varx = ClientID 'this is the ID in the row of the subform you click
on....
strsql = "SELECT tblClients.ClientID, tblClient.CName, tblClient.Address,
tblClient.Cty" & _
" FROM tblClient Where ClientID= " & [varx] & ""
Me.Parent.RecordSource = strsql
End Sub

Change names to your fields....

Damon





Jeff @ CI said:
Damon, thanks. I actually liked the way the subform looked and made the
search a little easier. The combobox works for now but not at the desired
level of satisfaction I wanted. Personal taste.

Again, thank you!

Damon Heron said:
How about using a combobox instead of a subform for the client list?
Then
it is a trivial issue to show details on the form based on the selection
of
the combobox.

Damon

I have a main form that displays detail on a client. I have a subform
(unlinked) that displays the entire client list. Using Access 2000

I want to set the main form to display the client detail filtered based
upon
selecting a record in the subform. I am trying to filter based on the
field
[ClientID].

Currently, I have the control source on the main form[ClientID] set to
=[subform].Form![ClientID]. However, when I select the client in the
subform, only the main form [ClientID] changes. All the remaining
detail
remains the same.

any help is appreciated

Jeff
 
R

raz at GI

"raz & G wrote: i tried this on on computer ant it worked on another computer
i received "ambigious name detected" - why ? the code is tahe same on bothe
cimouters!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top