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

    incremental value (Access 2000)

    Could you help me with my function for increment of an already registered record. In short my function FTransform is aimed to transform
    an order into an invoice.The number of the invoice is obtained by the field PaymentID.
    My list box "ListOrders" contains only orders that have no payment id ( IsNull)
    I want to convert this order into an order by increasing the paymentID with the next maximum value.
    For example if the previous paymentid is 1, then the next should be 2.
    I want to do it by clicking the button 'Transform order".
    Also, the order should disappear from the list box with an PaymentId not null.
    I want to do it by requering the form and the list box.
    That is the idea however my code is not running.My friends tell me is is an obsolete code. Could you help make it work ?

    I am applying an attachment









    Public Function ftransform()
    Dim f As Form
    Dim varItm As Variant
    Set f = Forms![FOrderInformation]
    Dim strOrderID As Control
    Set strOrderID = f![list].Requery
    End Function

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

    Re: incremental value (Access 2000)

    I would use entirely different code in the On Click event of the command button:

    Private Sub Command582_Click()
    Dim lngNextID As Long
    Dim strSQL As String
    ' Did user select an order?
    If Me.ListOrders.ListIndex = -1 Then Exit Sub
    ' Get next payment ID
    lngNextID = Nz(DMax("PaymentID", "Orders"), 0) + 1
    ' SQL for update query
    strSQL = "UPDATE Orders SET PaymentID = " & lngNextID & _
    " WHERE OrderID = " & Me.ListOrders
    ' Update record
    CurrentDb.Execute strSQL
    ' Update list box
    Me.ListOrders.Requery
    End Sub

    See attachment.

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

    Re: incremental value (Access 2000)

    It runs so smoothly now.Unbelievable.It is is simply unbelievable.
    Many thanks for your suggestion.It is so bulletproof.My code is flying now.
    Thank you once again.

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

    Re: incremental value (Access 2000)

    Dear Hans,

    After transforming the order i would like to open the report Invoice but on opening it i get the
    following message :


    extra ( in query expression '(paymentid=)'
    It can be seen in my attachment.

    What may be the reason ?

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

    Re: incremental value (Access 2000)

    You're referring to the PaymentID in the main form, but that hasn't been set. Instead, refer to the value you have just set:

    Dim stLinkCriteria As String
    Dim stDocName As String
    stDocName = "Invoice"
    stLinkCriteria = "paymentid = " & lngNextID
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

    Note: the following line

    Reports![invoice]![original].Visible = True

    will cause an error since there is no control named 'original' on the report. For the same reason, the Detail_Format code will fail - there is no control named 'cartons' on the report.

Posting Permissions

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