July 24, 2008 04:27am pdt

1. boag2000 109,090
2. capricorn1 88,575
3. LSMConsu... 86,054
4. DatabaseMX 64,189
5. peter57r 52,816
6. harfang 35,026
7. matthews... 30,872
8. jimhorn 21,490
9. hnasr 17,150
10. MikeToole 14,750
11. jefftwilley 14,700
12. frankytee 14,550
13. RgGray3 13,396
14. GRayL 13,347
15. JDettman 10,752

1. LSMConsu... 151,169
2. DatabaseMX 139,547
3. capricorn1 131,583
4. boag2000 117,065
5. peter57r 95,270
6. jefftwilley 60,468
7. matthews... 59,206
8. harfang 54,006
9. jimhorn 45,508
10. GRayL 33,779
11. frankytee 31,442
12. JDettman 31,027
13. MikeToole 28,750
14. stevbe 21,852
15. dqmq 21,760

08.05.2007 at 05:50PM PDT, ID: 22743272

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

5.4

Access Default Value = Another Columns Value

Zones: Access Architecture/Design, Microsoft Access Database, Access Forms

Tags: value, access, default, another, column

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.

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: jjrr007

Solution Provided By: DatabaseMX

Participating Experts: 2

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
Automotive
New Net Users
New Users

Software
Digital Music
Gaming World
Home Security

Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Displays / Monitors
Handhelds / PDAs
Components
Peripherals
Laptops/Notebooks

Servers
Misc
Apple
Embedded Hardware
Networking Hardware

Storage
Desktops
New Users

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMware
Misc
Web Development

OS
CYGWIN
Voice Recognition
Virtualization
Message Queue
Quality Assurance
Security
Firewalls

MultiMedia Applications
Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals
Devices

Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
Web Computing
WebApplications
IDS
Vulnerabilities
Email Clients
File Sharing

Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Consulting
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMware

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel
Automation

Algorithms
Game
Signal Processing
Project Management
Open Source
Database

Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Web Services

Images
Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration

WebApplications
Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Web Computing

Broadband
Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Lounge
Business Travel
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles
Automotive

Community Support

Suggestions
New to EE
New Topics
CleanUp

Announcements
General
Feedback

Input
EE Bugs

08.05.2007 at 05:53PM PDT, ID: 19635874

Rank: Genius

DatabaseMX:

You cannot do that in table design.

You would have to do that in code.

mx

08.05.2007 at 05:56PM PDT, ID: 19635882

jjrr007:

Thanks once again DatabaseMX,

How do I set this up in code?

08.05.2007 at 06:01PM PDT, ID: 19635888

Rank: Genius

DatabaseMX:

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

08.05.2007 at 06:04PM PDT, ID: 19635896

jjrr007:

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?

08.05.2007 at 06:10PM PDT, ID: 19635916

Rank: Genius

DatabaseMX:

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

08.05.2007 at 06:17PM PDT, ID: 19635957

Rank: Genius

GRayL:

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."

08.05.2007 at 06:24PM PDT, ID: 19635984

jjrr007:

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,

08.05.2007 at 06:27PM PDT, ID: 19635999

jjrr007:

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

08.05.2007 at 06:34PM PDT, ID: 19636008

Rank: Genius

DatabaseMX:

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 ?

08.05.2007 at 06:35PM PDT, ID: 19636010

jjrr007:

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?

08.05.2007 at 06:42PM PDT, ID: 19636021

jjrr007:

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.

08.05.2007 at 06:46PM PDT, ID: 19636029

jjrr007:

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.

08.05.2007 at 06:48PM PDT, ID: 19636038

Rank: Genius

DatabaseMX:

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

08.05.2007 at 06:52PM PDT, ID: 19636044

Rank: Genius

DatabaseMX:

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

08.06.2007 at 04:41PM PDT, ID: 19642416

jjrr007:

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?

08.06.2007 at 04:46PM PDT, ID: 19642439

Rank: Genius

DatabaseMX:

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

08.06.2007 at 05:19PM PDT, ID: 19642534

jjrr007:

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.

08.06.2007 at 05:45PM PDT, ID: 19642601

jjrr007:

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!

08.06.2007 at 05:46PM PDT, ID: 19642603

jjrr007:

I haven't gotten it to work yet.

08.06.2007 at 05:46PM PDT, ID: 19642604

Rank: Genius

DatabaseMX:

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

08.06.2007 at 06:24PM PDT, ID: 19642725

jjrr007:

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.

08.06.2007 at 09:37PM PDT, ID: 19643337

Rank: Genius

DatabaseMX:

"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

08.07.2007 at 09:23PM PDT, ID: 19651460

jjrr007:

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.

08.07.2007 at 09:25PM PDT, ID: 19651466

jjrr007:

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

08.07.2007 at 10:51PM PDT, ID: 19651793

Rank: Genius

DatabaseMX:

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

Accepted Solution

20080236-EE-VQP-29 / EE_QW_1_20070628