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
StampITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StampITAuthor Commented:
Thanks. That is what I needed.
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.