forms to validate data and finally save in access

[annajohn0503]

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!!

 

[theDBguy]

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.

 

[GinaWhipp]

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

 

[apr pillai]

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.

 

[Pat Hartman]

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.

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.