Problem checking two fields

C

crann

Registered User.
Local time
Today, 19:16
Joined
Nov 23, 2002
Messages
160
Hi Guys

This one ive been struggling with for ages.

I use the following code to check for dates within 30 days of today for some Fire Certificates from a table called: tblTenancies.

This code works fine but I want it to also check the dates in a second field called: GasCheck and obvisously produce a message box the same for the current FireCheck.

Both FireCheck and GasCheck fields are in the same table: tblTenancies

Can I simply add code into this somehow to ask it to check several fields or is my thinking completely off track?

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("tblTenancies")


If Rst.EOF Then Exit Sub

Do Until Rst.EOF


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


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

Else

End If
End If

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
 
Have you tried adding code to do the 2nd check?
 
RuralGuy

Hi
Well I am relative beginner so still spending a lot of my time trying to learn to read the code that others have either publised or helped me with, and then adapt and try adding small pieces of extra code myself.

I often get confused because some code I seem to be able to duplicate twice but simple change for example the field names then other time I end up with a storm of errors.

At first I assumed I could duplicate the section of this code that references the FireCheck field with for example the additon of the GasCheck field see below my beginners thought:

Im sure its a million miles away to what I should be thinking at this stage

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("tblTenancies")


If Rst.EOF Then Exit Sub

Do Until Rst.EOF


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


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

Else

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


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



Else
End If
End If

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
 
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("tblTenancies")
   If Rst.EOF Then Exit Sub
   Do Until Rst.EOF
      If Len(Nz(Rst!FireCheck)) > 0 Then
         If DateDiff("d", Now(), Rst!FireCheck) < 30 And DateDiff("d", Now(), Rst!FireCheck) < 30 Then
            MsgBox Rst!FireCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
         Else
            If Len(Nz(Rst!GasCheck)) > 0 Then
               If DateDiff("d", Now(), Rst!GasCheck) < 30 And DateDiff("d", Now(), Rst!GasCheck) < 30 Then
                  MsgBox Rst!GasCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
               Else
               End If
            End If
            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

Here's your code after I used a free program called Smart Indenter:
http://www.oaltd.co.uk/Indenter/Default.htm
It does a great job of matching up IF's and EndIf. Notice anything strange about your code?
 
Hi

Its a bloody mess?

If im honest I dont really know what I am looking at. Truth is I dont understand what the Indents are telling me???

Thanks Im downloading the Smart Indenter
 
The Loop line should line up with the Do line. You're missing an End If.
 
Ok sorry, are you saying that the code requires that positioning or is that just for our reading standards?

Got to be honest can't see where I would need another End If.
I am as im sure you would expect getting a 'Loop without Do error'

Thanks so much for your help

My code reads as follows is the Loop correct?


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("tblTenancies")
If Rst.EOF Then Exit Sub

Do Until Rst.EOF
If Len(Nz(Rst!FireCheck)) > 0 Then
If DateDiff("d", Now(), Rst!FireCheck) < 30 And DateDiff("d", Now(), Rst!FireCheck) < 30 Then
MsgBox Rst!FireCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "



Else
If Len(Nz(Rst!GasCheck)) > 0 Then
If DateDiff("d", Now(), Rst!GasCheck) < 30 And DateDiff("d", Now(), Rst!GasCheck) < 30 Then
MsgBox Rst!GasCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
Else
End If
End If

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
 
Here's your code with the needed End If added.
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("tblTenancies")
   If Rst.EOF Then Exit Sub

   Do Until Rst.EOF
      If Len(Nz(Rst!FireCheck)) > 0 Then
         If DateDiff("d", Now(), Rst!FireCheck) < 30 And DateDiff("d", Now(), Rst!FireCheck) < 30 Then
            MsgBox Rst!FireCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "



         Else
            If Len(Nz(Rst!GasCheck)) > 0 Then
               If DateDiff("d", Now(), Rst!GasCheck) < 30 And DateDiff("d", Now(), Rst!GasCheck) < 30 Then
                  MsgBox Rst!GasCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
               Else
               End If
            End If
         End If
      [B]End If[/B]
      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
Notice how the Loop and Do are now indented the same. If you surround your code with the CODE tags (the # button) it preserves the indenting.
 
Do you mean I should use # at the start and end of my code to preserve the indenting or is that onle for copying and pasting into here.

Ive copied this new code back but its still only notifying me of dates due from the FireCheck field not the GascHeck field as well.

Thanks
 
Just for posting here. If you use the "Post Reply" button below the post you will have additional tools above the post. One of them is a button with a "#" on it which will put code tags on your post. You're no longer getting any errors correct?
 
Ok thanks didnt realise

Dont have any errors. Form loads and message box appears with each date that is due within 30 days but only from the FireCheck Field not the Gas.

Thanks
 
I tend to solve one issue at a time. Now we are probably dealing with a logic issue. We'll take it one step at a time. The GasCheck field is ONLY tested if the following line is true:
If Len(Nz(Rst!FireCheck)) > 0 Then
 
Ok
Could you explain that piece of code to me what is actually saying? and why would the Gas Check only be tested if that statement is True. 0 = True 1 = False???

Thanks
 
I think it would be helpful if you could single step your code. you would then see how it executes. Left click one time outside the left margin of the code window on the DO UNTIL line. that will put a dot there which is a breakpoint. Then run your code and it will stop on that line with it highlighted. Pressing F8 will advance the code one line at a time. Give it a try and let me know how you make out.
 
Thats a very handy tool never knew that existed thanks.

Well as a beginner what im seeing here is that at no point does my code run attempt to run the code:

Else
If Len(Nz(Rst!GasCheck)) > 0 Then
If DateDiff("d", Now(), Rst!GasCheck) < 30 And DateDiff("d", Now(), Rst!GasCheck) < 30 Then
MsgBox Rst!GasCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "

It basically seems to loop past it I think.
 
Code: 
Else
            If Len(Nz(Rst!GasCheck)) > 0 Then
               If DateDiff("d", Now(), Rst!GasCheck) < 30 And DateDiff("d", Now(), Rst!GasCheck) < 30 Then
                  MsgBox Rst!GasCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "

I mean
 
The lines it executes are important. Does it execute like the following?
Code: 
   Do Until Rst.EOF
-->      If Len(Nz(Rst!FireCheck)) > 0 Then
-->         If DateDiff("d", Now(), Rst!FireCheck) < 30 And DateDiff("d", Now(), Rst!FireCheck) < 30 Then
            MsgBox Rst!FireCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "



         Else
            If Len(Nz(Rst!GasCheck)) > 0 Then
               If DateDiff("d", Now(), Rst!GasCheck) < 30 And DateDiff("d", Now(), Rst!GasCheck) < 30 Then
                  MsgBox Rst!GasCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
               Else
               End If
            End If
         End If
-->      End If
      Rst.MoveNext
   Loop
 
Thanks

Yes have the extra End If in place but its sequencing to the MsgBox as well before I guess getting to the code regarding our GasCheck Field?




Code: 
   Do Until Rst.EOF
-->      If Len(Nz(Rst!FireCheck)) > 0 Then
-->         If DateDiff("d", Now(), Rst!FireCheck) < 30 And DateDiff("d", Now(), Rst!FireCheck) < 30 Then
-->           MsgBox Rst!FireCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "



         Else
            If Len(Nz(Rst!GasCheck)) > 0 Then
               If DateDiff("d", Now(), Rst!GasCheck) < 30 And DateDiff("d", Now(), Rst!GasCheck) < 30 Then
                  MsgBox Rst!GasCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
               Else
               End If
            End If
         End If
-->      End If
      Rst.MoveNext
   Loop
 
You must log in or register to reply here.

Similar threads

W
The problem has been resolved and the program can now be executed. Thank you very much.
Replies
6
Views
1,132
Ken Sheridan
Ken Sheridan
Bobcat42
Solved Problem filling a textbox (format RichText) with Richtext
Replies
3
Views
758
Bobcat42
Bobcat42
T
Attache to email from attachment Field
Replies
2
Views
639
theinviter
T
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
10
Views
1,643
Gasman
Gasman
S
Solved Need help with number as text
2
Replies
23
Views
1,688
KitaYama
K

Users who are viewing this thread

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