Default date needs to earlier previous field + 1 day

[Fluffygoldfish]

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

[TheSmileyCoder]

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.

Private Sub tb_Date_AfterUpdate()
  Me.tb_DateEnd=DateAdd("d",1,Me.tb_Date)
End Sub

[Fluffygoldfish]

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

[TheSmileyCoder]

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:

Private Sub tb_Date_AfterUpdate() 
  Me.tb_DateEnd=DateAdd("d",1,Me.tb_Date) 
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

Private Sub tb_Date_AfterUpdate() 
  Me.tb_DateEnd=DateAdd("d",1,Me.tb_Date) 
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:

Private Sub tb_Date_AfterUpdate() 
  Me.tb_DateEnd.Value=DateAdd("d",1,Me.tb_Date.Value) 
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)

[NeoPa]

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?