Why does my Code Work - but Only Sometimes

[neelsfer]

** This thread was split from a related thread how to add 1 minute based on previous record in same field in access **

I developed a problem here now
On the first double click, it adds the 1 min, but when i click in the next row, instead of adding 1 minute onto the previous time, it shows "Invalid use of Null"

On Error GoTo TimeStart_DblClick_Err
    Dim strTime As String, strFraction As String
 
    strTime = DLookup("[Timestart]", _
                      "RaceEntry", _
                      "[RaceEntryID]=" & [RaceEntryId] - 1)
    strFraction = Split(strTime, ".")(1)
    strTime = Split(strTime, ".")(0)
    TimeStart = Format(DateAdd("n", 1, strTime), "dd/mm/yyyy HH:nn:ss.") & _
                strFraction
                DoCmd.RunCommand acCmdRefresh
TimeStart_DblClick_Exit:
    Exit Sub
 
TimeStart_DblClick_Err:
    MsgBox Error$
    Resume TimeStart_DblClick_Exit

In the 3rd line after double clicking inside it, it must show
"14/11/2011 21:23:03.019", but produces and error instead.
Please be so kind to assist

[neelsfer]

Neopa. this code only works now based on the first row of data and then it will correctly add 1 min to the 2nd row data, when double clicking inside timestart field in 2nd row.

If i ie add the initial date/time in maybe row 2 and doubleclick in ie row 3, then it will give me the invalid use of null error.This happens in every other row below the first one.

Pls assist when u have time

I hope it makes sense

[NeoPa]

It makes sense Neels. It seems this is a problem that you started with before you even asked the question. To help, I need some more info from you.

It appears the problem is with the reference on line #6 of your code to [RaceEntryID] (Not the reference within the string but the one afterwards that has - 1 after it). I would guess that it is the name of a Form control, but I have no real knowledge of it at all. It came with your question originally and I simply used it consistently with how your question was asked.

It seems clear that you would expect this to be set at three in the attached example picture, but that actually it's not even set at all. If you can tell me about it and where it's set and found then I may be able to help you get around the issue. If that's not possible for any reason we could probably use DMax() instead, but I'd need more understanding of how your data is stored and grouped to be able to do that for you. The former solution would be a neater and more appropriate one though if we can.

[neelsfer]

To put it simple
I press a button and the current time with msec is added to the "timestart" field -this workds 100%

When i 2x click one row below this, it adds 1 minute to the above overall time.this works fine.
When i go to the 3d row and 2x click inside it, it now produces this error.
The mainform = racesetupttsf
subform = RaceTimingTTSF1
field = timestart
table = racEntry
raceentryid.

I think i have an idea what problem is - the raceentryfield is sorted alphabetically and perhaps it cant find the next record.
I will check tonight if it works when its not sorted

[NeoPa]

Neels:
To put it simple

No. That's not simple at all. It explains nothing more than was already known, and nothing related to what I asked about. Your comment for [RaceEntryId] is simply :
raceentryid.

[RaceEntryId] is a reference to something in your project. It could be a field in the table, a control on one of the forms or a variable defined in your code. Whatever this is holds the key to the problem with your code. It is not set as you seem to expect it to be, but as there is nothing in anything you've said thus far that indicates how it's set than I can hardly help you resolve this issue. Hence my request, in post #9, for clarification of this particular reference.

PS. If it is indeed a variable, rather than a field or control object, then I also need to see the code where it is set as this would be the problem area in such a scenario.

[neelsfer]

Neopa here is a mini version that has the exact same problem. Instructions are on the screen; thx for your time

[NeoPa]

To explain the problem involved here I will need to explain a bunch of things which are absolutely relevant, yet were not included in the question. I eventually found it after sorting through fields in the table which had been both rearranged positionally and hidden.

The form [Rt_StartTTSF] is bound to a record source of the QueryDef [Rt_StartTTQ], which seems fundamentally to be a simple rehash of the table [RaceEntry]. This seems only to have the effect of making problems harder to identify. Tidiness of thought and design are not just for the purpose of looking good. They have the very real effect of making your project easier to work with, and therefore easier to find any problems in, when they do occur. At this point I'll also bring up some very basic points which you could benefit from. Your attachment was done very well, but not perfectly. Here's a link for all the tips I give for people before they attach databases. Many of them (like the Option Explicit one) are extremely useful in their own right and you'd benefit from generally (Attach Database (or other work)). Remember to explore the embedded links as they all have something to say that can help you.

Back to the explanation of the problem and why we're here. I found that the [RaceEntry] table, which to all intents and purposes is our bound record source for the form as well as the table of our DLookup() call, has a field called [RaceEntryId]. This (field) is also reflected by a control on the form (also named [RaceEntryId]), which is set visible, but whose width is set to zero (0) in Datasheet view (so is essentially hidden). This is an AutoNumber field.

You have made a logic assumption that such data will always be in sequence and your reference to [RaceEntryId] - 1 reflects this. When the previous record exists this works ok. When it doesn't, as in the case of [RaceEntryId]=641, it falls over in a heap.

Your data is :

Surname      FirstName      RaceEntryId
Adams        Cameron        638
Adams        Mason Stanton  639
AHLSCHLAGE   STEPHEN        641

NB. Don't be tempted to change (Fix) the data. The data is fine. It's the logic that's wrong.

[neelsfer]

Thx Neopa for the explanation.
What is happening here is that my database consists of about 4500 persons. Now only about 100 may start in this specific race, 1 min apart.
We usually sort the starting positions per "raceno" and that is where i believe the problem originated from now. The RaceentryId is in the wrong sequence because of that.Raceno 1 will start first and ie Raceno 100 on this specific Racedate, will be last.

[NeoPa]

NeoPa:
NB. Don't be tempted to change (Fix) the data. The data is fine. It's the logic that's wrong.

Don't forget this very important point Neels. It sounds like you are doing just that. AutoNumbers should never be used in such a way as to assume they are sequential. This is a worse case than normal, as you're not even including all records from the underlying table necessarily, but even if you were you still shouldn't be thinking along those lines. Change the DLookup() code to work regardless of gaps in the IDs.

[neelsfer]

Neopa - i add another field called "startsequence" where i can add the actual starting sequence for the cyclist.
I will add the first starting time for the cyclist with Startsequence = 1. After that i want to 2x click for 2nd rider , 3rd rider to add the starting times.

I have setup 2 racedates and startsequence 1,2,3 now to test it.
The dlookup with my "racedate" added has a filter problem. It does not look at the next "racedate" when adding the next time to the first "Timestart" field and subsequently 2x clicking in the others below it. Please look at it for me.

Dim strTime As String, strFraction As String
 
    strTime = DLookup("[Timestart]", _
                      "RaceEntry", _
                      "[startsequence]=" & [StartSequence] - 1) & " AND [RaceDate]= #" & Me.Racedate & "#"
    strFraction = Split(strTime, ".")(1)
    strTime = Split(strTime, ".")(0)
    TimeStart = Format(DateAdd("n", 1, strTime), "dd/mm/yyyy HH:nn:ss.") & _
                strFraction

raceno startsequence timestart
--2-- ---2--- ------19/11/2011 09:40:29.585
--3-- ---1--- ------19/11/2011 09:39:29.585
--1-- ---3--- ------19/11/2011 09:41:29.585

this is what it would like to achieve pls

[NeoPa]

Might it be somewhat easier to work on the basis of the original question but assume that double-clicking on any record will add one second to the latest [TimeStart] value so far in the table?

For this I would suggest replacing the original lines #4 through #6 with :

    strTime = DMax("[TimeStart]", "RaceEntry")

This should find the maximum time so far, but assumes the only records in the table are for that one race (as your original question implies and you have not explained otherwise). If this uses any invalid assumptions then you need to explain those and make clear the actual situation you're working within. The answers can only be as good as the question they match.

[neelsfer]

Thx Neopa; One cant perhaps take the Racedate field into consideration and make it the max time for that specific Racedate?

[NeoPa]

Sure you can, but to need to do that means the question needs to be changed to reflect a different requirement from what I've been working to, and I can't work without a clearly defined, and accurate, requirement specification.

Try the following anyway. I assume you have a control called [RaceDate] still on your subform :

    strTime = DMax("[TimeStart]", _
                   "[RaceEntry]", _
                   "[RaceDate] = #" & Format(Me.RaceDate, 'm/d/yyyy\#'))

[neelsfer]

Neopa Hooray!! Got it working. Your code was almost right. I just fiddled around after it was producing an error, and got the following code working miraculously. (the dd/mm/yyyy format is what we use mostly in my country)

strTime = DMax("[TimeStart]", "[RaceEntry]", "[RaceDate] = #" & Format(Racedate, "dd/mm/yyyy" & "#"))

[NeoPa]

I'm sorry Neels but that's just not right. I can't see what you have (or had) as an error, but only one of the changes you've made has improved the code. The others have damaged it to greater or lesser degrees. It's always a good idea to try things out yourself, and most of what you tried showed thinking - if not the relevant experience. Let me lay it all out for you so you understand what is what and why.

Firstly, and most importantly (as so many people get this wrong over and over again no matter how many times I try to tell everyone), dates in SQL are not location specific. I live in England and our dates are just like yours. Nevertheless SQL expects dates in m/d/yyyy format. You will fail to notice that it's not working for many test runs because it's also not stupid and fixes references for you if it can (when a date makes no sense in the wrong format). IE. Changing the date format is not just not right - it's actually thoroughly wrong.

Changing the format of the code to fit on a single line makes no difference to the code whatsoever. I simply do it that way to ensure anyone reading it can do so easily and therefore fewer mistakes are made. An important idea, but directly effects the results not at all.

Changing Me.RaceDate to simply Racedate will equally have no effect. It's simply somewhat clumsy coding. Me.RaceDate makes it clear that the code is referring to a control on the form whereas Racedate could just be a badly named variable. Remember we wasted some time (and posts) trying to determine where this came from originally. That was simply down to this poor choice of coding style.

Changing 'm/d/yyyy\#') to "dd/mm/yyyy" & "#" highlighted the one problem with my code (I used single-quotes (') instead of double-quotes (") within VBA code where they only work in SQL). It also illustrated a problem conceiving your code. It never makes sense to concatenate two string constants together except to fit them in the available space. "dd/mm/yyyy" & "#" has exactly the same effect as "dd/mm/yyyy#" except that it's clumsier and makes it harder to see that you've missed out an important character (Format() treats a # character as having special meaning but we want it to be ignored but passed along to SQL, where it has an important meaning for our code). A viable alternative could have been to add the hash (#), but after the first closing parenthesis of the Format() call (EG. "[RaceDate] = #" & Format(Me.RaceDate, "m/d/yyyy") & "#")).

Here's how the code should be :

    strTime = DMax("[TimeStart]", _
                   "[RaceEntry]", _
                   "[RaceDate] = #" & Format(Me.RaceDate, "m/d/yyyy\#"))

[neelsfer]

thx for the nice explanation and the good work you all do at this website

[NeoPa]

I'm always pleased to help Neels. I think I'm mostly pleased when I manage to explain things in such a way that people understand why things are done the way they are. This means they're better able to repeat it later on when required (Often just copying ideas or code doesn't leave them with a full understanding).