"Dont move on" and "If record selector is clicked"

[KAZZANeedsHelp]

I have a database created on access 2000.

I have a form called aDataManipulationF.

This form has a sub form on it called aStaffTrainingUpdateIndividuallyF.

The sub form opens ready to add a new record.

On this sub form I have a field called SSDetailsID which has a default value of 0 (zero). It is the first tab field on the report and data is required to be entered into this field.

This field has a LostFocus Event Procedure which I have written as follows (but it does not work the way I want):

***********************
Private Sub SSDetailsID_LostFocus()

On Error GoTo Err_SSDetailsID

Dim db As DAO.Database
Dim rs As DAO.Recordset

If Me.SSDetailsID = "0" Then
MsgBox "You must select Staff Site Details ID before moving on"

DoCmd.CancelEvent

SSDetailsID.SetFocus
End If

Exit_SSDetailsID:
Exit Sub

Err_SSDetailsID:
MsgBox Error$
Resume Exit_SSDetailsID

End Sub

**************************

What I want to do is to tell the user that "You must select Staff Site Details ID before moving on". I have done this through the use of a MsgBox which looks at whether there is a zero in the field. This works.

If there is a zero then they should not move on. I want the cursor to stay in the SSDetailsID field. This part of my code does not work. I have tried cancelling the event and setting the focus back to the field to no avail.

The other problem at the moment (as the code is written above) is if I click on the record selector (at the bottom of the form) the MsgBox shows up again. Apart from the irritation of the message showing up it does not stop the user from scrolling through previously created records (which is a good thing as I want the user to be able to scroll through previous records).

WHAT I NEED IS CODE THAT:
Gives the user an error message if the SSDetailsID field is = 0 (zero) and for the cursor to remain in the SSDetailsID UNLESS they click on the record selector, then it should allow them to scroll through previous records (without giving the initial error message.

I hope I have expained myself well enough here.

Can anyone help me?

Kazza

 

[TheAceMan1]

How are ya KAZZANeedsHelp . . .

Move the code to [blue]BeforeUpdate[/blue] event:

[blue]Dim db As DAO.Database, rs As DAO.Recordset
    
    If Me.SSDetailsID = "0" Then
        MsgBox "You must select Staff Site Details ID before moving on"
        
        Cancel = True
        
        SSDetailsID.SetFocus
    End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[KAZZANeedsHelp]

Hi there,

thans for your assistance.

As you suggested I added the code you wrote to the BeforeUpdate Event.
Then I tried it on the AfterUpdate Event.

In both cases the code did not behave how I described I needed it to. In both cases the msgbox did not appear and the cursor moved to the next field on the page. At no time did the msgbox appear that includes when scrolling through all the fields on the sub form or when using the record selectors.

Can you suggest what the problem might be?

 

[KAZZANeedsHelp]

After a bit of playing around and using the code suggested I have got it working.

There are two areas where I have placed the code.
One in the LostFocus Event of the Field
One in the Before Update Event of the Form.

I had to move it back to on lost focus (I figure that because nothing is being entered then nothing is being updated).
********************************
On Error GoTo Err_SSDetailsID

Dim db As DAO.Database, rs As DAO.Recordset

If Me.SSDetailsID = "0" Then
MsgBox "You must select Staff Site Details ID before moving on"
Cancel = True
Me.SSDetailsID.SetFocus '(Note this does not seem to work)

End If

Exit_SSDetailsID:
Exit Sub

Err_SSDetailsID:
MsgBox Error$
Resume Exit_SSDetailsID
************************************

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form

Dim db As DAO.Database, rs As DAO.Recordset

If Me.SSDetailsID = "0" Then
MsgBox "You must select Staff Site Details ID (SSDetails) before moving on"
Cancel = True
Me.SSDetailsID.SetFocus 'Note this does work

End If

Exit_Form:
Exit Sub

Err_Form:
MsgBox Error$
Resume Exit_Form

End Sub
******************************
Thanks heaps for assisting me to resolve this issue.

 

[TheAceMan1]

[blue]In both cases the code did not behave how I described I needed it to. [purple]In both cases the msgbox did not appear[/blue] and the cursor moved to the next field on the page.[/blue]

[blue]Change: If Me.SSDetailsID = [red][b]"[/b][/red]0[red][b]"[/b][/red] Then
To      If Me.SSDetailsID = 0 Then[/blue]

The beforeupdate even should be sufficient alone. Your problem was rying to compare a string value with a numeric one.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]