Need to Count Records in Query Result set

[Guest]

I have a Query named FRQ-Search-By-Case that does a left join between primary
table and secondary table based on primary key match between tables.

I determine if there are matching records with the following which works
well, but the problem is I also want to store the number of records found in
the following statement to be used for a record counter used later in the
same program.

How do I reference later the value stored in DCount, (say if the resulting
query has 5 records in it) I want to store the value of 5 in my working
record counter field for scrolling up/down through form changing records as I
go.

If DCount("*", "FRQ-Search-By-Case", "[DPS_FR_CASE_RECORDS.CASE_NUM_YR]= " &
Me.[CASE_NUM_YR_P1] & _
" And [DPS_FR_CASE_RECORDS.CASE_NUM]= " & Me.[CASE_NUM_P1]) = 0 Then
MsgBox " No Matching Records Found, Form-Current", vbOKOnly,
"Form: FRF-CR-U"
GoTo ByPass_FormCurrent_Sub
Else
MsgBox " Matching Records Found ", vbOKOnly, "Form: FRF-CR-U"
End If

Thanks in advance

 

[UpRider]

You might need to move your current DCOUNT code off the form into a module,
and run it from a command button on the form after the criteria is filled
in.
If your Dcount returns > 0 then you want to set your form recordsource (or
filter) to the same criteria in the Dcount so that your form "sees" only
those records.

HTH, UpRider

 

[Guest]

The query result set is the records source for the current form. The form
opens with the correct data in the fields, and when you page to next record
or previous record you see the proper records, I just want to be able to have
a record count available to the user, such as the results you see when you
open a form with multiple records, you see record 1 of 200 or record 5 of
200 ... etc. I want to take get the last record number and get that number,
store it so that i do not exceed results unless the user wants to add more
records via update entry functions.

Thanks for responding so quickly.



--
Robert Nusz
Sr. Programmer Analyst II

You might need to move your current DCOUNT code off the form into a module,
and run it from a command button on the form after the criteria is filled
in.
If your Dcount returns > 0 then you want to set your form recordsource (or
filter) to the same criteria in the Dcount so that your form "sees" only
those records.

HTH, UpRider

I have a Query named FRQ-Search-By-Case that does a left join between
primary
table and secondary table based on primary key match between tables.

I determine if there are matching records with the following which works
well, but the problem is I also want to store the number of records found
in
the following statement to be used for a record counter used later in the
same program.

How do I reference later the value stored in DCount, (say if the resulting
query has 5 records in it) I want to store the value of 5 in my working
record counter field for scrolling up/down through form changing records
as I
go.

If DCount("*", "FRQ-Search-By-Case", "[DPS_FR_CASE_RECORDS.CASE_NUM_YR]= "
&
Me.[CASE_NUM_YR_P1] & _
" And [DPS_FR_CASE_RECORDS.CASE_NUM]= " & Me.[CASE_NUM_P1]) = 0
Then
MsgBox " No Matching Records Found, Form-Current", vbOKOnly,
"Form: FRF-CR-U"
GoTo ByPass_FormCurrent_Sub
Else
MsgBox " Matching Records Found ", vbOKOnly, "Form: FRF-CR-U"
End If

Thanks in advance

 

[UpRider]

MyVariable = Dcount(........
MyVariable can be global or you can immediately store the value in an
unbound text box on the form....

UpRider

The query result set is the records source for the current form. The form
opens with the correct data in the fields, and when you page to next
record
or previous record you see the proper records, I just want to be able to
have
a record count available to the user, such as the results you see when you
open a form with multiple records, you see record 1 of 200 or record 5
of
200 ... etc. I want to take get the last record number and get that
number,
store it so that i do not exceed results unless the user wants to add more
records via update entry functions.

Thanks for responding so quickly.



--
Robert Nusz
Sr. Programmer Analyst II

You might need to move your current DCOUNT code off the form into a
module,
and run it from a command button on the form after the criteria is filled
in.
If your Dcount returns > 0 then you want to set your form recordsource
(or
filter) to the same criteria in the Dcount so that your form "sees" only
those records.

HTH, UpRider

I have a Query named FRQ-Search-By-Case that does a left join between
primary
table and secondary table based on primary key match between tables.

I determine if there are matching records with the following which
works
well, but the problem is I also want to store the number of records
found
in
the following statement to be used for a record counter used later in
the
same program.

How do I reference later the value stored in DCount, (say if the
resulting
query has 5 records in it) I want to store the value of 5 in my working
record counter field for scrolling up/down through form changing
records
as I
go.

If DCount("*", "FRQ-Search-By-Case",
"[DPS_FR_CASE_RECORDS.CASE_NUM_YR]= "
&
Me.[CASE_NUM_YR_P1] & _
" And [DPS_FR_CASE_RECORDS.CASE_NUM]= " & Me.[CASE_NUM_P1]) = 0
Then
MsgBox " No Matching Records Found, Form-Current", vbOKOnly,
"Form: FRF-CR-U"
GoTo ByPass_FormCurrent_Sub
Else
MsgBox " Matching Records Found ", vbOKOnly, "Form: FRF-CR-U"
End If

Thanks in advance