IsLoaded compliance error

[rob c]

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

 

[April]

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?

 

[Gina Whipp]

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.

 

[rob c]

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

 

[rob c]

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

 

[rob c]

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]

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.

 

[rob c]

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]

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

 

[rob c]

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]

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

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

 

[rob c]

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]

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.

 

[AccessVandal via AccessMonster.com]

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]

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.

 

[rob c]

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]

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.

 

[AccessVandal via AccessMonster.com]

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

 

[AccessVandal via AccessMonster.com]

Opps, should be "frmClients"

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