I need help with VB code

[hmho]

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

 

[RuralGuy]

A DCount() will tell you if there is a duplicate and a FindFirst will locate the record.

 

[hmho]

The link took me to Dlookup. I'm nit sure how to about DCount

 

[RuralGuy]

The syntax is the same for all of the Domain functions. VBA help should be able to fill in some blanks. I suggested DCount() because it returns 0 without a find rather then DLookup() which returns Null for the same condition.

 

[missinglinq]

I answered this on the other forum you posted on, but I'll post it here, as well, to help anyone searching here with a similar problem. As Allan suggested, I've use DCount() to see if the date already exists in a record, and if so, dump the new record you were starting and move to the existing record using FindFirst:

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

 

[hmho]

Should I post this VB in the beforeupdate or afterupdate events.

 

[missinglinq]

Sorry, that was a typo, should be in BeforeUpdate:

Private Sub ReportDate_BeforeUpdate(Cancel AS Integer)

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

 

[hmho]

Hi Missing,

I was able to put the VB codes and it worked but when i tried to enter duplicate it is not taking back to the original record.

 

[missinglinq]

Post the exact code you're using.

 

[hmho]

Here.

Private Sub Date_BeforeUpdate(Cancel AS Integer)

Dim rs As Object
Dim NewItem As Date
Set rs = Me.Recordset.Clone

If DCount("ReportDate", "TblDailyManager", "[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

 

[missinglinq]

From your previous posts, your control's name is ReportDate, not simply Date

so

Private Sub Date_BeforeUpdate(Cancel AS Integer)


should be

Private Sub ReportDate_BeforeUpdate(Cancel AS Integer)

And after making this change, while in Design View, selecet ReportDate then goto Properties - Events and make sure that in the BeforeUpdate box it says [Event Procedure]. If it doesn't, click on the Ellipsis (...) to the right and go to Code, which will bring you to the code window behind your form and 'connect' the code to your textbox.

 

[hmho]

Hi here is what I posted. And the control name is Report_date and control source is ReportDate. Still when I enter duplicate date to try I'm getting blank it is nor bringing back the original report but atleast is not excepting the duplicate so to put it is working half way. Can you tell me what I'm doing wrong. Thanks

Private Sub Report_Date_BeforeUpdate(Cancel As Integer)
Dim rs As Object
Dim NewItem As Date
Set rs = Me.Recordset.Clone
If DCount("ReportDate", "TblDailyManager", "[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

 

[RuralGuy]

Add the changes I have in RED.

Private Sub Report_Date_BeforeUpdate(Cancel As Integer)
   Dim rs As Object
   Dim NewItem As Date
   Set rs = Me.Recordset.Clone
   If DCount("ReportDate", "TblDailyManager", "[ReportDate] = #" & Me.ReportDate & "#") > 0 Then
      NewItem = Me.ReportDate
      Me.Undo
      [COLOR="Red"]rs.FindFirst "[ReportDate] = #" & Format(NewItem, "mm\/dd\/yyyy") & "#"
      If Not rs.EOF Then
         Me.Bookmark = rs.Bookmark
      Else
         MsgBox "Unable to locate the date [" & NewItem & "]"
      End If[/COLOR]
   End If
End Sub

P.S. The backslashes "\" in the Format function were not a typo.

 

[missinglinq]

Good catch, Allan! He didn't copy the code I posted, which included the back slashes. But whereas you have a messagebox telling him that the date wasn't found if that were the case, his requirement, as I read it, was for Access to go to the record, if it existed, but to continue creating a new record, if the date didn't already exist in the record set, which is why I didn't an else clause, just letting Access continue on continuing on!

 

[hmho]

Thank you guys it worked and also I was able to understant how it works I'm learning a lot from this forum and I realy appriciate your help and sharing the knowlegde

 

[RuralGuy]

Hi Linq,
I figured if the DCount() came back > 0 then we didn't want to duplicate the record, which is the only reason we were doing the FindFirst in the first place. That's my story and I'm sticking with it. I just put the Else in as a diagnostic so the OP could see what was happening.

 

[missinglinq]

That's why we're here! Glad we could help!