Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    skipping error 2486 (Access 2000)

    In the after date event of the control productid in my subform i have a code that warns the user that he has ordered more than the stock quantity:

    If CLng(DLookup("items" & city, "Products", strCondition)) <
    CLng(cartons) * CLng(pack) Then
    MsgBox "Not enought cartons on stock, vbInformation + vbOKOnly
    DoCmd.GoToControl "productid"
    Exit Function
    Else
    DoCmd.RunSQL UpdateCartons

    However, in sch a case,the message really appears, but after that a code eeror 2486 appears :
    "You cant carry out the action this time"

    The error points to the origin of the conflict which is in the after update event of the subfom:


    If IsNull(Me.Quantity) Then
    MsgBox " You must enter either cartons or items!", vbCritical
    Cancel = True
    DoCmd.GoToControl "productid"
    DoCmd.Beep
    DoCmd.Beep
    ClearForm
    Exit Sub
    End If



    I need both codes. I think both codes will work if i somehow
    cancel the appearance of the error 2486.
    Is it possible ?

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

    Re: skipping error 2486 (Access 2000)

    Instead of placing the test in the After Update event of ProductID, put it in the Before Update event, and instead of setting focus back to ProductID, set the Cancel argument to True if the condition is met:

    Private Sub ProductID_AfterUpdate()
    DoCmd.RunSQL UpdateCartons
    End Sub

    Private Sub ProductID_BeforeUpdate()
    If CLng(DLookup("items" & city, "Products", strCondition)) < CLng(cartons) * CLng(pack) Then
    MsgBox "Not enought cartons on stock, vbInformation
    Cancel = True
    End If
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping error 2486 (Access 2000)

    Dear Hans,


    Thank you very much for your reply.I get stuck now,because , i think,the code makes sense only after
    i have chosen the cartons.Before that the code will not know how many cartons i have chosen.
    I have already a before update event, but that is in case the user has not chosen any quantity at all.
    And my second condition is that the user has chosen more cartons than available.
    If for example there are only 2 cartons, and i enter 3 cartons, then first i get the message
    for not enough cartons, on clicking OK i get the message "you must enetr either cartons or items" and on clicking OK
    i get the message that product cannot be updated and my programme sticks.
    Is it possible to neglect the appearance of the other error messages and skip the errors ?

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

    Re: skipping error 2486 (Access 2000)

    I recommend that you rethink the logic of all these tests. Perhaps you should only test in the Before Update of the form, and not in the Before Update or After Update event of the individual controls, since that leads to conflicts.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping error 2486 (Access 2000)

    Dear Hans,


    Thank you for you helpful advice.I have removed all the tests from the BeforeUpdate event.When I use all the tests in one place then I get the code working,but the other 2
    tests , namely
    If IsNull(MySubform.cartons)
    and
    If Not IsNull(DLookup("productid", "Order Details", "productid = " & MySubform.productid & " and orderid = " & [Forms]![FOrderInformation].orderid))

    seem not to work.I get no error message for them, but nothing happens.Otherwise the last line of code, for which my question was sent,works now

    Will you please tell me did I write the codes correct.Perhaps they are not included in the If clause, or else not written rightly.


    Public Function SetBranches()
    Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
    Set liters = [Forms]![FOrderInformation]![Forder details extended].[Form].[liters]
    Set UnitPrice = [Forms]![FOrderInformation]![Forder details extended].[Form].[UnitPrice]
    Set ExtendedPrice = [Forms]![FOrderInformation]![Forder details extended].[Form].[ExtendedPrice]
    Set Quantity = [Forms]![FOrderInformation]![Forder details extended].[Form].[Quantity]
    Set cartons = [Forms]![FOrderInformation]![Forder details extended].[Form].[cartons]
    Set pack = [Forms]![FOrderInformation]![Forder details extended].[Form].[pack]


    '===========================================
    If IsNull(MySubform.cartons) Then
    MsgBox " please enter cartons !", vbCritical
    DoCmd.GoToControl "productid"
    RunCommand acCmdDeleteRecord
    Exit Function
    End If


    If Not IsNull(DLookup("productid", "Order Details", "productid = " & MySubform.productid & " and orderid = " & [Forms]![FOrderInformation].orderid)) Then
    MsgBox "The product already exists in the order "
    Exit Function
    End If

    If CLng(DLookup("items" & City, "Products", strCondition)) < CLng(cartons) * CLng(pack) Then
    MsgBox "Not enought cartons on stock", vbInformation + vbOKOnly
    DoCmd.GoToControl "productid"
    DoCmd.SetWarnings False
    RunCommand acCmdDeleteRecord
    Exit Function
    Else
    DoCmd.RunSQL UpdateCartons
    End If
    End Function

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

    Re: skipping error 2486 (Access 2000)

    You shouldn't use a function SetBranches, but write the code in the Before Update event of the form. Set the Cancel argument of the event procedure to True if the record doesn't meet requirements, to prevent it from being updated.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping error 2486 (Access 2000)

    Dear Hans,

    Thank you for your advices.They are very precious.For some of the cases i cannot have a recourse to the Before update event of the form, becasue it is not yeat certain whether the customer will commit the error or not.
    All in all it works, i only have to tackle the problem, when the customer forgets to enter a figure for the cartons.If he just clicks with empty cartons, he goes to a new line.I want to avoid that.But my code in the AfterUpdate event of cartons
    does not function.If you choose a poduct,forget to eneter cartons and click Enter, then you go to the new line.How can i forbid that?

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

    Re: skipping error 2486 (Access 2000)

    There are many problems with your database. Please see the general remarks in <post#=513538>post 513538</post#>.

    As I noted higher up in this thread, you should use the Before Update event of the form, not the Before Update or After Update event of individual controls.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping error 2486 (Access 2000)

    Of course you are right.As always. I was confused somewhat.The reason was the "Enter" event.Could i improve the code ?
    I am applying your database.I use your example which works.When i choose a product, but do choose number of cartons and click "enter",the the focus stops at quantity.Then i have to click "enter" again, and then you code works.
    Can i avoid the second clicking on the "enter" button?I mean on clicking Enter and no product chosen, to revert back to the product and not go to the quantity.

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

    Re: skipping error 2486 (Access 2000)

    You have code in the On Open event of forms and in the After Update event of several controls that sets focus to other controls. This is very confusing, I would remove all that code. Youshouldn't set ProductID and UnitPrice to 0 in the Before Update event of the form either, it makes no sense.

    If you look at the attached version, you'll see that the code has become MUCH simpler by throwing out lots of unneccessary ballast. Also please look at the general recommendations I referred to!

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping error 2486 (Access 2000)

    Dear Hans,

    Thank you for all your replies.Please be assured that i will follow all your advices and correct my database in the final variant. I have an essential question regarding the use of 0.
    I have observed that the code line for example
    If IsNull(Me.cartons) Then

    is valid only when the field cartons is blank.However,my field cartons is not blank, it is by defualt 0, and hence i think my headache.
    What will be your comment?

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

    Re: skipping error 2486 (Access 2000)

    I always clear the default value for number fields unless I have a good reason for wanting one. If you prefer to keep the default value of 0, you could change the test to

    If Nz(Me.Cartons, 0) = 0 Then

    The user will get a warning if Cartons is null or is 0.

  13. #13
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: skipping error 2486 (Access 2000)

    Dear Hans,

    Thank you with all my heart for your help an patience with me.I am happy with the solution and the problem.I have another problem but i think i sould raise another thread,since the questions for this thread are already solved.

Posting Permissions

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