Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Before Update problem (2000 all updates)

    I have a form called frmMaynooth, based on tblMaynooth.
    On the form there is a subform called fsubRollInfo, based on tblRollInfo
    On fsubRollInfo there is a field called Person1RollNbr

    After that field has been filled in, I need to check tblRollInfo and make sure the entry is not a duplicate of an already existing number.

    Following is code that I have tried on the BeforeUpdate event for the control Person1RollNbr

    Dim nbrTemp As Integer

    nbrTemp = DLookup("[Person1RollNbr]", "tblRollInfo", "[Person1RollNbr] = " & Forms!frmMaynooth!fsubRollInfo.Form!Person1RollInf o)

    If Not IsNull(nbrTemp) Then
    MsgBox "This Roll # already exists. Please re-enter."
    Cancel = True
    Forms!frmMaynooth!fsubRollInfo.Form.SetFocus
    Forms!frmMaynooth!fsubRollInfo.Form!Person1RollInf o.Undo
    Forms!frmMaynooth!fsubRollInfo.Form!Person1RollInf o.SetFocus

    End If

    This results in an error saying that there is a conflict with the OLE server or ActiveX control...the function may not be able to be evaluated.

    I have also tried the code on the BeforeUpdate event for the form, and that results in the same message.

    In other circumstances, I could set the properties for the Person1RollNbr field in the table to "no duplicates" however, there is an additional field called Person2RollNbr and I need to apply code to check that field as well. So I thought if I could get the above code to work then I could add to it.

    Thanks for your help.

    Tom

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

    Re: Before Update problem (2000 all updates)

    In the first place, you should declare nbrTemp as a Variant, for you can't assign Null to an Integer.
    Alternatively, you could leave it an Integer, and use

    nbrTemp = DCount(...)
    If nbrTemp > 0 Then ...

    You haven't told us on which line the error occurs. Is it on the Undo instruction? If so, comment it out.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Update problem (2000 all updates)

    Hans

    Sorry, I should have added that I started by declaring nbrTemp as a Variant, but that produced the same result.

    The error occurs immediately when you leave the control, regardless of the entry.

    The error box says...
    The expression Before Update you entered as the event property setting produced the following error: A problem occurred while Maynooth Database was communicating with the OLE server or ActiveX Control.

    * The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
    * There may have been an error evaluating the function, event, or macro.

    Tom

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

    Re: Before Update problem (2000 all updates)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Update problem (2000 all updates)

    Hans
    Here's a really stripped down version.

    Tom

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

    Re: Before Update problem (2000 all updates)

    You have stripped it down so far that there is no code that checks Person1RollNbr...

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Update problem (2000 all updates)

    Hans
    Well, I must say, that's pretty dumb of me.

    Try this one.

    Tom

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

    Re: Before Update problem (2000 all updates)

    1) There is some kind of corruption. Try creating a blank new database and import all database objects into this database.

    2) You use Person1RollInfo instead of Person1RollNbr a few times.

    3) You don't need to use Forms!... to refer to a control on the form running the code.

    4) You can't (and don't need to) set focus to a control in the Before Update event.

    See attached version.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Update problem (2000 all updates)

    Hans
    Yes, corruption does appear to be the issue. Not sure why that would be the case, but it surely seems so.

    Sorry about the errors in the code (using Person1RollInfo instead of Person1RollNbr). I worked so long on this thing that I got a bit bleary-eyed and could no longer see the trees for the forest. Thanks for spotting that.

    Initially, I tried just using Me rather that the form and subform names, but this error kept frustrating me.

    I will try creating a new database and importing the objects.

    Tom

  10. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Update problem (2000 all updates)

    Hans
    Corruption was definitely the issue. Once I created a blank database and imported the Objects, everything worked fine.

    Thanks again for your help.

    Tom

Posting Permissions

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