Recordsource Count

[TonyT]

Hi,

I have the following code in the on_Open event of a form opened from another
form;

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc

How can I find how many records, if any, fill the criteria? eg if 0 then i
will make form data entry, if 1 I will default to data entry = false if more
than 1 then I can add further code.

thanks in advance.

 

[Klatuu]

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc
lngRecs = Me.Recordset.RecordCount
lngRecs will now contain the number of records matching your criteria.

 

[TonyT]

Thats what I thought and have tried, but the variable (lngRecs in your
example lngRecCount in my previous atempts) always returns 0 whereas the form
displays the correct number (eg 8 for a value of lngCRefNum = 69) in a
combobox basically running the same code constructed on the fly, as does a
query created as a copy and paste into SQL view substituting the variable
with the number 69.

Is there a way to iterate thru the records selected with the strSQLRecSrc in
the vb editor so i can better see whats going wrong?

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc
lngRecs = Me.Recordset.RecordCount
lngRecs will now contain the number of records matching your criteria.

Hi,

I have the following code in the on_Open event of a form opened from another
form;

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc

How can I find how many records, if any, fill the criteria? eg if 0 then i
will make form data entry, if 1 I will default to data entry = false if more
than 1 then I can add further code.

thanks in advance.

 

[Klatuu]

What you get from a recordcount depends on when you call it. For example, if
you call it in the form Open event, you will probably get 1 or 0. That is
because the recordset has not yet been fully populated. To get an accurate
count. Do a MoveLast, MoveFirst before you do the RecordCount.

Thats what I thought and have tried, but the variable (lngRecs in your
example lngRecCount in my previous atempts) always returns 0 whereas the form
displays the correct number (eg 8 for a value of lngCRefNum = 69) in a
combobox basically running the same code constructed on the fly, as does a
query created as a copy and paste into SQL view substituting the variable
with the number 69.

Is there a way to iterate thru the records selected with the strSQLRecSrc in
the vb editor so i can better see whats going wrong?

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc
lngRecs = Me.Recordset.RecordCount
lngRecs will now contain the number of records matching your criteria.

Hi,

I have the following code in the on_Open event of a form opened from another
form;

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc

How can I find how many records, if any, fill the criteria? eg if 0 then i
will make form data entry, if 1 I will default to data entry = false if more
than 1 then I can add further code.

thanks in advance.

 

[Ken Snell [MVP]]

Add these steps to the end of your code:

DoEvents
Me.DataEntry = (Me.RecordsetClone.RecordCount = 0)

 

[TonyT]

I have got it working using klatuu's recommendation, which i used as i
understood it, would you be kind enough to explain what your version actually
does / how it works.

many thanks.

Add these steps to the end of your code:

DoEvents
Me.DataEntry = (Me.RecordsetClone.RecordCount = 0)

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have the following code in the on_Open event of a form opened from
another
form;

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc

How can I find how many records, if any, fill the criteria? eg if 0 then i
will make form data entry, if 1 I will default to data entry = false if
more
than 1 then I can add further code.

thanks in advance.

 

[Ken Snell [MVP]]

It sets the value of the DataEntry property based on whether there are zero
records in the form's recordsource or at least one record.

If there are zero records, the DataEntry property is set to True.

If there is at least one record, the DataEntry property is set to False.

The RecordsetClone object is used for testing the recordcount because it
usually has a more accurate count of the form's records than the Recordset
object.

--

Ken Snell
<MS ACCESS MVP>

I have got it working using klatuu's recommendation, which i used as i
understood it, would you be kind enough to explain what your version
actually
does / how it works.

many thanks.

Add these steps to the end of your code:

DoEvents
Me.DataEntry = (Me.RecordsetClone.RecordCount = 0)

--

Ken Snell
<MS ACCESS MVP>

Hi,

I have the following code in the on_Open event of a form opened from
another
form;

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc

How can I find how many records, if any, fill the criteria? eg if 0
then i
will make form data entry, if 1 I will default to data entry = false if
more
than 1 then I can add further code.

thanks in advance.

 

[TonyT]

Hi again,

Your solution worked fine yesterday, but just looked at it again this
morning and now i'm getting error 3021 'no current record' on the same
information that worked fine yesterday, should i be moving the code to the
On_load event or elsewhere to let it process fully before counting?

What you get from a recordcount depends on when you call it. For example, if
you call it in the form Open event, you will probably get 1 or 0. That is
because the recordset has not yet been fully populated. To get an accurate
count. Do a MoveLast, MoveFirst before you do the RecordCount.

Thats what I thought and have tried, but the variable (lngRecs in your
example lngRecCount in my previous atempts) always returns 0 whereas the form
displays the correct number (eg 8 for a value of lngCRefNum = 69) in a
combobox basically running the same code constructed on the fly, as does a
query created as a copy and paste into SQL view substituting the variable
with the number 69.

Is there a way to iterate thru the records selected with the strSQLRecSrc in
the vb editor so i can better see whats going wrong?

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc
lngRecs = Me.Recordset.RecordCount
lngRecs will now contain the number of records matching your criteria.
:

Hi,

I have the following code in the on_Open event of a form opened from another
form;

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc

How can I find how many records, if any, fill the criteria? eg if 0 then i
will make form data entry, if 1 I will default to data entry = false if more
than 1 then I can add further code.

thanks in advance.

 

[TonyT]

For the record the error is being fired at the me.Recordset.movelast line of
code.

Hi again,

Your solution worked fine yesterday, but just looked at it again this
morning and now i'm getting error 3021 'no current record' on the same
information that worked fine yesterday, should i be moving the code to the
On_load event or elsewhere to let it process fully before counting?

What you get from a recordcount depends on when you call it. For example, if
you call it in the form Open event, you will probably get 1 or 0. That is
because the recordset has not yet been fully populated. To get an accurate
count. Do a MoveLast, MoveFirst before you do the RecordCount.

Thats what I thought and have tried, but the variable (lngRecs in your
example lngRecCount in my previous atempts) always returns 0 whereas the form
displays the correct number (eg 8 for a value of lngCRefNum = 69) in a
combobox basically running the same code constructed on the fly, as does a
query created as a copy and paste into SQL view substituting the variable
with the number 69.

Is there a way to iterate thru the records selected with the strSQLRecSrc in
the vb editor so i can better see whats going wrong?

:

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc
lngRecs = Me.Recordset.RecordCount
lngRecs will now contain the number of records matching your criteria.
:

Hi,

I have the following code in the on_Open event of a form opened from another
form;

If IsLoaded("frmCustInfo1") Then
lngCRefNum = [Forms]![frmCustInfo1].[CID]
strSQLRecSrc = "SELECT * FROM tblCont WHERE ContCRefNum = " &
lngCRefNum & ""
Me.RecordSource = strSQLRecSrc

How can I find how many records, if any, fill the criteria? eg if 0 then i
will make form data entry, if 1 I will default to data entry = false if more
than 1 then I can add further code.

thanks in advance.