Transforming a record (Access 2000)

Topic

New Reply

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! = Date
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

May i have the help ?

Reply | Quote

Replies

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

Reply | Quote

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! = Date()

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

Dear Hans,

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Dear Hans,

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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! = Date()

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

Reply | Quote