Edit Existing Records by Using a Combobox to Automatically Fill Out Fields within the Form

[CAhawkeye10]

I am trying to use method #2 and I've followed the link to successfully have the required fields tagged with "*" automatically show as yellow. However, I can't get the code to correctly work in order to require certain fields like I originally wanted. Every time I delete a require field from an existing field, I get "runtime error 2467." See my code below:

Private Sub Form_Load()


'  Sets background color for required field -> Tag = *
Dim setColour As String
setColour = RGB(255, 244, 164)
Dim ctl As Control
For Each ctl In Form.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
            If InStr(1, ctl.Tag, "*") <> 0 Then
                ctl.BackColor = setColour
            End If
        End If
Next ctl
Set ctl = Nothing
End Sub


Private Sub myselector_AfterUpdate()
Me.Requery
End Sub


Private Sub SubmitAndClose_DblClick(Cancel As Integer)


Dim ctl As Control
For Each ctl In Form.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
            If InStr(1, ctl.Tag, "*") & "" = "" Then
            MsgBox "Enter data into control. Exit cancelled."
            Else
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.Close
            End If
        End If
Next ctl
Set ctl = Nothing


End Sub

[June7]

That may not have been the best example of using Tag.

Instead of the *, suggest you use a word.

Is this form set for single form view?

The code is not checking if control has any data. Checking for the tag value is only to restrict the controls that need to be checked.

If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox) And ctl.Tag = "Validate" Then
If ctl & "" = "" Then
...

Actually, by using the Tag it really is not necessary to check for the control type.


Why use code to set background color? Why not just set in Properties Sheet?

[CAhawkeye10]

I really appreciate the help, I think I'm almost there.

Yes, the form is set for single form view. I've included the line of code that you gave me but now I'm getting "runtime error 91." Logically, everything with the code below seems to make sense but I'm still having issues.

Private Sub SubmitAndOpen_DblClick(Cancel As Integer)

Dim ctl As Control
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox) And ctl.Tag = "Validate" Then
    If ctl & "" = "" Then
    MsgBox "Enter data into control. Exit cancelled."
    Else


    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
    DoCmd.OpenForm "Existing Change Estimate"
    End If


End If
End Sub

Thanks again.

[June7]

Did you change tag property of relevant controls to "Validate"?

What is the full exact message of that error?

Need the For Each and Next lines.

[CAhawkeye10]

Getting closer, yes the tag property of relevant controls says "Validate."

See my code below, I've gotten it to display the error message when required fields are missing. However, after clicking "OK" on the "Enter data into control. Exit cancelled." message box, I get the following error:

"Run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist."

Private Sub SubmitAndOpen_DblClick(Cancel As Integer)

Dim ctl As Control
For Each ctl In Form.Controls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox) And ctl.Tag = "Validate" Then
    If ctl & "" = "" Then
    MsgBox "Enter data into control. Exit cancelled."
    Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
    DoCmd.OpenForm "Existing Change Estimate"
    End If
End If
Next ctl
Set ctl = Nothing


End Sub

[June7]

What line triggers the error?

Learn debugging. Review link at bottom of my post for debugging guidelines.

[CAhawkeye10]

"Next ctl" is highlighted after I click debug.

[June7]

Controls and code are in the same form?

That code should work but I use simply:

Next

Have you step-debugged? Does the code get through multiple cycles of the loop?

I would also do:

For Each ctl in Me.Controls

[CAhawkeye10]

No, it's not running through multiple loops. I've attached the file below if you could take a look at it.

ExampleRev1.zip

Thanks again.

[June7]

Should exit the sub if encounter control with no data. Do not need the SaveRecord. Record is committed to table when move to another record, close form or run code.

Private Sub SubmitAndClose_DblClick(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox) And ctl.Tag = "Validate" Then
    If ctl & "" = "" Then
        MsgBox "Enter data into control. Exit cancelled."
        Exit Sub
    Else
        DoCmd.Close
    End If
End If
Next
End Sub

[CAhawkeye10]

Now the message box does pop up if nothing is entered in a required field. But if I click "OK" and then enter something in the required field, I get the following error again:

"Run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist."

[June7]

An alternative is to set the fields as required in table and let Access nag the users.

Why is the unbound combobox required to have input?

Try this one:

Private Sub SubmitAndClose_DblClick(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox) And ctl.Tag = "Validate" Then
    If ctl & "" = "" Then
        MsgBox "Enter data into control. Exit cancelled."
        Cancel = True
        Exit Sub
    End If
End If
Next
Set ctl = Nothing
If Cancel = False Then DoCmd.Close
End Sub

[CAhawkeye10]

Awesome, that worked! Last question (I think).

How do I get the form to not save if I click the "Cancel" button I've created? Right now, if information is entered into a required field, and then you click "Cancel," the form closes and saves the information. I'm also having this same issue if you just click the "x" in the upper-right hand corner of the window, the information is still saved. Is there an easy way to actually "cancel" everything that's been put in?

Thanks again for your help, greatly appreciated.

[CAhawkeye10]

Actually, I think I found the answer myself using "Me.Undo." I don't know if that's kosher but it seems to do the trick.

[June7]

I disable the X close on all forms. I also disable shortcut menu and shortcut keys, hide the navigation pane, customize the ribbon (to remove the Exit option), and disable the X app close. The latter two involve some advanced coding techniques.