Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America asked on

Checking for required fields, and updating unbound form

I have an unbound form that opens up in add mode with certain required fields.  I need to check if the fields are null and if so have the system return the user back to the null field(s).  

Once all required fields contain data I need to update the data into the table.  This form opens in add mode.  I have tried using the bound recordsource but that wants to create a record that would later need to be deleted if the user does not complete the processing of the form.  So I am trying to use an unbound form without using a temp table..

I need to create a form that validates that all required fields contain data, then update the table(recordsource) with the data from the unbound form.  Am I on the right track?


Private Sub cmdApproval_Click()
   On Error GoTo cmdApproval_Click_Error
    For Each ctl In Me.Controls
        If ctl.Tag = "C" Then
            If IsNull(Me.ActiveControl) Then
                Call MsgBox("There is Data Missing.", vbExclamation Or vbDefaultButton1, "Warning")
                Me.ActiveControl.BackColor = 128
                Me.ActiveControl.ForeColor = 16777215
                Exit Sub
            End If
        End If
    Next ctl
    Set rs = Currentdb.OpenRecordset("Select * from [Emergent Work]")
    If Not rs.EOF Then
       With rs
           !Tech_Grp_Person = Me.Tech_Grp_Person
           !Task_Description = Me.Task_Description
           !Requested_by = Me.Requested_by
           !Work_Estimate = Me.Work_Estimate
           !Planned_Start = Me.Planned_Start
           !Planned_End = Me.Planned_End
           !ECD = Me.ECD
           !In_Support_of = Me.In_Support_of
           !Work_Authority = Me.Work_Authority
           !Status = 1
       End With
    End If
        Set rs = Currentdb.OpenRecordset("Select Max(Seq_NO) as ID, Tech_Grp_Person from [Emergent Work]")
        Do Until rs.EOF
            gWR = rs.Fields("ID").Value
            Debug.Print rs.Fields("Tech_Grp_Person")
            gTP = rs.Fields("Tech_Grp_Person").Value
   On Error GoTo 0
   Exit Sub
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdApproval_Click of VBA Document Form_frmEmergentWork"
End Sub

Open in new window

Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

In each of the fields in which you require data in the On Lost Focus event put code returning focus to that field until the user complies and adds proper data to the field.


I of course am not yet awake. The correct syntax for the On Lost Focus event is:


Private Sub Insurance_ID_LostFocus()
If IsNull(Insurance_ID) Then Insurance_ID.SetFocus
End Sub

Sorry 'bout that, I'll get a cup of coffee.
Karen Schaefer

Thanks, for the input, however, the validation is only 1 part I am looking for the best approach to updating a record in a table - whether to use bound or unbound form in this case?


This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Karen Schaefer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

No objection from me. I'm glad Karen found her answer.

Have a great day Karen.