Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Thanked 0 Times in 0 Posts

    Clear Field on Form (2002)

    Hi! I wrote code that brings up an error message on a form if there is a record with what I typed in the field (SSN). That works. My problem - I want to go back to that field (SSN) on the form and clear it after I click OK on the error message.

    Thanks for your help!

    Private Sub SSN_BeforeUpdate(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Set dbs = CurrentDb

    ' Only continue if new record
    If Me.NewRecord = False Then Exit Sub
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Veteran WHERE SSN = 'Me!SSN'"

    ' Open recordset to test for duplicates
    Set rst = dbs.OpenRecordset("Veteran")
    ' Test if recordset is not empty
    If Not rst.EOF Then
    MsgBox "A record already exists with that Social Security Number.", vbOKOnly
    Response = acDataErrContinue
    Me.SSN.Value = " "
    End If

    ' Clean up
    Set rst = Nothing
    Set dbs = Nothing

    End Sub

    Question - How do you indent in post? When I viewed post, it's all to the left margin, but I put spaces in. Sorry ... it may be hard to read.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Warrington, Cheshire, England
    Thanked 0 Times in 0 Posts

    Re: Clear Field on Form (2002)

    Can't answer the hard question but if you have preformatted text, you can get this into a post using the Tag Panel to put preformatting tags round the text.
    There is a limit to the length of a preformatted line which shouldn't be exceeded because it throws the lounge page formatting out.
    When you preview your posting, text is inserted to show you that maximum length of the line.
    Hope this helps

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Clear Field on Form (2002)

    If you include the line

    Cancel = True

    the focus won't leave the text box. No need to use code like Me.SSN.SetFocus

    Note: the line

    Response = acDataErrContinue

    is meaningless in this context, it is used in event procedures that have a Response argument, such as the On Error event of the form, or the On Not In List event of a combo box.

Posting Permissions

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