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.
Mail1109.accdb
rhadashAsked:
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.

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"
  me.IANumber.setfocus
  cancel=true
  exit sub
end if


...

end sub
0
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
Next  
If Len(sCtlName) > 0 Then
    NullCtl = Mid(sCtlName, 1, InStr(sCtlName, ";") - 1)
    Me(NullCtl).SetFocus
   Cancel=true
    Exit Sub
End If

end sub
0
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

Tks.
0
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?
0
rhadashAuthor Commented:
Two character state code which is = IA_State in the State_tbl but IA State in IA_tbl.  
0
Rey Obrero (Capricorn1)Commented:
test this
Mail1109.accdb
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
rhadashAuthor Commented:
Sweet!! There is a lot I have to learn.  Thank you.
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.