Form setup and validation issues Access 2007

Hi Guys,
I am spinning my wheels here.  I have created a form called Add New IA which saves to the IA__tbl.  There are 4 fields for the user to populate.

1.  IA Number  - I have been able to code the IA Number so user is required to enter 6 digits.
2. IA Name - no issue at this time
3. IA State - I would like to use a list box here but can't get the setup right.  Is my relationship setup incorrect? State_tbl linked to IA_tbl
4. Ticket Type - list box like IA State

When saving record would like to validate that all 4 fields are populated.  My code does ask user if they want to save - thats ok but would really like to double check that all fields are populated.  

DB is attached.  Thanks.
Who is Participating?

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

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.

Rey Obrero (Capricorn1)Commented:
you will use the forms' beforeupdate event

this is just a sample code

private sub form_before_update(cancel as integer)

if me.IANumber & ""="" then
  msgbox "Please enter IANumber"
  exit sub
end if


end sub
Rey Obrero (Capricorn1)Commented:
Private sub form_beforeUpdate(cancel as integer)
Dim sCtlName As String, NullCtl As String
sCtlName = ""
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
            If IsNull(Me(ctl.Name)) Or Len(Me(ctl.Name)) = 0 Then
                sCtlName = sCtlName & ctl.Name & ";"
            End If
    End If
If Len(sCtlName) > 0 Then
    NullCtl = Mid(sCtlName, 1, InStr(sCtlName, ";") - 1)
    Exit Sub
End If

end sub
rhadashAuthor Commented:
I have added this code but get error on IA state. Currently the IA State shows only the State ID and not the actual state - need to correct this.

Error message reads:
Run-time error '3314'
You must enter a value in the 'IA_tbl.IA State' field

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
which do you want to save to the Sate_Id field, the state abbrev or the state id number?
rhadashAuthor Commented:
Two character state code which is = IA_State in the State_tbl but IA State in IA_tbl.  
Rey Obrero (Capricorn1)Commented:
test this

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
rhadashAuthor Commented:
Sweet!! There is a lot I have to learn.  Thank you.
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.