Get info from the previous scan made

[jgrondin]

So far, when I scan a product, it records the current time with a Now function. What I need is to know how much time elapsed since my last scan by doing a Datediff with the previous time recorded. My problem is that I can't manage to get the previous scan time.

I thought of two ways to do this.

1. Hours_finish = DMax("[Hours]", "Data", "Hours < Now()")
This does not work. If I change the criteria to "Hours <= Now()", it gives me the current time as if my function could not find other times recorded than the current time.

2. My second idea was to use a Dlookup and use my autonumber to look for "the current autonumber - 1" in order to get the previous time recorded. But I can't manage to create the variable in order to do this.


Anyone has any idea to solve this ? I've been working on this for 2 days already and it's getting on my nerves.

Thank you so much !

 

[jgrondin]

Any idea, even short ones ?

thanks !

 

[ChrisO]

What is the data type of the Hours field in the Data table?
What code are you using to store the hours in the Hours field?
Are you running the DMax code before or after the latest scan is entered?

Chris.

 

[jgrondin]

Hours field in the data table is set to Time
To store the hours in the hours field, I simply use: " Hours = Now() "
I'm running the DMax code after the latest scan is entered.

Thanks!

 

[jgrondin]

Here is a part of my actual code.


"Product" is the product number when an item is scanned.
"Hours" is the current time when an item is scanned.
I want "Hours_finish" to be the time of the previous scan.

If Product <> 0 Then
Hours = Now()
End If

If Product <> 0 Then
Hours_finish = DMax("[Hours]", "Data", "Hours < Now()")
End If

 

[CraigDolphin]

Well, here is what I would consider:

1. You need to build the where condition part of the string using date/time delimiters (#).
Thusly:

Hours_finish = DMax("[Hours]", "Data", "[Hours] < #" & Now() & "#")

2. Are there actually any previous scans present in the Data table for the function to find? If not, how should the code deal with a null result?

Hours_finish = Nz(DMax("[Hours]", "Data", "[Hours] < #" & Now() & "#"),0)

3. I'm not 100% sure but I think the value of Now() might change by a millisecond or two during code execution if you call it more than once. In that case you might get unexpected results if the current scan record gets saved to the table before the DMax function is used.

I would run Now() once and store the value in a date variable. Then use that variable to create the scan record, and in your Dmax where criterion. Something like:

Dim datScanTime as date

datScanTime = Now()

If Product <> 0 Then
  Hours = datScanTime
End If

If Product <> 0 Then
  Hours_finish = Nz(DMax("[Hours]", "Data", "[Hours] < #" & datScanTime & "#"),0)
End If

 

[jgrondin]

Thanks CraigDolphin, I'm finally moving forward!


I have previous entries already and now Hours_finish always gives me a result of: "00:00:00"

Hours still gives me the current time in this format: "2012-05-29 13:17:27" as an example.



Any idea why Hours_finish always give the same answer ?

 

[CraigDolphin]

Are then any null values/records in your table? The NZ function should return a 0 valued time if the dmax function finds no matching records or returns a null.

I've attached a quickie db with a form that uses the logic I gave you and it seems to work just fine.

When you click the button on the form it adds a new record to the table, and populates the product field and the hours field with the current time. It then then calculates the time of the previous scan and the number of seconds elapsed and displays them in unbound text boxes on the form.

Hopefully it helps you figure out what's going wrong in your situation.

 

[jgrondin]

Thanks CraigDolphin, your help is much appreciated !

I tried using the code after having modified it a bit. There seems to be some sort of mistake somewhere. I built a query using datediff and it works. The only part that does not seem to work is the vba code to get the previous scan time.


I attached my file, it might be easier. Do you think you could give it a quick check? I'm still getting that 0 valued time.


I'm starting to believe I will finally be able to make it work !

 

[jgrondin]

Any idea if I need to use SQL in order to store et get the previous scan time ?

 

[CraigDolphin]

There are some issues.

Firstly, You need to be sure that the field names used in the DMax function are the same as the field names in the data table. You were using the field name 'Time' in the DMax, while the actual field name in the table in 'Scan time'.

Secondly, your code module is not being run with the Option explicit. This means you don't get errors when you assign a value to an undeclared variable. You were assigning the value returned from the DMax function to 'something' called 'Previous scan'. VBA treated that 'something' as being a variable. If you were using option explicit, you would have got a warning alerting you to that. However, you were intending to place the value from the DMax function into the text box control named 'Previous scan', which is bound to the field that is also named 'Previous scan'.

Note: while not always critical, many experienced developers elect to rename the textbox control to avoid ambiguity. E.g., if the field is named 'PreviousScan' the text control bound to that field might be named txtPreviousScan.

However, even that (by itself) would not have solved the issue because you also need to specify that this named object is a control on the form, not just some randomly named variable you've made up.

To do this, you have to use something like:
[Forms]!FormName.ControlName = DMax(blah blah blah)

Or, you can use a commonly used VBA shortcut (Me) that refers to the form associated with the code module instead of spelling out the whole Forms!FormName part of the above.
ie:
Me.ControlName = DMax(blah blah blah)

You might also notice that, in my example database, I did not use spaces in field or control names. You should avoid using spaces in object names as they can create problems with vba code. However, if you DO use a space in an aboject name, you will have to encapsulate the complete name in [] so the vba code knows that the space is still part of the object name.

Also, the textbox control that is bound to the [Scan time] field had been named 'Time'.
Time is one of the 'reserved words' that Microsoft says you should never use as object names. http://support.microsoft.com/kb/286335

Finally, as I demonstrated in my example, there's no need to store the previous scan time in the data table. You can always look it up with a query or DMax function whenever you need it to be available. Storing it just takes up unnecessary disk space. You should be aware that Access has a file size limitation that cannot be changed. My understanding is that the theoretical maximum file size is around 2 GB, but that bad things start to happen at file sizes exceeding 1 GB. It may not matter if your database only ever holds a few thousand rows of data. But it is never a bad idea to do things right from the beginning as these things can take on a life of their own over time.