Database forms not accepting date after New Year

[new begentle]

Hi. We have a database that was commercially bought about 12 years ago but the vendor is no longer trading. It is a clinical psychology practice management database. Since the new year we have developed a problem inputting some new data.

It has forms where we input the date of a new service provided or a new expense incurred. The input format is dd/mm/yyyy or dd/mm/yy, which the form then converts to dd/mm/yyyy. But it won't accept 20 or 2020 as the year. Other forms where we input the date are not affected, such as invoices, payments, receipts etc. There are also forms for editing data, and I can edit the service or expense dates to 2020. This has given us a temporary work around while I work out what the problem is, input a date for last year then edit the date later.

So the problem is limited to these two input forms, and doesn't seem to be related to the tables where the data resides, as the correct date can be input via the edit function. :banghead:

I'm using Access 2010, the database is about 12 years old and there doesn't seem to be any 'lock down' of the database, I seem to be able to look at and edit whatever I wish (naturally on a back up).

Any advice would be much appreciated. If the solution is likely to be complicated for someone with only the most basic of skills, then advice on where to go for help in Adelaide, South Australia would be appreciated.

 

[theDBguy]

Hi. Welcome to AWF!


Could you try to post some screenshots of the form in question, both in normal and design view? It might help us get some ideas on where to have you look for the source of the problem.

 

[new begentle]

Thank you for the welcome and responding to my query. I have attached the screenshots of what I think you asked for.

 

[new begentle]

I've been looking around a bit more, and opened up the attached windows. It seems that I need to find out where this bit of code checks the correctness of the date. The action just before this seems to check that the date is a valid date.

It occurs to me that if I just delete that bit of code, then maybe my problem is solved? Except that I can then put an incorrect, if not invalid, date. I might give this a go. I can always delete and replace the DB if I make an error.

Check_Correct_Dates([Date of Service]) Then
Else
MsgBox ("Please enter an appropriate date or press Cancel + Exit")
DoCmd.GoToControl "[Date of Service]"
Exit Sub
End If

 

[Galaxiom]

Check_Correct_Dates([Date of Service]) Then

Check_Correct_Dates() will be a function. Use the binoculars facility in VBA to Search the Current Project for the term.

 

[Gasman]

Do not delete the ines of code.
Merely put a ' (single quote mark) in front of the code. That will comment out the code and very easy to put back by removing the single quote.

Perhaps the develeoper never expected the DB to last this long?, sortsightedness if that is the case.

Best to inspect the function as Galaxiom advises and correct the function.

Much better solution.

Just post the code when found back here within the code tags (# icon)

HTH

I've been looking around a bit more, and opened up the attached windows. It seems that I need to find out where this bit of code checks the correctness of the date. The action just before this seems to check that the date is a valid date.

It occurs to me that if I just delete that bit of code, then maybe my problem is solved? Except that I can then put an incorrect, if not invalid, date. I might give this a go. I can always delete and replace the DB if I make an error.

Check_Correct_Dates([Date of Service]) Then
Else
MsgBox ("Please enter an appropriate date or press Cancel + Exit")
DoCmd.GoToControl "[Date of Service]"
Exit Sub
End If

 

[isladogs]

Crossposted at https://www.accessforums.net/showthread.php?t=79335

Please read this article about the etiquette of crossposting https://www.accessforums.net/showthread.php?t=79335

 

[new begentle]

Check_Correct_Dates() will be a function. Use the binoculars facility in VBA to Search the Current Project for the term.

Thanks for the advice, much appreciated, you've put me on the right track, even if I haven't quite got there yet.

I did as you suggested and found the following (single quotation mark added here just in case ):

' Public Function Check_Correct_Dates(in_Date As Variant) As Boolean

If (in_Date > #1/1/2020#) Or (in_Date < #1/1/1980#) Then
Check_Correct_Dates = False
Else
Check_Correct_Dates = True
End If

End Function

This looks to be exactly the problem! So I changed the year from 2020 to 2030 (definitely won't be using the software in 10 years!) and saved the changes, but it hasn't solved the problem. I found the function again and confirmed that I had correctly saved the change.

Is there anything else I have to do to make the change take effect?

 

[Gasman]

Compile again?

Remove the single quote from the Function line.
You need to do it on all lines of affeected code or none of them.

The only two you could have left alone would have been the Function and End Function lines.

HTH

 

[isladogs]

As Gaasman said … Remove that quotation mark as its preventing the function running
A slight variation you could try

Public Function Check_Correct_Dates(in_Date As Variant) As Boolean

If Year(in_Date)> 2030 Or Year(in_Date) <1980 Then
    Check_Correct_Dates = False
    Else
    Check_Correct_Dates = True
    End If

End Function

If In_date is a date field, then use As Date rather than as Variant

 

[new begentle]

Thanks again Gasman and I'm sorry for the really basic questions, I have Googled these but it seems that anyone mucking around in this area is assumed to have a lot more knowledge than me.

I found the compile option under the Debug menu and clicked on it. That option is now grey. How long does it take and how do I know if compilation is complete? Do I have to save changes back in Access as well?

 

[moke123]

Thanks again Gasman and I'm sorry for the really basic questions, I have Googled these but it seems that anyone mucking around in this area is assumed to have a lot more knowledge than me.

I found the compile option under the Debug menu and clicked on it. That option is now grey. How long does it take and how do I know if compilation is complete? Do I have to save changes back in Access as well?

Pretty much instantly. Once compiled it will stay greyed out until code changes are made again.

 

[new begentle]

Thanks guys, much appreciated. I'm getting there!

I've worked out now that there are 2 databases, one mdb, the other mde. I've done some googling and I understand why there's two types. So when we click on the shortcut in the Start Menu, we're opening the mde database. It is clear that the mde database updates the mdb database, but not vice versa, at least not the code! And you can't edit the mde database in Access, that's the whole point of an mde DB.

So the mde database opens up in an interface that doesn't appear at all like Access. I tried changing the path to the mdb database which brings up the same log in screen but I come up with some error messages as I log in.

But! When I open up the mdb file in Access, I can use all the forms, and the 2020 date is now accepted. So I have a working database, so long as I go in via Access.

So all I need to do now is create a new mde DB, and all should be good. If only. As the DB is in an earlier format, I need to convert the DB to Access 2007 format first. And that causes problems with Database Names in the DB, as there are different file extensions. So I'll need to solve that problem! Or find a computer running Access 2003 and make the changes and create a new mde DB there.

Anyway, it is very late here and I think I need to come at this fresh over the weekend.

Thanks once again for all the help, very much appreciated.

 

[Gasman]

The mde database should be the one being used.
The mdb is the 'master' where you make changes and then compile and when all is working, you make a new mde and pass that out.

If you can use what I presume is Access 2003 to open the mdb you can create a new mde, otherwise you will need to create a 2007 accde file, same thing really I believe, just different extension. Then pass that out to the users and amend the shortcuts.

This db should also be split, if more than one person using it.?

I myself still have a mdb from Access 2003 and I use it as an mdb even with 2007, but it is only for me.?

 

[new begentle]

I'm 99.9% there solving this problem. Thanks so much for all the help, Gasman and others.

The DB was actually in Access 2000 format. I tried loading Access 2000 on three computers, but it appears incompatible with Windows 10. As I couldn't access a Windows 2007 machine I thought I'd give trying to solve the database names problem in my DB a go. But when I went to save in Access 2010 format, I noted there was an option to save as mde, but it wouldn't work on my DB! Turns out Access 2010 can create an mde file from an Access 2003 format file. So I saved the DB as 2003 format, created the new mde file, spent a bit of time mucking around with the shortcut to open the DB from outside of Access 2010, and hallelujah, there was my DB, working perfectly.

Almost. For some reason, the annual P&L report form doesn't work correctly, I'm prompted to add the start and end date of the financial year, as well as name the financial year. But I can live with that for the time being, every other report and form seems to work perfectly. And the one user of the DB, my lovely wife, is very happy that now she can insert today's date with two mouse clicks instead of manually typing it in and thinks I'm a genius. I won't disabuse her that this is an Access 2010 feature and nothing to do with me.

Thanks again everyone.

 

[new begentle]

One last question, how do I register thanks here for the people who helped and advised me? I can see how many people have been thanked but can't work out how to do that.

 

[isladogs]

Hi
You can use the Thanks button at the bottom right of any posts that you found helpful.