Count number of recently entered records

[Mark H.]

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]

I would use a Dcount for every record scanned that day?
Hopefully you are storing the date scanned as well?

 

[bob fitz]

Do you need a count of each days packages or a count of packages for each delivery?

 

[Mark H.]

Do you need a count of each days packages or a count of packages for each delivery?

Hi bob, I need a count of packages for each delivery.

 

[Mark H.]

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]

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

 

[bob fitz]

Hi bob, I need a count of packages for each delivery.

Then I would suggest that you need a unique identifier for each delivery

 

[Mark H.]

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]

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

 

[bob fitz]

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

Won't that also count records from days other than today.

 

[Mark H.]

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.

 

[Gasman]

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]

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]

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]

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]

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]

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().

 

[The_Doc_Man]

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]

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]

Well I did mention that in post #12?