Thread: I need help with VB code
05-23-09, 12:44 #1Registered User
- Join Date
- Apr 2009
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.
05-23-09, 21:53 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
Use DCount() to see if the date already exists in a record, and if so, move to that 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 SubHope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007