Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access Default Value =  Another Columns Value

Posted on 2007-08-05
25
Medium Priority
?
7,806 Views
Last Modified: 2016-08-29
I just wanted to ask how I can set a default value in MS Access that is equat to another column in the same row.  Also, I want to set another column's default value to be 2 higher than another column in the same row.  Could you please let me know how to do that?  Thanks.
0
Comment
Question by:jjrr007
  • 14
  • 10
25 Comments
 
LVL 75
ID: 19635874
You cannot do that in table design.

You would have to do that in code.

mx
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19635882
Thanks once again DatabaseMX,

How do I set this up in code?
0
 
LVL 75
ID: 19635888
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:jjrr007
ID: 19635896
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?
0
 
LVL 75
ID: 19635916
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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19635957
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."
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19635984
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,
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19635999
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
0
 
LVL 75
ID: 19636008
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 ?
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19636010
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?
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19636021
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.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19636029
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.
0
 
LVL 75
ID: 19636038
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
0
 
LVL 75
ID: 19636044
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
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19642416
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?
0
 
LVL 75
ID: 19642439

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
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19642534
DatabaseMX,

Thanks again for your time. I have uploaded the file and was given this link:
https://filedb.experts-exchange.com/incoming/ee-stuff/4271-Question-22743272.zip 

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.  

 
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19642601
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!
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19642603
I haven't gotten it to work yet.  
0
 
LVL 75
ID: 19642604
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
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19642725
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.
0
 
LVL 75
ID: 19643337
"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
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19651460
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.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 19651466
BTW, I can change the format of the StartDate Column so that it more easily shows the time and the date if need be.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 19651793
Ok ... Do this:

1) On the property sheet for StartDate - set the Default Value to
Now()

2) Put THIS code in the BeforeInsert event ... I'm showing the entire event here:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.StartTime1 = TimeValue(Me.StartDate)              '** THIS LINE
    Me.EndTime1 = DateAdd("h", 4, Me.StartTime1)     '** And THIS LINE
End Sub

So ... for example ... as soon as say ... you type something(the first character) in Project ID ..
StartTime 1 will populate with the Time Value of Now()
and
EndTime 1 will populate with 4 hours later than that.

mx
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question