SubForm Nor Refreshed Correctly When Directional Arrow Used

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

I have a form with a subform. When a certain control (txtSCRID) changes
value on the form, a different filter is applied to the subform. The
subform's default view is DataSheet and rows from a table are displayed in
it. This works well except on the subform at the bottom where it says "Record
1 of 4 (Filtered)" (when I have 4 rows).

When 3 rows are displayed in the subform, it will display the count of rows
from the previous recordset until I hit the right arrow to get the next row
and then I'll see "Record 1 of 3 (Filtered)".

I'm using this code :
Private Sub txtSCRID_LostFocus()
Me.[SCR_Links_Subform].Form.FilterOn = True
If Not IsNull(Me.txtSCRID) Then
Me.[SCR_Links_Subform].Form.Filter = "LinkedSCRorDefect <> "
& Me.txtSCRID
End If
End Sub

I get the correct rows coming up on the subform. What's incorrect is the
count of rows returned that shows up at the bottom of the grid on the
subform. Once I hit the right arrow it then displays correctly.

Any suggestions will be greatly appreciated.

Rita
 
RitaG said:
Hi.

I have a form with a subform. When a certain control (txtSCRID)
changes value on the form, a different filter is applied to the
subform. The subform's default view is DataSheet and rows from a
table are displayed in it. This works well except on the subform at
the bottom where it says "Record 1 of 4 (Filtered)" (when I have 4
rows).

When 3 rows are displayed in the subform, it will display the count
of rows from the previous recordset until I hit the right arrow to
get the next row and then I'll see "Record 1 of 3 (Filtered)".

I'm using this code :
Private Sub txtSCRID_LostFocus()
Me.[SCR_Links_Subform].Form.FilterOn = True
If Not IsNull(Me.txtSCRID) Then
Me.[SCR_Links_Subform].Form.Filter = "LinkedSCRorDefect <> "
& Me.txtSCRID
End If
End Sub

I get the correct rows coming up on the subform. What's incorrect is
the count of rows returned that shows up at the bottom of the grid on
the subform. Once I hit the right arrow it then displays correctly.

Any suggestions will be greatly appreciated.

Rita
Click to expand...

Is there some reason you aren't using normal subform linkage via the
subform control's Link Master Fields and Link Child Fields properties?
Then you wouldn't need any special code to manage the linkage, and
whatever is going wrong with the record counts wouldn't be happening.
 
Hi Dirk,

I do use the normal subform linkage via the subform control's Link Master
Fields and Link Child Fields properties (they link to a BaseNbr). The extra
filter I'm applying is to remove the returned row if it's LinkedSCROrDefect =
BaseNbr.

Rita

Dirk Goldgar said:
RitaG said:
Hi.

I have a form with a subform. When a certain control (txtSCRID)
changes value on the form, a different filter is applied to the
subform. The subform's default view is DataSheet and rows from a
table are displayed in it. This works well except on the subform at
the bottom where it says "Record 1 of 4 (Filtered)" (when I have 4
rows).

When 3 rows are displayed in the subform, it will display the count
of rows from the previous recordset until I hit the right arrow to
get the next row and then I'll see "Record 1 of 3 (Filtered)".

I'm using this code :
Private Sub txtSCRID_LostFocus()
Me.[SCR_Links_Subform].Form.FilterOn = True
If Not IsNull(Me.txtSCRID) Then
Me.[SCR_Links_Subform].Form.Filter = "LinkedSCRorDefect <> "
& Me.txtSCRID
End If
End Sub

I get the correct rows coming up on the subform. What's incorrect is
the count of rows returned that shows up at the bottom of the grid on
the subform. Once I hit the right arrow it then displays correctly.

Any suggestions will be greatly appreciated.

Rita
Click to expand...

Is there some reason you aren't using normal subform linkage via the
subform control's Link Master Fields and Link Child Fields properties?
Then you wouldn't need any special code to manage the linkage, and
whatever is going wrong with the record counts wouldn't be happening.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
RitaG said:
Hi Dirk,

I do use the normal subform linkage via the subform control's Link
Master Fields and Link Child Fields properties (they link to a
BaseNbr). The extra filter I'm applying is to remove the returned row
if it's LinkedSCROrDefect = BaseNbr.
Click to expand...

Oh, I see now. Sorry, I was tired last night.

I don't know why you are getting the behavior you report. I see that in
your code, you are effectively executing the filtering statements in
this order:

Me.[SCR_Links_Subform].Form.FilterOn = True

Me.[SCR_Links_Subform].Form.Filter = _
"LinkedSCRorDefect <> " & Me.txtSCRID

Does it make any difference if you reverse the order of the statements?
There are some quirks in the filtering logic with regard to subforms and
main forms, so it seems to me that it's worth a try.

I wouldn't use the text box's LostFocus event for this, by the way; I'd
use the AfterUpdate event, since that fires if and only if the value in
the text box has changed.

I suppose you may be able to work around the problem by having the
subform move to the next record and back:

' *** untested AIR CODE ***

Private Sub txtSCRID_AfterUpdate()

With Me.[SCR_Links_Subform].Form

If IsNull(Me.txtSCRID) Then
.FilterOn = False
Else

.Filter = "LinkedSCRorDefect <> "& Me.txtSCRID
.FilterOn = True

With .Recordset
If .RecordCount > 0 Then
.MoveNext
.MoveFirst
End If
End With

End If

End With

End Sub

It's also conceivable that you can make it behave without changing
records, just by forcing the subform's RecordsetClone to move to the
last record. I don't know.
 
RitaG said:
Hi Dirk,

I do use the normal subform linkage via the subform control's Link
Master Fields and Link Child Fields properties (they link to a
BaseNbr). The extra filter I'm applying is to remove the returned row
if it's LinkedSCROrDefect = BaseNbr.
Click to expand...

Also, what about requerying the subform after you set its filter? Does
that make the record count come out right?
 
Hi Dirk,

Thanks for the suggestions. I tried them all but to no avail.
BTW, I had to keep the LostFocus event as the AfterUpdate event was not
fired when I need it to be.

Thanks.

Rita
 
RitaG said:
Hi Dirk,

Thanks for the suggestions. I tried them all but to no avail.
Click to expand...

Huh. I may have time to attempt to reproduce this over the weekend.
BTW, I had to keep the LostFocus event as the AfterUpdate event was
not fired when I need it to be.
Click to expand...

I think you are probably missing a bet, but suit yourself. That's not
the problem we're trying to fix, anyway.
 
Dirk,

I step debugged through the code and I finally realized that the value in
Me.txtSCRID
was not the value I needed. I'm using the "Find" screen to find a particular
SCRID and it's the value in the "Find and Replace" form that I need for the
SCRID.

I need to play around with the different events and see if there's one I can
use.

Thanks for all your help.
Rita
 
RitaG said:
Dirk,

I step debugged through the code and I finally realized that the
value in Me.txtSCRID
was not the value I needed. I'm using the "Find" screen to find a
particular SCRID and it's the value in the "Find and Replace" form
that I need for the SCRID.

I need to play around with the different events and see if there's
one I can use.
Click to expand...

Good luck! Post back if you get stuck. I don't see how you're going to
find out what SCRID was searched for, unless it was found and is now the
current record.
 
Hi Dirk,

I finally figured it out. Without your suggestions I would not have been
able to do it - so thanks!

I placed the following code in the Main form's Current event and it now
works as required:

Me.[SCR_Links_Subform].Form.FilterOn = True
If Not IsNull(Me.txtSCRID) Then
Me.[SCR_Links_Subform].Form.Filter = "LinkedSCRorDefect <> " &
Me.txtSCRID
Me.SCR_Links_Subform.Requery
End If

Rita
 
RitaG said:
Hi Dirk,

I finally figured it out. Without your suggestions I would not have
been able to do it - so thanks!

I placed the following code in the Main form's Current event and it
now works as required:

Me.[SCR_Links_Subform].Form.FilterOn = True
If Not IsNull(Me.txtSCRID) Then
Me.[SCR_Links_Subform].Form.Filter = "LinkedSCRorDefect <> " &
Me.txtSCRID
Me.SCR_Links_Subform.Requery
End If
Click to expand...

You're welcome. I'm glad you found a solution.
 
Back
Top