Filter Problem on Form

[Tom]

I have a problem with forms and subform.

TABLE STRUCTURE BACKGROUND INFO
- tblCompanies (a "Company" can have many "Contacts")
- tblContacts (a "Contact" can have many "Notes")
- tblNotes


FORMS BACKGROUND INFO:
- A main form (frmCompanies) is linked to "tblCompanies" (Single Form view)
- "frmCompanies" has a subform (sfrmContacts) that is linked to
"tblContacts". The sfrmContacts is also in "Single Form" view.
- "sfrmContacts" then also has a subform "sfrmNotes" (this the view is in
datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event (betweens the ***s)
- When frmSelect is opened, I can select a company name which then will open
the frmCompanies (and its underlying subforms).... so far so good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

************************


HERE NOW IS THE PROBLEM:
- When a company name is selected from the frmSelect, the frmCompanies shows
"(Filtered") next to the navigation buttons. I expected this since I
selected a specific record from the entire record set of companies.
- However, if the selected company has "more than 1 contacts" I cannot
access any "contacts records except the 1st one.
- This does not happen if I don't use the frmSelect (the one w/ the combo
box).
If I directly open the frmCompanies I then would see the navigation buttons
for navigate back/forth between the Contacts.


QUESTION:
- Is there are way to utilize the frmSelect and allowing to SHOW ALL
contacts
that are "children" of the selected company?



Thanks,
Tom

 

[Wayne Morgan]

In Access 2003, I just opened a main form that has a subform using the
syntax you have with the Link Criteria. I set the subform to Single Form
view prior to opening the main form. It showed the record selectors in the
subform. Try clicking into the subform and use the Page Up and Page Down
keys to see if you can move between records. If so, the records are there
and the problem is just the display of the record selectors. You may try
increasing the Height of the subform control on the main form to see if it
exposes them. There might be something slightly different in the display
when the main form is filtered.

There are also some know problems with filters on forms that have subforms.
See if any of these may apply.
http://members.iinet.net.au/~allenbrowne/bug-02.html

--
Wayne Morgan
MS Access MVP

I have a problem with forms and subform.

TABLE STRUCTURE BACKGROUND INFO
- tblCompanies (a "Company" can have many "Contacts")
- tblContacts (a "Contact" can have many "Notes")
- tblNotes


FORMS BACKGROUND INFO:
- A main form (frmCompanies) is linked to "tblCompanies" (Single Form view)
- "frmCompanies" has a subform (sfrmContacts) that is linked to
"tblContacts". The sfrmContacts is also in "Single Form" view.
- "sfrmContacts" then also has a subform "sfrmNotes" (this the view is in
datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event (betweens the ***s)
- When frmSelect is opened, I can select a company name which then will open
the frmCompanies (and its underlying subforms).... so far so good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

************************


HERE NOW IS THE PROBLEM:
- When a company name is selected from the frmSelect, the frmCompanies shows
"(Filtered") next to the navigation buttons. I expected this since I
selected a specific record from the entire record set of companies.
- However, if the selected company has "more than 1 contacts" I cannot
access any "contacts records except the 1st one.
- This does not happen if I don't use the frmSelect (the one w/ the combo
box).
If I directly open the frmCompanies I then would see the navigation buttons
for navigate back/forth between the Contacts.


QUESTION:
- Is there are way to utilize the frmSelect and allowing to SHOW ALL
contacts
that are "children" of the selected company?

 

[Tom]

Wayne:

Thank you so much for sharing the info with me.... yes, you're right...
after I changed some of the settings, I was able to view the record
selectors for the subform.

I now only need to figure out one more thing...

I use a page break on the main form. The page break separates the info of
the main form and the subform.

For visualization purposes, I have created a fields layout that makes it
look as if I had 2 different forms that allows to switch back and forth.

I order to view the navigation buttons of the subform though, I have to
increase the height of the mainform. As a result of that, when on "Page 1"
(containing info of only the mainform), I see bits and pieces of the subform
(which I don't want).

To overcome this problem, I actually added 4 individual command buttons on
the subform so that I can reduce the height of the subform but still can
navigate between the records.

So far so good... here's the problem now....

Using the individual command buttons, I cannot see if I have "1 of 1 record"
or e.g. "1 of 6 records". Is there a way to somehow show how many records
a subform has (without using the navigation buttons? Again, I set the
"navigation button" = "no".

Thanks,
Tom

In Access 2003, I just opened a main form that has a subform using the
syntax you have with the Link Criteria. I set the subform to Single Form
view prior to opening the main form. It showed the record selectors in the
subform. Try clicking into the subform and use the Page Up and Page Down
keys to see if you can move between records. If so, the records are there
and the problem is just the display of the record selectors. You may try
increasing the Height of the subform control on the main form to see if it
exposes them. There might be something slightly different in the display
when the main form is filtered.

There are also some know problems with filters on forms that have subforms.
See if any of these may apply.
http://members.iinet.net.au/~allenbrowne/bug-02.html

--
Wayne Morgan
MS Access MVP

I have a problem with forms and subform.

TABLE STRUCTURE BACKGROUND INFO
- tblCompanies (a "Company" can have many "Contacts")
- tblContacts (a "Contact" can have many "Notes")
- tblNotes


FORMS BACKGROUND INFO:
- A main form (frmCompanies) is linked to "tblCompanies" (Single Form view)
- "frmCompanies" has a subform (sfrmContacts) that is linked to
"tblContacts". The sfrmContacts is also in "Single Form" view.
- "sfrmContacts" then also has a subform "sfrmNotes" (this the view is in
datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event (betweens the ***s)
- When frmSelect is opened, I can select a company name which then will open
the frmCompanies (and its underlying subforms).... so far so good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

************************


HERE NOW IS THE PROBLEM:
- When a company name is selected from the frmSelect, the frmCompanies shows
"(Filtered") next to the navigation buttons. I expected this since I
selected a specific record from the entire record set of companies.
- However, if the selected company has "more than 1 contacts" I cannot
access any "contacts records except the 1st one.
- This does not happen if I don't use the frmSelect (the one w/ the combo
box).
If I directly open the frmCompanies I then would see the navigation buttons
for navigate back/forth between the Contacts.


QUESTION:
- Is there are way to utilize the frmSelect and allowing to SHOW ALL
contacts
that are "children" of the selected company?

 

[Wayne Morgan]

You can find the number of records, but there may be an easier way. To keep
the subform from showing when on Page 1, you may want to set the visible
property of the subform control to False or place a rectangle over the top
of the subform and set the visible property of the rectangle to false when
viewing the subform.

To get the information you are after and place it in a textbox you can use
Me.Recordset.RecordCount and Me.Recordset.AbsolutePosition. If you use these
in the control source of a textbox, it won't recognize the Me word, just
leave it off or give the full path to the form. You could place a textbox on
the main form that refers to the subform if you prefer. The control source
for that would look like

=[NameOfSubformControl].[Form].[Recordset].[AbsolutePosition] + 1 & " of " &
[NameOfSubformControl].[Form].[Recordset].[RecordCount]

--
Wayne Morgan
MS Access MVP

Wayne:

Thank you so much for sharing the info with me.... yes, you're right...
after I changed some of the settings, I was able to view the record
selectors for the subform.

I now only need to figure out one more thing...

I use a page break on the main form. The page break separates the info of
the main form and the subform.

For visualization purposes, I have created a fields layout that makes it
look as if I had 2 different forms that allows to switch back and forth.

I order to view the navigation buttons of the subform though, I have to
increase the height of the mainform. As a result of that, when on "Page 1"
(containing info of only the mainform), I see bits and pieces of the subform
(which I don't want).

To overcome this problem, I actually added 4 individual command buttons on
the subform so that I can reduce the height of the subform but still can
navigate between the records.

So far so good... here's the problem now....

Using the individual command buttons, I cannot see if I have "1 of 1 record"
or e.g. "1 of 6 records". Is there a way to somehow show how many records
a subform has (without using the navigation buttons? Again, I set the
"navigation button" = "no".

Thanks,
Tom

In Access 2003, I just opened a main form that has a subform using the
syntax you have with the Link Criteria. I set the subform to Single Form
view prior to opening the main form. It showed the record selectors in the
subform. Try clicking into the subform and use the Page Up and Page Down
keys to see if you can move between records. If so, the records are there
and the problem is just the display of the record selectors. You may try
increasing the Height of the subform control on the main form to see if it
exposes them. There might be something slightly different in the display
when the main form is filtered.

There are also some know problems with filters on forms that have subforms.
See if any of these may apply.
http://members.iinet.net.au/~allenbrowne/bug-02.html

--
Wayne Morgan
MS Access MVP

I have a problem with forms and subform.

TABLE STRUCTURE BACKGROUND INFO
- tblCompanies (a "Company" can have many "Contacts")
- tblContacts (a "Contact" can have many "Notes")
- tblNotes


FORMS BACKGROUND INFO:
- A main form (frmCompanies) is linked to "tblCompanies" (Single Form view)
- "frmCompanies" has a subform (sfrmContacts) that is linked to
"tblContacts". The sfrmContacts is also in "Single Form" view.
- "sfrmContacts" then also has a subform "sfrmNotes" (this the view is in
datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event (betweens the ***s)
- When frmSelect is opened, I can select a company name which then

will
open

the frmCompanies (and its underlying subforms).... so far so good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

************************


HERE NOW IS THE PROBLEM:
- When a company name is selected from the frmSelect, the frmCompanies shows
"(Filtered") next to the navigation buttons. I expected this since I
selected a specific record from the entire record set of companies.
- However, if the selected company has "more than 1 contacts" I cannot
access any "contacts records except the 1st one.
- This does not happen if I don't use the frmSelect (the one w/ the combo
box).
If I directly open the frmCompanies I then would see the navigation buttons
for navigate back/forth between the Contacts.


QUESTION:
- Is there are way to utilize the frmSelect and allowing to SHOW ALL
contacts
that are "children" of the selected company?

 

[Tom]

Wayne:

Thanks again for the info.

Two more follow up questions though...

1. If I didn't want to go w/ the "Gray Textbox solution" but wanted to use
the Visible/Not Visbible property, how do I do that?

On the mainform, I don't see the "visible property" of the subform. If I
set the value to "no" on the subform, then it certainly does not show up at
all on the mainform. Am I missing something?

2. Record count... I added a textbox onto the subform (it's called
"sfrmContacts"). I called its name on the Other tab "RecordCount". When I
now open the subform, the textbox shows "#Name?".

Is the below correct?

=[RecordCount].[Form].[Recordset].[AbsolutePosition] + 1 & " of " &
[RecordCount].[Form].[Recordset].[RecordCount]

Thanks,
Tom

You can find the number of records, but there may be an easier way. To keep
the subform from showing when on Page 1, you may want to set the visible
property of the subform control to False or place a rectangle over the top
of the subform and set the visible property of the rectangle to false when
viewing the subform.

To get the information you are after and place it in a textbox you can use
Me.Recordset.RecordCount and Me.Recordset.AbsolutePosition. If you use these
in the control source of a textbox, it won't recognize the Me word, just
leave it off or give the full path to the form. You could place a textbox on
the main form that refers to the subform if you prefer. The control source
for that would look like

=[NameOfSubformControl].[Form].[Recordset].[AbsolutePosition] + 1 & " of " &
[NameOfSubformControl].[Form].[Recordset].[RecordCount]

--
Wayne Morgan
MS Access MVP

Wayne:

Thank you so much for sharing the info with me.... yes, you're right...
after I changed some of the settings, I was able to view the record
selectors for the subform.

I now only need to figure out one more thing...

I use a page break on the main form. The page break separates the info of
the main form and the subform.

For visualization purposes, I have created a fields layout that makes it
look as if I had 2 different forms that allows to switch back and forth.

I order to view the navigation buttons of the subform though, I have to
increase the height of the mainform. As a result of that, when on

"Page

1"
(containing info of only the mainform), I see bits and pieces of the subform
(which I don't want).

To overcome this problem, I actually added 4 individual command buttons on
the subform so that I can reduce the height of the subform but still can
navigate between the records.

So far so good... here's the problem now....

Using the individual command buttons, I cannot see if I have "1 of 1 record"
or e.g. "1 of 6 records". Is there a way to somehow show how many records
a subform has (without using the navigation buttons? Again, I set the
"navigation button" = "no".

Thanks,
Tom

if

it

exposes them. There might be something slightly different in the display
when the main form is filtered.

There are also some know problems with filters on forms that have subforms.
See if any of these may apply.
http://members.iinet.net.au/~allenbrowne/bug-02.html

--
Wayne Morgan
MS Access MVP


I have a problem with forms and subform.

TABLE STRUCTURE BACKGROUND INFO
- tblCompanies (a "Company" can have many "Contacts")
- tblContacts (a "Contact" can have many "Notes")
- tblNotes


FORMS BACKGROUND INFO:
- A main form (frmCompanies) is linked to "tblCompanies" (Single Form
view)
- "frmCompanies" has a subform (sfrmContacts) that is linked to
"tblContacts". The sfrmContacts is also in "Single Form" view.
- "sfrmContacts" then also has a subform "sfrmNotes" (this the view

is
in

datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event (betweens the
***s)
- When frmSelect is opened, I can select a company name which then will
open
the frmCompanies (and its underlying subforms).... so far so good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

************************


HERE NOW IS THE PROBLEM:
- When a company name is selected from the frmSelect, the frmCompanies
shows
"(Filtered") next to the navigation buttons. I expected this since I
selected a specific record from the entire record set of companies.
- However, if the selected company has "more than 1 contacts" I cannot
access any "contacts records except the 1st one.
- This does not happen if I don't use the frmSelect (the one w/ the combo
box).
If I directly open the frmCompanies I then would see the navigation
buttons
for navigate back/forth between the Contacts.


QUESTION:
- Is there are way to utilize the frmSelect and allowing to SHOW ALL
contacts
that are "children" of the selected company?

 

[Wayne Morgan]

2) The code sample is for a textbox on the main form that is refering to the
subform If the textbox is on the subform

=[Recordset].[AbsolutePosition] + 1 & " of " & [Recordset].[RecordCount]

I also had to add Me.NameOfTextbox.Requery to the subform's Current event.

1) The Visible property is for the subform control, not the subform. The
subform control is a control on the main form. The subform is held in this
control. Double click the detail section background of the main form to open
the Properties sheet if it isn't already open then click on the subform ONE
time. The properties sheet will now show the properties for the subform
control. If you click on the subform a second time, you'll be in the subform
and the properties sheet will show the properties of the subform, not the
control holding it. However, I'm not sure what you would want to do to
activate it. A button click would be one option. You could also do so once
you enter a certain control on the main form.

--
Wayne Morgan
MS Access MVP

Wayne:

Thanks again for the info.

Two more follow up questions though...

1. If I didn't want to go w/ the "Gray Textbox solution" but wanted to use
the Visible/Not Visbible property, how do I do that?

On the mainform, I don't see the "visible property" of the subform. If I
set the value to "no" on the subform, then it certainly does not show up at
all on the mainform. Am I missing something?

2. Record count... I added a textbox onto the subform (it's called
"sfrmContacts"). I called its name on the Other tab "RecordCount". When I
now open the subform, the textbox shows "#Name?".

Is the below correct?

=[RecordCount].[Form].[Recordset].[AbsolutePosition] + 1 & " of " &
[RecordCount].[Form].[Recordset].[RecordCount]

Thanks,
Tom

You can find the number of records, but there may be an easier way. To keep
the subform from showing when on Page 1, you may want to set the visible
property of the subform control to False or place a rectangle over the top
of the subform and set the visible property of the rectangle to false when
viewing the subform.

To get the information you are after and place it in a textbox you can use
Me.Recordset.RecordCount and Me.Recordset.AbsolutePosition. If you use these
in the control source of a textbox, it won't recognize the Me word, just
leave it off or give the full path to the form. You could place a

textbox

on
the main form that refers to the subform if you prefer. The control source
for that would look like

=[NameOfSubformControl].[Form].[Recordset].[AbsolutePosition] + 1 & " of

"

&
[NameOfSubformControl].[Form].[Recordset].[RecordCount]

--
Wayne Morgan
MS Access MVP

Wayne:

Thank you so much for sharing the info with me.... yes, you're right...
after I changed some of the settings, I was able to view the record
selectors for the subform.

I now only need to figure out one more thing...

I use a page break on the main form. The page break separates the

info

of "Page

buttons

on in
the if

view

is

in
datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event (betweens the
***s)
- When frmSelect is opened, I can select a company name which then will
open
the frmCompanies (and its underlying subforms).... so far so good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![cboCompany]

&
"'"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

************************


HERE NOW IS THE PROBLEM:
- When a company name is selected from the frmSelect, the frmCompanies
shows
"(Filtered") next to the navigation buttons. I expected this

since

I

 

[Tom]

Wayne,

thanks! One more final question though...

The record count on the subform is first incorrect, then adjusts and
provides the correct total number of records on the subform.

Let's say I have a company with 6 contacts... here's what I see on the form.

Procedure:
- select the company name from form that only contains a combo box
- latter brought up the "main form" with the apppropriate "company"
selection
- click "Page 2" on mainform which then brings up subform
- Record count indicates "1 of 1" (should be 1 of 6)
- use the command button to move to next record
- now it says "2 of 6", "3 of 6", etc....
- if I go back to first record again, it NOW says "1 of 6"

Why is that? When I added the "Me.RecordCount.Requery" on the subforms'
OnCurrent event and then saved the subform, an Access dialog box pops up.
The error message says: "Microsoft Access can't find the macro 'Me.'"

Am I still missing something?

Thanks in advance for more info.

--

Tom

2) The code sample is for a textbox on the main form that is refering to the
subform If the textbox is on the subform

=[Recordset].[AbsolutePosition] + 1 & " of " & [Recordset].[RecordCount]

I also had to add Me.NameOfTextbox.Requery to the subform's Current event.

1) The Visible property is for the subform control, not the subform. The
subform control is a control on the main form. The subform is held in this
control. Double click the detail section background of the main form to open
the Properties sheet if it isn't already open then click on the subform ONE
time. The properties sheet will now show the properties for the subform
control. If you click on the subform a second time, you'll be in the subform
and the properties sheet will show the properties of the subform, not the
control holding it. However, I'm not sure what you would want to do to
activate it. A button click would be one option. You could also do so once
you enter a certain control on the main form.

--
Wayne Morgan
MS Access MVP

Wayne:

Thanks again for the info.

Two more follow up questions though...

1. If I didn't want to go w/ the "Gray Textbox solution" but wanted to use
the Visible/Not Visbible property, how do I do that?

On the mainform, I don't see the "visible property" of the subform. If I
set the value to "no" on the subform, then it certainly does not show up at
all on the mainform. Am I missing something?

2. Record count... I added a textbox onto the subform (it's called
"sfrmContacts"). I called its name on the Other tab "RecordCount".

When

I
now open the subform, the textbox shows "#Name?".

Is the below correct?

=[RecordCount].[Form].[Recordset].[AbsolutePosition] + 1 & " of " &
[RecordCount].[Form].[Recordset].[RecordCount]

Thanks,
Tom

You can find the number of records, but there may be an easier way. To keep
the subform from showing when on Page 1, you may want to set the visible
property of the subform control to False or place a rectangle over the top
of the subform and set the visible property of the rectangle to false when
viewing the subform.

To get the information you are after and place it in a textbox you can use
Me.Recordset.RecordCount and Me.Recordset.AbsolutePosition. If you use these
in the control source of a textbox, it won't recognize the Me word, just
leave it off or give the full path to the form. You could place a

textbox

on
the main form that refers to the subform if you prefer. The control source
for that would look like

=[NameOfSubformControl].[Form].[Recordset].[AbsolutePosition] + 1 & "

of

"

&
[NameOfSubformControl].[Form].[Recordset].[RecordCount]

--
Wayne Morgan
MS Access MVP


Wayne:

Thank you so much for sharing the info with me.... yes, you're right...
after I changed some of the settings, I was able to view the record
selectors for the subform.

I now only need to figure out one more thing...

I use a page break on the main form. The page break separates the

info

of
the main form and the subform.

For visualization purposes, I have created a fields layout that

makes

it buttons still
can Single
Form

selectors

in

the
subform. Try clicking into the subform and use the Page Up and

Page
Down

keys to see if you can move between records. If so, the records are
there
and the problem is just the display of the record selectors. You

may
try

increasing the Height of the subform control on the main form to

see
if

it
exposes them. There might be something slightly different in the display
when the main form is filtered.

There are also some know problems with filters on forms that have
subforms.
See if any of these may apply.
http://members.iinet.net.au/~allenbrowne/bug-02.html

--
Wayne Morgan
MS Access MVP


I have a problem with forms and subform.

TABLE STRUCTURE BACKGROUND INFO
- tblCompanies (a "Company" can have many "Contacts")
- tblContacts (a "Contact" can have many "Notes")
- tblNotes


FORMS BACKGROUND INFO:
- A main form (frmCompanies) is linked to "tblCompanies" (Single Form
view)
- "frmCompanies" has a subform (sfrmContacts) that is linked to
"tblContacts". The sfrmContacts is also in "Single Form" view.
- "sfrmContacts" then also has a subform "sfrmNotes" (this the

view

is
in
datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event

(betweens
the

***s)
- When frmSelect is opened, I can select a company name which then
will
open
the frmCompanies (and its underlying subforms).... so far so good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" &

Me![cboCompany]

& since

 

[Wayne Morgan]

To add this to the OnCurrent event you have to set the OnCurrent event to
[Event Procedure] then click the ... button to the right to open the VBA
editor. The code line will go there. It will look something like

Private Sub Form_Current()
Me.RecordCount.Requery
End Sub

The first and last lines will be automatically inserted for you. The Requery
should get rid of the problem you mention and is why I had added it, I had
the same problem.

--
Wayne Morgan
MS Access MVP

Wayne,

thanks! One more final question though...

The record count on the subform is first incorrect, then adjusts and
provides the correct total number of records on the subform.

Let's say I have a company with 6 contacts... here's what I see on the form.

Procedure:
- select the company name from form that only contains a combo box
- latter brought up the "main form" with the apppropriate "company"
selection
- click "Page 2" on mainform which then brings up subform
- Record count indicates "1 of 1" (should be 1 of 6)
- use the command button to move to next record
- now it says "2 of 6", "3 of 6", etc....
- if I go back to first record again, it NOW says "1 of 6"

Why is that? When I added the "Me.RecordCount.Requery" on the subforms'
OnCurrent event and then saved the subform, an Access dialog box pops up.
The error message says: "Microsoft Access can't find the macro 'Me.'"

Am I still missing something?

Thanks in advance for more info.

--

Tom

2) The code sample is for a textbox on the main form that is refering to the
subform If the textbox is on the subform

=[Recordset].[AbsolutePosition] + 1 & " of " & [Recordset].[RecordCount]

I also had to add Me.NameOfTextbox.Requery to the subform's Current event.

1) The Visible property is for the subform control, not the subform. The
subform control is a control on the main form. The subform is held in this
control. Double click the detail section background of the main form to open
the Properties sheet if it isn't already open then click on the subform ONE
time. The properties sheet will now show the properties for the subform
control. If you click on the subform a second time, you'll be in the subform
and the properties sheet will show the properties of the subform, not the
control holding it. However, I'm not sure what you would want to do to
activate it. A button click would be one option. You could also do so once
you enter a certain control on the main form.

--
Wayne Morgan
MS Access MVP

Wayne:

Thanks again for the info.

Two more follow up questions though...

1. If I didn't want to go w/ the "Gray Textbox solution" but wanted to use
the Visible/Not Visbible property, how do I do that?

On the mainform, I don't see the "visible property" of the subform.

If

I

set the value to "no" on the subform, then it certainly does not show

up
at

all on the mainform. Am I missing something?

2. Record count... I added a textbox onto the subform (it's called
"sfrmContacts"). I called its name on the Other tab "RecordCount".

When

I
now open the subform, the textbox shows "#Name?".

Is the below correct?

=[RecordCount].[Form].[Recordset].[AbsolutePosition] + 1 & " of " &
[RecordCount].[Form].[Recordset].[RecordCount]

Thanks,
Tom



You can find the number of records, but there may be an easier way. To
keep
the subform from showing when on Page 1, you may want to set the visible
property of the subform control to False or place a rectangle over

the
top

of the subform and set the visible property of the rectangle to

false
when

viewing the subform.

To get the information you are after and place it in a textbox you

can
use

Me.Recordset.RecordCount and Me.Recordset.AbsolutePosition. If you use
these
in the control source of a textbox, it won't recognize the Me word, just
leave it off or give the full path to the form. You could place a textbox
on
the main form that refers to the subform if you prefer. The control source
for that would look like

=[NameOfSubformControl].[Form].[Recordset].[AbsolutePosition] + 1 &

"

of

"
&
[NameOfSubformControl].[Form].[Recordset].[RecordCount]

--
Wayne Morgan
MS Access MVP


Wayne:

Thank you so much for sharing the info with me.... yes, you're right...
after I changed some of the settings, I was able to view the record
selectors for the subform.

I now only need to figure out one more thing...

I use a page break on the main form. The page break separates the info
of
the main form and the subform.

For visualization purposes, I have created a fields layout that

makes

it
look as if I had 2 different forms that allows to switch back and forth.

I order to view the navigation buttons of the subform though, I

have
to

increase the height of the mainform. As a result of that, when on
"Page
1"
(containing info of only the mainform), I see bits and pieces of the
subform
(which I don't want).

To overcome this problem, I actually added 4 individual command buttons
on
the subform so that I can reduce the height of the subform but still
can
navigate between the records.

So far so good... here's the problem now....

Using the individual command buttons, I cannot see if I have "1 of 1
record"
or e.g. "1 of 6 records". Is there a way to somehow show how many
records
a subform has (without using the navigation buttons? Again, I set the
"navigation button" = "no".

Thanks,
Tom





message
In Access 2003, I just opened a main form that has a subform

using
the

syntax you have with the Link Criteria. I set the subform to Single
Form
view prior to opening the main form. It showed the record

selectors

in
the
subform. Try clicking into the subform and use the Page Up and Page
Down
keys to see if you can move between records. If so, the records are
there
and the problem is just the display of the record selectors. You may
try
increasing the Height of the subform control on the main form to see
if
it
exposes them. There might be something slightly different in the
display
when the main form is filtered.

There are also some know problems with filters on forms that have
subforms.
See if any of these may apply.
http://members.iinet.net.au/~allenbrowne/bug-02.html

--
Wayne Morgan
MS Access MVP


I have a problem with forms and subform.

TABLE STRUCTURE BACKGROUND INFO
- tblCompanies (a "Company" can have many "Contacts")
- tblContacts (a "Contact" can have many "Notes")
- tblNotes


FORMS BACKGROUND INFO:
- A main form (frmCompanies) is linked to "tblCompanies" (Single
Form
view)
- "frmCompanies" has a subform (sfrmContacts) that is linked to
"tblContacts". The sfrmContacts is also in "Single Form" view.
- "sfrmContacts" then also has a subform "sfrmNotes" (this the view
is
in
datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event (betweens
the
***s)
- When frmSelect is opened, I can select a company name which then
will
open
the frmCompanies (and its underlying subforms).... so far so good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" &

Me![cboCompany]

&
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

************************


HERE NOW IS THE PROBLEM:
- When a company name is selected from the frmSelect, the
frmCompanies
shows
"(Filtered") next to the navigation buttons. I expected this since
I
selected a specific record from the entire record set of companies.
- However, if the selected company has "more than 1 contacts" I
cannot
access any "contacts records except the 1st one.
- This does not happen if I don't use the frmSelect (the one

w/
the

combo
box).
If I directly open the frmCompanies I then would see the navigation
buttons
for navigate back/forth between the Contacts.


QUESTION:
- Is there are way to utilize the frmSelect and allowing to

SHOW
ALL

contacts
that are "children" of the selected company?

 

[Tom]

Wayne:

Yes, it did get rid of the error... however, the record count still does not
show the correct count when pulling the record initially.

First, it shows 1 of 1, then 2 of 6... going back shows then 1 of 6.

Any suggestions why this might happen?
--
Thanks,
Tom

To add this to the OnCurrent event you have to set the OnCurrent event to
[Event Procedure] then click the ... button to the right to open the VBA
editor. The code line will go there. It will look something like

Private Sub Form_Current()
Me.RecordCount.Requery
End Sub

The first and last lines will be automatically inserted for you. The Requery
should get rid of the problem you mention and is why I had added it, I had
the same problem.

--
Wayne Morgan
MS Access MVP

Wayne,

thanks! One more final question though...

The record count on the subform is first incorrect, then adjusts and
provides the correct total number of records on the subform.

Let's say I have a company with 6 contacts... here's what I see on the form.

Procedure:
- select the company name from form that only contains a combo box
- latter brought up the "main form" with the apppropriate "company"
selection
- click "Page 2" on mainform which then brings up subform
- Record count indicates "1 of 1" (should be 1 of 6)
- use the command button to move to next record
- now it says "2 of 6", "3 of 6", etc....
- if I go back to first record again, it NOW says "1 of 6"

Why is that? When I added the "Me.RecordCount.Requery" on the subforms'
OnCurrent event and then saved the subform, an Access dialog box pops up.
The error message says: "Microsoft Access can't find the macro 'Me.'"

Am I still missing something?

Thanks in advance for more info.

--

Tom

2) The code sample is for a textbox on the main form that is refering

to
the

subform If the textbox is on the subform

=[Recordset].[AbsolutePosition] + 1 & " of " & [Recordset].[RecordCount]

I also had to add Me.NameOfTextbox.Requery to the subform's Current event.

1) The Visible property is for the subform control, not the subform. The
subform control is a control on the main form. The subform is held in this
control. Double click the detail section background of the main form

to
open

the Properties sheet if it isn't already open then click on the

subform
ONE

time. The properties sheet will now show the properties for the subform
control. If you click on the subform a second time, you'll be in the subform
and the properties sheet will show the properties of the subform, not the
control holding it. However, I'm not sure what you would want to do to
activate it. A button click would be one option. You could also do so once
you enter a certain control on the main form.

--
Wayne Morgan
MS Access MVP


Wayne:

Thanks again for the info.

Two more follow up questions though...

1. If I didn't want to go w/ the "Gray Textbox solution" but wanted

to
use

the Visible/Not Visbible property, how do I do that?

On the mainform, I don't see the "visible property" of the subform.

If

I
set the value to "no" on the subform, then it certainly does not

show

up

at
all on the mainform. Am I missing something?

2. Record count... I added a textbox onto the subform (it's called
"sfrmContacts"). I called its name on the Other tab "RecordCount". When
I
now open the subform, the textbox shows "#Name?".

Is the below correct?

=[RecordCount].[Form].[Recordset].[AbsolutePosition] + 1 & " of " &
[RecordCount].[Form].[Recordset].[RecordCount]

Thanks,
Tom



message
You can find the number of records, but there may be an easier

way.

To

keep
the subform from showing when on Page 1, you may want to set the visible
property of the subform control to False or place a rectangle over the
top
of the subform and set the visible property of the rectangle to false
when
viewing the subform.

To get the information you are after and place it in a textbox you can
use
Me.Recordset.RecordCount and Me.Recordset.AbsolutePosition. If you use
these
in the control source of a textbox, it won't recognize the Me

word,
just

leave it off or give the full path to the form. You could place a
textbox
on
the main form that refers to the subform if you prefer. The control
source
for that would look like

=[NameOfSubformControl].[Form].[Recordset].[AbsolutePosition] + 1

&

"

of
"
&
[NameOfSubformControl].[Form].[Recordset].[RecordCount]

--
Wayne Morgan
MS Access MVP


Wayne:

Thank you so much for sharing the info with me.... yes, you're
right...
after I changed some of the settings, I was able to view the record
selectors for the subform.

I now only need to figure out one more thing...

I use a page break on the main form. The page break separates the
info
of
the main form and the subform.

For visualization purposes, I have created a fields layout that makes
it
look as if I had 2 different forms that allows to switch back and
forth.

I order to view the navigation buttons of the subform though, I have
to
increase the height of the mainform. As a result of that, when on
"Page
1"
(containing info of only the mainform), I see bits and pieces of the
subform
(which I don't want).

To overcome this problem, I actually added 4 individual command
buttons
on
the subform so that I can reduce the height of the subform but still
can
navigate between the records.

So far so good... here's the problem now....

Using the individual command buttons, I cannot see if I have "1

of

1

record"
or e.g. "1 of 6 records". Is there a way to somehow show how many
records
a subform has (without using the navigation buttons? Again, I

set
the

"navigation button" = "no".

Thanks,
Tom





message
In Access 2003, I just opened a main form that has a subform using
the
syntax you have with the Link Criteria. I set the subform to Single
Form
view prior to opening the main form. It showed the record selectors
in
the
subform. Try clicking into the subform and use the Page Up and Page
Down
keys to see if you can move between records. If so, the

records
are

there
and the problem is just the display of the record selectors.

You
may

try
increasing the Height of the subform control on the main form

to
see

if
it
exposes them. There might be something slightly different in the
display
when the main form is filtered.

There are also some know problems with filters on forms that have
subforms.
See if any of these may apply.
http://members.iinet.net.au/~allenbrowne/bug-02.html

--
Wayne Morgan
MS Access MVP


I have a problem with forms and subform.

TABLE STRUCTURE BACKGROUND INFO
- tblCompanies (a "Company" can have many "Contacts")
- tblContacts (a "Contact" can have many "Notes")
- tblNotes


FORMS BACKGROUND INFO:
- A main form (frmCompanies) is linked to "tblCompanies" (Single
Form
view)
- "frmCompanies" has a subform (sfrmContacts) that is linked to
"tblContacts". The sfrmContacts is also in "Single Form" view.
- "sfrmContacts" then also has a subform "sfrmNotes" (this the
view
is
in
datasheet view).


PROCESS HOW RECORDS ARE SELECTED:
- I have another form "frmSelect" that only contains a combo box.
- The combo box contains the following AfterUpdate event (betweens
the
***s)
- When frmSelect is opened, I can select a company name

which
then

will
open
the frmCompanies (and its underlying subforms).... so far so
good!!!


************************

Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[CompanyName] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![cboCompany]
&
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click


End Sub

************************


HERE NOW IS THE PROBLEM:
- When a company name is selected from the frmSelect, the
frmCompanies
shows
"(Filtered") next to the navigation buttons. I expected this
since
I
selected a specific record from the entire record set of
companies.
- However, if the selected company has "more than 1

contacts"

I

 

[Wayne Morgan]

Placing the requery in the subform's Current event is how I got rid of the 1
of 1 problem. The Current event was running late enough after the form
loaded to get a good count. Is RecordCount the name you gave to the textbox
that this information is in? Is there anything else with that name?

 

[Tom]

Wayne:

Thanks again for the reply... I have verified what you have suggested but I
couldn't find another detail/name that has also "RecordCount" for its name.
Below is some more info about the subform and textbox details.



Subform Info:
Name: sfrmContacts

Events: Private Sub Form_Current()
Me.RecordCount.Requery
End Sub


Textbox Info on subform:
Name RecordCount
Controlsource =Recordset.AbsolutePosition+1 & " of " & Recordset.RecordCount
Events: None selected


Do you have any additional suggestions as to how this can be fixed? Thanks
in advance.

 

[Wayne Morgan]

Basiclly, it is a timing issue. I believe the subform loads before the main
form. You may want to move the requery to the main forms current event. The
syntax would change slightly:

Me.NameOfSubformControl.Form.RecordCount.Requery

 

[Tom]

Wayne:

I'm giving up on this.... I thought the "NameOfSubformControl" =
"RecordCount".

Therefore I would interpret this...

change
Me.NameOfSubformControl.Form.RecordCount.Requery

to
Me.RecordCount.Form.RecordCount.Requery

well, I'm giving up on this.... Thanks anyhow for your help on this.

 

[Wayne Morgan]

Tom,

NameOfSubformControl is the name of a control on the main form that is of
the type Subform Control. It is what actually holds the subform on the main
form. To get this name, open the main form in design mode, open the
Properties sheet, and click on the subform ONE time. The properties sheet
should show the name of the subform control. If you click on the subform a
second time, you will be in the subform and the Properties sheet will show
the name of the subform itself, not the control holding it; however,
depending on how you put the subform on the main form, they may both be the
same name.

 

[Tom]

Again, thanks so much for providing all of your replies to this thread. I
truly appreciate it. Although the count still not works properly, I
definitely have learned a great deal about the "requery" function.

Tom