Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    67

    Unanswered: I need help with VB code

    I have table [TblSales] that I keep sales data and it is entered once daily with all the details. I have field [ReportDate] that is key. Also I set up form where the data is entered daily. What I want to do is set up codes in the Before Update event that will give me message if I enter duplicate reportdate and then will take me to the record.
    Thanks

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Use DCount() to see if the date already exists in a record, and if so, move to that record using FindFirst:
    Code:
    Private Sub ReportDate_AfterUpdate()
    
    Dim rs As Object
    Dim NewItem As Date
    Set rs = Me.Recordset.Clone
    
    If DCount("ReportDate", "YourTableName", "[ReportDate] = #" & Me.ReportDate & "#") > 0 Then
       NewItem = Me.ReportDate
       Me.Undo
       rs.FindFirst "[ReportDate] = #" & Format(NewItem, "mm\/dd\/yyyy") & "#"
       If Not rs.EOF Then Me.Bookmark = rs.Bookmark
      End If
    
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •