|
|
I need to have one subform automatically filter information based on the
"filter by selection" of another subform that is open. I copied the
following code and put it in the codebuilder for the form called frmClients.
When I try to filter by selection on frmClients I get the following
Compliance error message: ""Sub or Function not defined" and the "IsLoaded"
is highlighted.
Can you tell me what else needs to be done to make this work.
Private Sub Form_Current()
If IsLoaded("frmInvoices") Then
Forms!frmInvoices.Filter = _
"ClientID = Forms!frmClients!ClientID"
Forms!frmInvoices.FilterOn = True
End If
End Sub
|
|
Did you also copy a function called "IsLoaded"? If not, you need to go back
and grab that and put it in your DB as a function. If you already have the
"IsLoaded" function, is it declared as Private?
"rob c" wrote:
[Quoted Text] > I need to have one subform automatically filter information based on the
> "filter by selection" of another subform that is open. I copied the
> following code and put it in the codebuilder for the form called frmClients.
>
> When I try to filter by selection on frmClients I get the following
> Compliance error message: ""Sub or Function not defined" and the "IsLoaded"
> is highlighted.
>
> Can you tell me what else needs to be done to make this work.
>
>
> Private Sub Form_Current()
>
> If IsLoaded("frmInvoices") Then
> Forms!frmInvoices.Filter = _
> "ClientID = Forms!frmClients!ClientID"
> Forms!frmInvoices.FilterOn = True
> End If
>
> End Sub
|
|
Rob C,
You probably need this is a module:
Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet
view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function
F.Y.I. Do NOT name the module IsLoaded.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"rob c" <robc[ at ]discussions.microsoft.com> wrote in message
news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
[Quoted Text] >I need to have one subform automatically filter information based on the
> "filter by selection" of another subform that is open. I copied the
> following code and put it in the codebuilder for the form called
> frmClients.
>
> When I try to filter by selection on frmClients I get the following
> Compliance error message: ""Sub or Function not defined" and the
> "IsLoaded"
> is highlighted.
>
> Can you tell me what else needs to be done to make this work.
>
>
> Private Sub Form_Current()
>
> If IsLoaded("frmInvoices") Then
> Forms!frmInvoices.Filter = _
> "ClientID = Forms!frmClients!ClientID"
> Forms!frmInvoices.FilterOn = True
> End If
>
> End Sub
|
|
Thanks April,
I don't see a function called "IsLoaded" in the book I used. Can you tell
me where I can find one?
Rob
"April" wrote:
[Quoted Text] > Did you also copy a function called "IsLoaded"? If not, you need to go back
> and grab that and put it in your DB as a function. If you already have the
> "IsLoaded" function, is it declared as Private?
>
>
>
> "rob c" wrote:
>
> > I need to have one subform automatically filter information based on the
> > "filter by selection" of another subform that is open. I copied the
> > following code and put it in the codebuilder for the form called frmClients.
> >
> > When I try to filter by selection on frmClients I get the following
> > Compliance error message: ""Sub or Function not defined" and the "IsLoaded"
> > is highlighted.
> >
> > Can you tell me what else needs to be done to make this work.
> >
> >
> > Private Sub Form_Current()
> >
> > If IsLoaded("frmInvoices") Then
> > Forms!frmInvoices.Filter = _
> > "ClientID = Forms!frmClients!ClientID"
> > Forms!frmInvoices.FilterOn = True
> > End If
> >
> > End Sub
|
|
Gina / April,
That makes the code work, thanks!
It turns out that the code I had doesn't do exactly what I need.
The code I have only shows the record that the curser is sitting on in the
first form. I need to be able to do a sort that could result in several
records and I need to have all those records show up on the second form.
Example is that I sort on a product and end up with several records for that
product in the first form. I would need all those records to show up on the
second form regardless of which record the curser was sitting on.
Do you have any suggestions as to where I could find a code that shows all
the post filter records and not just the one that cursor is pointing to?
Rob
"April" wrote:
[Quoted Text] > Did you also copy a function called "IsLoaded"? If not, you need to go back
> and grab that and put it in your DB as a function. If you already have the
> "IsLoaded" function, is it declared as Private?
>
>
>
> "rob c" wrote:
>
> > I need to have one subform automatically filter information based on the
> > "filter by selection" of another subform that is open. I copied the
> > following code and put it in the codebuilder for the form called frmClients.
> >
> > When I try to filter by selection on frmClients I get the following
> > Compliance error message: ""Sub or Function not defined" and the "IsLoaded"
> > is highlighted.
> >
> > Can you tell me what else needs to be done to make this work.
> >
> >
> > Private Sub Form_Current()
> >
> > If IsLoaded("frmInvoices") Then
> > Forms!frmInvoices.Filter = _
> > "ClientID = Forms!frmClients!ClientID"
> > Forms!frmInvoices.FilterOn = True
> > End If
> >
> > End Sub
|
|
Gina / April,
That makes the code work, thanks!
It turns out that the code I had doesn't do exactly what I need.
The code I have only shows the record that the curser is sitting on in the
first form. I need to be able to do a sort that could result in several
records and I need to have all those records show up on the second form.
Example is that I sort on a product and end up with several records for that
product in the first form. I would need all those records to show up on the
second form regardless of which record the curser was sitting on.
Do you have any suggestions as to where I could find a code that shows all
the post filter records and not just the one that cursor is pointing to?
"Gina Whipp" wrote:
[Quoted Text] > Rob C,
>
> You probably need this is a module:
>
> Function IsLoaded(ByVal strFormName As String) As Boolean
> ' Returns True if the specified form is open in Form view or Datasheet
> view.
>
> Const conObjStateClosed = 0
> Const conDesignView = 0
>
> If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
> conObjStateClosed Then
> If Forms(strFormName).CurrentView <> conDesignView Then
> IsLoaded = True
> End If
> End If
>
> End Function
>
> F.Y.I. Do NOT name the module IsLoaded.
>
> --
> Gina Whipp
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
> >I need to have one subform automatically filter information based on the
> > "filter by selection" of another subform that is open. I copied the
> > following code and put it in the codebuilder for the form called
> > frmClients.
> >
> > When I try to filter by selection on frmClients I get the following
> > Compliance error message: ""Sub or Function not defined" and the
> > "IsLoaded"
> > is highlighted.
> >
> > Can you tell me what else needs to be done to make this work.
> >
> >
> > Private Sub Form_Current()
> >
> > If IsLoaded("frmInvoices") Then
> > Forms!frmInvoices.Filter = _
> > "ClientID = Forms!frmClients!ClientID"
> > Forms!frmInvoices.FilterOn = True
> > End If
> >
> > End Sub
>
>
>
|
|
Maybe what you need is:
Private Sub Form_Current()
If IsLoaded("frmInvoices") Then
DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID ' If numeric
DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" & ClientID & "'"
'If text
End If
End Sub
However, a few notes here. I do not know if you Client ID field is text or
numeric, see above. Do NOT use both lines, select the correct one and
delete the other. I am also not certain that Client ID is the same name on
both forms, so adjustments accordingly.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"rob c" <robc[ at ]discussions.microsoft.com> wrote in message
news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
[Quoted Text] > Gina / April,
>
> That makes the code work, thanks!
>
> It turns out that the code I had doesn't do exactly what I need.
>
> The code I have only shows the record that the curser is sitting on in the
> first form. I need to be able to do a sort that could result in several
> records and I need to have all those records show up on the second form.
>
> Example is that I sort on a product and end up with several records for
> that
> product in the first form. I would need all those records to show up on
> the
> second form regardless of which record the curser was sitting on.
>
> Do you have any suggestions as to where I could find a code that shows all
> the post filter records and not just the one that cursor is pointing to?
>
>
> "Gina Whipp" wrote:
>
>> Rob C,
>>
>> You probably need this is a module:
>>
>> Function IsLoaded(ByVal strFormName As String) As Boolean
>> ' Returns True if the specified form is open in Form view or Datasheet
>> view.
>>
>> Const conObjStateClosed = 0
>> Const conDesignView = 0
>>
>> If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
>> conObjStateClosed Then
>> If Forms(strFormName).CurrentView <> conDesignView Then
>> IsLoaded = True
>> End If
>> End If
>>
>> End Function
>>
>> F.Y.I. Do NOT name the module IsLoaded.
>>
>> --
>> Gina Whipp
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors
>> II
>> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
>> >I need to have one subform automatically filter information based on the
>> > "filter by selection" of another subform that is open. I copied the
>> > following code and put it in the codebuilder for the form called
>> > frmClients.
>> >
>> > When I try to filter by selection on frmClients I get the following
>> > Compliance error message: ""Sub or Function not defined" and the
>> > "IsLoaded"
>> > is highlighted.
>> >
>> > Can you tell me what else needs to be done to make this work.
>> >
>> >
>> > Private Sub Form_Current()
>> >
>> > If IsLoaded("frmInvoices") Then
>> > Forms!frmInvoices.Filter = _
>> > "ClientID = Forms!frmClients!ClientID"
>> > Forms!frmInvoices.FilterOn = True
>> > End If
>> >
>> > End Sub
>>
>>
>>
|
|
Gina,
Thank you for the help.
The field name is ClientID for both forms.
The type is numeric on both forms, I used"
Private Sub Form_Current()
If IsLoaded("frmInvoices") Then
DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID
End If
End Sub
and have the "Private Sub Form_Current()" that you provided earlier in
Module 1 .
The frmInvoices, does not change at all when perform a filter by selection
on frmClients.
Along with the current
"Gina Whipp" wrote:
[Quoted Text] > Maybe what you need is:
>
> Private Sub Form_Current()
>
> If IsLoaded("frmInvoices") Then
> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID ' If numeric
> DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" & ClientID & "'"
> 'If text
> End If
>
> End Sub
>
> However, a few notes here. I do not know if you Client ID field is text or
> numeric, see above. Do NOT use both lines, select the correct one and
> delete the other. I am also not certain that Client ID is the same name on
> both forms, so adjustments accordingly.
> --
> Gina Whipp
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>
> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
> > Gina / April,
> >
> > That makes the code work, thanks!
> >
> > It turns out that the code I had doesn't do exactly what I need.
> >
> > The code I have only shows the record that the curser is sitting on in the
> > first form. I need to be able to do a sort that could result in several
> > records and I need to have all those records show up on the second form.
> >
> > Example is that I sort on a product and end up with several records for
> > that
> > product in the first form. I would need all those records to show up on
> > the
> > second form regardless of which record the curser was sitting on.
> >
> > Do you have any suggestions as to where I could find a code that shows all
> > the post filter records and not just the one that cursor is pointing to?
> >
> >
> > "Gina Whipp" wrote:
> >
> >> Rob C,
> >>
> >> You probably need this is a module:
> >>
> >> Function IsLoaded(ByVal strFormName As String) As Boolean
> >> ' Returns True if the specified form is open in Form view or Datasheet
> >> view.
> >>
> >> Const conObjStateClosed = 0
> >> Const conDesignView = 0
> >>
> >> If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
> >> conObjStateClosed Then
> >> If Forms(strFormName).CurrentView <> conDesignView Then
> >> IsLoaded = True
> >> End If
> >> End If
> >>
> >> End Function
> >>
> >> F.Y.I. Do NOT name the module IsLoaded.
> >>
> >> --
> >> Gina Whipp
> >>
> >> "I feel I have been denied critical, need to know, information!" -
> >> Tremors
> >> II
> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
> >> >I need to have one subform automatically filter information based on the
> >> > "filter by selection" of another subform that is open. I copied the
> >> > following code and put it in the codebuilder for the form called
> >> > frmClients.
> >> >
> >> > When I try to filter by selection on frmClients I get the following
> >> > Compliance error message: ""Sub or Function not defined" and the
> >> > "IsLoaded"
> >> > is highlighted.
> >> >
> >> > Can you tell me what else needs to be done to make this work.
> >> >
> >> >
> >> > Private Sub Form_Current()
> >> >
> >> > If IsLoaded("frmInvoices") Then
> >> > Forms!frmInvoices.Filter = _
> >> > "ClientID = Forms!frmClients!ClientID"
> >> > Forms!frmInvoices.FilterOn = True
> >> > End If
> >> >
> >> > End Sub
> >>
> >>
> >>
>
>
>
|
|
Rob,
You know I just noticed you have this on the Form_Current() event. I am a
bit confused as to what you are trying to do... You have frmInvoices opened
and you want frmClients to open at the same time filtered only the Client
that is selected??? How does the frmClients know which one if the
frmInvoices is opening? Perhaps providing more detail will help get you the
solution you are looking for...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"rob c" <robc[ at ]discussions.microsoft.com> wrote in message
news:3CE9054F-55F2-47DF-BE40-56EC71AD8C09[ at ]microsoft.com...
[Quoted Text] > Gina,
> Thank you for the help.
>
> The field name is ClientID for both forms.
> The type is numeric on both forms, I used"
>
> Private Sub Form_Current()
>
> If IsLoaded("frmInvoices") Then
> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID
> End If
>
> End Sub
>
> and have the "Private Sub Form_Current()" that you provided earlier in
> Module 1 .
>
> The frmInvoices, does not change at all when perform a filter by selection
> on frmClients.
>
> Along with the current
>
> "Gina Whipp" wrote:
>
>> Maybe what you need is:
>>
>> Private Sub Form_Current()
>>
>> If IsLoaded("frmInvoices") Then
>> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID ' If
>> numeric
>> DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" & ClientID & "'"
>> 'If text
>> End If
>>
>> End Sub
>>
>> However, a few notes here. I do not know if you Client ID field is text
>> or
>> numeric, see above. Do NOT use both lines, select the correct one and
>> delete the other. I am also not certain that Client ID is the same name
>> on
>> both forms, so adjustments accordingly.
>> --
>> Gina Whipp
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors
>> II
>>
>> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
>> > Gina / April,
>> >
>> > That makes the code work, thanks!
>> >
>> > It turns out that the code I had doesn't do exactly what I need.
>> >
>> > The code I have only shows the record that the curser is sitting on in
>> > the
>> > first form. I need to be able to do a sort that could result in
>> > several
>> > records and I need to have all those records show up on the second
>> > form.
>> >
>> > Example is that I sort on a product and end up with several records for
>> > that
>> > product in the first form. I would need all those records to show up
>> > on
>> > the
>> > second form regardless of which record the curser was sitting on.
>> >
>> > Do you have any suggestions as to where I could find a code that shows
>> > all
>> > the post filter records and not just the one that cursor is pointing
>> > to?
>> >
>> >
>> > "Gina Whipp" wrote:
>> >
>> >> Rob C,
>> >>
>> >> You probably need this is a module:
>> >>
>> >> Function IsLoaded(ByVal strFormName As String) As Boolean
>> >> ' Returns True if the specified form is open in Form view or
>> >> Datasheet
>> >> view.
>> >>
>> >> Const conObjStateClosed = 0
>> >> Const conDesignView = 0
>> >>
>> >> If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
>> >> conObjStateClosed Then
>> >> If Forms(strFormName).CurrentView <> conDesignView Then
>> >> IsLoaded = True
>> >> End If
>> >> End If
>> >>
>> >> End Function
>> >>
>> >> F.Y.I. Do NOT name the module IsLoaded.
>> >>
>> >> --
>> >> Gina Whipp
>> >>
>> >> "I feel I have been denied critical, need to know, information!" -
>> >> Tremors
>> >> II
>> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
>> >> >I need to have one subform automatically filter information based on
>> >> >the
>> >> > "filter by selection" of another subform that is open. I copied the
>> >> > following code and put it in the codebuilder for the form called
>> >> > frmClients.
>> >> >
>> >> > When I try to filter by selection on frmClients I get the following
>> >> > Compliance error message: ""Sub or Function not defined" and the
>> >> > "IsLoaded"
>> >> > is highlighted.
>> >> >
>> >> > Can you tell me what else needs to be done to make this work.
>> >> >
>> >> >
>> >> > Private Sub Form_Current()
>> >> >
>> >> > If IsLoaded("frmInvoices") Then
>> >> > Forms!frmInvoices.Filter = _
>> >> > "ClientID = Forms!frmClients!ClientID"
>> >> > Forms!frmInvoices.FilterOn = True
>> >> > End If
>> >> >
>> >> > End Sub
>> >>
>> >>
>> >>
>>
>>
>>
|
|
Gina,
I'll give some history.
(I used ClientID when I initially asked for help because that is what was
used in the book and I wanted to understand what I needed to do and see it
work before using it on the database I am working on)
I have a table that contains information for projects. Each project has one
record. The end users need to be able to perform various filters and see
what the subtotals are on various fields that have dollar amounts in them and
to be able to perform various filters and see the new totals then make
changes to dollar amounts as necessary.
The endusers are not allowed to touch the actual table.
My first try was to create a query which duplicated the table that would be
filtered and then have a second query showing only the "sums" based on the
first query This did not work because the totals did not change in the
second Query when I performed "filter by selection" on the first Query.
I then tried to put the entire query onto a form, but I got an error message
from Access which said that there were too many fields for the form.
My current attempt is to put two subforms onto a form. Both subforms are
initially populated based on the endusers login and sorted by project
number. Since each project is on both subforms once and only once, all the
information for each project is in line on the screen.
Both subforms are based on the same table and so they have a "Project
number" that is one-to-one. My plan was both subforms would be open anytime
the main form is open. and that when a "filter by selection" is performed on
something like a product or a region there would be certain projects left and
that the second subform would have those same projects.
If there is a better or simpler method, I am open to it.
Thank you
Rob
"Gina Whipp" wrote:
[Quoted Text] > Rob,
>
> You know I just noticed you have this on the Form_Current() event. I am a
> bit confused as to what you are trying to do... You have frmInvoices opened
> and you want frmClients to open at the same time filtered only the Client
> that is selected??? How does the frmClients know which one if the
> frmInvoices is opening? Perhaps providing more detail will help get you the
> solution you are looking for...
>
> --
> Gina Whipp
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> news:3CE9054F-55F2-47DF-BE40-56EC71AD8C09[ at ]microsoft.com...
> > Gina,
> > Thank you for the help.
> >
> > The field name is ClientID for both forms.
> > The type is numeric on both forms, I used"
> >
> > Private Sub Form_Current()
> >
> > If IsLoaded("frmInvoices") Then
> > DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID
> > End If
> >
> > End Sub
> >
> > and have the "Private Sub Form_Current()" that you provided earlier in
> > Module 1 .
> >
> > The frmInvoices, does not change at all when perform a filter by selection
> > on frmClients.
> >
> > Along with the current
> >
> > "Gina Whipp" wrote:
> >
> >> Maybe what you need is:
> >>
> >> Private Sub Form_Current()
> >>
> >> If IsLoaded("frmInvoices") Then
> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID ' If
> >> numeric
> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" & ClientID & "'"
> >> 'If text
> >> End If
> >>
> >> End Sub
> >>
> >> However, a few notes here. I do not know if you Client ID field is text
> >> or
> >> numeric, see above. Do NOT use both lines, select the correct one and
> >> delete the other. I am also not certain that Client ID is the same name
> >> on
> >> both forms, so adjustments accordingly.
> >> --
> >> Gina Whipp
> >>
> >> "I feel I have been denied critical, need to know, information!" -
> >> Tremors
> >> II
> >>
> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
> >> > Gina / April,
> >> >
> >> > That makes the code work, thanks!
> >> >
> >> > It turns out that the code I had doesn't do exactly what I need.
> >> >
> >> > The code I have only shows the record that the curser is sitting on in
> >> > the
> >> > first form. I need to be able to do a sort that could result in
> >> > several
> >> > records and I need to have all those records show up on the second
> >> > form.
> >> >
> >> > Example is that I sort on a product and end up with several records for
> >> > that
> >> > product in the first form. I would need all those records to show up
> >> > on
> >> > the
> >> > second form regardless of which record the curser was sitting on.
> >> >
> >> > Do you have any suggestions as to where I could find a code that shows
> >> > all
> >> > the post filter records and not just the one that cursor is pointing
> >> > to?
> >> >
> >> >
> >> > "Gina Whipp" wrote:
> >> >
> >> >> Rob C,
> >> >>
> >> >> You probably need this is a module:
> >> >>
> >> >> Function IsLoaded(ByVal strFormName As String) As Boolean
> >> >> ' Returns True if the specified form is open in Form view or
> >> >> Datasheet
> >> >> view.
> >> >>
> >> >> Const conObjStateClosed = 0
> >> >> Const conDesignView = 0
> >> >>
> >> >> If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
> >> >> conObjStateClosed Then
> >> >> If Forms(strFormName).CurrentView <> conDesignView Then
> >> >> IsLoaded = True
> >> >> End If
> >> >> End If
> >> >>
> >> >> End Function
> >> >>
> >> >> F.Y.I. Do NOT name the module IsLoaded.
> >> >>
> >> >> --
> >> >> Gina Whipp
> >> >>
> >> >> "I feel I have been denied critical, need to know, information!" -
> >> >> Tremors
> >> >> II
> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> >> news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
> >> >> >I need to have one subform automatically filter information based on
> >> >> >the
> >> >> > "filter by selection" of another subform that is open. I copied the
> >> >> > following code and put it in the codebuilder for the form called
> >> >> > frmClients.
> >> >> >
> >> >> > When I try to filter by selection on frmClients I get the following
> >> >> > Compliance error message: ""Sub or Function not defined" and the
> >> >> > "IsLoaded"
> >> >> > is highlighted.
> >> >> >
> >> >> > Can you tell me what else needs to be done to make this work.
> >> >> >
> >> >> >
> >> >> > Private Sub Form_Current()
> >> >> >
> >> >> > If IsLoaded("frmInvoices") Then
> >> >> > Forms!frmInvoices.Filter = _
> >> >> > "ClientID = Forms!frmClients!ClientID"
> >> >> > Forms!frmInvoices.FilterOn = True
> >> >> > End If
> >> >> >
> >> >> > End Sub
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
|
|
Rob,
Based on what I am reading sounds like your table structure got off to a
rocky start and should probably be evaluated. But let's move on... Okay
then where do the Projects come from? In previous messages you talked of
frmInvoices and frmClients. OR do the products show on frmInvoices. Is the
Main form the Projects?
Is the object of this database to track/view Projects submitted by Clients
and invoiced to Clients? See additonal comments below...
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"rob c" <robc[ at ]discussions.microsoft.com> wrote in message
news:DF3469D5-1125-4BE4-8564-714D3122F94D[ at ]microsoft.com...
[Quoted Text] > Gina,
>
> I'll give some history.
>
> (I used ClientID when I initially asked for help because that is what was
> used in the book and I wanted to understand what I needed to do and see it
> work before using it on the database I am working on)
>
> I have a table that contains information for projects. Each project has
> one
> record. The end users need to be able to perform various filters and see
> what the subtotals are on various fields that have dollar amounts in them
> and
> to be able to perform various filters and see the new totals then make
> changes to dollar amounts as necessary.
>
> The endusers are not allowed to touch the actual table.
>
> My first try was to create a query which duplicated the table that would
> be
> filtered and then have a second query showing only the "sums" based on the
> first query This did not work because the totals did not change in the
> second Query when I performed "filter by selection" on the first Query.
This should have worked but not sure how you are doing the 'filter by
slection'
>
> I then tried to put the entire query onto a form, but I got an error
> message
> from Access which said that there were too many fields for the form.
Well, that depends, I get that message every blue moon and since I REALLY
need that many fields I ignore it but in your case it might be helpful to
know how many fields you were trying to put on the form.
>
> My current attempt is to put two subforms onto a form. Both subforms are
> initially populated based on the endusers login and sorted by project
> number. Since each project is on both subforms once and only once, all
> the
> information for each project is in line on the screen.
I am not sure why you would want both subforms showing identical
information, see below.
>
> Both subforms are based on the same table and so they have a "Project
> number" that is one-to-one. My plan was both subforms would be open
> anytime
> the main form is open. and that when a "filter by selection" is performed
> on
> something like a product or a region there would be certain projects left
> and
> that the second subform would have those same projects.
The second subform should be linked Master/Child to the second subform which
should give you the 'filter' you want.
>
> If there is a better or simpler method, I am open to it.
I don't know about simpler method because I'm not sure of your table
structure. If I want to filter on filtered information I sometimes use the
'double-click - open pop-up with filter information' technique. The
information can be altered and then upon closing it requeries the main form
thereby updating the totals/information/etc...
>
> Thank you
>
> Rob
>
>
>
> "Gina Whipp" wrote:
>
>> Rob,
>>
>> You know I just noticed you have this on the Form_Current() event. I am
>> a
>> bit confused as to what you are trying to do... You have frmInvoices
>> opened
>> and you want frmClients to open at the same time filtered only the Client
>> that is selected??? How does the frmClients know which one if the
>> frmInvoices is opening? Perhaps providing more detail will help get you
>> the
>> solution you are looking for...
>>
>> --
>> Gina Whipp
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors
>> II
>> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> news:3CE9054F-55F2-47DF-BE40-56EC71AD8C09[ at ]microsoft.com...
>> > Gina,
>> > Thank you for the help.
>> >
>> > The field name is ClientID for both forms.
>> > The type is numeric on both forms, I used"
>> >
>> > Private Sub Form_Current()
>> >
>> > If IsLoaded("frmInvoices") Then
>> > DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID
>> > End If
>> >
>> > End Sub
>> >
>> > and have the "Private Sub Form_Current()" that you provided earlier in
>> > Module 1 .
>> >
>> > The frmInvoices, does not change at all when perform a filter by
>> > selection
>> > on frmClients.
>> >
>> > Along with the current
>> >
>> > "Gina Whipp" wrote:
>> >
>> >> Maybe what you need is:
>> >>
>> >> Private Sub Form_Current()
>> >>
>> >> If IsLoaded("frmInvoices") Then
>> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID ' If
>> >> numeric
>> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" & ClientID &
>> >> "'"
>> >> 'If text
>> >> End If
>> >>
>> >> End Sub
>> >>
>> >> However, a few notes here. I do not know if you Client ID field is
>> >> text
>> >> or
>> >> numeric, see above. Do NOT use both lines, select the correct one and
>> >> delete the other. I am also not certain that Client ID is the same
>> >> name
>> >> on
>> >> both forms, so adjustments accordingly.
>> >> --
>> >> Gina Whipp
>> >>
>> >> "I feel I have been denied critical, need to know, information!" -
>> >> Tremors
>> >> II
>> >>
>> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
>> >> > Gina / April,
>> >> >
>> >> > That makes the code work, thanks!
>> >> >
>> >> > It turns out that the code I had doesn't do exactly what I need.
>> >> >
>> >> > The code I have only shows the record that the curser is sitting on
>> >> > in
>> >> > the
>> >> > first form. I need to be able to do a sort that could result in
>> >> > several
>> >> > records and I need to have all those records show up on the second
>> >> > form.
>> >> >
>> >> > Example is that I sort on a product and end up with several records
>> >> > for
>> >> > that
>> >> > product in the first form. I would need all those records to show
>> >> > up
>> >> > on
>> >> > the
>> >> > second form regardless of which record the curser was sitting on.
>> >> >
>> >> > Do you have any suggestions as to where I could find a code that
>> >> > shows
>> >> > all
>> >> > the post filter records and not just the one that cursor is pointing
>> >> > to?
>> >> >
>> >> >
>> >> > "Gina Whipp" wrote:
>> >> >
>> >> >> Rob C,
>> >> >>
>> >> >> You probably need this is a module:
>> >> >>
>> >> >> Function IsLoaded(ByVal strFormName As String) As Boolean
>> >> >> ' Returns True if the specified form is open in Form view or
>> >> >> Datasheet
>> >> >> view.
>> >> >>
>> >> >> Const conObjStateClosed = 0
>> >> >> Const conDesignView = 0
>> >> >>
>> >> >> If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
>> >> >> conObjStateClosed Then
>> >> >> If Forms(strFormName).CurrentView <> conDesignView Then
>> >> >> IsLoaded = True
>> >> >> End If
>> >> >> End If
>> >> >>
>> >> >> End Function
>> >> >>
>> >> >> F.Y.I. Do NOT name the module IsLoaded.
>> >> >>
>> >> >> --
>> >> >> Gina Whipp
>> >> >>
>> >> >> "I feel I have been denied critical, need to know, information!" -
>> >> >> Tremors
>> >> >> II
>> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> >> news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
>> >> >> >I need to have one subform automatically filter information based
>> >> >> >on
>> >> >> >the
>> >> >> > "filter by selection" of another subform that is open. I copied
>> >> >> > the
>> >> >> > following code and put it in the codebuilder for the form called
>> >> >> > frmClients.
>> >> >> >
>> >> >> > When I try to filter by selection on frmClients I get the
>> >> >> > following
>> >> >> > Compliance error message: ""Sub or Function not defined" and
>> >> >> > the
>> >> >> > "IsLoaded"
>> >> >> > is highlighted.
>> >> >> >
>> >> >> > Can you tell me what else needs to be done to make this work.
>> >> >> >
>> >> >> >
>> >> >> > Private Sub Form_Current()
>> >> >> >
>> >> >> > If IsLoaded("frmInvoices") Then
>> >> >> > Forms!frmInvoices.Filter = _
>> >> >> > "ClientID = Forms!frmClients!ClientID"
>> >> >> > Forms!frmInvoices.FilterOn = True
>> >> >> > End If
>> >> >> >
>> >> >> > End Sub
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
|
Gina,
Thank you for your patience.
The frmInvoices and frm Clients were in a book I was trying to learn from.
They are not part of the database I am working on.
The only thing I am using the main form for is to show the name of the
person who will be working with the information in the subform and to make
sure they don’t do work on something with someone elses name
The objects of the database is to allow project managers to make changes to
records that are already on the table that is being used. In the past
project managers were sending emails to one person and that person made
changes directly into a table. The info was exported to excel, mailed to the
project managers, then the process repeated. Management decided that it
would be better to have a form so that the project managers could make their
own changes in the fields they are responsible for, but not in fields they
are not responsible for. They would like the form to look as much like an
excel spreadsheet as possible.
For filter by selection, I am clicking on a cell and then clicking on the
icon in the toolbar which says “filter by selection†when hovering over the
icon.
Thank you for the suggestions.
Regards,
Rob
"Gina Whipp" wrote:
[Quoted Text] > Rob,
>
> Based on what I am reading sounds like your table structure got off to a
> rocky start and should probably be evaluated. But let's move on... Okay
> then where do the Projects come from? In previous messages you talked of
> frmInvoices and frmClients. OR do the products show on frmInvoices. Is the
> Main form the Projects?
>
> Is the object of this database to track/view Projects submitted by Clients
> and invoiced to Clients? See additonal comments below...
>
> --
> Gina Whipp
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> news:DF3469D5-1125-4BE4-8564-714D3122F94D[ at ]microsoft.com...
> > Gina,
> >
> > I'll give some history.
> >
> > (I used ClientID when I initially asked for help because that is what was
> > used in the book and I wanted to understand what I needed to do and see it
> > work before using it on the database I am working on)
> >
> > I have a table that contains information for projects. Each project has
> > one
> > record. The end users need to be able to perform various filters and see
> > what the subtotals are on various fields that have dollar amounts in them
> > and
> > to be able to perform various filters and see the new totals then make
> > changes to dollar amounts as necessary.
> >
> > The endusers are not allowed to touch the actual table.
> >
> > My first try was to create a query which duplicated the table that would
> > be
> > filtered and then have a second query showing only the "sums" based on the
> > first query This did not work because the totals did not change in the
> > second Query when I performed "filter by selection" on the first Query.
>
> This should have worked but not sure how you are doing the 'filter by
> slection'
>
> >
> > I then tried to put the entire query onto a form, but I got an error
> > message
> > from Access which said that there were too many fields for the form.
>
> Well, that depends, I get that message every blue moon and since I REALLY
> need that many fields I ignore it but in your case it might be helpful to
> know how many fields you were trying to put on the form.
>
> >
> > My current attempt is to put two subforms onto a form. Both subforms are
> > initially populated based on the endusers login and sorted by project
> > number. Since each project is on both subforms once and only once, all
> > the
> > information for each project is in line on the screen.
>
> I am not sure why you would want both subforms showing identical
> information, see below.
>
> >
> > Both subforms are based on the same table and so they have a "Project
> > number" that is one-to-one. My plan was both subforms would be open
> > anytime
> > the main form is open. and that when a "filter by selection" is performed
> > on
> > something like a product or a region there would be certain projects left
> > and
> > that the second subform would have those same projects.
>
> The second subform should be linked Master/Child to the second subform which
> should give you the 'filter' you want.
>
> >
> > If there is a better or simpler method, I am open to it.
>
> I don't know about simpler method because I'm not sure of your table
> structure. If I want to filter on filtered information I sometimes use the
> 'double-click - open pop-up with filter information' technique. The
> information can be altered and then upon closing it requeries the main form
> thereby updating the totals/information/etc...
>
> >
> > Thank you
> >
> > Rob
> >
> >
> >
> > "Gina Whipp" wrote:
> >
> >> Rob,
> >>
> >> You know I just noticed you have this on the Form_Current() event. I am
> >> a
> >> bit confused as to what you are trying to do... You have frmInvoices
> >> opened
> >> and you want frmClients to open at the same time filtered only the Client
> >> that is selected??? How does the frmClients know which one if the
> >> frmInvoices is opening? Perhaps providing more detail will help get you
> >> the
> >> solution you are looking for...
> >>
> >> --
> >> Gina Whipp
> >>
> >> "I feel I have been denied critical, need to know, information!" -
> >> Tremors
> >> II
> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> news:3CE9054F-55F2-47DF-BE40-56EC71AD8C09[ at ]microsoft.com...
> >> > Gina,
> >> > Thank you for the help.
> >> >
> >> > The field name is ClientID for both forms.
> >> > The type is numeric on both forms, I used"
> >> >
> >> > Private Sub Form_Current()
> >> >
> >> > If IsLoaded("frmInvoices") Then
> >> > DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID
> >> > End If
> >> >
> >> > End Sub
> >> >
> >> > and have the "Private Sub Form_Current()" that you provided earlier in
> >> > Module 1 .
> >> >
> >> > The frmInvoices, does not change at all when perform a filter by
> >> > selection
> >> > on frmClients.
> >> >
> >> > Along with the current
> >> >
> >> > "Gina Whipp" wrote:
> >> >
> >> >> Maybe what you need is:
> >> >>
> >> >> Private Sub Form_Current()
> >> >>
> >> >> If IsLoaded("frmInvoices") Then
> >> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID ' If
> >> >> numeric
> >> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" & ClientID &
> >> >> "'"
> >> >> 'If text
> >> >> End If
> >> >>
> >> >> End Sub
> >> >>
> >> >> However, a few notes here. I do not know if you Client ID field is
> >> >> text
> >> >> or
> >> >> numeric, see above. Do NOT use both lines, select the correct one and
> >> >> delete the other. I am also not certain that Client ID is the same
> >> >> name
> >> >> on
> >> >> both forms, so adjustments accordingly.
> >> >> --
> >> >> Gina Whipp
> >> >>
> >> >> "I feel I have been denied critical, need to know, information!" -
> >> >> Tremors
> >> >> II
> >> >>
> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> >> news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
> >> >> > Gina / April,
> >> >> >
> >> >> > That makes the code work, thanks!
> >> >> >
> >> >> > It turns out that the code I had doesn't do exactly what I need.
> >> >> >
> >> >> > The code I have only shows the record that the curser is sitting on
> >> >> > in
> >> >> > the
> >> >> > first form. I need to be able to do a sort that could result in
> >> >> > several
> >> >> > records and I need to have all those records show up on the second
> >> >> > form.
> >> >> >
> >> >> > Example is that I sort on a product and end up with several records
> >> >> > for
> >> >> > that
> >> >> > product in the first form. I would need all those records to show
> >> >> > up
> >> >> > on
> >> >> > the
> >> >> > second form regardless of which record the curser was sitting on.
> >> >> >
> >> >> > Do you have any suggestions as to where I could find a code that
> >> >> > shows
> >> >> > all
> >> >> > the post filter records and not just the one that cursor is pointing
> >> >> > to?
> >> >> >
> >> >> >
> >> >> > "Gina Whipp" wrote:
> >> >> >
> >> >> >> Rob C,
> >> >> >>
> >> >> >> You probably need this is a module:
> >> >> >>
> >> >> >> Function IsLoaded(ByVal strFormName As String) As Boolean
> >> >> >> ' Returns True if the specified form is open in Form view or
> >> >> >> Datasheet
> >> >> >> view.
> >> >> >>
> >> >> >> Const conObjStateClosed = 0
> >> >> >> Const conDesignView = 0
> >> >> >>
> >> >> >> If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
> >> >> >> conObjStateClosed Then
> >> >> >> If Forms(strFormName).CurrentView <> conDesignView Then
> >> >> >> IsLoaded = True
> >> >> >> End If
> >> >> >> End If
> >> >> >>
> >> >> >> End Function
> >> >> >>
> >> >> >> F.Y.I. Do NOT name the module IsLoaded.
> >> >> >>
> >> >> >> --
> >> >> >> Gina Whipp
> >> >> >>
> >> >> >> "I feel I have been denied critical, need to know, information!" -
> >> >> >> Tremors
> >> >> >> II
> >> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> >> >> news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
> >> >> >> >I need to have one subform automatically filter information based
> >> >> >> >on
> >> >> >> >the
> >> >> >> > "filter by selection" of another subform that is open. I copied
> >> >> >> > the
> >> >> >> > following code and put it in the codebuilder for the form called
> >> >> >> > frmClients.
> >> >> >> >
> >> >> >> > When I try to filter by selection on frmClients I get the
> >> >> >> > following
> >> >> >> > Compliance error message: ""Sub or Function not defined" and
> >> >> >> > the
> >> >> >> > "IsLoaded"
> >> >> >> > is highlighted.
> >> >> >> >
> >> >> >> > Can you tell me what else needs to be done to make this work.
> >> >> >> >
> >> >> >> >
> >> >> >> > Private Sub Form_Current()
> >> >> >> >
> >> >> >> > If IsLoaded("frmInvoices") Then
> >> >> >> > Forms!frmInvoices.Filter = _
> >> >> >> > "ClientID = Forms!frmClients!ClientID"
> >> >> >> > Forms!frmInvoices.FilterOn = True
> >> >> >> > End If
> >> >> >> >
> >> >> >> > End Sub
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
|
|
Rob,
Okay then for clarity I think you need to only stick to the names of the
forms in your database and what you are trying to do. It's gets a little
confusing when you mix apples (book project) and oranges (your project).
What about my double clicking idea, instead of...
[Quoted Text] > For filter by selection, I am clicking on a cell and then clicking on the
> icon in the toolbar which says "filter by selection" when hovering over
> the
> icon.
....allow the person to double-click in the field (only called cell in Excel)
and have a form open for editing purposes?
On another note... sounds like Management doesn't realize Access is not
Excel on steriods and operates in a VERY different way. But that does
explain why you are doing it the way you are doing it.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"rob c" <robc[ at ]discussions.microsoft.com> wrote in message
news:64D08F03-92FC-442A-8CAB-BCB04F97E864[ at ]microsoft.com...
> Gina,
>
> Thank you for your patience.
>
> The frmInvoices and frm Clients were in a book I was trying to learn from.
> They are not part of the database I am working on.
>
> The only thing I am using the main form for is to show the name of the
> person who will be working with the information in the subform and to make
> sure they don't do work on something with someone elses name
>
> The objects of the database is to allow project managers to make changes
> to
> records that are already on the table that is being used. In the past
> project managers were sending emails to one person and that person made
> changes directly into a table. The info was exported to excel, mailed to
> the
> project managers, then the process repeated. Management decided that it
> would be better to have a form so that the project managers could make
> their
> own changes in the fields they are responsible for, but not in fields they
> are not responsible for. They would like the form to look as much like an
> excel spreadsheet as possible.
>
> For filter by selection, I am clicking on a cell and then clicking on the
> icon in the toolbar which says "filter by selection" when hovering over
> the
> icon.
>
> Thank you for the suggestions.
>
> Regards,
>
> Rob
>
>
> "Gina Whipp" wrote:
>
>> Rob,
>>
>> Based on what I am reading sounds like your table structure got off to a
>> rocky start and should probably be evaluated. But let's move on... Okay
>> then where do the Projects come from? In previous messages you talked of
>> frmInvoices and frmClients. OR do the products show on frmInvoices. Is
>> the
>> Main form the Projects?
>>
>> Is the object of this database to track/view Projects submitted by
>> Clients
>> and invoiced to Clients? See additonal comments below...
>>
>> --
>> Gina Whipp
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors
>> II
>> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> news:DF3469D5-1125-4BE4-8564-714D3122F94D[ at ]microsoft.com...
>> > Gina,
>> >
>> > I'll give some history.
>> >
>> > (I used ClientID when I initially asked for help because that is what
>> > was
>> > used in the book and I wanted to understand what I needed to do and see
>> > it
>> > work before using it on the database I am working on)
>> >
>> > I have a table that contains information for projects. Each project
>> > has
>> > one
>> > record. The end users need to be able to perform various filters and
>> > see
>> > what the subtotals are on various fields that have dollar amounts in
>> > them
>> > and
>> > to be able to perform various filters and see the new totals then make
>> > changes to dollar amounts as necessary.
>> >
>> > The endusers are not allowed to touch the actual table.
>> >
>> > My first try was to create a query which duplicated the table that
>> > would
>> > be
>> > filtered and then have a second query showing only the "sums" based on
>> > the
>> > first query This did not work because the totals did not change in
>> > the
>> > second Query when I performed "filter by selection" on the first Query.
>>
>> This should have worked but not sure how you are doing the 'filter by
>> slection'
>>
>> >
>> > I then tried to put the entire query onto a form, but I got an error
>> > message
>> > from Access which said that there were too many fields for the form.
>>
>> Well, that depends, I get that message every blue moon and since I REALLY
>> need that many fields I ignore it but in your case it might be helpful to
>> know how many fields you were trying to put on the form.
>>
>> >
>> > My current attempt is to put two subforms onto a form. Both subforms
>> > are
>> > initially populated based on the endusers login and sorted by project
>> > number. Since each project is on both subforms once and only once, all
>> > the
>> > information for each project is in line on the screen.
>>
>> I am not sure why you would want both subforms showing identical
>> information, see below.
>>
>> >
>> > Both subforms are based on the same table and so they have a "Project
>> > number" that is one-to-one. My plan was both subforms would be open
>> > anytime
>> > the main form is open. and that when a "filter by selection" is
>> > performed
>> > on
>> > something like a product or a region there would be certain projects
>> > left
>> > and
>> > that the second subform would have those same projects.
>>
>> The second subform should be linked Master/Child to the second subform
>> which
>> should give you the 'filter' you want.
>>
>> >
>> > If there is a better or simpler method, I am open to it.
>>
>> I don't know about simpler method because I'm not sure of your table
>> structure. If I want to filter on filtered information I sometimes use
>> the
>> 'double-click - open pop-up with filter information' technique. The
>> information can be altered and then upon closing it requeries the main
>> form
>> thereby updating the totals/information/etc...
>>
>> >
>> > Thank you
>> >
>> > Rob
>> >
>> >
>> >
>> > "Gina Whipp" wrote:
>> >
>> >> Rob,
>> >>
>> >> You know I just noticed you have this on the Form_Current() event. I
>> >> am
>> >> a
>> >> bit confused as to what you are trying to do... You have frmInvoices
>> >> opened
>> >> and you want frmClients to open at the same time filtered only the
>> >> Client
>> >> that is selected??? How does the frmClients know which one if the
>> >> frmInvoices is opening? Perhaps providing more detail will help get
>> >> you
>> >> the
>> >> solution you are looking for...
>> >>
>> >> --
>> >> Gina Whipp
>> >>
>> >> "I feel I have been denied critical, need to know, information!" -
>> >> Tremors
>> >> II
>> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> news:3CE9054F-55F2-47DF-BE40-56EC71AD8C09[ at ]microsoft.com...
>> >> > Gina,
>> >> > Thank you for the help.
>> >> >
>> >> > The field name is ClientID for both forms.
>> >> > The type is numeric on both forms, I used"
>> >> >
>> >> > Private Sub Form_Current()
>> >> >
>> >> > If IsLoaded("frmInvoices") Then
>> >> > DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID
>> >> > End If
>> >> >
>> >> > End Sub
>> >> >
>> >> > and have the "Private Sub Form_Current()" that you provided earlier
>> >> > in
>> >> > Module 1 .
>> >> >
>> >> > The frmInvoices, does not change at all when perform a filter by
>> >> > selection
>> >> > on frmClients.
>> >> >
>> >> > Along with the current
>> >> >
>> >> > "Gina Whipp" wrote:
>> >> >
>> >> >> Maybe what you need is:
>> >> >>
>> >> >> Private Sub Form_Current()
>> >> >>
>> >> >> If IsLoaded("frmInvoices") Then
>> >> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID ' If
>> >> >> numeric
>> >> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" & ClientID &
>> >> >> "'"
>> >> >> 'If text
>> >> >> End If
>> >> >>
>> >> >> End Sub
>> >> >>
>> >> >> However, a few notes here. I do not know if you Client ID field is
>> >> >> text
>> >> >> or
>> >> >> numeric, see above. Do NOT use both lines, select the correct one
>> >> >> and
>> >> >> delete the other. I am also not certain that Client ID is the same
>> >> >> name
>> >> >> on
>> >> >> both forms, so adjustments accordingly.
>> >> >> --
>> >> >> Gina Whipp
>> >> >>
>> >> >> "I feel I have been denied critical, need to know, information!" -
>> >> >> Tremors
>> >> >> II
>> >> >>
>> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> >> news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
>> >> >> > Gina / April,
>> >> >> >
>> >> >> > That makes the code work, thanks!
>> >> >> >
>> >> >> > It turns out that the code I had doesn't do exactly what I need.
>> >> >> >
>> >> >> > The code I have only shows the record that the curser is sitting
>> >> >> > on
>> >> >> > in
>> >> >> > the
>> >> >> > first form. I need to be able to do a sort that could result in
>> >> >> > several
>> >> >> > records and I need to have all those records show up on the
>> >> >> > second
>> >> >> > form.
>> >> >> >
>> >> >> > Example is that I sort on a product and end up with several
>> >> >> > records
>> >> >> > for
>> >> >> > that
>> >> >> > product in the first form. I would need all those records to
>> >> >> > show
>> >> >> > up
>> >> >> > on
>> >> >> > the
>> >> >> > second form regardless of which record the curser was sitting on.
>> >> >> >
>> >> >> > Do you have any suggestions as to where I could find a code that
>> >> >> > shows
>> >> >> > all
>> >> >> > the post filter records and not just the one that cursor is
>> >> >> > pointing
>> >> >> > to?
>> >> >> >
>> >> >> >
>> >> >> > "Gina Whipp" wrote:
>> >> >> >
>> >> >> >> Rob C,
>> >> >> >>
>> >> >> >> You probably need this is a module:
>> >> >> >>
>> >> >> >> Function IsLoaded(ByVal strFormName As String) As Boolean
>> >> >> >> ' Returns True if the specified form is open in Form view or
>> >> >> >> Datasheet
>> >> >> >> view.
>> >> >> >>
>> >> >> >> Const conObjStateClosed = 0
>> >> >> >> Const conDesignView = 0
>> >> >> >>
>> >> >> >> If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
>> >> >> >> conObjStateClosed Then
>> >> >> >> If Forms(strFormName).CurrentView <> conDesignView Then
>> >> >> >> IsLoaded = True
>> >> >> >> End If
>> >> >> >> End If
>> >> >> >>
>> >> >> >> End Function
>> >> >> >>
>> >> >> >> F.Y.I. Do NOT name the module IsLoaded.
>> >> >> >>
>> >> >> >> --
>> >> >> >> Gina Whipp
>> >> >> >>
>> >> >> >> "I feel I have been denied critical, need to know,
>> >> >> >> information!" -
>> >> >> >> Tremors
>> >> >> >> II
>> >> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> >> >> news:7E25155C-8BDF-4311-9800-96225346B1F5[ at ]microsoft.com...
>> >> >> >> >I need to have one subform automatically filter information
>> >> >> >> >based
>> >> >> >> >on
>> >> >> >> >the
>> >> >> >> > "filter by selection" of another subform that is open. I
>> >> >> >> > copied
>> >> >> >> > the
>> >> >> >> > following code and put it in the codebuilder for the form
>> >> >> >> > called
>> >> >> >> > frmClients.
>> >> >> >> >
>> >> >> >> > When I try to filter by selection on frmClients I get the
>> >> >> >> > following
>> >> >> >> > Compliance error message: ""Sub or Function not defined" and
>> >> >> >> > the
>> >> >> >> > "IsLoaded"
>> >> >> >> > is highlighted.
>> >> >> >> >
>> >> >> >> > Can you tell me what else needs to be done to make this work.
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > Private Sub Form_Current()
>> >> >> >> >
>> >> >> >> > If IsLoaded("frmInvoices") Then
>> >> >> >> > Forms!frmInvoices.Filter = _
>> >> >> >> > "ClientID = Forms!frmClients!ClientID"
>> >> >> >> > Forms!frmInvoices.FilterOn = True
>> >> >> >> > End If
>> >> >> >> >
>> >> >> >> > End Sub
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
|
Try this,
Private Sub Form_Current()
If CurrentProject.AllForms("frmInvoices").IsLoaded Then
Forms!frmInvoices.Filter = _
"ClientID = Forms!frmClients!ClientID"
Forms!frmInvoices.FilterOn = True
End If
End Sub
rob c wrote:
[Quoted Text] >I need to have one subform automatically filter information based on the
>"filter by selection" of another subform that is open. I copied the
>following code and put it in the codebuilder for the form called frmClients.
>
>When I try to filter by selection on frmClients I get the following
>Compliance error message: ""Sub or Function not defined" and the "IsLoaded"
>is highlighted.
>
>Can you tell me what else needs to be done to make this work.
>
>Private Sub Form_Current()
>
> If IsLoaded("frmInvoices") Then
> Forms!frmInvoices.Filter = _
> "ClientID = Forms!frmClients!ClientID"
> Forms!frmInvoices.FilterOn = True
> End If
>
>End Sub
--
Please Rate the posting if helps you
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1
|
|
Thanks for the help, but it does the same thing as the other codes I tried,
which is that it filters out everything but the the one record that
currently has the cursor in it. I need to have the filter work so that all
the ClientID's that are on the frmClients are on frmInvoices even after a
filter is performed on frmClients.
"AccessVandal via AccessMonster.com" wrote:
[Quoted Text] > Try this, > > Private Sub Form_Current() > > If CurrentProject.AllForms("frmInvoices").IsLoaded Then > Forms!frmInvoices.Filter = _ > "ClientID = Forms!frmClients!ClientID" > Forms!frmInvoices.FilterOn = True > End If > > End Sub > > > rob c wrote: > >I need to have one subform automatically filter information based on the > >"filter by selection" of another subform that is open. I copied the > >following code and put it in the codebuilder for the form called frmClients. > > > >When I try to filter by selection on frmClients I get the following > >Compliance error message: ""Sub or Function not defined" and the "IsLoaded" > >is highlighted. > > > >Can you tell me what else needs to be done to make this work. > > > >Private Sub Form_Current() > > > > If IsLoaded("frmInvoices") Then > > Forms!frmInvoices.Filter = _ > > "ClientID = Forms!frmClients!ClientID" > > Forms!frmInvoices.FilterOn = True > > End If > > > >End Sub > > -- > Please Rate the posting if helps you > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1> > |
|
Gina,
Thanks for the advice.
I haven't had time to try it yet, would the form that opens up show:
1. all the records on the subform being double-clicked that remained after
the filter; or
2. all the records, including the ones that had been filtered out in the
first form; or
3. Only the record that is being double-clicked?
rob
"Gina Whipp" wrote:
[Quoted Text] > Rob,
>
> Okay then for clarity I think you need to only stick to the names of the
> forms in your database and what you are trying to do. It's gets a little
> confusing when you mix apples (book project) and oranges (your project).
>
> What about my double clicking idea, instead of...
>
> > For filter by selection, I am clicking on a cell and then clicking on the
> > icon in the toolbar which says "filter by selection" when hovering over
> > the
> > icon.
>
> ....allow the person to double-click in the field (only called cell in Excel)
> and have a form open for editing purposes?
>
> On another note... sounds like Management doesn't realize Access is not
> Excel on steriods and operates in a VERY different way. But that does
> explain why you are doing it the way you are doing it.
>
> --
> Gina Whipp
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>
> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> news:64D08F03-92FC-442A-8CAB-BCB04F97E864[ at ]microsoft.com...
> > Gina,
> >
> > Thank you for your patience.
> >
> > The frmInvoices and frm Clients were in a book I was trying to learn from.
> > They are not part of the database I am working on.
> >
> > The only thing I am using the main form for is to show the name of the
> > person who will be working with the information in the subform and to make
> > sure they don't do work on something with someone elses name
> >
> > The objects of the database is to allow project managers to make changes
> > to
> > records that are already on the table that is being used. In the past
> > project managers were sending emails to one person and that person made
> > changes directly into a table. The info was exported to excel, mailed to
> > the
> > project managers, then the process repeated. Management decided that it
> > would be better to have a form so that the project managers could make
> > their
> > own changes in the fields they are responsible for, but not in fields they
> > are not responsible for. They would like the form to look as much like an
> > excel spreadsheet as possible.
> >
> > For filter by selection, I am clicking on a cell and then clicking on the
> > icon in the toolbar which says "filter by selection" when hovering over
> > the
> > icon.
> >
> > Thank you for the suggestions.
> >
> > Regards,
> >
> > Rob
> >
> >
> > "Gina Whipp" wrote:
> >
> >> Rob,
> >>
> >> Based on what I am reading sounds like your table structure got off to a
> >> rocky start and should probably be evaluated. But let's move on... Okay
> >> then where do the Projects come from? In previous messages you talked of
> >> frmInvoices and frmClients. OR do the products show on frmInvoices. Is
> >> the
> >> Main form the Projects?
> >>
> >> Is the object of this database to track/view Projects submitted by
> >> Clients
> >> and invoiced to Clients? See additonal comments below...
> >>
> >> --
> >> Gina Whipp
> >>
> >> "I feel I have been denied critical, need to know, information!" -
> >> Tremors
> >> II
> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> news:DF3469D5-1125-4BE4-8564-714D3122F94D[ at ]microsoft.com...
> >> > Gina,
> >> >
> >> > I'll give some history.
> >> >
> >> > (I used ClientID when I initially asked for help because that is what
> >> > was
> >> > used in the book and I wanted to understand what I needed to do and see
> >> > it
> >> > work before using it on the database I am working on)
> >> >
> >> > I have a table that contains information for projects. Each project
> >> > has
> >> > one
> >> > record. The end users need to be able to perform various filters and
> >> > see
> >> > what the subtotals are on various fields that have dollar amounts in
> >> > them
> >> > and
> >> > to be able to perform various filters and see the new totals then make
> >> > changes to dollar amounts as necessary.
> >> >
> >> > The endusers are not allowed to touch the actual table.
> >> >
> >> > My first try was to create a query which duplicated the table that
> >> > would
> >> > be
> >> > filtered and then have a second query showing only the "sums" based on
> >> > the
> >> > first query This did not work because the totals did not change in
> >> > the
> >> > second Query when I performed "filter by selection" on the first Query.
> >>
> >> This should have worked but not sure how you are doing the 'filter by
> >> slection'
> >>
> >> >
> >> > I then tried to put the entire query onto a form, but I got an error
> >> > message
> >> > from Access which said that there were too many fields for the form.
> >>
> >> Well, that depends, I get that message every blue moon and since I REALLY
> >> need that many fields I ignore it but in your case it might be helpful to
> >> know how many fields you were trying to put on the form.
> >>
> >> >
> >> > My current attempt is to put two subforms onto a form. Both subforms
> >> > are
> >> > initially populated based on the endusers login and sorted by project
> >> > number. Since each project is on both subforms once and only once, all
> >> > the
> >> > information for each project is in line on the screen.
> >>
> >> I am not sure why you would want both subforms showing identical
> >> information, see below.
> >>
> >> >
> >> > Both subforms are based on the same table and so they have a "Project
> >> > number" that is one-to-one. My plan was both subforms would be open
> >> > anytime
> >> > the main form is open. and that when a "filter by selection" is
> >> > performed
> >> > on
> >> > something like a product or a region there would be certain projects
> >> > left
> >> > and
> >> > that the second subform would have those same projects.
> >>
> >> The second subform should be linked Master/Child to the second subform
> >> which
> >> should give you the 'filter' you want.
> >>
> >> >
> >> > If there is a better or simpler method, I am open to it.
> >>
> >> I don't know about simpler method because I'm not sure of your table
> >> structure. If I want to filter on filtered information I sometimes use
> >> the
> >> 'double-click - open pop-up with filter information' technique. The
> >> information can be altered and then upon closing it requeries the main
> >> form
> >> thereby updating the totals/information/etc...
> >>
> >> >
> >> > Thank you
> >> >
> >> > Rob
> >> >
> >> >
> >> >
> >> > "Gina Whipp" wrote:
> >> >
> >> >> Rob,
> >> >>
> >> >> You know I just noticed you have this on the Form_Current() event. I
> >> >> am
> >> >> a
> >> >> bit confused as to what you are trying to do... You have frmInvoices
> >> >> opened
> >> >> and you want frmClients to open at the same time filtered only the
> >> >> Client
> >> >> that is selected??? How does the frmClients know which one if the
> >> >> frmInvoices is opening? Perhaps providing more detail will help get
> >> >> you
> >> >> the
> >> >> solution you are looking for...
> >> >>
> >> >> --
> >> >> Gina Whipp
> >> >>
> >> >> "I feel I have been denied critical, need to know, information!" -
> >> >> Tremors
> >> >> II
> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> >> news:3CE9054F-55F2-47DF-BE40-56EC71AD8C09[ at ]microsoft.com...
> >> >> > Gina,
> >> >> > Thank you for the help.
> >> >> >
> >> >> > The field name is ClientID for both forms.
> >> >> > The type is numeric on both forms, I used"
> >> >> >
> >> >> > Private Sub Form_Current()
> >> >> >
> >> >> > If IsLoaded("frmInvoices") Then
> >> >> > DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID
> >> >> > End If
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >> > and have the "Private Sub Form_Current()" that you provided earlier
> >> >> > in
> >> >> > Module 1 .
> >> >> >
> >> >> > The frmInvoices, does not change at all when perform a filter by
> >> >> > selection
> >> >> > on frmClients.
> >> >> >
> >> >> > Along with the current
> >> >> >
> >> >> > "Gina Whipp" wrote:
> >> >> >
> >> >> >> Maybe what you need is:
> >> >> >>
> >> >> >> Private Sub Form_Current()
> >> >> >>
> >> >> >> If IsLoaded("frmInvoices") Then
> >> >> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID ' If
> >> >> >> numeric
> >> >> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" & ClientID &
> >> >> >> "'"
> >> >> >> 'If text
> >> >> >> End If
> >> >> >>
> >> >> >> End Sub
> >> >> >>
> >> >> >> However, a few notes here. I do not know if you Client ID field is
> >> >> >> text
> >> >> >> or
> >> >> >> numeric, see above. Do NOT use both lines, select the correct one
> >> >> >> and
> >> >> >> delete the other. I am also not certain that Client ID is the same
> >> >> >> name
> >> >> >> on
> >> >> >> both forms, so adjustments accordingly.
> >> >> >> --
> >> >> >> Gina Whipp
> >> >> >>
> >> >> >> "I feel I have been denied critical, need to know, information!" -
> >> >> >> Tremors
> >> >> >> II
> >> >> >>
> >> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
> >> >> >> news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
> >> >> >> > Gina / April,
> >> >> >> >
> >> >> >> > That makes the code work, thanks!
> >> >> >> >
> >> >> >> > It turns out that the code I had doesn't do exactly what I need.
> >> >> >> >
> >> >> >> > The code I have only shows the record that the curser is sitting
> >> >> >> > on
> >> >> >> > in
> >> >> >> > the
> >> >> >> > first form. I need to be able to do a sort that could result in
> >> >> >> > several
> >> >> >> > records and I need to have all those records show up on the
> >> >> >> > second
> >> >> >> > form.
> >> >> >> >
> >> >> >> > Example is that I sort on a product and end up with several
> >> >> >> > records
> >> >> >> > for
> >> >> >> > that
> >> >> >> > product in the first form. I would need all those records to
> >> >> >> > show
> >> >> >> > up
> >> >> >> > on
> >> >> >> > the
> >> >> >> > second form regardless of which record the curser was sitting on.
> >> >> >> >
> >> >> >> > Do you have any suggestions as to where I could find a code that
> >> >> >> > shows
> >> >> >> > all
> >> >> >> > the post filter records and not just the one that cursor is
> >> >> >> > pointing
> >> >> >> > to?
> >> >> >> >
> >> >> >> >
> >> >> >> > "Gina Whipp" wrote:
> >> >> >> >
> >> >> >> >> Rob C,
> >> >> >> >>
> >> >> >> >> You probably need this is a module:
> >> >> >> >>
|
|
The records on the subform would remain the same. Only the record being
double-clicked would show up in the pop-up. That way they could always go
back to the subform and double-click something else and get the newly
filtered records.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
"rob c" <robc[ at ]discussions.microsoft.com> wrote in message
news:A3281E9A-B978-467E-BE3A-BDE9604F9B10[ at ]microsoft.com...
[Quoted Text] > Gina,
> Thanks for the advice.
>
> I haven't had time to try it yet, would the form that opens up show:
>
> 1. all the records on the subform being double-clicked that remained after
> the filter; or
> 2. all the records, including the ones that had been filtered out in the
> first form; or
> 3. Only the record that is being double-clicked?
>
> rob
> "Gina Whipp" wrote:
>
>> Rob,
>>
>> Okay then for clarity I think you need to only stick to the names of the
>> forms in your database and what you are trying to do. It's gets a little
>> confusing when you mix apples (book project) and oranges (your project).
>>
>> What about my double clicking idea, instead of...
>>
>> > For filter by selection, I am clicking on a cell and then clicking on
>> > the
>> > icon in the toolbar which says "filter by selection" when hovering over
>> > the
>> > icon.
>>
>> ....allow the person to double-click in the field (only called cell in
>> Excel)
>> and have a form open for editing purposes?
>>
>> On another note... sounds like Management doesn't realize Access is not
>> Excel on steriods and operates in a VERY different way. But that does
>> explain why you are doing it the way you are doing it.
>>
>> --
>> Gina Whipp
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors
>> II
>>
>> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> news:64D08F03-92FC-442A-8CAB-BCB04F97E864[ at ]microsoft.com...
>> > Gina,
>> >
>> > Thank you for your patience.
>> >
>> > The frmInvoices and frm Clients were in a book I was trying to learn
>> > from.
>> > They are not part of the database I am working on.
>> >
>> > The only thing I am using the main form for is to show the name of the
>> > person who will be working with the information in the subform and to
>> > make
>> > sure they don't do work on something with someone elses name
>> >
>> > The objects of the database is to allow project managers to make
>> > changes
>> > to
>> > records that are already on the table that is being used. In the past
>> > project managers were sending emails to one person and that person made
>> > changes directly into a table. The info was exported to excel, mailed
>> > to
>> > the
>> > project managers, then the process repeated. Management decided that
>> > it
>> > would be better to have a form so that the project managers could make
>> > their
>> > own changes in the fields they are responsible for, but not in fields
>> > they
>> > are not responsible for. They would like the form to look as much like
>> > an
>> > excel spreadsheet as possible.
>> >
>> > For filter by selection, I am clicking on a cell and then clicking on
>> > the
>> > icon in the toolbar which says "filter by selection" when hovering over
>> > the
>> > icon.
>> >
>> > Thank you for the suggestions.
>> >
>> > Regards,
>> >
>> > Rob
>> >
>> >
>> > "Gina Whipp" wrote:
>> >
>> >> Rob,
>> >>
>> >> Based on what I am reading sounds like your table structure got off to
>> >> a
>> >> rocky start and should probably be evaluated. But let's move on...
>> >> Okay
>> >> then where do the Projects come from? In previous messages you talked
>> >> of
>> >> frmInvoices and frmClients. OR do the products show on frmInvoices.
>> >> Is
>> >> the
>> >> Main form the Projects?
>> >>
>> >> Is the object of this database to track/view Projects submitted by
>> >> Clients
>> >> and invoiced to Clients? See additonal comments below...
>> >>
>> >> --
>> >> Gina Whipp
>> >>
>> >> "I feel I have been denied critical, need to know, information!" -
>> >> Tremors
>> >> II
>> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> news:DF3469D5-1125-4BE4-8564-714D3122F94D[ at ]microsoft.com...
>> >> > Gina,
>> >> >
>> >> > I'll give some history.
>> >> >
>> >> > (I used ClientID when I initially asked for help because that is
>> >> > what
>> >> > was
>> >> > used in the book and I wanted to understand what I needed to do and
>> >> > see
>> >> > it
>> >> > work before using it on the database I am working on)
>> >> >
>> >> > I have a table that contains information for projects. Each project
>> >> > has
>> >> > one
>> >> > record. The end users need to be able to perform various filters
>> >> > and
>> >> > see
>> >> > what the subtotals are on various fields that have dollar amounts in
>> >> > them
>> >> > and
>> >> > to be able to perform various filters and see the new totals then
>> >> > make
>> >> > changes to dollar amounts as necessary.
>> >> >
>> >> > The endusers are not allowed to touch the actual table.
>> >> >
>> >> > My first try was to create a query which duplicated the table that
>> >> > would
>> >> > be
>> >> > filtered and then have a second query showing only the "sums" based
>> >> > on
>> >> > the
>> >> > first query This did not work because the totals did not change in
>> >> > the
>> >> > second Query when I performed "filter by selection" on the first
>> >> > Query.
>> >>
>> >> This should have worked but not sure how you are doing the 'filter by
>> >> slection'
>> >>
>> >> >
>> >> > I then tried to put the entire query onto a form, but I got an error
>> >> > message
>> >> > from Access which said that there were too many fields for the form.
>> >>
>> >> Well, that depends, I get that message every blue moon and since I
>> >> REALLY
>> >> need that many fields I ignore it but in your case it might be helpful
>> >> to
>> >> know how many fields you were trying to put on the form.
>> >>
>> >> >
>> >> > My current attempt is to put two subforms onto a form. Both
>> >> > subforms
>> >> > are
>> >> > initially populated based on the endusers login and sorted by
>> >> > project
>> >> > number. Since each project is on both subforms once and only once,
>> >> > all
>> >> > the
>> >> > information for each project is in line on the screen.
>> >>
>> >> I am not sure why you would want both subforms showing identical
>> >> information, see below.
>> >>
>> >> >
>> >> > Both subforms are based on the same table and so they have a
>> >> > "Project
>> >> > number" that is one-to-one. My plan was both subforms would be open
>> >> > anytime
>> >> > the main form is open. and that when a "filter by selection" is
>> >> > performed
>> >> > on
>> >> > something like a product or a region there would be certain projects
>> >> > left
>> >> > and
>> >> > that the second subform would have those same projects.
>> >>
>> >> The second subform should be linked Master/Child to the second subform
>> >> which
>> >> should give you the 'filter' you want.
>> >>
>> >> >
>> >> > If there is a better or simpler method, I am open to it.
>> >>
>> >> I don't know about simpler method because I'm not sure of your table
>> >> structure. If I want to filter on filtered information I sometimes
>> >> use
>> >> the
>> >> 'double-click - open pop-up with filter information' technique. The
>> >> information can be altered and then upon closing it requeries the main
>> >> form
>> >> thereby updating the totals/information/etc...
>> >>
>> >> >
>> >> > Thank you
>> >> >
>> >> > Rob
>> >> >
>> >> >
>> >> >
>> >> > "Gina Whipp" wrote:
>> >> >
>> >> >> Rob,
>> >> >>
>> >> >> You know I just noticed you have this on the Form_Current() event.
>> >> >> I
>> >> >> am
>> >> >> a
>> >> >> bit confused as to what you are trying to do... You have
>> >> >> frmInvoices
>> >> >> opened
>> >> >> and you want frmClients to open at the same time filtered only the
>> >> >> Client
>> >> >> that is selected??? How does the frmClients know which one if the
>> >> >> frmInvoices is opening? Perhaps providing more detail will help
>> >> >> get
>> >> >> you
>> >> >> the
>> >> >> solution you are looking for...
>> >> >>
>> >> >> --
>> >> >> Gina Whipp
>> >> >>
>> >> >> "I feel I have been denied critical, need to know, information!" -
>> >> >> Tremors
>> >> >> II
>> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> >> news:3CE9054F-55F2-47DF-BE40-56EC71AD8C09[ at ]microsoft.com...
>> >> >> > Gina,
>> >> >> > Thank you for the help.
>> >> >> >
>> >> >> > The field name is ClientID for both forms.
>> >> >> > The type is numeric on both forms, I used"
>> >> >> >
>> >> >> > Private Sub Form_Current()
>> >> >> >
>> >> >> > If IsLoaded("frmInvoices") Then
>> >> >> > DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID
>> >> >> > End If
>> >> >> >
>> >> >> > End Sub
>> >> >> >
>> >> >> > and have the "Private Sub Form_Current()" that you provided
>> >> >> > earlier
>> >> >> > in
>> >> >> > Module 1 .
>> >> >> >
>> >> >> > The frmInvoices, does not change at all when perform a filter by
>> >> >> > selection
>> >> >> > on frmClients.
>> >> >> >
>> >> >> > Along with the current
>> >> >> >
>> >> >> > "Gina Whipp" wrote:
>> >> >> >
>> >> >> >> Maybe what you need is:
>> >> >> >>
>> >> >> >> Private Sub Form_Current()
>> >> >> >>
>> >> >> >> If IsLoaded("frmInvoices") Then
>> >> >> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & ClientID '
>> >> >> >> If
>> >> >> >> numeric
>> >> >> >> DoCmd.OpenForm "frmClients", , , "ClientID=" & "'" &
>> >> >> >> ClientID &
>> >> >> >> "'"
>> >> >> >> 'If text
>> >> >> >> End If
>> >> >> >>
>> >> >> >> End Sub
>> >> >> >>
>> >> >> >> However, a few notes here. I do not know if you Client ID field
>> >> >> >> is
>> >> >> >> text
>> >> >> >> or
>> >> >> >> numeric, see above. Do NOT use both lines, select the correct
>> >> >> >> one
>> >> >> >> and
>> >> >> >> delete the other. I am also not certain that Client ID is the
>> >> >> >> same
>> >> >> >> name
>> >> >> >> on
>> >> >> >> both forms, so adjustments accordingly.
>> >> >> >> --
>> >> >> >> Gina Whipp
>> >> >> >>
>> >> >> >> "I feel I have been denied critical, need to know,
>> >> >> >> information!" -
>> >> >> >> Tremors
>> >> >> >> II
>> >> >> >>
>> >> >> >> "rob c" <robc[ at ]discussions.microsoft.com> wrote in message
>> >> >> >> news:01C58D20-7615-4A69-9B79-B327985390B9[ at ]microsoft.com...
>> >> >> >> > Gina / April,
>> >> >> >> >
>> >> >> >> > That makes the code work, thanks!
>> >> >> >> >
>> >> >> >> > It turns out that the code I had doesn't do exactly what I
>> >> >> >> > need.
>> >> >> >> >
>> >> >> >> > The code I have only shows the record that the curser is
>> >> >> >> > sitting
>> >> >> >> > on
>> >> >> >> > in
>> >> >> >> > the
>> >> >> >> > first form. I need to be able to do a sort that could result
>> >> >> >> > in
>> >> >> >> > several
>> >> >> >> > records and I need to have all those records show up on the
>> >> >> >> > second
>> >> >> >> > form.
>> >> >> >> >
>> >> >> >> > Example is that I sort on a product and end up with several
>> >> >> >> > records
>> >> >> >> > for
>> >> >> >> > that
>> >> >> >> > product in the first form. I would need all those records to
>> >> >> >> > show
>> >> >> >> > up
>> >> >> >> > on
>> >> >> >> > the
>> >> >> >> > second form regardless of which record the curser was sitting
>> >> >> >> > on.
>> >> >> >> >
>> >> >> >> > Do you have any suggestions as to where I could find a code
>> >> >> >> > that
>> >> >> >> > shows
>> >> >> >> > all
>> >> >> >> > the post filter records and not just the one that cursor is
>> >> >> >> > pointing
>> >> >> >> > to?
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Gina Whipp" wrote:
>> >> >> >> >
>> >> >> >> >> Rob C,
>> >> >> >> >>
>> >> >> >> >> You probably need this is a module:
>> >> >> >> >>
|
|
Sorry about that, I had withheld comments on the filter string. The filter
string will not work, as the syntax is incorrect. So, the currentproject.
allforms works right?
You’ll need to filter the correct syntax.
Private Sub Form_Current()
Dim intClientID as Integer ‘or long if you wish
If CurrentProject.AllForms("frmInvoices").IsLoaded Then
IntClientID = Forms!frmInvoices!ClientID
Forms!frmInvoices.Filter = _
"ClientID = " & IntClientID
Forms!frmInvoices.FilterOn = True
End If
End Sub
rob c wrote:
[Quoted Text] >Thanks for the help, but it does the same thing as the other codes I tried,
>which is that it filters out everything but the the one record that
>currently has the cursor in it. I need to have the filter work so that all
>the ClientID's that are on the frmClients are on frmInvoices even after a
>filter is performed on frmClients.
--
Please Rate the posting if helps you
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1
|
|
Opps, should be "frmClients"
AccessVandal wrote:
[Quoted Text] >Sorry about that, I had withheld comments on the filter string. The filter
>string will not work, as the syntax is incorrect. So, the currentproject.
>allforms works right?
>
>You’ll need to filter the correct syntax.
>
>Private Sub Form_Current()
>
>Dim intClientID as Integer ‘or long if you wish
>
If CurrentProject.AllForms("frmInvoices").IsLoaded Then
IntClientID = Forms!Clients!ClientID
Forms!frmInvoices.Filter = _
"ClientID = " & IntClientID
Forms!frmInvoices.FilterOn = True
End If
End Sub
--
Please Rate the posting if helps you
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1
|
|
|