Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transforming a record (Access 2000)

    I want to transform a record by assigning 2 values and open a report with these new values,but somehow i cannot do it.
    I think that in order to get the best advice i need to explain at first what i am trying to do and after that to show what i have done with the errors.
    Our idea is the following.If an order is paid, it is not an order anymore and it becomes an invoice.The number of the invoice is the field paymentid.If paymentid is 0, then
    we have an order, if it is not,then we have an invoice.I use the DMax in order to get the next number of the payment.

    So i have a form and on it a list box called ListOrders. The row source of the list box is :

    SELECT orders.orderid, orders.orderdate, orders.paymentid FROM orders WHERE (((orders.paymentid)=0))
    ORDER BY orders.orderdate;

    My idea is to correlate the orderid from the list box with the orderid of the form, set the DMax and the Date function, and open the report
    with the new changes.But it is exactly in the correlation betwen the listbox and the form where i get the error.
    In the line Me![orderid] = Me![list] = Date
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

    May i have the help ?

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

    Re: Transforming a record (Access 2000)

    My guess is that you don't want to SET OrderID, but to locate the existing record with the specified OrderID. In particular, if OrderID is an AutoNumber field, you can't assign a value to it yourself.

    To locate the record, you can use code similar to what is generated by the List Box Wizard:

    Dim rs As Object
    ' Get out if nothing is selected
    If Me.ListOrders.ListIndex = -1 Then Exit Sub
    ' Clone of recordset
    Set rs = Me.Recordset.Clone
    ' Find record with specified OrderID
    rs.FindFirst "[OrderID] = " & Me.ListOrders
    ' Move to this record
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    ' Release object memory
    Set rs = Nothing

  3. #3
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transforming a record (Access 2000)

    Dear Hans,

    You are right,of course.I would like to ask you the following.
    Would you please have a look at the code below,
    it has been given to me by a friend a year ago, and is working, but i do not understand quite why should i use ItemData collection.My question is, is it a propert code,is it robust, and can we simplify it further?



    Dim f As Form
    Dim varItm As Variant
    Set f = Forms![MainForm]
    Dim strOrderID As Control
    Set strOrderID = f![list] = Date()

    DoCmd.RunCommand acCmdSaveRecord
    Dim stLinkCriteria As String
    Dim stDocName As String
    stDocName = "Invoice"
    stLinkCriteria = "paymentid = " & StrPaymentid
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

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

    Re: Transforming a record (Access 2000)

    This code is meant to be used for a multi-select list box, that is a list box in which the user can select more than one item. Normally, you can select only a single item in a list box, but by setting the MultiSelect property to Simple or Extended instead of None, multiple selection becomes possible. The ItemsSelected collection contains the index values of all items that have been selected. Do you have a multi-select list box? If not, this code is not useful to you.

  5. #5
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transforming a record (Access 2000)

    Thank you very much for your replu.No,i do not need a multi select box and do not need it. Is there any possibility to simplify the code further?

    Best regards

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

    Re: Transforming a record (Access 2000)

    If you have a standard, single-select list box, you can remove part of the code, but what is it meant to do? You apparently have a mainform open, then open it again?

  7. #7
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transforming a record (Access 2000)

    Dear Hans,



    I am afraid I cannot understand some basic rules. I have followed your advice
    But I receive the following message :

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

    Re: Transforming a record (Access 2000)

    It always helps to be specific. On which line of the code does the error occur?

  9. #9
    Lounger
    Join Date
    Dec 2003
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transforming a record (Access 2000)

    The debug line begins with DoCmd.OpenReport.... Maybe the fault lies somewhere else in my my invoice
    and i will use the older function which works.
    I want to thank you for all the excellent help you are giving and to wish you a nice weekend

Posting Permissions

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