set access filter

[Guest]

I'm a .NET programmer trying to throw together a quick Access app but can't
figure out the object reference methods. I've got a main form with two
subforms, each in data sheet view. Datasheet 1 has a client list, datasheet
2 has a project list (multiple prj per client) and the main form has detail
project info. I want to click on a client and have the prj datasheet filter
to only those project for that client. Then click on a project and view the
prj detail on the main form for editing.

In the Form_Current event of the Client subform, I'm trying this:

Forms![MainFrm]![ActiveProjectLst].SourceObject.filter = "select name from
ActiveProjectsQry where ClientID = '" & ActiveClientLst.ClientID & "' "

where MainFrm is the top level form
ActiveProjectLst is the subform container for datasheet 2
ActiveClientLst is the subform container for datasheet 1

My first question is: is this the proper approach? Second: How do I refer
to the filter property of a datasource on a subform????? Also, How do I get
the current ID value out of one subform and use it in the filter for another
subform?

Pls help - I bid 8 hours on this "simple" app and am already 5 hours into it
with no end in sight.

 

[Rob Oldfield]

Hmmm. I think I see what it is that you're trying to do. Using filters is
not the way to go. Instead you're going to be better off using Master/Child
fields to control which records display on the subform. (If you haven't
played with this technique then I'd suggest using the Form Wizard to create
a form/subform (to display clients and projects for example) so that you get
the idea of how it works at a basic level.

To use the same idea in your situation, you're going to need a textbox
(could be anything really, but a textbox is simplest) on the main form. For
here, I'll call it txtCli. This can be hidden in the production version.

When you click a different client on the clients subform, you need to
transfer the key field info back to the main form. That would be something
like...

parent.txtCli=me.clientID

....in the current event of the client subform.

You can then filter the projects subform by settings its master field to
txtCli (OK, it's not really a field, but it works) and its child field to
ClientID.

 

[Guest]

NICE! Thanks - but I'm only halfway there. The form opens with the project
list filtered to the first client. When I click on a different client the
hidden field updates properly but the project list does not - it stays
filtered to the projects for the first client. How do I get the project list
to refresh? I tried this but it doesn't work:

Me.Parent!ActiveProjectLst.Refresh

In case it helps, here's my code to get teh clientID out of the first list:
Private Sub Form_Current()
Me.Parent!ClientIDLbl.Caption = Me.ClientID
End Sub

and on the project subform I set
LInk Child Fieds = ClientID
Link Master Fields = ClientIDLbl.Caption

thanks in advance.....

brad

Hmmm. I think I see what it is that you're trying to do. Using filters is
not the way to go. Instead you're going to be better off using Master/Child
fields to control which records display on the subform. (If you haven't
played with this technique then I'd suggest using the Form Wizard to create
a form/subform (to display clients and projects for example) so that you get
the idea of how it works at a basic level.

To use the same idea in your situation, you're going to need a textbox
(could be anything really, but a textbox is simplest) on the main form. For
here, I'll call it txtCli. This can be hidden in the production version.

When you click a different client on the clients subform, you need to
transfer the key field info back to the main form. That would be something
like...

parent.txtCli=me.clientID

....in the current event of the client subform.

You can then filter the projects subform by settings its master field to
txtCli (OK, it's not really a field, but it works) and its child field to
ClientID.

I'm a .NET programmer trying to throw together a quick Access app but can't
figure out the object reference methods. I've got a main form with two
subforms, each in data sheet view. Datasheet 1 has a client list, datasheet
2 has a project list (multiple prj per client) and the main form has detail
project info. I want to click on a client and have the prj datasheet filter
to only those project for that client. Then click on a project and view the
prj detail on the main form for editing.

In the Form_Current event of the Client subform, I'm trying this:

Forms![MainFrm]![ActiveProjectLst].SourceObject.filter = "select name from
ActiveProjectsQry where ClientID = '" & ActiveClientLst.ClientID & "' "

where MainFrm is the top level form
ActiveProjectLst is the subform container for datasheet 2
ActiveClientLst is the subform container for datasheet 1

My first question is: is this the proper approach? Second: How do I refer
to the filter property of a datasource on a subform????? Also, How do I get
the current ID value out of one subform and use it in the filter for another
subform?

Pls help - I bid 8 hours on this "simple" app and am already 5 hours into it
with no end in sight.

 

[Guest]

BTW, I also tried this:

Private Sub Form_Current()
Dim strNewRecord As String

strNewRecord = "SELECT * FROM ActiveProjects WHERE ClientID = '" _
& Me.ClientID & "'"
Me.Parent!ActiveProjectLst.RecordSource = strNewRecord
End Sub

but I get the error:
Object doesn't support this property or method

thx

Hmmm. I think I see what it is that you're trying to do. Using filters is
not the way to go. Instead you're going to be better off using Master/Child
fields to control which records display on the subform. (If you haven't
played with this technique then I'd suggest using the Form Wizard to create
a form/subform (to display clients and projects for example) so that you get
the idea of how it works at a basic level.

To use the same idea in your situation, you're going to need a textbox
(could be anything really, but a textbox is simplest) on the main form. For
here, I'll call it txtCli. This can be hidden in the production version.

When you click a different client on the clients subform, you need to
transfer the key field info back to the main form. That would be something
like...

parent.txtCli=me.clientID

....in the current event of the client subform.

You can then filter the projects subform by settings its master field to
txtCli (OK, it's not really a field, but it works) and its child field to
ClientID.

I'm a .NET programmer trying to throw together a quick Access app but can't
figure out the object reference methods. I've got a main form with two
subforms, each in data sheet view. Datasheet 1 has a client list, datasheet
2 has a project list (multiple prj per client) and the main form has detail
project info. I want to click on a client and have the prj datasheet filter
to only those project for that client. Then click on a project and view the
prj detail on the main form for editing.

In the Form_Current event of the Client subform, I'm trying this:

Forms![MainFrm]![ActiveProjectLst].SourceObject.filter = "select name from
ActiveProjectsQry where ClientID = '" & ActiveClientLst.ClientID & "' "

where MainFrm is the top level form
ActiveProjectLst is the subform container for datasheet 2
ActiveClientLst is the subform container for datasheet 1

My first question is: is this the proper approach? Second: How do I refer
to the filter property of a datasource on a subform????? Also, How do I get
the current ID value out of one subform and use it in the filter for another
subform?

Pls help - I bid 8 hours on this "simple" app and am already 5 hours into it
with no end in sight.

 

[Brendan Reynolds]

Try ...

Me.Parent!ActiveProjectLst.Form.RecordSource = strNewRecord

Note the reference to the Form property of the subform control.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

BTW, I also tried this:

Private Sub Form_Current()
Dim strNewRecord As String

strNewRecord = "SELECT * FROM ActiveProjects WHERE ClientID = '" _
& Me.ClientID & "'"
Me.Parent!ActiveProjectLst.RecordSource = strNewRecord
End Sub

but I get the error:
Object doesn't support this property or method

thx

Hmmm. I think I see what it is that you're trying to do. Using filters
is
not the way to go. Instead you're going to be better off using
Master/Child
fields to control which records display on the subform. (If you haven't
played with this technique then I'd suggest using the Form Wizard to
create
a form/subform (to display clients and projects for example) so that you
get
the idea of how it works at a basic level.

To use the same idea in your situation, you're going to need a textbox
(could be anything really, but a textbox is simplest) on the main form.
For
here, I'll call it txtCli. This can be hidden in the production version.

When you click a different client on the clients subform, you need to
transfer the key field info back to the main form. That would be
something
like...

parent.txtCli=me.clientID

....in the current event of the client subform.

You can then filter the projects subform by settings its master field to
txtCli (OK, it's not really a field, but it works) and its child field to
ClientID.

I'm a .NET programmer trying to throw together a quick Access app but can't
figure out the object reference methods. I've got a main form with two
subforms, each in data sheet view. Datasheet 1 has a client list, datasheet
2 has a project list (multiple prj per client) and the main form has detail
project info. I want to click on a client and have the prj datasheet filter
to only those project for that client. Then click on a project and
view the
prj detail on the main form for editing.

In the Form_Current event of the Client subform, I'm trying this:

Forms![MainFrm]![ActiveProjectLst].SourceObject.filter = "select name
from
ActiveProjectsQry where ClientID = '" & ActiveClientLst.ClientID & "' "

where MainFrm is the top level form
ActiveProjectLst is the subform container for datasheet 2
ActiveClientLst is the subform container for datasheet 1

My first question is: is this the proper approach? Second: How do I
refer
to the filter property of a datasource on a subform????? Also, How do
I get
the current ID value out of one subform and use it in the filter for another
subform?

Pls help - I bid 8 hours on this "simple" app and am already 5 hours
into it
with no end in sight.

 

[Rob Oldfield]

To be honest, I'm not sure how you're getting the project list to filter at
all.... I've just tried it using a label caption and I can't get it to work
at all. Just try it with a text box instead. (If you want to use the label
for display purposes, you could always set both the text box and the label
caption to be the Client ID, and just hide the textbox.... or you could just
format the text box to look like a label.)

I've set it up using a text box and it works straight out of the box - no
refresh required. 'Parent' by the way, works as a standalone -
parent.txtCli refers directly to a control called txtCli on a parent form -
you don't need the me.parent bits.

NICE! Thanks - but I'm only halfway there. The form opens with the project
list filtered to the first client. When I click on a different client the
hidden field updates properly but the project list does not - it stays
filtered to the projects for the first client. How do I get the project list
to refresh? I tried this but it doesn't work:

Me.Parent!ActiveProjectLst.Refresh

In case it helps, here's my code to get teh clientID out of the first list:
Private Sub Form_Current()
Me.Parent!ClientIDLbl.Caption = Me.ClientID
End Sub

and on the project subform I set
LInk Child Fieds = ClientID
Link Master Fields = ClientIDLbl.Caption

thanks in advance.....

brad

Hmmm. I think I see what it is that you're trying to do. Using filters is
not the way to go. Instead you're going to be better off using Master/Child
fields to control which records display on the subform. (If you haven't
played with this technique then I'd suggest using the Form Wizard to create
a form/subform (to display clients and projects for example) so that you get
the idea of how it works at a basic level.

To use the same idea in your situation, you're going to need a textbox
(could be anything really, but a textbox is simplest) on the main form. For
here, I'll call it txtCli. This can be hidden in the production version.

When you click a different client on the clients subform, you need to
transfer the key field info back to the main form. That would be something
like...

parent.txtCli=me.clientID

....in the current event of the client subform.

You can then filter the projects subform by settings its master field to
txtCli (OK, it's not really a field, but it works) and its child field to
ClientID.

I'm a .NET programmer trying to throw together a quick Access app but can't
figure out the object reference methods. I've got a main form with two
subforms, each in data sheet view. Datasheet 1 has a client list, datasheet
2 has a project list (multiple prj per client) and the main form has detail
project info. I want to click on a client and have the prj datasheet filter
to only those project for that client. Then click on a project and

view
the

prj detail on the main form for editing.

In the Form_Current event of the Client subform, I'm trying this:

Forms![MainFrm]![ActiveProjectLst].SourceObject.filter = "select name from
ActiveProjectsQry where ClientID = '" & ActiveClientLst.ClientID & "' "

where MainFrm is the top level form
ActiveProjectLst is the subform container for datasheet 2
ActiveClientLst is the subform container for datasheet 1

My first question is: is this the proper approach? Second: How do I refer
to the filter property of a datasource on a subform????? Also, How do

I
get

the current ID value out of one subform and use it in the filter for another
subform?

Pls help - I bid 8 hours on this "simple" app and am already 5 hours

into
it

with no end in sight.

 

[Guest]

Thats it! Thanks for the help.

To be honest, I'm not sure how you're getting the project list to filter at
all.... I've just tried it using a label caption and I can't get it to work
at all. Just try it with a text box instead. (If you want to use the label
for display purposes, you could always set both the text box and the label
caption to be the Client ID, and just hide the textbox.... or you could just
format the text box to look like a label.)

I've set it up using a text box and it works straight out of the box - no
refresh required. 'Parent' by the way, works as a standalone -
parent.txtCli refers directly to a control called txtCli on a parent form -
you don't need the me.parent bits.

NICE! Thanks - but I'm only halfway there. The form opens with the project
list filtered to the first client. When I click on a different client the
hidden field updates properly but the project list does not - it stays
filtered to the projects for the first client. How do I get the project list
to refresh? I tried this but it doesn't work:

Me.Parent!ActiveProjectLst.Refresh

In case it helps, here's my code to get teh clientID out of the first list:
Private Sub Form_Current()
Me.Parent!ClientIDLbl.Caption = Me.ClientID
End Sub

and on the project subform I set
LInk Child Fieds = ClientID
Link Master Fields = ClientIDLbl.Caption

thanks in advance.....

brad

Hmmm. I think I see what it is that you're trying to do. Using filters is
not the way to go. Instead you're going to be better off using Master/Child
fields to control which records display on the subform. (If you haven't
played with this technique then I'd suggest using the Form Wizard to create
a form/subform (to display clients and projects for example) so that you get
the idea of how it works at a basic level.

To use the same idea in your situation, you're going to need a textbox
(could be anything really, but a textbox is simplest) on the main form. For
here, I'll call it txtCli. This can be hidden in the production version.

When you click a different client on the clients subform, you need to
transfer the key field info back to the main form. That would be something
like...

parent.txtCli=me.clientID

....in the current event of the client subform.

You can then filter the projects subform by settings its master field to
txtCli (OK, it's not really a field, but it works) and its child field to
ClientID.


I'm a .NET programmer trying to throw together a quick Access app but
can't
figure out the object reference methods. I've got a main form with two
subforms, each in data sheet view. Datasheet 1 has a client list,
datasheet
2 has a project list (multiple prj per client) and the main form has
detail
project info. I want to click on a client and have the prj datasheet
filter
to only those project for that client. Then click on a project and view
the
prj detail on the main form for editing.

In the Form_Current event of the Client subform, I'm trying this:

Forms![MainFrm]![ActiveProjectLst].SourceObject.filter = "select name from
ActiveProjectsQry where ClientID = '" & ActiveClientLst.ClientID & "' "

where MainFrm is the top level form
ActiveProjectLst is the subform container for datasheet 2
ActiveClientLst is the subform container for datasheet 1

My first question is: is this the proper approach? Second: How do I refer
to the filter property of a datasource on a subform????? Also, How do I
get
the current ID value out of one subform and use it in the filter for
another
subform?

Pls help - I bid 8 hours on this "simple" app and am already 5 hours into
it
with no end in sight.

 

[Rob Oldfield]

Yes. That will also work. Except if ClientID is numeric in which case you
won't need the single speech marks...

strNewRecord = "SELECT * FROM ActiveProjects WHERE ClientID = "& Me.ClientID

Try ...

Me.Parent!ActiveProjectLst.Form.RecordSource = strNewRecord

Note the reference to the Form property of the subform control.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

BTW, I also tried this:

Private Sub Form_Current()
Dim strNewRecord As String

strNewRecord = "SELECT * FROM ActiveProjects WHERE ClientID = '" _
& Me.ClientID & "'"
Me.Parent!ActiveProjectLst.RecordSource = strNewRecord
End Sub

but I get the error:
Object doesn't support this property or method

thx

Hmmm. I think I see what it is that you're trying to do. Using filters
is
not the way to go. Instead you're going to be better off using
Master/Child
fields to control which records display on the subform. (If you haven't
played with this technique then I'd suggest using the Form Wizard to
create
a form/subform (to display clients and projects for example) so that you
get
the idea of how it works at a basic level.

To use the same idea in your situation, you're going to need a textbox
(could be anything really, but a textbox is simplest) on the main form.
For
here, I'll call it txtCli. This can be hidden in the production version.

When you click a different client on the clients subform, you need to
transfer the key field info back to the main form. That would be
something
like...

parent.txtCli=me.clientID

....in the current event of the client subform.

You can then filter the projects subform by settings its master field to
txtCli (OK, it's not really a field, but it works) and its child field to
ClientID.


I'm a .NET programmer trying to throw together a quick Access app but
can't
figure out the object reference methods. I've got a main form with two
subforms, each in data sheet view. Datasheet 1 has a client list,
datasheet
2 has a project list (multiple prj per client) and the main form has
detail
project info. I want to click on a client and have the prj datasheet
filter
to only those project for that client. Then click on a project and
view
the
prj detail on the main form for editing.

In the Form_Current event of the Client subform, I'm trying this:

Forms![MainFrm]![ActiveProjectLst].SourceObject.filter = "select name
from
ActiveProjectsQry where ClientID = '" & ActiveClientLst.ClientID & "' "

where MainFrm is the top level form
ActiveProjectLst is the subform container for datasheet 2
ActiveClientLst is the subform container for datasheet 1

My first question is: is this the proper approach? Second: How do I
refer
to the filter property of a datasource on a subform????? Also, How do
I
get
the current ID value out of one subform and use it in the filter for
another
subform?

Pls help - I bid 8 hours on this "simple" app and am already 5 hours
into
it
with no end in sight.

 

[Guest]

OK - I'm about to give up on Access as a development environment. Nothing is
intuitive here.

Rob, your technique worked but apparently has a depth limitation. My
previous description of the prob was simplified for posting - what I'm doing
is creating a billabel time app where one client can have more than one
project which can have more than one time entry. I put in the client and
project forms like you described and it works. I tried to add another
subform, using the same technique, to list all the time entries for the
project. When I put any kind of event code on the Form_Current of the
billable time subform, I get:

"The expression On Current you entered as the event property setting
produced the following error: A problem occurred while Microsoft Office
Access was communicating with the OLE server or ActiveX Control."

This error occurs no matter what code I put in that Form_Current. Here's
all the code I have in this app:

on the Client form:
Private Sub Form_Current()
Parent!ClientIDTxt = Me.ClientID
End Sub

On the Project form:
Private Sub Form_Current()
Parent!ProjectIDTxt = Me.ProjectID
End Sub

On the Time form:
Private Sub Form_Current()
MsgBox ("hello")
End Sub

The odd thing is that having any kind of event code causes the error. If I
commetn out the msgbox statement so there is no code in the procedure, I
still get the error. If I delete the procedure entirely, the error goes away.

Is a 3 tier data structure too much to ask of SQL's baby brother? Do I
need to return to .NET?????

Hmmm. I think I see what it is that you're trying to do. Using filters is
not the way to go. Instead you're going to be better off using Master/Child
fields to control which records display on the subform. (If you haven't
played with this technique then I'd suggest using the Form Wizard to create
a form/subform (to display clients and projects for example) so that you get
the idea of how it works at a basic level.

To use the same idea in your situation, you're going to need a textbox
(could be anything really, but a textbox is simplest) on the main form. For
here, I'll call it txtCli. This can be hidden in the production version.

When you click a different client on the clients subform, you need to
transfer the key field info back to the main form. That would be something
like...

parent.txtCli=me.clientID

....in the current event of the client subform.

You can then filter the projects subform by settings its master field to
txtCli (OK, it's not really a field, but it works) and its child field to
ClientID.

I'm a .NET programmer trying to throw together a quick Access app but can't
figure out the object reference methods. I've got a main form with two
subforms, each in data sheet view. Datasheet 1 has a client list, datasheet
2 has a project list (multiple prj per client) and the main form has detail
project info. I want to click on a client and have the prj datasheet filter
to only those project for that client. Then click on a project and view the
prj detail on the main form for editing.

In the Form_Current event of the Client subform, I'm trying this:

Forms![MainFrm]![ActiveProjectLst].SourceObject.filter = "select name from
ActiveProjectsQry where ClientID = '" & ActiveClientLst.ClientID & "' "

where MainFrm is the top level form
ActiveProjectLst is the subform container for datasheet 2
ActiveClientLst is the subform container for datasheet 1

My first question is: is this the proper approach? Second: How do I refer
to the filter property of a datasource on a subform????? Also, How do I get
the current ID value out of one subform and use it in the filter for another
subform?

Pls help - I bid 8 hours on this "simple" app and am already 5 hours into it
with no end in sight.

 

[Albert D. Kallal]

I'm a .NET programmer trying to throw together a quick Access app but
can't
figure out the object reference methods.

ms-access has a far more complex and feature rich forms model then does VB.
You need a few months of work to get up to speed to really use the ms-access
forms model. You can't write and be productive in .net in a day, and in fact
since ms-access has a steeper learning curve then does VB..I don't see how
in a few hours you can learn ms-access that requites FAR MORE knowledge then
even does VB to use.

However, lots attack a few things:

You got two sub-forms

datasheet2 only needs the linkmaster/link child set to the other subform
(datasheet1). You don't need a bunch of code to try and filter
this..ms-access will do this for you. However, since it is two sub-forms,
then in datasheet1, when you navigate, you do have to trigger the datasheet2
to requery (if you have a main to sub-form arrangement, then this re-query
is done for you automatic..but not in your case).

So, for the link master/child fields in datasheet2 control, you put:

linkChildFields main_id
(whatever is the name of the field in this sub-form that is used to
relate back to the datasheet1 table)

LinkMasterFields [MasterForm].[form].[ID]
("masterForm" is the name of the contorl you used to hold the
datasheet1. , [form] is the forms property, and should not be changed. [id]
of course is the key id used in datasheet1 Most of the time the control
"masterform" and the sheetname are the same, but they don't have to be..and
is often a source of confusing, so in your case we get:
LinkMasterFields [datasheet1].[form].[ID]

As mentioned, because you are using sub-form, you do have to tell datasheet2
when datasheet1 changes, so, in the datasheets1 on-current event, you go:

me.Parent.datasheet2.Requery

The above will sync the two datasheets (and, note how we only have written
ONE line of code so far!!). This arrangement will also allow you to add
records to datasheet2, and the link field actually gets set for you! (again,
this saves all kinds of code to maintain the relation).

Next, in the datasheet1 on-current, we have to set the parent record to the
project also, and to do so we add the following to the on-current event of
the datasheet1:

me.Parent.Form.RecordSouce = "select * from tblProjcutes where projectID = "
& me.PID

So, up to this point, we are up to two lines of code to make the whole thing
happen. Can you cook up anything REMOTELY close in VB, or even vbn.net that
lets you solve your whole problem and sync everything with two lines of
code?

Further, when the form loads, the sub-forms load first, and could cause an
error (so, lets just ignore errors..and use a resume next). So, I going to
suggest you break down, and write 3 lines of code in total!. So, all we need
to solve this is the following code in the on-current event:

on error resume next
me.Parent.datasheet2.Requery
me.Parent.Form.RecordSource = "select * from tblProjcutes where projectID =
" & me.PID

So, 3 lines gets you the soltion....much less time then it takes to write
this post!!