TextBox Entry

Smurfit-Stone

Active Member
Joined
Dec 1, 2004
Messages
485
Hello Board,

I have a textbox on a form to enter a date, along with some other textboxes for other data. There are hundreds of records entered into this form daily. I'd like to know if there is a way I could have the date in this textbox remain whenever I enter the first record and change only when I enter another day's date. I'm trying to eliminate entering the date everytime for each daily record. Thanks in advance.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Yes, you can accomplish that fairly easy.

Create another field on your form. After testing, change this field to "hidden - yes".

Add code in the On Open Event that checks to see if this hidden field is blank... if not, you should null it out.

In the On Update, On Change or Lost Focus Event of your Date field, you add code that simply makes your hidden field equal to your Date field. Hopefully, you have some sort of PostIt or Save button that moves you to the next record. Add code to this button that sets your Date field to equal what is stored in your hidden field. This needs to happen after you move off of the record you just created.

PS... Smurfit-Stone in Chicago? Small world. My wife worked for Roger Stone for a bunch of years.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I would like to offer a different solution, in fact one I just wrote this past week. I have set the Default Value property of the date field on the form to be =DefaultDate(). Therefore, I have a function named DefaultDate that is in charge of calculating the default date for this field. I actually have my user enter the date he/she wants defaulted. Then this DefaultDate function returns whatever date was keyed into the "Use This Date" field at the start of the batch entry data. This way, nothing is being written directly to the field in the next record, which causes the record to be "created, ready to be saved" whereas a default date does not cause any record to be created. It will be the actual entry by the operator into any field in the record that will cause the record to be a new record.
HTH,
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
I would agree that my solution will create a new record simply by updating the new record's Date field. But, there are ways around that too.

If I understand you correctly, your DefaultDate is an unbound field and when the user saves the record, it updates the Date field accordingly. So, this would be the reverse of my solution.... Hide the Date field, create a DefaultDate field and when the user goes to save the record, your code updates the Date field with that which is in the DefaultDate field. Because it is an unbound field, it will retain the Date entered until you change it.

If you don't follow this... Smurfit-Stone... just holler.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

MyBoo,
The DefaultDate field is a little like your hidden date field. That is, it is within the main form. The Date field that is in the record has it's Default Value property set to be equal to the DefaultDate() function. The DefaultDate() function will read the DefaultDate field and return that date. Therefore, the Default Value is effectivly set to whatever date has been entered into the DefaultDate field within the main form. I'm assuming a main form/sub-form combination here. But the DefaultDate() function will work within a sub-form or on the main form, it does not matter.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hopefully this doesn't confuse the matter, but you can also use the control's Tag property and a couple of lines of code.
The last post in this thread shows how.

Denis
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

The "beauty" of using a Default Value is that it is just that, a Default Value. Access handles defaults quite nicely, at least I think so, and there is no worry about BeforeUpdates, OnCurrent, somehow ignore the record that has now been started, etc. If the user adds the record with other data, then the default value is included. If the user does not add the record by keying other fields, the record is never added. By capitalizing on the Default Value, it makes it much simpler. At least that is my opinion. Which is one reason I really like this board, we all get to share our way of doing things, and others jump in and help too. Thanks all!
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Vic,

Yes, I can see the simplicity of your approach. Exactly the reason I like this Board too... always more than one way to skin a cat, so your repertoire expands all the time.

I tend to use the Tag method because... I tend to use the Tag method :biggrin:
Actually, it's because I can easily adapt tag data for duplicating records, and / or toggling visibility of controls. I find that very flexible, so I use it wherever it looks appropriate.

MyBoo,

I often use hidden text boxes for grabbing IDs and synchronising forms. Hadn't thought of holding a default value there...

Denis
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
I tend to use the Tag method because... I tend to use the Tag method
Denis, that is a BEAUTIFUL quote! I laughed and really enjoyed it. Thank you!
Keep up the good work.
 

Smurfit-Stone

Active Member
Joined
Dec 1, 2004
Messages
485
MyBoo
Thanks so much for your direction. Please understand that I'm not Access literate. I do comprehend some of your instruction....its the coding I can't do. I would greatly appreciate it if you could provide the steps and code I need to accomplish this magic. Thanks to all of you for your insight.

P.S. Smurfit-Stone in Cincinnati. It is a small world, I'm in contact with Chicago daily. Oh yea, what's a Tag?
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top