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?
Rey Obrero (Capricorn1)Commented:
test this
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.