Macro on form getting incorrect date (1899!) 1

[Polyphonica]

Hi,

I'm doing a project for sixth form and need to record the date that a record is created. The thing is, I have for tables linked in a query and two of the tables need the date.

I have created a form based on the above query. One of the date fields has the =date() function and works great.

For the other field, I have created a macro with a Setvalue function and it supposedly sets the field on the form with =date(). For some weird reason though, it puts it as 30/12/1899. I know this is the first date tht Access recognizes but why is it doing this?

I have, to rule out my database, created a test database with one table, one query and one form (based on the query). I did the exact same thing; I created a macro to set the value of the date field on the form and i have the same problem.

Is it a known bug in Access or something? I'm using version 2002 and my database is in 2000 mode (although I have the same problem as 2002 mode).

Er, help!

 

[Polyphonica]

update:

I have changed the test database:

i have two fields on one table: ID(autonumber), date.

i have created a form based on the table with the wizard. then I added a command button to run a macro on the form with the function Setvalue.

It sets [Forms]![Table1]![date] with the expression =date()

I still have the same problem when I run the macro, it inserts the date as 12/30/1899 which, I hasten to point out, isn't the correct date!

 

[KenReay]

Hi

One point to note

You are using Date as a column name in your table and quite probably a control name on your report, but Date is an access function (which returns the system date),

can I suggest if you are starting out to learn Access VBA or any other programming language you start off with a good habit and use a naming convention

so the column in the table would be datDate of tyope date time, and you can set a default for it in the table definition eg Date() or you can set it in code:

datDate = Date()

no criticism, just a bit of friendly advice

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Polyphonica]

Thanks I'll do that in future (if i ever get this problem fixed! ;-) )

 

[KenReay]

Hi

I think you may find it is the cause of your current problem

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Polyphonica]

Thank you for your help but unfortunately it didn't solve it. I renamed it to datDate (in all honesty I've tried plenty of other names too) and it doesn't work.

If I set the default value to =date() then yes, it does work, but this isn't appropriate for my system. I need the field updated when the user clicks a command button so to do this I assigned a macro to the button with the function "SetValue".

I told it to setvalue on the datdate field on the form with the expression =date() and it still came up as 30 December 1899 :-(

Hmm. I'm beginning to think it is a bug in Access 2002 because SetValue has worked in the past for other projects; it just doesn't seem to like setting the date.

 

[Polyphonica]

In fact, I've just changed it to =now() just to see what would happen.

It has come up with the macro failing to run and in the arguments section of the error it says:

[Forms]![Table1]![datDate], 17/03/2004 14:13:45

so therefore it is successfully fetching the date/time but it isn't posting it correctly on the form. The field is set as general date.

-------

Ok, I've just come back from changing it to long time and used the expression =time() on the macro.

It fails every time it runs and says "the expression you entered contains invalid syntax" yet in the arguments bit of the error it shows the correct time. mad!

I'm seriously confused now! surely =time(), =date() or even =now() is valid?!

 

[luceze]

Can you post your code?

Eric

 

[Polyphonica]

Eric,

I don't actually know VBA code yet so I have just made the macro with the main access screens.

I clicked macro on the left, then 'new' and then on the screen that comes up i clicked setvalue and then at the bottom I entered the field name [Forms]![Table1]![datDate]
and in the expression box I entered =date()

then, on the form, i created a cmd button to run this macro. the field then shows 30/12/1899 or 00:04 if set to date or time respectively.

I could post screenshots if you like.

Alternatively, to get round these problems, could I get the command button to run VBA code which would update the field to display the current date? Would you be able to help me write it as I don't know VBA (just pascal I am afraid)?

Thanks

 

[luceze]

Sure. In the button's on click event add this line of code.

[Forms]![Table1]![datDate]=date


If your field is in the same form as the command button this will work also:

me.datdate=date

HTH,
Eric

 

[Polyphonica]

I've been trying to figure this out for nearly three hours. You fixed it in three minutes!!!


I love you!

Ahem.

That fixed it, thanks mate. I had a look at the vba code for the command button and substituted the bit where it ran the macro (something like DoCmd.macro..etc) for the code that you gave: me.datdate=date

What is the 'me' bit for?

Thanks!

 

[luceze]

I'm probably not the best one to answer this since I've never seen an actual definition. So if I'm wrong, someone please correct me.
"Me" refers to the current object that has the focus. In this instance it is your form. It makes code easier to write since you don't have to refer to the object everytime.

HTH,
Eric