Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Validating multiple fields prior to database record insertion/update

Posted on 2008-10-27
14
191 Views
Last Modified: 2011-10-19
I've got 3 fields on a form,  start time, end time and duration; pretty simple and I'm trying to validate them.  Start time and end time are optional if duration is provided.  Likewise if start and end times are provided, duration will be calculated automatically if not provided.  If all 3 are present, duration must be the difference between the start and end times.  Finally, start time without end time is prohibited and visa-versa.  

I was attempting to validate this in the Before_Update event but since I have all the validation code present, checking all the conditions, when entering a new db record, I'm getting lots of error messages informing me of conditions I know aren't correct because I haven't entered the data yet.   What I seem to need is an event that gets fired just before the record is to be written so I can validate all fields together not each individually since there is a dependency.  How can this validation be done without the use of an Update/Insert button type of logic?
0
Comment
Question by:ejefferson213
  • 8
  • 6
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22815574
which before update event are you using? for every field?

use the form's beforeupdate event to validate the values.
0
 

Author Comment

by:ejefferson213
ID: 22815620
Thank you for your prompt reply.  Actually, I have both but I could adjust that I suppose.  However, reading some documentation, it appears that after a control field's BeforeUpdate event is handled, the form's BeforeUpdate event is fired.  So even if I remove the field's BeforeUpdate event, the form's BeforeUpdate event will fire (prematurely in my case) resulting in my same issue. I'm back to needing something to trigger after all fields have been entered so I can collectively validate them.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22815831
did you try it?
the form's before update should fire when you moved to another record in editing record or adding new records.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:ejefferson213
ID: 22823303
I removed the events for each of the fields leaving only the form's BeforeUpdate event and it's acting as before.  If I try entering a start time and tab to the end time field, my validation rules are firing stating that an end time is needed if a start time is provided.  It looks like I'll need to provide an update button for each line of my datasheet (YUK!) unless there are other alternatives.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22823365
if you are validating the fields in the form's beforeupdate event, you don't need the validation rules in your controls and fields.
0
 

Author Comment

by:ejefferson213
ID: 22833596
I've removed the events for the controls and eliminated the validation in the table fields.  Therefore, the only thing left is the BeforeUpdate event on the form.  It's weird, it's validating the fields when moving between the fields on the form rather than at the end when moving off of this new entry. I've downgraded my validation so I'm "by" the problem but I'd like to know how to resolve this more fully.  Thanks again.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22833845
how about the form's controls validation ?
0
 

Author Comment

by:ejefferson213
ID: 22835102
This form is actually a subform and it has a BeforeUpdate event which is the only place where the validation is taking place.  No validation is being done on the individual fields or in the main form.  Hopefully that answers your question.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22835258
did you open the form in design view, select the control, hit f4, click the Data tab, look in the property  Validation Rule, Validation Text
0
 

Author Comment

by:ejefferson213
ID: 22835321
Did as you said and no field on the form has a validation rule or validation text.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22835400
can you upload your db? check Attach File below. just remove sensitive data
0
 

Author Comment

by:ejefferson213
ID: 22835638
To use this app:
  1).  Open database
  2). Open form TimeSheetUseridPassword
  3). In Name box, select EJ
  4). Enter the password abc and click on submit
  5).  Click on Add/Change Times
  6).  Try entering a new time sheet

Thank you.
DebuggingTimeSheets.mdb
0
 

Author Comment

by:ejefferson213
ID: 22844153
BTW, when using the form to enter a date, the field has a Format of Short Data and an input mask of 09/09/0099;0;_    When the cursor is placed in that field to enter information and valid numbers entered, it immediately (not when moving to another field) says: "The value you entered isn't appropriate for the input mask '09/09/0099;0;_' specified for this field.   Do you have any idea why that would be?  The digits are numeric and it ultimately takes it anyway. (I'm sure you'll experience the same thing.)  
0
 

Author Closing Comment

by:ejefferson213
ID: 31510457
After recreating the form, the problem disappeared.  Thanks for working with me to attempt a fix!!!!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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