Record Count in a subform? (1 Viewer)

ChampionDuy

Registered User.
Local time
Today, 17:09
Joined
Mar 14, 2002
Messages
94
I have a subform that displays one field of data called Renew Date. There are multiple renew dates that are listed in the sub form. How do I go about searching each one of those Dates and checking to see if there is one that is between 2 dates that a user enters. The If statement that I have shown below only compares the first record. I know I need to do a loop and a record count but I am not sure how to do that. The variables I use for something Else.


Y = 0
Dim LcRenew As Date

RENEW_DATE.SetFocus
LcRenew = DateValue(RENEW_DATE.Value)

If LcRenew >= StartingDate And LcRenew <= EndingDate Then
Y = 0
Else: Y = 1
End If
Ynum.SetFocus
Ynum.Text = Y
 

llkhoutx

Registered User.
Local time
Today, 12:09
Joined
Feb 26, 2001
Messages
4,018
Use a recordset based on the subform record source and then cycle through the recordset.
 

ChampionDuy

Registered User.
Local time
Today, 17:09
Joined
Mar 14, 2002
Messages
94
I am sorry but I have never done anything like that before. Could you please elaborate? Thanks
 

ChampionDuy

Registered User.
Local time
Today, 17:09
Joined
Mar 14, 2002
Messages
94
yes I am just using it now to double check my code and make sure that the comparison is working.
 

ChampionDuy

Registered User.
Local time
Today, 17:09
Joined
Mar 14, 2002
Messages
94
I have used a query and I can not get correct information. Here is the DCount that I use to count the number of records returned fromt he query. but it does not give me correct information sometimes it tells me that rows are returned and there are not any rows. Here it is maybe I got something wrong. The first part of the If Statement is fine its the two DCount Statements that are returning unreliable results. Renew_date is the field that the query, RenewQuery Returns and the same goes for the Int_Date.


If (LcAuth >= StartingDate And LcAuth <= EndingDate) Then
DoCmd.Close acForm, "QtrReport"
MsgBox ("This program Sucks")
ElseIf (LcExp >= StartingDate And LcExp <= EndingDate) Then
DoCmd.Close acForm, "QtrReport"
MsgBox (" I want to go Home ")
ElseIf (DCount("[renew_date]", "[renewquery]")) > 0 Then
DoCmd.Close acForm, "QtrReport"
MsgBox ("Renew Date works")
ElseIf (DCount("[Int_date]", "[tardatequery]")) > 0 Then
MsgBox ("IntDate Works")
DoCmd.Close acForm, "QtrReport"
Else
MsgBox ("There is no activity in this quarter")
DoCmd.OpenForm "QtrReport"
End If
 
R

Rich

Guest
It's easier to use
If RecordsetClone.RecordCount=0 Then
MsgBox"Whatever"
DoCmd.Close etc
on the form
 

Users who are viewing this thread

Top Bottom