Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test for New/Existing Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    I have a datasheet form for entering new and maintaining existing records with a code field as the last field of the record.

    I need the following form behavior:

    1. When entering a new record after entering the code I want control to go first field in a new record.

    2. When changing changing the code in a existing record, I want the control to go to the code field in the next record.

    In the AfterUpdate Event of the code field I need the proper code/syntax to test and see if I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Test for New/Existing Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Me.NewRecord is True if you are on a new record, False otherwise.

    Is After Update of the code field really the appropriate event? It also fires if the user clicks in another control in the same record (after changing the code), or if the user clicks the Previous record button in the navigation buttons. It would be disconcerting to be taken to the next record.

    I would use the On Current event of the form:

    Private Sub Form_Current()
    If Me.NewRecord = True Then
    Me.[CodeField].SetFocus
    Else
    Me.[FirstField].SetFocus
    End If
    End Sub

    Replace CodeField and FirstField with the actual names.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test for New/Existing Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Hi Hans

    I was able to get my results with the following and seems to work:

    Thanks for your help.

    John

    Private Sub Status_BeforeUpdate(Cancel As Integer)

    If Status.Column(1) = "PC" Or Status.Column(1) = "CP" Then ' Pulled Claim
    If Me.NewRecord = True Then ' New Claim
    MsgBox "Must select another Status Code"
    Cancel = True
    End If
    End If

    End Sub

    Private Sub Status_AfterUpdate()

    On Error GoTo Err_Add_Record

    If Status.Column(1) = "PC" Then ' Pulled Claim
    Me.Pulled = Date
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToControl "[Status]"
    Else
    If Status.Column(1) = "CP" Then ' Claim Processed
    Me.Processed = Date
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToControl "[Status]"
    Else
    Me.Pulled = Null
    Me.Processed = Null
    If Me.NewRecord = True Then ' New Claim
    DoCmd.GoToRecord , , acNewRec
    DoCmd.GoToControl "[SS No]"
    Else ' Existing Claim Change
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToControl "[Status]"
    End If
    End If
    End If

    Exit_Add_Record:
    Exit Sub

    Err_Add_Record:
    If Err.number = "2105" Then 'You can't go to the specified record
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToControl "[SS No]"
    Resume Exit_Add_Record
    Else
    If Err.number = "2110" Then 'Can't move the focus to the control Status
    DoCmd.GoToRecord , , acNewRec
    DoCmd.GoToControl "[SS No]"
    Resume Exit_Add_Record
    Else
    MsgBox Err.Description
    Resume Exit_Add_Record
    End If
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •