Testing Code that only changes once a year?

[grubnz]

Hi there,

I was wondering if I could get some advice please on testing code that will only change yearly.

I have created a LogNumber (CustID),

With a customisation of the code that I have found in this forum thank you.

--------------------------------------------------------------------------------------
Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)

If IsNull(Me![BaseID]) Then

Me![BaseID] = Format(Nz(DMax("[BaseID]", "[tblOEMPublications]"), 0) + 1)

End If

Me![LogNumber] = Format([YearID], "yyyy") & "-" & Format([BaseID], "0000")

End Sub
--------------------------------------------------------------------------------------
The above gives me the below result...

YearID - BaseID
2017-0001
2017-0002
2017-0003
2017-0004
2017-0005 excetra

However in this case I only have two days to wait and test for 2018. But because I have altered the code and did get it to work I may have altered something I shouldn't have and it may have affected the reset of the BaseID back to 2018-0001.

But of course you can't always wait for the year to roll over to check if the code will work.

Is anybody I able to assist with my question it is always greatly appreciated.

Sorry if I have put this in the wrong place.

Thanking you for your assistance.

Wish everyone a Happy New Year.

Ani

 

[isladogs]

I'm not sure how any of us can advise you as we don't have the database

However its easy enough for you to test it.
Create a copy of the database then TEST the COPY by adding a new record.

Good luck

 

[arnelgp]

you should also check the Year first, if it leaped:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim lngCount As Long
If IsNull(Me![BaseID]) Then
	'
	' you supply "YourTable" with correct tablename
	' where you are saving [LogNumber]
	'
	' check if Current Year is in the table
	' if not (change in Year), reset the
	' BaseID to 1.
	lngCount = DCount("*", "YourTable", "Left([LogNumber], 4)='" & Year(Date) & "'")
	
	If lngCount = 0 Then
		' i think this is redundant, the year?
		' you can delete this line if you want
		Me![YearID] = Year(Date)

		' reset the BaseID to 1
		Me![BaseID] = 1
	Else
		Me![BaseID] = Nz(DMax("[BaseID]", "[tblOEMPublications]"),0) + 1
	End If
End If
Me![LogNumber] = Format([YearID], "yyyy") & "-" & Format([BaseID], "0000")
End Sub

 

[arnelgp]

you should also check the Year first, if it leaped:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim lngCount As Long
If IsNull(Me![BaseID]) Then
	'
	' you supply "YourTable" with correct tablename
	' where you are saving [LogNumber]
	'
	' check if Current Year is in the table
	' if not (change in Year), reset the
	' BaseID to 1.
	'
	' i think you will be Looking at the table where
	' you are saving [LogNumber] ("YourTable" in my sample)
	' since when you check on tblOEMPlublication, you
	' will get the (Max + 1), regardless if the Year has changed
	' or Not
	'
	'Me![BaseID] = Nz(DMax("[BaseID]", "[tblOEMPublications]"),0) + 1
		'
	Me![BaseID] = Nz(DMax("Right([LogNumber], 4)", "YourTable", "Left([LogNumber], 4)='" & Year(Date) & "'"), 0) + 1
	Me![YearID] = Year(Date)
		
End If
Me![LogNumber] = Format([YearID], "yyyy") & "-" & Format([BaseID], "0000")
End Sub

 

[grubnz]

Hi arnelgp,

Hope all is well.

Thank you for your assistance.

I have tried the code below.

I tried altering different items to see if I could get it to work but I don't have the knowledge...

I have got it to come up with the first number in the sequence. But it came up with the same number for the second record as well.

1905-0001
1905-0001
2016-0001
2016-0001
2017-0001
2017-0001

I used this to test the different years as seen above but have commented it out after doing this.

' Me![YearID] = Year(Date) - as suggested.

I have attached what I have done to show you.

Thank you once again for your time and assistance.

Have a wonderful day.

Ani

 

[arnelgp]

Youll have your answer, if no one does, as soon as i arrived home.

 

[The_Doc_Man]

If you are worried about your production (i.e. visible and usable by user base) code because you changed it, you just committed a cardinal sin of programming.

NEVER expose untested production code to anyone. You should ALWAYS ALWAYS ALWAYS have a test version of the DB, change THAT, and then test what you changed. If it works, you can either cut-paste the new code into your production version or you can keep a log book of what you changed to apply it again. OR...

What I did was assured that I had a "pure" split database - back-end (BE) and front-end (FE) - and then made changes to the TEST version of the FE. Though it is possible to have temporary local tables in an FE, no permanent data can EVER stay there. The purity is that all "real" data is in the BE and all code/forms/templates are in the FE.

You do your testing on the test FE which is linked to a test BE. For my site, I just copied the production BE to the test folders, and the test FE is always linked to the test BE in those folders. This works because the linked table manager goes by name, not by any internal file ID number. So if you overlay the old test BE with another test BE of the same name, you are good to go.

So... I did my testing. If and ONLY IF the test copy of the FE worked against a test copy of the BE, I then copied the test FE to a "staging" FE. Got into the staging FE and relinked it to the production BE. And then copied the updated staging copy to the production FE.

For complex reasons, I had to notify my users when the FE changed so they could copy the new FE to their seats. The idea of using an auto-update launcher script was good but our security people, being security people, didn't like it. (When do they EVER like a good idea?)

If you take this approach, or something similar, you never have to worry again about whether your new code will work. Because you have already tested it.

Some people refer to this as a "promotion" scheme. At my site, we had a development copy where stuff would get updated. Code would get promoted to test status via staging. Then tested code would be promoted to production status, again via staging. We also had code promoted to post-production status to act as a backup just in case stuff went berserk on us. Sounds like a lot of versions? Naw, not at all. Remember that these days, a terabyte disk is dirt-cheap. If your FE is pure, it probably won't exceed 30 Mb unless you have done something silly like embedded images in the FE. (Hint for that case: Keep them external and use hyperlinks.) How hard is it to store 30 Mb files for a couple of months until they "age off the system" by having other, newer versions to take their place? Not hard at all.

 

[missinglinq]

...in this case I only have two days to wait and test for 2018...
...you can't always wait for the year to roll over to check if the code will work...

What I do, when I need to check something like this, is to go into the Windows Time/Date window (by clicking on the Date/Time display in the Desktop Toolbar) and change the Date setting to New Year's day of the upcoming year...then run the code.

Just remember to reset it to the current date, when you're done!

Linq ;0)>

 

[grubnz]

Hi The_Doc_Man and missinglinq,

Thank you both for your for your posts, I appreciated what you have taught me.

Have a lovely day.

Ani

 

[arnelgp]

Sorry for being late.

 

[The_Doc_Man]

linq - got to watch out for changing system time/date. If a file gets created with a future date on it then you might have some utilities that actively dislike you. Didn't used to be a problem back in the MS-DOS days, clocks were a nicety that were mostly ignored. But as Windows became more and more of a real O/S, safeguards were placed in the file system. I have seen a WinXP dislike a "future" file and force its date to be reset to the equivalent of Now().

At the Navy site, Win7 was tuned in a way (by our ever-vigilant security persons) to treat future-date files as a security call-out on the grounds that the file might have come from a hacker or other viral threat. I can't tell you how much fun that was when I first tried that.

But then, someone else pointed out that this problem could occur if Windows clocks weren't synchronized and just "drifted" a bit (shades of the Julian calendar). Of course, instead of turning off whatever it was that was enforcing the date situation, the security mavens required us all to implement Network Time Protocol (NTP) on every machine. Fortunately, NTP is a standard part of most TCP/IP stacks so we were able to do this, but it is an insight into how security folks think.

Anyway, back to the issue at hand... it would be best to test something like this on an isolated system if you are going to dink around with the clock. In a corporate or network environment, that test would set off alarms.

 

[grubnz]

Hi arnelgp,

No worries you weren't late. I had to go to bed as it was getting onto 11pm and I had a long day.

Thank you very much for all your time that you have spent on this. I have just had a look at the completed code.

Have a wonderful New Year and All the best for 2018.

Ani

 

[grubnz]

Thank you The_Doc_Man,

For the information it is good to know. Thank you for your time.

Enjoy your New Year and all the best for 2018.

 

[arnelgp]

Happy new year to you!