List box and data validation issue 1

[ccjmcd]

I am new, so I apologize in advance. I have a form that uses a list box for record selection. Once the form is modified I check data for validity using a macro in the AfterUpdate section of the form. The problem I have is the macro fires and checks the data and if invalid displays my message, but the record moves on to the next record selected in the list box. How do I keep the current record when I find bad data, so the user can make the changes needed?

 

[Remou]

Please post your code.

 

[ccjmcd]

The List box is unbound and the code is this:

Private Sub List61_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MachineID] = '" & Me![List61] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I'm not sure how to attach the Macro code so I will paste in what I can.

([Check9]=True) And (IsNull([Text15])) MsgBox
... GoToControl

 

[Remou]

You could do checks in the before update event of the lisbox, for example:

Private Sub List61_BeforeUpdate(Cancel As Integer)
If [Check9]=True And IsNull([Text15]) Then
    'Prevent record changing
    Me.List61.Undo
    Cancel = True
    DoCmd.GoToControl "Text15"
End If
End Sub

You can save macros as code.
It is best to use names that mean something for controls: txtCarType, lstPickMe, cboColours.

 

[ccjmcd]

Thank you Remou for your reply.

I tried the code and receive the following error: Run-time error '2108' You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method.

 

[Remou]

Oops. Please delete that line.

 

[ccjmcd]

The problem I encounter now is that I can't use the list box to select any records. The other issue is when I find an error I do want to direct the cursor to the field in error.

 

[Remou]

Ok. Try this:

Private Sub List61_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    'This is a guess at what to check
    If [Check9]=True And IsNull([Text15]) Then
        'Clear selection
        Me.List61 = ""
        Me.Text15.SetFocus
    Else
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[MachineID] = '" & Me![List61] & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
End Sub

It is possible that a number of records are failing the check.

 

[ccjmcd]

Thank you Remou that works perfectly.