Help with code please.

C

crann

Registered User.
Local time
Today, 11:32
Joined
Nov 23, 2002
Messages
160
Hi

I have a db for some property maintenance. I have some code on the 'on open' function on my Nav form which checks some safety check dates within a safety check table and if a check is due within 30 days a simple alert pops up to remind me.

I now want the code to check the dates in two fields with the table but im not sure how to code a second field within the same table to be checked.

Here is the code I use: (Have commented out some of code)

Private Sub Form_Open(Cancel As Integer)
On Error GoTo err

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("tblSafetyChecks")

'If there are no records in the table, just exit
If Rst.EOF Then Exit Sub

'Loop over all the records in the table until the end of file
Do Until Rst.EOF

'If the date is not entered, do not evaluate
If Len(Nz(Rst!NextCheckA)) > 0 Then



'Handle 30 days
If DateDiff("d", Now(), Rst!NextCheckA) < 30 Then

MsgBox Rst!NextCheckA & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "







'Else, handle 60 days
'ElseIf DateDiff("d", Now(), rst!NextCheck) < 60 Then
' MsgBox rst!NextCheck & " is less than 60 days away!"

'Else, handle 90 days
'ElseIf DateDiff("d", Now(), rst!NextCheck) < 90 Then
'MsgBox rst!NextCheck & " is less than 90 days away!"

'Its far away
'Else
'MsgBox "Chillax! You've got time. :-)" & vbCrLf & vbCrLf & "NextCheck:" & rst!NextCheck
End If
End If

'Think of a recordset as a table in memory. When we opened the table with the set rst=... command,
'the table opened, and there is a pointer at the first record.
'We have to tell the pointer to go to the next record
Rst.MoveNext

'Go back to the DO statement above and start over
Loop

'We opened some objects in our computers memory, we have to close them
On Error Resume Next
Set db = Nothing
Rst.Close
Set Rst = Nothing
Exit Sub

'This is called an error handler
'Should an error occur, this function will alert you to the error, and then stop running
err:
MsgBox err.Number & ": " & err.Description



End Sub

I want to add another field to be checked called 'NextCheck' as well as the existing field 'NextCheckA'.

Im a beginner so can say I have generated this existing code with help of other users.

Thanks
 
Pop-ups are not a good tool for handling lists with items requiring attention. Simply because they become items to be dismissed, since people stop reading the text.

A common solution is to show a list of deadlined items with conditional formatting, and perhaps, on the main page, have an item telling you how many there is of each kind. Fx items overdue would be red, items to be attended to shortly yellow, items to be tended to within a month green, and the rest no colour.
 
Thanks

I completely understand and agree with you however in this instance I have a login system so before the db even opens onto the Nav form any saftey checks that are due pop up as a reminder, we then immediately action that reminder.

Due to the nature of the safetycheck for example fire safety or gas we found that an alert when database is first logged in works very well for us we are only a few team members.

The problem is now I want it to check several of our table fields rather than one generic field if that makes sense.

Any advice would be great.
 
If you want them merged into one then maybe a pattern like the following would work


If DateDiff("d", Now(), rst!NextCheckA) < 30 And DateDiff("d", Now(), rst!NextCheck) < 30 Then
MsgBox rst!NextCheckA & " and " & rst!NextCheck & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
ElseIf DateDiff("d", Now(), rst!NextCheckA) < 30 And Not DateDiff("d", Now(), rst!NextCheck) < 30 Then
MsgBox rst!NextCheckA & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
ElseIf Not DateDiff("d", Now(), rst!NextCheckA) < 30 And DateDiff("d", Now(), rst!NextCheck) < 30 Then
MsgBox rst!NextCheck & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
'Elsif

'....
Else

End If
 
Last edited:
Hi
Thanks for reply, Im getting a little lost I have amended my code to include the code you suggested which now looks like the following below.

Im just getting an error message when I open my form reading 'Loop without Do'

As a beginner im struggling to work out what has changed from my code before.

Any guidance please?

Thanks



Private Sub Form_Open(Cancel As Integer)
On Error GoTo err

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("tblSafetyChecks")


If Rst.EOF Then Exit Sub

Do Until Rst.EOF


If Len(Nz(Rst!NextCheckA)) > 0 Then


If DateDiff("d", Now(), Rst!NextCheckA) < 30 And DateDiff("d", Now(), Rst!NextCheck) < 30 Then
MsgBox Rst!NextCheckA & " and " & Rst!NextCheck & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "

ElseIf DateDiff("d", Now(), Rst!NextCheckA) < 30 Then
MsgBox Rst!NextCheckA & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "

ElseIf DateDiff("d", Now(), Rst!NextCheck) < 30 Then
MsgBox Rst!NextCheck & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "

'Elsif

'....
Else

End If

Loop


On Error Resume Next
Set db = Nothing
Rst.Close
Set Rst = Nothing
Exit Sub


err:
MsgBox err.Number & ": " & err.Description



End Sub
 
Ok update I noticed a piece of code hadn't been copied across

The code now runs when I open the form but its only alerting me to dates due within 30 days from the NextCheckA field from the table and not looking at the NextCheck field

any advice?

Thanks

Here is the updated code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo err

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("tblSafetyChecks")


If Rst.EOF Then Exit Sub

Do Until Rst.EOF


If Len(Nz(Rst!NextCheckA)) > 0 Then


If DateDiff("d", Now(), Rst!NextCheckA) < 30 And DateDiff("d", Now(), Rst!NextCheck) < 30 Then
MsgBox Rst!NextCheckA & " and " & Rst!NextCheck & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "

ElseIf DateDiff("d", Now(), Rst!NextCheckA) < 30 Then
MsgBox Rst!NextCheckA & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "

ElseIf DateDiff("d", Now(), Rst!NextCheck) < 30 Then
MsgBox Rst!NextCheck & ": Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "

'Elsif

'....
Else



End If
End If

'Think of a recordset as a table in memory. When we opened the table with the set rst=... command,
'the table opened, and there is a pointer at the first record.
'We have to tell the pointer to go to the next record
Rst.MoveNext





Loop


On Error Resume Next
Set db = Nothing
Rst.Close
Set Rst = Nothing
Exit Sub


err:
MsgBox err.Number & ": " & err.Description



End Sub

Private Sub Form_Timer()
Static count As Integer
count = count + 1
If count = 25 Then
Me.TimerInterval = 0
Call YouHaveTaskDue
Exit Sub
End If
End Sub

Sub YouHaveTaskDue()
On Error GoTo err

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("TaskDue")

'If there are no records in the table, just exit
If Rst.EOF Then Exit Sub

'Loop over all the records in the table until the end of file
Do Until Rst.EOF

'If the date is not entered, do not evaluate
If Len(Nz(Rst!DueDate)) > 0 Then

'Handle 30 days
If DateDiff("d", Now(), Rst!DueDate) < 14 Then


stDocName = "TaskAlert"
DoCmd.OpenForm stDocName, , , stLinkCriteria



'MsgBox rst!NextCheck & " NEXT SAFETY CHECK DUE!.", vbOKOnly, "ALERT! Safety Check Due "




'Else, handle 60 days
'ElseIf DateDiff("d", Now(), rst!NextCheck) < 60 Then
' MsgBox rst!NextCheck & " is less than 60 days away!"

'Else, handle 90 days
'ElseIf DateDiff("d", Now(), rst!NextCheck) < 90 Then
'MsgBox rst!NextCheck & " is less than 90 days away!"

'Its far away
'Else
'MsgBox "Chillax! You've got time. :-)" & vbCrLf & vbCrLf & "NextCheck:" & rst!NextCheck
End If
End If

'Think of a recordset as a table in memory. When we opened the table with the set rst=... command,
'the table opened, and there is a pointer at the first record.
'We have to tell the pointer to go to the next record
Rst.MoveNext

'Go back to the DO statement above and start over
Loop

'We opened some objects in our computers memory, we have to close them
On Error Resume Next
Set db = Nothing
Rst.Close
Set Rst = Nothing
Exit Sub

'This is called an error handler
'Should an error occur, this function will alert you to the error, and then stop running
err:
MsgBox err.Number & ": " & err.Description


End Sub
 
You must log in or register to reply here.

Similar threads

Bobcat42
Solved Problem filling a textbox (format RichText) with Richtext
Replies
3
Views
760
Bobcat42
Bobcat42
A
Solved checkbox placed in frame by a query, not responding
Replies
1
Views
814
tvanstiphout
T
S
Solved Need help with number as text
2
Replies
23
Views
1,695
KitaYama
K
N
Solved Failing To Save Imported Data from the internet in Ms Access
Replies
6
Views
1,197
nector
N
I
Solved Confused on what Form event(s) to use that display form with only statis text before VBA code executes
Replies
13
Views
1,173
isaksp00
I

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom