While Not Loop

[la1814]

When I Load a Datasheet Form I am attempting to check every record and 'flag' it if it meets my criteria. Each record consists of a persons name and a long set of qualifications with expiration dates. The code to check the expirations and set the blFlag works as advertised. The Loop I am attempting to set up does not.

I keep getting the following error:

Runtime error 2465: Access can't find the field referenced...referring to this: Set rst = [QualExpiresDatasheetSub2Form].Form.Recordset

This is a valid Form. Also, should I put an error statement in here and at the end zero out the code before exiting the Sub? Thanks for your help.

 Private Sub Form_Load()
 
Dim blFlag As Boolean
Dim Days As Integer, Days1 As Integer, Days2 As Integer, Days3 As Integer, Days4 As Integer, Days5 As Integer, Days6 As Integer, Days7 As Integer, Days8 As Integer, Days9 As Integer, Days10 As Integer, Days11 As Integer
Dim rst As DAO.Recordset
 Set rst = [QualExpiresDatasheetSub2Form].Form.Recordset
 While Not rst.EOF
     ' Set flag to False
    blFlag = False
  
  ' Default date of 12/12/2099 for null value
    Days = DateDiff("d", Now, Nz(Me.PhysicalExpires, #12/12/2099#))
    Days1 = DateDiff("d", Now, Nz(Me.EFExpires, #12/12/2099#))
    Days2 = DateDiff("d", Now, Nz(Me.ADExpires, #12/12/2099#))
    Days3 = DateDiff("d", Now, Nz(Me.CExpires, #12/12/2099#))
    Days4 = DateDiff("d", Now, Nz(Me.CheckExpires, #12/12/2099#))
    Days5 = DateDiff("d", Now, Nz(Me.PhysExpires, #12/12/2099#))
    Days6 = DateDiff("d", Now, Nz(Me.SeatExpires, #12/12/2099#))
    Days7 = DateDiff("d", Now, Nz(Me.LabExpires, #12/12/2099#))
    Days8 = DateDiff("d", Now, Nz(Me.CRMAFExpires, #12/12/2099#))
    Days9 = DateDiff("d", Now, Nz(Me.CRMTExpires, #12/12/2099#))
    Days10 = DateDiff("d", Now, Nz(Me.TXExpires, #12/12/2099#))
    Days11 = DateDiff("d", Now, Nz(Me.ReviewDateExpire, #12/12/2099#))
     ' Set flag to True if AllRead is checked, default to False if null
    blFlag = blFlag Or Nz(Me.AllRead, False)
     ' Set flag to True if Discrepancies is checked, default to False if null
    blFlag = blFlag Or Nz(Me.Discrepancies, False)
  
    ' Set flag to True if Days(x) < 14 days
    blFlag = blFlag Or (Days < 14)
    blFlag = blFlag Or (Days1 < 14)
    blFlag = blFlag Or (Days2 < 14)
    blFlag = blFlag Or (Days3 < 14)
    blFlag = blFlag Or (Days4 < 14)
    blFlag = blFlag Or (Days5 < 14)
    blFlag = blFlag Or (Days6 < 14)
    blFlag = blFlag Or (Days7 < 14)
    blFlag = blFlag Or (Days8 < 14)
    blFlag = blFlag Or (Days9 < 14)
    blFlag = blFlag Or (Days10 < 14)
    blFlag = blFlag Or (Days11 < 14)
  
    Me.NewNameLookup = Me.NameLookup
  
  ' Add ^ if flag is set to True
    If blFlag Then Me.NewNameLookup = Me.NewNameLookup & " ^"
    
    rst.MoveNext
Wend
    
    Form.Refresh
    
End Sub

 

[vbaInet]

1. If you had qualified the name of the form with "Me." it would have indicated if that's the correct subform name.
2. Use Date() instead of Now() if all you need is the date part.
3. Most importantly, perform this calculation in the Record Source (i.e. the query) of the subform. No need to loop.

 

[la1814]

vbaInet,

Great point on the Date, thanks.

Just so I am not confused. Do I convert the code into an expression for an Update Query criteria? Then when the form loads it will go through all the names.

Thanks,
la1814

 

[vbaInet]

It will just be a calculated field within the query.

 

[la1814]

Thanks vbaInet. This is going to blow my mind trying to convert the vba to an expression. Have a good evening.

 

[vbaInet]

If it's too complex for an expression, write a generic function that you can use on all fields and call the function in the query.
It's the early hours of the morning now so have a good morning

 

[la1814]

Good morning. Enjoy the day...after some good sleep!

 

[la1814]

Thanks again vbaINet...ended up doing it as an expression...my eyes hurt!

 

[vbaInet]

How does it look?

 

[la1814]

Ugly...but it works...thanks again. You have been a wealth of knowledge and outstanding help!

 [FONT=Calibri][SIZE=3]Expr1: IIf(([AllRead] Or ((DateDiff("d",Date(),Nz([PhysicalExpires],#12 Dec 2099#)))<14) Or ((DateDiff("d",Date(),Nz([EFExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([ADExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([CExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([CheckExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([PhysExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([SeatExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([LabExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([CRMAFExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([CRMCExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([FireExpires],#12/12/2099#)))<14) Or ((DateDiff("d",Date(),Nz([ReviewDateExpire]#12/12/2099#)))<14),([NameLookup] & "^"),[NameLookup])[/SIZE][/FONT]

 

[Galaxiom]

Performing a calculation on every date field then testing the result is very inefficient.

The repeating nature of your controls and your requirement to perform the same tests on separate fields suggest that this data would be better much implemented as multiple records in a related table and displayed in a subform.

ExpiryDate then becomes a single field and your code would then only need to test Min(ExpiryDate) was within fourteen days from today. This test would be against the index of the ExpiryDate field and would be extremely efficient.

 

[Galaxiom]

Your current implementation also requires fields to be added to the table and the form modified to add any new qualification. This should always ring alarm bells.

The related table allows new qualifications to be added by simply adding records to the Qualifications table.

Further, if you were wanting to query for which of your employees had certain qualifications you would need to have the same number of almost identical queries as you have qualifications.

With the related table structure a single parameter query can do the job.

 

[la1814]

Galaxiom,

Thanks for the info. I will surely take it...I will likely have many more changes as I am new to this and self taught. Do you have a god book on "Designing Databases" that you would recommend?

Thanks,
la1814