Count number of recently entered records (1 Viewer)

Mark H.

New member
Local time
Today, 03:10
Joined
May 20, 2022
Messages
7
Hello everyone, I created a simple access table for our receiving department and a form with the following fields: Receiving Date, Time, Courier, and Tracking Number. I need help with what it should be a simple code that counts the number of scanned Tracking Numbers each time a delivery is made (our scanners automatically press enter when a scan is made). We receive between 45 to 70 packages multiple times a day and I would like a number on the form that shows how many tracking numbers were scanned and then I guess a button to reset the counter to zero?

I tried the code below that I found in the forums. Thank you in advance, hope someone can help.

- This one only gives me total records
Private Sub Form_Current()
Me.BoxCount.Caption = _
Me.Recordset.RecordCount
End Sub

- The ones below should work but I cannot see the numbers

Private Sub Tracking_Number_Enter()
Me.BoxCount = Me.BoxCount + 1
End Sub

Private Sub Tracking_Number_AfterUpdate()
Me.BoxCount = Me.BoxCount + 1
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:10
Joined
Sep 21, 2011
Messages
10,561
I would use a Dcount for every record scanned that day?
Hopefully you are storing the date scanned as well?
 

bob fitz

AWF VIP
Local time
Today, 08:10
Joined
May 23, 2011
Messages
4,444
Do you need a count of each days packages or a count of packages for each delivery?
 

Mark H.

New member
Local time
Today, 03:10
Joined
May 20, 2022
Messages
7
I would use a Dcount for every record scanned that day?
Hopefully you are storing the date scanned as well?
Hi Gasman, I have a separate code that keeps time and date current and it's stored on a table after each scan. Just need a count of packages for each delivery on the form, not for the day.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
16,421
as suggested, use DCount() to count it from your table:

private sub Form_Current()
Me!BoxCount = DCount("1", "yourTableName","[Receiving Date] = " & Format(Date(), "\#mm\/dd\/yyyy\#"))
end sub
 

Mark H.

New member
Local time
Today, 03:10
Joined
May 20, 2022
Messages
7
as suggested, use DCount() to count it from your table:

private sub Form_Current()
Me!BoxCount = DCount("1", "yourTableName","[Receiving Date] = " & Format(Date(), "\#mm\/dd\/yyyy\#"))
end sub
That worked great, thank you. Now how can I reset the count to zero for the next delivery?
For example, we receive 38 packages at 8am then the counter needs to reset to zero to count the next delivery.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
16,421
what does Time field does? is it the time the package is scanned?
you add an Unbound textbox (txtTime) to your form (Visible=false).
on the Load event , initialized the unbound textbox:

private sub form_load()
me![txtTime] = Now()
end sub

now, change the current event of the form:

private sub form_current()
Me!BoxCount = DCount("1", "yourTableName","[Time] >= #" Me![txtTime] & "#")
end sub

add code to the Click event of the button that will reset txtTime textbox:

private sub button_click()
call form_load
call form_current
end sub
 

Mark H.

New member
Local time
Today, 03:10
Joined
May 20, 2022
Messages
7
Me!BoxCount = DCount("1", "yourTableName","[Time] >= #" Me![txtTime] & "#")
Thank you so much for your help. I have everything setup but I get the attached error. I fairly new to coding, sorry I can't figure it out.
 

Attachments

  • Error.png
    Error.png
    5.4 KB · Views: 59

Gasman

Enthusiastic Amateur
Local time
Today, 08:10
Joined
Sep 21, 2011
Messages
10,561
I would have thought you would need a delivery ref to store, then just DCount() on that?
No need for a reset, that happens when new delivery appears?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
16,421
Won't that also count records from days other than today.
the field is Date/Time, although formatted to show Time only.
if the OP is using Now() to save the time value, current date is also saved.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:10
Joined
Sep 21, 2011
Messages
10,561
the field is Date/Time, although formatted to show Time only.
if the OP is using Now() to save the time value, current date is also saved.
Isn't the time going to change though, between scans?
Would take a while for 38 packages surely?
 

bob fitz

AWF VIP
Local time
Today, 08:10
Joined
May 23, 2011
Messages
4,444
the field is Date/Time, although formatted to show Time only.
if the OP is using Now() to save the time value, current date is also saved.
I don't recall seeing any info on the "Time" field but thank you for the clarification.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:10
Joined
Sep 21, 2011
Messages
10,561
I don't recall seeing any info on the "Time" field but thank you for the clarification.
@bob fitz See post #5 in response to my enquiry on date being stored.
That might even answer my question, if the Now() value is stored at the start? and not the actual Now() on each scan. ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
16,421
That might even answer my question, if the Now() value is stored at the start? and not the actual Now() on each scan. ?
see this demo, the records are saved on the Actual Now().
 

Attachments

  • barcode.accdb
    536 KB · Views: 64

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:10
Joined
Feb 28, 2001
Messages
22,821
All of these things that count time or date are missing the real point. If you care about how many packages for a delivery, you need to have a number that identifies the delivery and then tag the packages with that number. It may mean adding a table for deliveries, but here is the answer plain and simple. If you want to account for X, then X has to be part of what you are tracking. You want to know something about deliveries? You have to identify and track the fact of a delivery. Going by date or time is the wrong answer.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:10
Joined
Feb 19, 2002
Messages
36,352
Doc beat me to it. Create a delivery table to store the common information. Use the DeliveryID as a FK as you add each scanned package. Then count the packages for DeliveryID = Forms!yourform!DeliveryID
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:10
Joined
Sep 21, 2011
Messages
10,561
Well I did mention that in post #12?
 

Users who are viewing this thread

Top Bottom