Problem with RecordCount or RecordClone!

[Niklas Östergren]

Hi!

I have found out that I suddenly have problem with either RecordCount or
RecordClone (or something else).

I have some code which goes in a forms Open_Event (see below). This is a
main form (frmListMembers) in which I have a subform (frmsubListMembers). On
frmListMemebers form_foot I have a textbox (txtNumberOfPersons) in which I
want to display how many members are listed in the subform
frmsubListMembers.

I knwo that this code have worked a couple of weeks ago but it doesn´t right
now and I have made several changes to the mainform since then and I´m
having problem to find out why it doesn´t work anylonger.

TIA!
// Niklas


This is the code I use:
========================================================
Private Sub Form_Open(Cancel As Integer)

Dim recFormListmembers As Recordset

Set recFormListmembers = Me.frmsubListMembers.Form.RecordsetClone

Me.txtNumberOfPersons = recFormListmembers.RecordCount

recFormListmembers.Close

' Set focus on cboSortOrder
Me.cboSortOrder.SetFocus

End Sub
========================================================

 

[Dirk Goldgar]

Hi!

I have found out that I suddenly have problem with either RecordCount
or RecordClone (or something else).

I have some code which goes in a forms Open_Event (see below). This
is a main form (frmListMembers) in which I have a subform
(frmsubListMembers). On frmListMemebers form_foot I have a textbox
(txtNumberOfPersons) in which I want to display how many members are
listed in the subform frmsubListMembers.

I knwo that this code have worked a couple of weeks ago but it
doesn´t right now and I have made several changes to the mainform
since then and I´m having problem to find out why it doesn´t work
anylonger.

TIA!
// Niklas


This is the code I use:
========================================================
Private Sub Form_Open(Cancel As Integer)

Dim recFormListmembers As Recordset

Set recFormListmembers = Me.frmsubListMembers.Form.RecordsetClone

Me.txtNumberOfPersons = recFormListmembers.RecordCount

recFormListmembers.Close

' Set focus on cboSortOrder
Me.cboSortOrder.SetFocus

End Sub
========================================================

You don't say in what way it doesn't work, but my guess would be that
you're not getting an accurate count, because you're not forcing the
recordset to move to the last record before getting the count. Also,
you *should not* close the recordsetclone, since you didn't open it.
Try this variation:

'----- start of revised code -----
Private Sub Form_Open(Cancel As Integer)

With Me.frmsubListMembers.Form.RecordsetClone
If .EOF Then
Me.txtNumberOfPersons = 0
Else
.MoveLast
Me.txtNumberOfPersons = .RecordCount
End If

' Set focus on cboSortOrder
Me.cboSortOrder.SetFocus

End Sub

'----- end of revised code -----

Note that this won't automatically adjust the count in the text box if
records are added or deleted from the subform, and it assumes the
subform is not linked to, and thus filtered by, any fields on the main
form. If it were, then the count would be inaccurate as you move from
record to record on the main form.

 

[Niklas Östergren]

Sorry that I wasn´t clear enough. The problem I have (and still have even
with your example) is that RecordCount is giving the value 0
(Me.txtNumberOfPersons = 0) but there sure ar records in the underlying
query (qryValidMemberShip) of the subform. I´m in the devlopfase so I only
have 3 records but it should work anyway!

I have tryed with your code but still get Me.txtNumberOfPersons = 0!

Any mor idéas?

// Niklas

 

[Niklas Östergren]

It work´s if I use DCount function but if I have understand this correct I
should try to only use D-functions for single records (more or less) and
here we are going to have between 200 - 350 records which needs to be
counted every time this form is opened! So I´d like to use another method
for this this time!

The strange thing is that the code I have HAVE worked before so there must
be some problem whith it OR some other code that´s not correct. And I don´t
have any other function writing to control txtNumberOfPersons.

// Niklas

 

[Niklas Östergren]

Hmm, strange!

I´v got it to work now (but for how longe I don´t know).

What i didd is to open up the subform in design mode and delete propertie
"Sort By" (which was [LastName] & [FirstName]).

I sort the subform using a combobox on the mainform where the user can
select different criteria to sort by (see code below).

Any ídéas what may have caused this and what I could do to prevet it from
happening in the future?

TIA!
// Niklas


Using this code to sort the subform:
==============================================================
Private Sub cboSortOrder_AfterUpdate()

Dim frm As Form
Dim strSQL As String

Set frm = Me.frmsubListMembers.Form

' Set record source
frm.RecordSource = "qryValidMemberShip"

' Select sort order for form frmsubListMembers
Select Case Me.cboSortOrder

Case "Efternamn"
frm.OrderBy = "[LastName] & [FirstName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, LastName & ' ' & [FirstName] " _
& " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY LastName & [FirstName]"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "FödelseDatum"
frm.OrderBy = "[DoB]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, DoB & ', ' & LastName " _
& " & ' ' & [FirstName] AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY DoB"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "Förnamn"
frm.OrderBy = "[FirstName] & [LastName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, [FirstName] & ' ' & LastName " _
& " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY [FirstName] & LastName"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "Medlemsnummer"
frm.OrderBy = "[MemberNo]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, MemberNo & ', ' & LastName " _
& " & ' ' & [FirstName] AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY MemberNo"
Me.cboSearchfrmsubListMembers.RowSource = strSQL


Case Else
frm.OrderBy = "[LastName] & [FirstName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, LastName & ' ' & [FirstName] " _
& " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY LastName & [FirstName]"
Me.cboSearchfrmsubListMembers.RowSource = strSQL


End Select

' Set focus to subform control and requery
Me.frmsubListMembers.SetFocus

' Requery cboSearchfrmsubListMembers
cboSearchfrmsubListMembers.Requery

' Set focus on cboSearchfrmsubListMembers
Me.cboSearchfrmsubListMembers.SetFocus


Set frm = Nothing


End Sub
===============================================================

 

[Dirk Goldgar]

Hmm, strange!

I´v got it to work now (but for how longe I don´t know).

What i didd is to open up the subform in design mode and delete
propertie "Sort By" (which was [LastName] & [FirstName]).

I sort the subform using a combobox on the mainform where the user can
select different criteria to sort by (see code below).

Any ídéas what may have caused this and what I could do to prevet it
from happening in the future?

TIA!
// Niklas


Using this code to sort the subform:
==============================================================
Private Sub cboSortOrder_AfterUpdate()

Dim frm As Form
Dim strSQL As String

Set frm = Me.frmsubListMembers.Form

' Set record source
frm.RecordSource = "qryValidMemberShip"

' Select sort order for form frmsubListMembers
Select Case Me.cboSortOrder

Case "Efternamn"
frm.OrderBy = "[LastName] & [FirstName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, LastName & ' ' & [FirstName] "
_ & " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY LastName & [FirstName]"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "FödelseDatum"
frm.OrderBy = "[DoB]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, DoB & ', ' & LastName " _
& " & ' ' & [FirstName] AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY DoB"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "Förnamn"
frm.OrderBy = "[FirstName] & [LastName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, [FirstName] & ' ' & LastName "
_ & " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY [FirstName] & LastName"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "Medlemsnummer"
frm.OrderBy = "[MemberNo]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, MemberNo & ', ' & LastName " _
& " & ' ' & [FirstName] AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY MemberNo"
Me.cboSearchfrmsubListMembers.RowSource = strSQL


Case Else
frm.OrderBy = "[LastName] & [FirstName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, LastName & ' ' & [FirstName] "
_ & " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY LastName & [FirstName]"
Me.cboSearchfrmsubListMembers.RowSource = strSQL


End Select

' Set focus to subform control and requery
Me.frmsubListMembers.SetFocus

' Requery cboSearchfrmsubListMembers
cboSearchfrmsubListMembers.Requery

' Set focus on cboSearchfrmsubListMembers
Me.cboSearchfrmsubListMembers.SetFocus


Set frm = Nothing


End Sub
===============================================================

I'm not sure what's going on, Niklas, but there are some things in that
code that I don't quite follow. First, why are you resetting the
subform's recordsource, with

' Set record source
frm.RecordSource = "qryValidMemberShip"

, every time cboSortOrder is updated? Is there some reason to believe
it has changed? Are you aware that changing a form's RecordSource
property forces a requery of the form?

Second, your explicit requery of cboSearchfrmsubListMembers, with

' Requery cboSearchfrmsubListMembers
cboSearchfrmsubListMembers.Requery

, isn't necessary, because your earlier code changes its RowSource
property, and changing the RowSource property of a combo or list box
forces a requery of the control.

I also don't understand why you are setting the focus first to the
subform control and then to the combo box on the main form. There would
seem to be no point in setting the focus to the subform control,
although I guess it's harmless.

I note that you are continually sorting by the expression [LastName] &
[FirstName], rather than sorting by the two fields themselves:
[LastName], [FirstName]. Are you doing this on purpose, to get a rather
unusual sort order? Consider: the following records are shown in the
order resulting from "Order By LastName, FirstName":

LastName FirstName
----------- -----------
A BCDEF
AB CDE
ABC D

Here's the result of "Order By LastName & FirstName":

LastName FirstName
----------- -----------
ABC D
AB CDE
A BCDEF

So it makes a significant difference how you specify your Order By
clause in a query, or the Order By property of a form. Also note that
if you have indexes on LastName and FirstName, "Order By LastName &
FirstName" won't be able to take advantage of them, but "Order By
LastName, FirstName" will.

All that doesn't help much with the specific problem of your
txtRecordCount control. Possibly you are changing the subform's
recordsource before your code has a change to pick up the value. But
why not just put a control in the subform's form footer (even if that
footer is set to be invisible), with its ControlSource set to
"=Count(*)" ? Then you could have a calculated control on the main form
that just picks up the count value from that control on the subform, and
I don't think any code would be needed.

 

[Niklas Östergren]

Hmm, strange!

I´v got it to work now (but for how longe I don´t know).

What i didd is to open up the subform in design mode and delete
propertie "Sort By" (which was [LastName] & [FirstName]).

I sort the subform using a combobox on the mainform where the user can
select different criteria to sort by (see code below).

Any ídéas what may have caused this and what I could do to prevet it
from happening in the future?

TIA!
// Niklas


Using this code to sort the subform:
==============================================================
Private Sub cboSortOrder_AfterUpdate()

Dim frm As Form
Dim strSQL As String

Set frm = Me.frmsubListMembers.Form

' Set record source
frm.RecordSource = "qryValidMemberShip"

' Select sort order for form frmsubListMembers
Select Case Me.cboSortOrder

Case "Efternamn"
frm.OrderBy = "[LastName] & [FirstName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, LastName & ' ' & [FirstName] "
_ & " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY LastName & [FirstName]"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "FödelseDatum"
frm.OrderBy = "[DoB]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, DoB & ', ' & LastName " _
& " & ' ' & [FirstName] AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY DoB"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "Förnamn"
frm.OrderBy = "[FirstName] & [LastName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, [FirstName] & ' ' & LastName "
_ & " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY [FirstName] & LastName"
Me.cboSearchfrmsubListMembers.RowSource = strSQL

Case "Medlemsnummer"
frm.OrderBy = "[MemberNo]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, MemberNo & ', ' & LastName " _
& " & ' ' & [FirstName] AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY MemberNo"
Me.cboSearchfrmsubListMembers.RowSource = strSQL


Case Else
frm.OrderBy = "[LastName] & [FirstName]"
frm.OrderByOn = True
strSQL = "SELECT PersonID, LastName & ' ' & [FirstName] "
_ & " & ', ' & Street AS searchSource " _
& "FROM qryValidMemberShip " _
& "ORDER BY LastName & [FirstName]"
Me.cboSearchfrmsubListMembers.RowSource = strSQL


End Select

' Set focus to subform control and requery
Me.frmsubListMembers.SetFocus

' Requery cboSearchfrmsubListMembers
cboSearchfrmsubListMembers.Requery

' Set focus on cboSearchfrmsubListMembers
Me.cboSearchfrmsubListMembers.SetFocus


Set frm = Nothing


End Sub
===============================================================

I'm not sure what's going on, Niklas, but there are some things in that
code that I don't quite follow. First, why are you resetting the
subform's recordsource, with

' Set record source
frm.RecordSource = "qryValidMemberShip"

, every time cboSortOrder is updated? Is there some reason to believe
it has changed? Are you aware that changing a form's RecordSource
property forces a requery of the form?

Second, your explicit requery of cboSearchfrmsubListMembers, with

' Requery cboSearchfrmsubListMembers
cboSearchfrmsubListMembers.Requery

, isn't necessary, because your earlier code changes its RowSource
property, and changing the RowSource property of a combo or list box
forces a requery of the control.

I also don't understand why you are setting the focus first to the
subform control and then to the combo box on the main form. There would
seem to be no point in setting the focus to the subform control,
although I guess it's harmless.

I note that you are continually sorting by the expression [LastName] &
[FirstName], rather than sorting by the two fields themselves:
[LastName], [FirstName]. Are you doing this on purpose, to get a rather
unusual sort order? Consider: the following records are shown in the
order resulting from "Order By LastName, FirstName":

LastName FirstName
----------- -----------
A BCDEF
AB CDE
ABC D

Here's the result of "Order By LastName & FirstName":

LastName FirstName
----------- -----------
ABC D
AB CDE
A BCDEF

So it makes a significant difference how you specify your Order By
clause in a query, or the Order By property of a form. Also note that
if you have indexes on LastName and FirstName, "Order By LastName &
FirstName" won't be able to take advantage of them, but "Order By
LastName, FirstName" will.

All that doesn't help much with the specific problem of your
txtRecordCount control. Possibly you are changing the subform's
recordsource before your code has a change to pick up the value. But
why not just put a control in the subform's form footer (even if that
footer is set to be invisible), with its ControlSource set to
"=Count(*)" ? Then you could have a calculated control on the main form
that just picks up the count value from that control on the subform, and
I don't think any code would be needed.

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

(please reply to the newsgroup)

Hi Dirk!

Well you´r right! To resetting the subforms recordsource is uneccesary! I
don´t remember why I did that. Probably because I was first thinking to use
the same form in two different purpuses. Showing members and showing people
in our db (not members). Which I don´t so that line of code is gone.

You have spoted some minor problem in my code, which´s good, so I have
removed even the requery of the combobox (cboSearchfrmsubListMembers as
well), thanks!

I´m trying to let the user select in which way he/she want to sort the
subform (list of member) which now is possible. But you have also made me
aware of the sort order problem and I´m going to change the code since it
was NOT my purpose to select this sort order. I just didn´t thought of the
result.

I´m not longer resetting the subform´s recordsource and I will therfore
leave the code and keep an eay on txtNumberOfPersons and see if the problem
accure again. If so I´ll try the solution you specifyed.

Thank´s a lot for helping out Dirk! I´ll apreciatet it and I also learn from
it!

// Niklas