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?
 
Rey Obrero (Capricorn1)Commented:
test this
Mail1109.accdb
0
 
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
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

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