Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-10-2019, 09:27 AM   #1
annajohn0503
Newly Registered User
 
Join Date: Sep 2019
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
annajohn0503 is on a distinguished road
forms to validate data and finally save in access

i would like to develop some form or wizard; to let users enter details [around 5 tables with hundreds of records]. The main thing is i need to validate the user inputs and save it in access database.

could someone please guide me different ways to do this?

i used to be using excel sheet. But the problem is many times data is not valid and i need to validate and ensure data is valid and finally import into access.
i would like to make this step easy. ready to learn and develop anything.
and i would like to give users some help info regarding what they are filling, which i was not able to in excel.
Access forms validation messages are not so user friendly which might not help.

your suggestions means so much to me.

Thanks!!

annajohn0503 is offline   Reply With Quote
Old 09-10-2019, 09:37 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,707
Thanks: 57
Thanked 1,259 Times in 1,240 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: forms to validate data and finally save in access

Hi. Welcome to AWF! In Access, you can create a form for data entry; and to validate the data being entered, you can use the form's BeforeUpdate event to check if the user's input is correct or not. It is usually easier to use bound forms because then you won't have to worry about saving the data into a table since Access will do it for you.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-10-2019, 11:54 AM   #3
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,380
Thanks: 21
Thanked 896 Times in 881 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: forms to validate data and finally save in access

Hmm, well, after you build the Form have a look at...
https://www.access-diva.com/vba13.html

__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 09-16-2019, 07:32 AM   #4
apr pillai
Newly Registered User
 
apr pillai's Avatar
 
Join Date: Jan 2005
Location: India
Posts: 684
Thanks: 3
Thanked 109 Times in 96 Posts
apr pillai will become famous soon enough
Send a message via AIM to apr pillai Send a message via Yahoo to apr pillai Send a message via Skype™ to apr pillai
Re: forms to validate data and finally save in access

For your info: Excel Cells have some data validation features, which you can enable for required Cells to control data entry.
  1. Select a Cell
  2. Select Data from the Main Menu.
  3. Select 'Data Validation' from the 'Data Tools' Group.
  4. A Data Validation dialogbox will open.
  5. On the Settings tab select the data Type in the 'Allow' control you want to allow in the cell.
  6. Set the validation rule options given below the 'Allow' control.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Learn MS-Access Tips and Tricks)

All responses are based on Access2003/2007
apr pillai is offline   Reply With Quote
Old 09-16-2019, 10:20 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,243
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: forms to validate data and finally save in access

You can use wizards to create forms that the user can use to enter/update data. They won't be pretty but you can open them in design view and adjust control sizes and change the labels to user friendly names. They are fully functional as is. However, you have recognized that people make mistakes and so you will need to add code to the form events to ensure that the data is valid. theDBGuy suggested the Form's BeforeUpdate event and I concur. Probably 99% of my validation code gets placed into that event. In some cases it makes sense to use the control's BeforeUpdate event for validation but since that event cannot be used to ensure that a control contains a value, I just use the Form level event so that all the code is in a single event which makes it easier to study and modify.

When a field fails the validation tests, you need to cancel the update. Here's some samples that show how to do various tests.
Code:
If IsDate(Me.txtStartDT) Then
    If Me.txtStartDT > Date() Then
        Msgbox "Start Date must be <= Today's date.", vbOKOnly
        Cancel = True
        Me.txtStartDT.SetFocus
        Exit Sub
    End If
Else
    Msgbox "Start Date must be a valid date.", vbOKOnly
    Cancel = True
    Me.txtStartDT.SetFocus
    Exit Sub
End If

If IsDate(Me.txtEndDT) Then
    If Me.txtEndDT > Date() Then
        Msgbox "EndDate must be <= Today's date.", vbOKOnly
        Cancel = True
        Me.txtEndDT.SetFocus
        Exit Sub
    Else
        If Me.txtStartDT > Me.txtEndDT Then
            Msgbox "End Date must be <= Start Date", vbOKOnly
            Cancel = True
            Me.txtEndDT.SetFocus
            Exit Sub
        End If
    End If
Else
    Msgbox "End Date must be a valid date.", vbOKOnly
    Cancel = True
    Me.txtEndDT.SetFocus
    Exit Sub
End If

If Me.CustName & "" = "" Then
    Msgbox "Customer Name is required.", vbOKOnly
    Cancel = True
    Me.CustName.SetFocus
    Exit Sub
End If
.....
As you can see, the code validates one field at a time or validates the relationship between two fields and cancels the update if an error is found, places the cursor in the field with the error and exits. Eventually, all edits will succeed and the record will be saved. In the real world, once the user gets the hang of using the form, there will rarely be more than a single error found so there isn't really any need to write the more complex code needed to do all the validation and then report multiple errors at once.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
access , form , validate forms , validation

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
save data from two forms into one table vagues0ul Forms 10 11-04-2018 02:19 AM
Question Need VB coding for access data base to save the value in text box using save button JAWAJANA General 6 12-19-2017 09:46 PM
Not New to Access, Finally Trying to Use Forms Old_Timey_User Introduce Yourself 1 10-14-2012 10:48 AM
how to validate data in ms-access using vb.net prabhustrikes Queries 2 03-31-2009 02:41 PM
forms cannot save the data rahultiwari Forms 4 03-03-2005 06:42 PM




All times are GMT -8. The time now is 06:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World