Access Default Value = Another Columns Value

Thanks once again DatabaseMX,

How do I set this up in code?

Where do you have the 1st default value set?  Table or Form level ?

This is what I would do:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.SomeTextBox1 = "someDefaultValue"
    Me.SomeTextBox2 = "someOtherDefaultValue"
End Sub

As soon as the user types in any bound field on the Form ... the default values will be set.

mx

I would like to set this up at the table level.  That way If I need to import data using Excel, then I  will not need to import as many columns.  Should I use the code above for that?

Well, if you are just importing into a table, then that code will not help.  At the table level, you cannot reference a default value from another column, sorry.

mx

It begs the question: "Why store a value that is 2 higher that another column when that value is 'instantly' available via a query?  You should never store derivable values unless you have a very good reason, like security for example."

I will be importing data from Excel at times as well as using a form to enter data.  I could use the code you have above for the form. I am just not sure what to put for "someDefaultValue"- I want this to be set to another field.  Thanks,

That is true, I am using an existing application that uses the other column.  So I have no control on how they do this.  

For the first field I need to set it's a date/time column. So should I change the code to:

Private Sub Form_BeforeInsert(Cancel As DateTime)
    Me.SomeTextBox1 = Me.SomeTexBoxtBox2
End Sub

Well .. you said two higher than another value ... so for example:


Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.SomeTextBox2 = Me.SomeTextBox1.DefaultValue + 2    
End Sub

This assumes you have the default value for textbox1 set in the table.

Something like that ?

I tried to put the following code in the Macro section:

Private Sub Form_ActualFormName(Cancel As DateTime)
    Me.StartDate = Me.EndDate
End Sub

When I go to the form, I don't have the EndDate field automatically equalt the StartDate field.  Is there something different I should do.  Do I need to execute this code or put this somewhere else?

Hi DatabaseMX,

Thanks again for your assistance. One of the fields on the form should be set automatically equal one of the other fields.  Also, I wanted another field to be 2 greater than another field. My main focus is getting the field that equals another field set properly.  

I wanted to kindly ask if I have the correct syntax below?  

Private Sub Form_ActualFormName(Cancel As DateTime)
    Me.StartDate = Me.EndDate
End Sub

When I go to the form, I don't have the EndDate field automatically equal the StartDate field.  I enter this as a Macro or should I enter this somewhere else?  The EndDate field should automatically update.

I'm sorry I mean that I entered this as a Module. Should I go somewhere else to put the code?  Based on your most recent post I have updated the Module to:

Private Sub ActualFormName(Cancel As DateTime)
    Me.StartDate = Me.EndDate.DefaultValue
End Sub

I'm not gettng the EndDate field equal to the StartDate field.  I'm not sure what I'm doing wrong.

Well... this should work:

Private Sub Form_BeforeInsert(Cancel As DateTime)
    Me.StartDate = Me.EndDate
End Sub

"Private Sub Form_ActualFormName(Cancel As DateTime)"

That is not really an Event on the form.  All Form events are of the syntax
Private Sub Form_<event name> (<SomeArgument> sometimes)

mx

No ...  not in an external module.  You put it in the BeforeInsert event on your form.

On the Form's property sheet ... Event tab ... you will see Before Insert.  Click on the three dots on the right. This will take you to code with looks like this:

Private Sub Form_BeforeInsert(Cancel As Integer)

End Sub

You add you line(s) of code - such that the end result looks like:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.StartDate = Me.EndDate
End Sub

mx

DatabaseMX,

I followed the steps you mentioned in your most recent post.  For some reason, the end date column is not automaticallys et. What do you think I should do?  

Also, for the second value I am trying to set.  How do I say that the default value of EndTime is 4 hours after StartTime?

You can use Me.EndTime = DateAdd("h",4,[StartTime])


"What do you think I should do?  "

Can you upload to http://www.ee-stuff.com/Expert/Upload/upload.php ... removing any sensitive data of course?  

Then, provide me the link EE-Stuff gives you to the file location ... back here in this thread.

Note: There is a  4MB upload limit.

mx

DatabaseMX,

Thanks again for your time. I have uploaded the file and was given this link:
http://www.ee-stuff.com/Expert/Upload/viewFilesQuestion.php?qid=22743272

I took out the priopetary information.  Also, when I tried to add
Me.EndTime = DateAdd("h",4,[StartTime])
to the existing code, I am getting some type of error.  I think I'm doing something wrong.  

 

DatabaseMX,

I have replaced Me.EndTime = DateAdd("h",4,[StartTime])
With
Me.EndTime1 = DateAdd("h",4,[StartTime1])

I had the column named wrong.  Thanks again!

I haven't gotten it to work yet.  

ok ... let's review what you need here:

1) Are these the only fields on the form?
My guess there are more fields.  We need to be able to trigger the Before Insert ... by starting to type in some field ... so that we can set the default values >>>

2) When you go to a new record (where default values apply) ... what exactly do you want to happen in each of the four fields on the form you sent me.

I'm leaving work ... will back online in a couple of hours ...

until then ...

mx

DatabaseMX,

Yes, there are some other fields on the form.  There is another column that comes before the "StartDate" and some columns after "EndTime1".  

What i'm utimately trying to do is have to type in less values in this form.  Either if I type in the form or paste from Excel (ideally the default values can work both ways, either way of inputting is fine).  I would like the "StartTime1" colum to pull the time from "StartDate"  (I was going to make a change to the code, but I found it easier to explain it the way I did).  That way there will be less data to enter.  If there is a value put in for StartTime1 then it will use that.  So I thought an default value was ideal.

For the second part, I would like the field "EndTime1" to be 4 hours after the field "StartTime1" as a default value.

"I would like the "StartTime1" colum to pull the time from "StartDate" "

Sorry ... but I am confused.  How do you want to pull a Time from a Date ?

"So I thought an default value was ideal."

What do you want the default values to be ... and where?

mx

DatabaseMX,

I appreciate your time and assitance.  Based on this question and other questions you have helped me with, I think you are very good at these things.  Thanks again.

The column "StartDate" contains a time as well.  I would like the default value of "StartTime1" to be obtained by getting the time from the "StartDate" column.  Also, I would please like the default value of "EndTime1"- to be 4 hours after the time entered for "StartTime1".  

In terms of where the default values will be set; i think you mentioned that this could be done on the form and not on the table.  I would prefer on the table, but the form will work.

BTW, I can change the format of the StartDate Column so that it more easily shows the time and the date if need be.