Post reply

MS Access error 2147217887

  • December 7, 2006 at 3:59 pm

    #67896

    I am brand new to this group so please excuse me if I am asking a question that has been asked before.

    I get this error when reaching the update in this code:

    PartnerInit = rsPartnerCompSel.Fields("PartnerInit")

    ManagingInit = rsPartnerCompSel.Fields("ManagingInit")

    MatterCode = rsPartnerCompSel.Fields("MatterCode")

    OriginatingPct = rsPartnerCompSel.Fields("OriginatingPct")

    ManagingPct = rsPartnerCompSel.Fields("ManagingPct")

    ManagingFactor = CalcMgtFactor(PartnerInit, ManagingInit, ManagingPct, OriginatingPct, MatterCode)

    rsPartnerCompSel!ManagingFactor = ManagingFactor

    rsPartnerCompSel.Update

    I get the additional error:

    The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

    I'm the only one on my laptop.

    Any thoughts?

  • December 8, 2006 at 2:10 am

    #676829

    Hi,

    This should answer your first question

    http://support.microsoft.com/kb/q297502/

    As to your second problem.

    Without seeing all of the code it's diffcult to be 100% precise however the Jet error in these situations is usually the result of you having a lock on the record because you are viewing the record in a bound form and then trying to run an update on the same record via code. I guess you are aware that a practical application of 3rd normal form would tell you not to hold calculated data in a table i.e. data that is derived from one or more fields in the table that aren't the primary key, i am guessing this is the root cause of your problem because you need someway of getting the calculation into the table which effectively prevents you from binding that field to the form. If your really want to store it you might try to use the afterupdate event of the last item to be completed. Test that the other items have been completed properly and then return to the result of the calculation to the field rather than updating a recordset.

    Private Sub ComboManagingPct_AfterUpdate()

    ' test to make sure that all fields have been completed

    'Then

    ManagingFactor = CalcMgtFactor(PartnerInit, ManagingInit, ManagingPct, OriginatingPct, MatterCode)

    'where ManagingFactor is the field name

    End Sub.

     

    I hope that's not too far off the mark

     

    K.

  • December 8, 2006 at 1:01 pm

    #677029

    Try it like this:

    rsPartnerCompSel.Edit

    rsPartnerCompSel!ManagingFactor = ManagingFactor

    rsPartnerCompSel.Update

  • December 13, 2006 at 5:36 am

    #677729

    Tend to agree with first reply about storing calculated values in the table.

    You can calculate values on the fly in Access (forms and reports) so not sure why you'd want to store them directly in the database.

    Doesn't answer your question but it might help eliminate the problem anyway by getting you to question your design.

     

    Kindest Regards,

    DaveyG

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply