Create an updateable daily counter to display on entries in a continuous form in Access 2010

Hi Experts,

I have a continuous form where the users wanted a daily sequential count to display on each record in the form.

i.e. if there were 10 entries on October 1st (the record entry date), entries would be labelled 1 through 10, on Oct 2nd the first entry would display 1, then 2, etc....

The code below worked great until a new requirement came in to be able to backdate the entry date.  I'm stumped on what to do to update the daily count display when later on an entry date gets back-dated.

Any ideas would be appreciated!

(Note:  The ID field is a primary key auto-number field)

'Daily Count Code
Private Sub Form_BeforeInsert(Cancel As Integer)

Dim dEnterDate As Date
Dim dPreviousDate As Date
Dim lPreviousID As Long
Dim lPreviousDailyCount As Long
Dim LValue As Long

dEnterDate = Me.ENTRY_DATE
lPreviousID = DMax("ID", "Main")

lPreviousDailyCount = DLookup("[daily_count]", "Main", "ID = " & lPreviousID)

dPreviousDate = DLookup("[Entry_Date]", "Main", "ID = " & lPreviousID)

    If DateDiff("d", dPreviousDate, dEnterDate) > 0 Then
        Me.[Daily_Count].Value = 1
    Else
        Me.[Daily_Count].Value = lPreviousDailyCount + 1
    End If
   
End Sub
grmcraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this query and use it as record source of your form

SELECT MAIN.[ID], MAIN.[ENTRY_DATE], (select count(*) from MAIN M where M.[ENTRY_DATE]=MAIN.[ENTRY_DATE] And M.[ID]<=MAIN.[ID]) AS Daily_Count
FROM MAIN
Order By MAIN.[ENTRY_DATE]


you don't need those codes to assign the count number.
0
grmcraAuthor Commented:
Hi Capricorn1,

The query works great on load, however, when the user navigates to 'last record' using either a button on the form or the record selectors at the bottom, all of the daily_count fields change to read '1'....how would I fix this issue?

Thanks!
0
grmcraAuthor Commented:
...also, I can no longer change data in the continuous form....if I remove the select count portion of the query I can change data again...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Nick67Commented:
As sample is always good :)
To be able to change the data, the underlying query has to be editable -- the changes suggested by Capricorn1 have probably made the query read-only.

Sequential numbering, whether it is on a form or in a query, can be a real bitch to do.
I think you really need another table, and what you were generating on the fly, you now may want to bang into a table

tblSequenceNumbers would have
SequenceID as AutoNumber
ID as Long
SequenceNumber as Long

You'd then need some code to populate this table one-off, and some code in the AfterUpdate event of the entry date control.

I'll see if I can't build you a sample
0
Nick67Commented:
Play with this
Sequence.mdb
0
Nick67Commented:
Here it is with more commenting.
It still has a nice little bug in the AfterUpdate.

I had assume since we were doing this in the AfterUpdate, that the recordset code should grab the just-changed record.

It doesn't!

So we'll need a UNION query, and then it should work right
Sequence.mdb
0
Nick67Commented:
Here is the sample with all the bugs ironed out.

Now, while I did it with a separate table -- because that is ultimately simpler in terms of updating -- the relationship is ultimately a 1-to-1, which means you could do it by adding a column to table MAIN.

The updating of the data is bitchy to code if you do that, though, because you're working with self-joins.  Those work, but they are not any fun!
Sequence.mdb
0
grmcraAuthor Commented:
Hi Nick,

Thanks so much I've been struggling trying to figure out your first sample, I'll try this one now!
0
grmcraAuthor Commented:
....I cannot seem to get it to work in Access 2010 .accdb file.  I tried changing the reference library to match, but it still will not work....any ideas?
0
Nick67Commented:
Here it is as an accdb -- though that should make no difference
get it to work

How so?  It contains VBA code, and the 2010 Office edition will disable code in any file that is not trusted, or any file not opened in a Trusted Location

What isn't working?
And what is your general skill level with VBA?
Sequence.accdb
0
grmcraAuthor Commented:
Hi Nick,

Sorry had a dog emergency and haven't been back at the computer in a couple days.  

My VBA knowledge is really weak with recordsets, that's why I'm grateful for all your notes so that I can study the meaning of each line.  When I download your accdb example and change a date it gets stuck on various lines i.e. (.FindFirst "ID = " & rs1!ID (with the first "ID" highlighted.)

After trying to research some things online, I tried to change your code to specify dao:

Dim db As dao.Database
Dim rs As dao.Recordset
Dim rs1 As dao.Recordset

This seems to 'unstick' it and this is what happens:

Pretend Jan-1-13 has 5 records and Jan-2-13 has 4 records.

I back date the second record on Jan-2-13 TO Jan-1-13:

Jan-1-13 now has 6 records with SequenceNumber 1 through 6 (Hurray!)
Jan-2-13 has 3 records with Sequence Number 1, 3, 4 instead of 1, 2, 3 (Bugger)

The users have been pretty adamant about wanting to see these sequential numbers or I would have given up by now....I never thought it would have been so complicated.  The bonus is that I am getting a lesson on recordsets because the resources I have haven't made much sense to me.

Let me know if I should repost a second question to fix the above because you have definitely provided way more information than 500 points is worth!
0
Nick67Commented:
So, that's an oversight in the way I coded it.
I coded for the date the record went TO to recalculate its sequence order

BUT

I forgot to code that the date it went FROM also needs to be re-sequenced.

Did you get the mdb file to work?
Because that's what I'd like to post with a fix
0
Nick67Commented:
So we'll add a BeforeUpdate event to capture the previous date and store it in a new Public variable.  And then add some code to re-sequence the date we took an item out of.

The code then looks like this
Option Compare Database
Option Explicit
'new public variable!
Public PreviousDate As Date


Private Sub EntryDate_BeforeUpdate(Cancel As Integer)
'new BeforeUpdate event
PreviousDate = Me.EntryDate.OldValue
End Sub

Private Sub EntryDate_AfterUpdate()
'we'll need to reorder the sequences for Everything for this date
'And then requery the form
'And return to the original record

Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim x As Integer
Dim theID As Long

theID = Me.ID ' save the current ID for the end!

Set db = CurrentDb
'we're gonna edit these records to reflect the new sequence
Set rs = db.OpenRecordset("select * from tblSequenceNumbers", dbOpenDynaset, dbSeeChanges)

'Grab all the records that have the same date as the one we just changed
Set rs1 = db.OpenRecordset("select * from Main where entrydate = #" & Format(Me.EntryDate, "dd-mmm-yyyy") & "# Union Select * from Main where ID = " & theID & " order by ChangeIt", dbOpenDynaset, dbSeeChanges)
'start the sequence at 1
x = 1
Do Until rs1.EOF
    With rs
        .FindFirst "ID = " & rs1!ID ' find the ID of the record in SequenceNumbers that matches the one in Main
        .Edit
        !SequenceNumber = x ' put in the new sequence number
        .Update
    End With
    x = x + 1 ' bump 'er up
    rs1.MoveNext 'move on to the next record
Loop

'clean up
rs1.Close
Set rs1 = Nothing

'New ************ 18-Oct-2013 ***************
'Now we need to resequence the date that our ID used to be on
'We captured that date in the public variable PreviousDate
'Grab all the records that have the same previous date as the one we just changed
Set rs1 = db.OpenRecordset("select * from Main where entrydate = #" & Format(PreviousDate, "dd-mmm-yyyy") & "# order by ChangeIt", dbOpenDynaset, dbSeeChanges)
'start the sequence at 1
x = 1
Do Until rs1.EOF
    With rs
        .FindFirst "ID = " & rs1!ID ' find the ID of the record in SequenceNumbers that matches the one in Main
        .Edit
        !SequenceNumber = x ' put in the new sequence number
        .Update
    End With
    x = x + 1 ' bump 'er up
    rs1.MoveNext 'move on to the next record
Loop

'clean up
rs1.Close
Set rs1 = Nothing
' ************ 18-Oct-2013 ***************


'everything's changed  -- get the form to reflect that
Me.Requery
Me.OrderBy = "EntryDate, SequenceNumber"
Me.OrderByOn = True

'go back to the ID we saved at the beginning
Set rs1 = Me.RecordsetClone
rs1.MoveFirst
rs1.FindFirst "ID = " & theID
If rs1.NoMatch = False Then
    Me.Bookmark = rs1.Bookmark
Else
    MsgBox "WTF!"
End If

'clean up and done
rs1.Close
Set rs1 = Nothing

MsgBox "done!"

End Sub

Open in new window

Sequence.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.