Issues storing date as a variable

[Beansy]

Gday all, having issues storing dates as variables. The intent of this beforeUpdate() function is to check the 'latest update' field of the Product table and if the latest update of the product was over 2 weeks prior then undoChange. The problem is that this is only working for some of the products in the table. I tested whats being assigned to the variable by outputting the result to a text box on the form. some are coming back with 12:00:00 am and some come back with the date format that i want, ie '20/12/2012. And even after I update these products that return the correct date values they still return the correct format so its not updating thats the problem. Though all new products that I insert into the table only return 12:00:00am. Im a beginner programmer so this has me fairly stumped. Anyway heres the code...

PS . I apologise for the poorly named command buttons and text boxes. Ill get round to changing the names at some point

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim CurrentDate As Date
Dim UndoneAction As Boolean
Dim SelectedProduct As String
Dim ProductName As String
Dim ProductDate As Date
Dim varDateDiff As Integer
Dim dbs As DAO.Database
Dim Product As DAO.Recordset
UndoneAction = False
SelectedProduct = Me.ProductName
CurrentDate = Date

Set dbs = CurrentDb
Set Product = dbs.OpenRecordset("Product")

Product.GetRows
If Product("ProductName").Value = SelectedProduct Then
ProductDate = Product("lastUpdate").Value

End If
' TEST
Me.Text21.Value = ProductDate


If DateDiff("d", CurrentDate, ProductDate) <= -14 Then
MsgBox ("The Product Price has not been updated for a while.. Please recheck this price with the Supplier then Update the product before continuing ")
DoCmd.RunCommand acCmdUndo
UndoneAction = True
End If




If UndoneAction = False And MsgBox("Save Changes to this Record", vbQuestion + vbYesNo) = vbNo Then
DoCmd.RunCommand acCmdUndo
End If
End Sub

 

[Beansy]

Sorry here's the code again, spaced slightly better

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim CurrentDate As Date
Dim UndoneAction As Boolean
Dim SelectedProduct As String
Dim ProductName As String
Dim ProductDate As Date
Dim varDateDiff As Integer
Dim dbs As DAO.Database
Dim Product As DAO.Recordset
    
    UndoneAction = False
    SelectedProduct = Me.ProductName
    CurrentDate = Date
    
Set dbs = CurrentDb
Set Product = dbs.OpenRecordset("Product")

Product.GetRows
    If Product("ProductName").Value = SelectedProduct Then
         ProductDate = Product("lastUpdate").Value
            
    End If
    
Me.Text21.Value = ProductDate
    
    
    If DateDiff("d", CurrentDate, ProductDate) <= -14 Then
        MsgBox ("The Product Price has not been updated for a while.. Please recheck this price with the Supplier then Update the product before continuing ")
        DoCmd.RunCommand acCmdUndo
        UndoneAction = True
    End If
    
    
    
        
If UndoneAction = False And MsgBox("Save Changes to this Record", vbQuestion + vbYesNo) = vbNo Then
        DoCmd.RunCommand acCmdUndo
End If
End Sub

 

[MarkK]

One thing is that your code is verbose and it makes it harder to see what is going on. Consider this, which I believe preserves all the steps you are taking, but removes variables you don't really need ...

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Product")
    With rst
        If !ProductName = Me.ProductName Then Me.Text21 = !lastUpdate
        .Close
    End With
    
    If DateDiff("d", Date, Me.Text21) <= -14 Then
        MsgBox ("This product's price is stale.  Please verify, update, and retry the operation.")
        Me.Undo
        Cancel = True
    ElseIf MsgBox("Save changes?", vbQuestion + vbYesNo) = vbNo Then
        Me.Undo
        Cancel = True
    End If
End Sub

... so looking at it that way it's easier to see that the recordset you open will always open to the first record, and that product name will almost never be the same as the one in the current record of the form, and so Text21 will almost never get set to !LastUpdate. That must be a mistake.

Also, 12:00p is zero in a date variable, so that's what you get if you declare the variable but never assign it a value.

Also, you don't need to do this in code ...

dim tmp as date
tmp = date()
msgbox tmp

... just do this ...

msgbox date()

... so you don't need a variable as an intermediary. Just go right to the source.

hope this helps...

 

[Beansy]

Thanks Lagbolt, works perfectly now.

 

[Beansy]

ahh nevermind, re-read over your comment and see what you mean now. So how do I go about comparing Me.ProductName to every entry in that particular field of the Product table? Thanks for the help.

 

[Beansy]

Aha! nevermind, I figured it out. I used a Do until end of file loop instead to search through all records and set text21 value to the LastUpdate when the ProductName matches the user selection. Works exactly how it should now. Thanks very much with the help, would have taken alot longer to figure it out without it.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Product")
    
With rst
    Do Until .EOF
        If !ProductName = Me.ProductName Then Me.Text21 = !lastUpdate
        .MoveNext
    Loop
End With

    
If DateDiff("d", Date, Me.Text21) <= -14 Then
        MsgBox ("This product's price is stale.  Please verify, update, and retry the operation.")
        Me.Undo
        Cancel = True
    ElseIf MsgBox("Save changes?", vbQuestion + vbYesNo) = vbNo Then
        Me.Undo
        Cancel = True
    End If

End Sub

 

[MarkK]

And just to keep tweaking a little, what is more efficient still is to open the recordset to exactly the record(s) you need, so consider ...

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset( _
        "SELECT LastUpdate " & _
        "FROM Product " & _
        "WHERE ProductName = '" & Me.ProductName & "'")
    If Not rst.EOF then Me.Text21 = rst!LastUpdate
    rst.Close

    If DateDiff("d", Date, Me.Text21) <= -14 Then
        MsgBox ("This product's price is stale.  Please verify, update, and retry the operation.")
        Me.Undo
        Cancel = True
    ElseIf MsgBox("Save changes?", vbQuestion + vbYesNo) = vbNo Then
        Me.Undo
        Cancel = True
    End If

End Sub

... so that eliminates the loop. It's getting close to it's leanest, I think.
Cheers,