Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,

    Hope someone can help, as I'm being driven insane..

    I want to check to see if an id already exists in a table, I'm using an unbound form, the code I'm using is as follows:-

    Private Sub Fred_BeforeUpdate(Cancel As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT [Tote Id] " & _
    "FROM [Tote Boxes] " & _
    "WHERE [Tote Id] = '" & Me.Fred & "'")

    If rs.RecordCount <> 0 Then
    MsgBox ("Tote Id Already Exists !!")
    Cancel = True
    Me.Fred.Undo
    Me.Fred.Value = " "
    End If

    Set rs = Nothing
    Set db = Nothing
    End Sub

    This works to a point as in it detects the duplicate, but I need the value to be cleared and the routine fails on the Me.Fred.Value="" with a runtime error 2115

    Any ideas?

    Many Thanks

    Bob

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='BobR' post='774123' date='08-May-2009 03:45']Cancel = True
    Me.Fred.Undo

    This works to a point as in it detects the duplicate, but I need the value to be cleared and the routine fails on the Me.Fred.Value="" with a runtime error 2115[/quote]

    Does me.Fred.Undo clear the value from the field?
    What extra do you want to achieve with Me.Fred.Value = " " ?
    Later on you describe this as Me.Fred.Value = "". These are different. One is setting it to an empty string, the other to a one character length space.

    One of the properties of a text field is "Allow Zero Length". If this is No (which is the default in some versions of Access) the field cannot have a value of "".
    Regards
    John



  3. #3
    New Lounger
    Join Date
    May 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='774220' date='08-May-2009 00:20']Does me.Fred.Undo clear the value from the field?
    What extra do you want to achieve with Me.Fred.Value = " " ?
    Later on you describe this as Me.Fred.Value = "". These are different. One is setting it to an empty string, the other to a one character length space.

    One of the properties of a text field is "Allow Zero Length". If this is No (which is the default in some versions of Access) the field cannot have a value of "".[/quote]

    Me.Fred.Undo doesn't appear to clear the value, the focus goes back to the field with original value highlighted.

    This causes a problem if the user just hits enter, the value is retained and they can fill the rest of the form in, however the save will fail as it causes an error due to primary key violation.

    I was trying to clear the value using Me.Fred.Value="" or " " I realise these are different I was just trying to find away of clearing the field, so I can trap for a IsNull or equiv, basically I don't want the user to move from this field until it's OK to do so.

    Thanks

    Bob

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Does me.undo do what you want?
    Regards
    John



  5. #5
    New Lounger
    Join Date
    May 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='774259' date='08-May-2009 10:47']Does me.undo do what you want?[/quote]
    No me.undo doesn't do what I'm after, however I've now found a way to do what I want.

    I've set up a global variable within the form code called DirtyData of type boolean. If the field doesn't meet the requirements this variable gets set to True, if the user then tries to jump to another field within that fields got focus event I check this DirtyData variable if it's set they get sent straight back to that field.

    Thanks for the suggestions.

    Regards

    Bob

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad you have something that works.

    In a similar situation, I just use Cancel=true, which returns the focus back to the value that has been entered, but leaves it there.
    The user cannot move on until they change something, but allows them to see the previous entry, and perhaps just add another character to the end.
    Regards
    John



Posting Permissions

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