• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 28
  • Last Modified:

How to ensure entry fields are valid when adding a record to a table using a form

In an Access 2016 database(attached) I have a table of machine set up records called tblSetup. Records need to be added to this table via form frmSetup. I need to verify that the Part_num, Press_num, and Oper_num are valid by comparing to tblRouting without disturbing existing records. A combo box seems to be the best way to do this. I believe that would verify the values are valid by automatically starting the entry by writing those three fields. Is that the best method of doing this ? If so what are the steps. Thanks.
AutoSS_Test.accdb
0
StampIT
Asked:
StampIT
  • 5
  • 4
1 Solution
 
PatHartmanCommented:
When lists are available, combos make sense.  Always set their limit to list property to yes to prevent people from being lazy and adding typos.  Make them go to the normal maintenance form to add new entries where they will have better search options so they can be sure they are not creating a typo.  The best technique is to use lookup tables and enforce RI between the main table and the lookup.  This will allow the database engine to prevent invalid entries made by queries or updating the table directory.  Do NOT set lookups on fields in tables.  Only create combos on forms. Lookups at the table level cause much confusion and are totally unnecessary.

Columns that are required should be set to required at the table level.  This will allow the database engine to prevent  rows from being added when required fields are empty.

The BeforeUpdate event of the FORM is the last event that fires before a record is saved.  This is the event where you will check for presence.  You do this so you can give the users better error messages.  Any validation error should stop the record from being saved by"

Cancel = True

Then you would set focus to the control with the error and exit the sub.  I don't undo edits except in one case and that case is if you are not authorized to make any changes.  Otherwise it is best to leave the invalid data for the user to see and correct.
0
 
StampITAuthor Commented:
Thanks Pat for the response. I am not familiar with lookup tables. What is that technique ? Is this in conjunction with a combo ?
0
 
PatHartmanCommented:
You should NOT use lookups on tables so don't worry about that technique.  

A combo allows you to specify a value list or point to a table or query.  A value list embeds the choices within the control.  This is OK for fields where there are only a couple of options and they NEVER change.  Gender used to be one of these but the politically correct have changed that.   With the table option, you would define a separate table for each type of data ie, MaritalStatus, Language, Gender, etc.  The Lookup tables that support these lists are usually just three columns.  For example
LanguageID
Language
ActiveFlg

The ActiveFlg defaults to true and is used to allow you to deactivate a value without removing it from the list.

I use a more complex solution which is more generic.  I put all my lookup tables into a single table and I use a single interface to manage the tables rather than creating separate forms for each.  In this case, I would use a query as the RowSource for the combo.  This allows me to select only the "language" rows from the larger table that includes MaritalStatus and Gender and others as well.

If you have only a few lookup lists in your app, it's fine to use individual tables.  If you know ABSOLUTELY that the list will never change (keep Gender in mind) then use a Value List on the table field itself.  If you start this way, the value list will propagate to any forms that use it.  But, tables are always safer.

As I mentioned, using combos is only part of the solution.  Combos keep invalid data from being added via a form but they don't protect against missing data or data added via an append or update query or via another form where you used a different combo with a different Value List.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
StampITAuthor Commented:
In the database I attached I did not have a combo box. So I should add one based on the reference table tblRouting and set it up so the user can type in or scroll for a part number, press #, and operation ? The reference table(tblRouting) is a live table from our ERP software so not sure about establishing referential integrity between tblRouting and tblSetup. Is this an issue ? Also I do not want the combo to interfere with the user using the form to find existing records. Should I have a form that is for adding records only and one for inquiry ?  Thanks.
0
 
PatHartmanCommented:
Create a query that selects the valid values for the field.  Use the combo wizard and use the query as the row source.  The wizard will set all the properties correctly.
0
 
StampITAuthor Commented:
I have created the query and the combo box in the form. Works fine if the user is in add mode. However if the user while in an existing record clicks on the combo and inadvertently selects a different item the item number is changed. This will not work. is there a way to prevent this behavior or have the combo only active when the user is adding a record ? Thanks.
0
 
PatHartmanCommented:
It is rare that you would actually never have a need to change the value of a non-key field.  At a minimum, people make mistakes.  It may require a "higher authority" to authorize the change, but you eventually will need to allow it.

You can use the BeforeUpdate event of the combo to control this.

If Me.NewRecord Then
Else
    MsgBox ("This value cannot be changed.",vbOKOnly
    Me.yourcombo.Undo
    Cancel = True
    Exit Sub
End if


If you want to allow the field to be changed, add code in the Else clause to prompt for a password and allow or disallow the update.  The code to disallow, uses Undo and also cancels the update to the control so that Access doesn't consider it dirty any more.
0
 
StampITAuthor Commented:
Thanks. That is what I needed.
0
 
PatHartmanCommented:
You're welcome.
0

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now