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

Default date needs to earlier previous field + 1 day

P: 11
Hi

I have a staff database to record absence.

I have a field in a table "Return notified" to display the date we were notified centrally staff returned to work (this could be some time after they actually came back - major management issue!!)

I want the default in this field to be the "last day absent" (an earlier field in the same table) + 1 day. Able to be typed over and ideally blank until we have a return date. It doesn’t seem to let me build this formula in the default box.

Can you help please?
Thanks in advance for your help
Jan 22 '10 #1
Share this Question
Share on Google+
4 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Lets pretend your textbox holding the first date is called tb_Date, and that after updating it, you want another text called tb_DateEnd to be filled in automatically.

Expand|Select|Wrap|Line Numbers
  1. Private Sub tb_Date_AfterUpdate()
  2.   Me.tb_DateEnd=DateAdd("d",1,Me.tb_Date)
  3. End Sub
Jan 22 '10 #2

P: 11
Thanks for this.

I want the field in the table to be completed so I am unsure of where to put the code?

Sorry I'm fairly new to access so still finding my way around it.

Thanks again
Jan 22 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Open your form in design view. As I understant it you fill in a date in this form in some field. Select that field, and open its properties (F4). Look for the property name, and name it tb_Date or whatever you find practical. Don't use spaces in control names.

Next click the tab "Event" and find the event called AfterUpdate. You should see a small box with 3 dots on the far right. Click the dots and select Event Procedure.

Your now in VBA mode, Visual Basic for Aplications. Might seem scary at first, but its actually not so hard.

If you named it tb_Date access will allready have added some code for you:
Expand|Select|Wrap|Line Numbers
  1. Private Sub tb_Date_AfterUpdate() 
  2.   Me.tb_DateEnd=DateAdd("d",1,Me.tb_Date) 
  3. End Sub 
This is an event procedure, a small piece of code to be executed after the field tb_Date is updated, I.E. after you have entered the first date. Now we want it to do something.

Go back to your form, and select the field you want to be automatically filled in, and name it. In my example I just named tb_DateEnd. You can call controls almost anything, but its a good idea to not have spaces in it, and there are a few reserved words like "Date", which you should not use

Expand|Select|Wrap|Line Numbers
  1. Private Sub tb_Date_AfterUpdate() 
  2.   Me.tb_DateEnd=DateAdd("d",1,Me.tb_Date) 
  3. End Sub 
This code says, after updating tb_Date, we want to perform some code. We want to set the field tb_DateEnd in the current form (Me is a reference to the current form) equal to something, so we call a built in function DateAdd. If you want information about DateAdd, just click on DateAdd and press F1. The first argument "d" tells the function we want to add DAYS (M for Months) to something. The 1 says how many of "d" we want to add, and finally Me.tb_Date is the variable we want to add something to.
The above code could also be written as:
Expand|Select|Wrap|Line Numbers
  1. Private Sub tb_Date_AfterUpdate() 
  2.   Me.tb_DateEnd.Value=DateAdd("d",1,Me.tb_Date.Value) 
  3. End Sub 
However when Value is excluded Access defaults to assuming you wanted hte Value property.

If you have more questions feel free to ask (Possibly in a seperate thread)
Jan 22 '10 #4

NeoPa
Expert Mod 15k+
P: 31,263
You talk about fields and the table, but do you possibly mean controls and a form that is built on the table?

Setting the default for a form control is much simpler, and what most people would be asking. Is that your question? Or do you really want to manage this at a field/table level?
Jan 25 '10 #5

Post your reply

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