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

how to autofill a date when I enter data in another field

P: 52
Hi,

I'm new to asking for help on the web, but haven't been able to find an answer to my question through Google - probably because I am not using the right words.

In my form, I want to be able to make an entry in the 'worker' field, and then on a mouse click, it automatically puts today's date in the 'Date allocated' field.

I have tried to build an event through expression builder, but that didn't work. I thought if i made the event on a mouse click, that would work for my database. But I don't have experience writing expressions.

I'm using Access 2003.

Hopefully this gives you enough information to solve my problem.

Thanks,
Marc
Oct 31 '08 #1
Share this Question
Share on Google+
8 Replies


puppydogbuddy
Expert 100+
P: 1,923
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worker_AfterUpdate()
  2.     Me!txtDate.Value = Date()
  3. End Sub
Nov 1 '08 #2

P: 52
Thanks for the code - but can you please tell me where to put it?

I went into my form, and right clicked on the date allocated field, and selected Build Event. When I selected Code it took me into Visual Basic and displayed a long list of code - is this the code for the whole form?

At the top of the screen, it had the name of that field (Date Allocated) and BeforeUpdate.

And then the first line of code displayed - was:

Private Sub Date_Allocated_BeforeUpdate (Cancel As Integer)

End Sub

I typed your code in between those two lines.

Initially, it told me it couldn't find the fields - and I fixed that to the names of the fields as they are in my database. Now I get a Compile error - Syntax error. and the line that is highlighted in yellow with an arrow says (followed by the second line):

Private Sub Worker_AfterUpdate()
Me!Date Allocated.Value = DATE()

Please forgive me - but I am a complete novice when it comes to using code. I have found Access fantastic - but until now have managed without getting into the coding.

thanks for your help - and your patience,
Nov 1 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
Thanks for the code - but can you please tell me where to put it?

I went into my form, and right clicked on the date allocated field, and selected Build Event. When I selected Code it took me into Visual Basic and displayed a long list of code - is this the code for the whole form?

At the top of the screen, it had the name of that field (Date Allocated) and BeforeUpdate.

And then the first line of code displayed - was:

Private Sub Date_Allocated_BeforeUpdate (Cancel As Integer)

End Sub

I typed your code in between those two lines.

Initially, it told me it couldn't find the fields - and I fixed that to the names of the fields as they are in my database. Now I get a Compile error - Syntax error. and the line that is highlighted in yellow with an arrow says (followed by the second line):

Private Sub Worker_AfterUpdate()
Me!Date Allocated.Value = DATE()

Please forgive me - but I am a complete novice when it comes to using code. I have found Access fantastic - but until now have managed without getting into the coding.

thanks for your help - and your patience,
No problem.

Generally, unless you have posted your code, experts at this (or any other) forum can only provide illustrative code that you have to adapt to your application. In regards to your compile error, each Private Sub/End Sub block below is a separate procedure and you can 't put one inside the other. Yes to your question about all the code being the code for the form.

Part of the confusion is due to the fact that your Date Allocated field name has a space in it. If the field is named Date Allocated, you would have to enclose it in brackets like this [Date Allocated] when referring to it in code, since Access interprets spaces as the end of name in object names, unless the full name is bracketed. That is why spaces in object names are discouraged. In regard to naming your fields and other objects, you should follow "naming" standards for objects in an Access Database application. The link below will explain the Leszynski Naming Conventions for MS Access, which is the de facto standard used by most Access Developers.
http://www.acc-technology.com/namconv.htm

Further, I placed the code that you requested for autofilling your date field in the procedure named "Worker_AfterUpdate" because you stated that you wanted the date to fill in after the "Worker" field was filled in. If this is not the case, please provide details and we will adjust the code accordingly. Make sure that there is a field named Worker in your application.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Worker_AfterUpdate()
  2. Me!Date_Allocated.Value = DATE()
  3. End Sub
'As far as I can tell the procedure below is of no use and should be deleted
Expand|Select|Wrap|Line Numbers
  1. Private Sub Date_Allocated_BeforeUpdate (Cancel As Integer)
  2. End Sub
Nov 1 '08 #4

P: 52
puppydogbuddy - you are my hero. Thank you so much for your help.

A couple of follow up questions -

I have just looked at the link you gave for naming - if I go back to table design and rename the fields by removing spaces and using capital letters to mark a new word and avoid the characters listed - will that do the job? I expect that I will have to go into reports and forms and make the changes flow through. The three letter preface is when you are in Visual Basic mode isn't it?

With the example of code that you said served no purpose (the Cancel as Integer) - it appears a number of times in my code - can I delete all of them?

Now that you have solved that question for me, I do have another, but will look to rename my fields first.

Thanks again for your time.

Marcella
Nov 1 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
puppydogbuddy - you are my hero. Thank you so much for your help.

A couple of follow up questions -

I have just looked at the link you gave for naming - if I go back to table design and rename the fields by removing spaces and using capital letters to mark a new word and avoid the characters listed - will that do the job? I expect that I will have to go into reports and forms and make the changes flow through. The three letter preface is when you are in Visual Basic mode isn't it?

With the example of code that you said served no purpose (the Cancel as Integer) - it appears a number of times in my code - can I delete all of them?

Now that you have solved that question for me, I do have another, but will look to rename my fields first.

Thanks again for your time.

Marcella
Marcella,
You are very welcome.

In answer to your followup questions:

1. Yes, you will have to go into reports and forms and make the changes flow through. The object names must be consistent throughout the application.
2. The three letter preface applies to all object names, whether or not they are used in your Visual Basic code. For example, the textbox where you want the date filled in should (for example) be named txtDateAllocated and the underlying field that is the control source should be named fldDateAllocated to distinguish the control from the field...but, you are correct in thinking the primary benefit is in making the code more manageable and easier to follow.
3. You can not automatically delete the code you referred to without reviewing to see if it is usefull or relevant.....however, you can't have duplicate procedure names ....if that is the case...yes you would have to delete or you would not be able to compile your code without error.
Nov 2 '08 #6

P: 52
So - on Naming: I should use the three letter preface when in design mode for everything I do - and if Access provides a default name, I need to go into the properties box and insert the preface.

Thank you again for your help.

Marcella
Nov 2 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
So - on Naming: I should use the three letter preface when in design mode for everything I do - and if Access provides a default name, I need to go into the properties box and insert the preface.

Thank you again for your help.

Marcella
Yes....not required, but encouraged for making applications consistant, and easier to understand and maintain.
Nov 2 '08 #8

P: 52
Thanks again - that has been most helpful!
Nov 2 '08 #9

Post your reply

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