By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Why does my Code Work - but Only Sometimes

100+
P: 547
** 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"
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo TimeStart_DblClick_Err
  2.     Dim strTime As String, strFraction As String
  3.  
  4.     strTime = DLookup("[Timestart]", _
  5.                       "RaceEntry", _
  6.                       "[RaceEntryID]=" & [RaceEntryId] - 1)
  7.     strFraction = Split(strTime, ".")(1)
  8.     strTime = Split(strTime, ".")(0)
  9.     TimeStart = Format(DateAdd("n", 1, strTime), "dd/mm/yyyy HH:nn:ss.") & _
  10.                 strFraction
  11.                 DoCmd.RunCommand acCmdRefresh
  12. TimeStart_DblClick_Exit:
  13.     Exit Sub
  14.  
  15. TimeStart_DblClick_Err:
  16.     MsgBox Error$
  17.     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

Attached Images
File Type: jpg Magical Snap - 2011.11.14 21.21 - 004.jpg (39.5 KB, 680 views)
Nov 14 '11 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1.     strTime = DMax("[TimeStart]", _
  2.                    "[RaceEntry]", _
  3.                    "[RaceDate] = #" & Format(Me.RaceDate, "m/d/yyyy\#"))

Share this Question
Share on Google+
16 Replies


100+
P: 547
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
Nov 16 '11 #2

NeoPa
Expert Mod 15k+
P: 31,707
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.
Nov 16 '11 #3

100+
P: 547
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
Nov 17 '11 #4

NeoPa
Expert Mod 15k+
P: 31,707
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.
Nov 17 '11 #5

100+
P: 547
Neopa here is a mini version that has the exact same problem. Instructions are on the screen; thx for your time
Attached Files
File Type: zip timecopyissue2003.zip (70.3 KB, 49 views)
Nov 17 '11 #6

NeoPa
Expert Mod 15k+
P: 31,707
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 :
Expand|Select|Wrap|Line Numbers
  1. Surname      FirstName      RaceEntryId
  2. Adams        Cameron        638
  3. Adams        Mason Stanton  639
  4. AHLSCHLAGE   STEPHEN        641
NB. Don't be tempted to change (Fix) the data. The data is fine. It's the logic that's wrong.
Nov 18 '11 #7

100+
P: 547
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.
Nov 18 '11 #8

NeoPa
Expert Mod 15k+
P: 31,707
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.
Nov 18 '11 #9

100+
P: 547
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strTime As String, strFraction As String
  2.  
  3.     strTime = DLookup("[Timestart]", _
  4.                       "RaceEntry", _
  5.                       "[startsequence]=" & [StartSequence] - 1) & " AND [RaceDate]= #" & Me.Racedate & "#"
  6.     strFraction = Split(strTime, ".")(1)
  7.     strTime = Split(strTime, ".")(0)
  8.     TimeStart = Format(DateAdd("n", 1, strTime), "dd/mm/yyyy HH:nn:ss.") & _
  9.                 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
Nov 19 '11 #10

NeoPa
Expert Mod 15k+
P: 31,707
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 :
Expand|Select|Wrap|Line Numbers
  1.     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.
Nov 19 '11 #11

100+
P: 547
Thx Neopa; One cant perhaps take the Racedate field into consideration and make it the max time for that specific Racedate?
Nov 19 '11 #12

NeoPa
Expert Mod 15k+
P: 31,707
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 :
Expand|Select|Wrap|Line Numbers
  1.     strTime = DMax("[TimeStart]", _
  2.                    "[RaceEntry]", _
  3.                    "[RaceDate] = #" & Format(Me.RaceDate, 'm/d/yyyy\#'))
Nov 19 '11 #13

100+
P: 547
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)
Expand|Select|Wrap|Line Numbers
  1. strTime = DMax("[TimeStart]", "[RaceEntry]", "[RaceDate] = #" & Format(Racedate, "dd/mm/yyyy" & "#"))
Nov 19 '11 #14

NeoPa
Expert Mod 15k+
P: 31,707
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 :
Expand|Select|Wrap|Line Numbers
  1.     strTime = DMax("[TimeStart]", _
  2.                    "[RaceEntry]", _
  3.                    "[RaceDate] = #" & Format(Me.RaceDate, "m/d/yyyy\#"))
Nov 19 '11 #15

100+
P: 547
thx for the nice explanation and the good work you all do at this website
Nov 20 '11 #16

NeoPa
Expert Mod 15k+
P: 31,707
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).
Nov 20 '11 #17

Post your reply

Sign in to post your reply or Sign up for a free account.