problem with this routine

[David b]

Any thoughts on why this code does not do its stuff.
Using BPDATE as 05/04/2003 then any movedate where types = died between 06/04/2003 and 10/06/2003 should return the msgbox I think !! but it does`nt




Private Sub tag_combo_AfterUpdate()
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM Beefpremdate WHERE(((Beefpremdate.[BPDATe])> (" & DateAdd("d", -65, movDate) & " )) AND ((Beefpremdate.[Record No])= " & [Forms]![Movement Form 2]![Tag combo].Column(3) & "))")

If Types = "DIED" Then
If Not (rst.BOF) Then

MsgBox "THIS ANIMAL MAY BE IN BEEF PREMIUM RETENTION. CHECK YOUR RECORDS AND CONTACT DEFRA WITHIN 10 DAYS IF IT IS."
Else
If (rst.BOF) Then


Exit Sub

End If
End If
End If

Set dbs = Nothing
Set rst = Nothing
Exit Sub

End Sub

TIA
David B

 

[WayneRyan]

David,

First you must put the # around your date fields in
the SQL.

Second, where does Types get its value from? If it
is not equal to "DIED", then nothing will happen.

Wayne

 

[David b]

Thanks for the reply.
Types is a control on the form.
The set up that the focus starts in types, (sold, died, temporary). Next is the movdate,. Then the tagcombo where the animal is
selected and the after update checks this code
David

 

[WayneRyan]

David,

Private Sub tag_combo_AfterUpdate() 
Dim dbs As Database 
Dim rst As Recordset 

Set dbs = CurrentDb 
Set rst = dbs.OpenRecordset("" & _
"SELECT * " & _
"FROM Beefpremdate " & _
"WHERE Beefpremdate.[BPDATe])> #" & DateAdd("d", -65, movDate) & "# AND " & _
"      Beefpremdate.[Record No] = " & [Forms]![Movement Form 2]![Tag combo].Column(3) & ";" 

If Types = "DIED" Then 
   If Not (rst.BOF) Then 
       MsgBox "THIS ANIMAL MAY BE IN BEEF PREMIUM RETENTION. CHECK YOUR RECORDS AND CONTACT DEFRA WITHIN 10 DAYS IF IT IS." 
    Else 
       If (rst.BOF) Then 
           Exit Sub 
       End If 
    End If 
End If 

Set dbs = Nothing 
Set rst = Nothing 
Exit Sub 

End Sub

Wayne