rs.MoveLast appears to not be consistant

Hi Guys, im new to this forum, and have a question to ask, many thanks.

Im using the rs.MoveLast in an app and find that it is not consistant with actually "moving to the last record set" every single time consistantly.

(eg: on say 45 entries, it appears ok then on the 46th entry it duplicates the 45th entry again. and again. Sometimes the problem may happen earlier or later than this entry)

The DataBase Table itself "Work Orders" contains a number which seems to duplicate itself after a certain amount of time.

Does the problem exsit in the VB app or the DataBase Table itself?

Here is the vb code..

---------------------------------------------

'Open up the Work Order DataBase Record Set
Set rs = db.OpenRecordset("Work Orders")

'Move to the Last DataBase Record Set
rs.MoveLast

'Add in a new ARCentre DataBase Record Set
rs.AddNew

'Set the Entry Record Fields to the DataBase Record Set
rs!RMANumber = txtOrderReturnMaterialsAuthorisationNumber.Text
rs!DistributorName = txtOrderDistributor.Text
rs!DistributorCustomerNumber = txtOrderDistributorNumber.Text
rs!PurchaseOrderNumber = txtOrderPurchaseOrder.Text
rs!DistributorEndUser = txtOrderDistributorEndUser.Text
rs!WorkTypeRequested = cboOrderWorkType.Text
rs!PartNumber = txtOrderCatalogueNumber.Text
rs!PartDescription = txtOrderCatalogueDescription.Text
rs!AdditionalInformation1 = txtOrderWorkRequestComments1.Text
rs!AdditionalInformation2 = txtOrderWorkRequestComments2.Text
rs!SerialNumber = txtOrderSerialNumber.Text
rs!FreightOption = cboOrderFreightOption.Text
rs!Pricing = txtOrderPrice.Text
rs!GST = txtOrderGST.Text
rs!SubTotal = txtOrderSubTotal.Text
rs!ShippingAddressforReturnGoods = txtOrderShipppingAddress.Text
rs!ShippingStateforReturnGoods = cboOrderShippingState.Text
rs!ShippingPostcodeforReturnGoods = txtOrderShippingPostCode.Text
rs!ShippingContactNameforReturnGoods = txtOrderShippingContactName.Text
rs!ShippingPhoneforReturnGoods = txtOrderShippingContactPhoneNumber.Text

'Update the DataBase Record Set
rs.Update

'Close the DataBase Record Set
rs.Close

-------------------------------------------------------

Many Thanks, Peter.

Hi!Peter!I think it is impossible that the DataBase Table itself "Work Orders" contains a number which seems to duplicate itself after a certain amount of time. But i haven't found the mistake in your code...Plz tell me your table data structure??..It maybe has some thread to find the reason..

Without an ORDER BY in the select sql, there is no guarantee the last displayed record in a table or recordset is in fact the last record that was inserted.

Hi Doug, many thanks for the reply.

This does seem like the problem i am experiencing

How is ORDER BY in the select sql, performed within Access or can it be done in the VB code itself?

Could you please provide an example.

Many Thanks,

Peter

I'm not sure with DAO. You'll want to use a select statement such as SELECT * FROM [Work Orders] ORDER BY someField

Doug G is right!

this problem is often tied to the fact that the Query isn't in the exact order expected, so after adding a new record the last (46th) was actually the 45th, which got bumped down since the new record showed up in the middle somewhere.
Often this can be sidestepped by using an Order By parameter to the query, specifying an AutoIncrementing ID field (counter) or insertion timestamp field (if they exist) or adding a new field which does this.

If "Work Orders" is a query in access, all you need to do is go into the query window and add the appropriate ordering under the column name (if you were to have used * for the columns, add another with the specific column - in this case I am guessing RMAnumber is either itself or based on an automatically incrementing counter field). If it were a "calculated" field, you need the counter field upon which it is based...
If you are writing the SQL code yourself, then Doug G has the right idea.

Hi Guys, Many Thanks for the feedback,

Yes this seems the way to go, however the problem is the VB Code writes directly to a Table instead of a Query.

This table being the "Work Orders" Table (See attachment)

As far as i'm aware, there is no way to sort a field as ascending order within a table consistanly?

I guess the only thing i could do would be to set up another field in the Work Orders Table called "Autonumber" and use the Autonumber system access provides?

Any ideas?

Thanks,

that seems strange, any ODBC compliant ActiveX should accept a SQL Statement like "Select * from Sometable Order By SomeField Asc"

I would definitely avoid calling it Autonumber and use something like WorkID that definitely can't confuse Access seeming to be a reserved term...

You also might try AVOIDING naming your tables with Spaces in the name...

Also a naming convention will help to avoid problems if you have queries with the same names as tables...

For instance...
Name your 'Work Orders' table this: tbl_WorkOrder

this way you COULD create a query and call it: qry_WorkOrder
and you wouldn't have to worry about naming hassles...

Just my two cents...
good luck...

What I suggest you do is to change your table a little bit like people told you before.

you could just add a No_Order that would be the primary key, and you set it up as an increment. That's mean it will always be in a order where you have inserted them and when you say move last it's goes to the last one, and when you ask him to make a new one then he will add a new row under the last row.

I hope you understand me.

But becareful how you use it and why. It's not the best way to solve problems but sometimes it's just easier.
exemple : The project I am working on right now, the guy before added an incremented primary that you don't need to anywhere. Instead of using a VP number that my program use everywhere and that is kinda the primary key in the program but not in table. It's just that I can do querys etc... using it but problems happens when I try to remove or add or modify directly. Soo I needed to do more new functions etc... that just made the program slower.

Soo I say be careful when you will do it. But I think it's the best way to solve your problem right now.

Hi Guys,

Many thanks to the USA guys for there help, and the other support guys as well.

OK, i think it is now fixed! - time will tell!

OldJacques and the other guys were pretty much on the ball with the problem, all the Table required was an "AutoIncrument Field", using the AutoNumber in Access.

And i have called it "WorkOrders" (seeing this represents how many WorkOrders were created) as a name instead of AutoNumber which incruments on every new record insertion.
(See Attached)

After testing with 43 new entries from my VB app, not one entry missed a beat, all entered into the table one after another in accending numerical order.
(See Attatched)

It appears the AutoNumber in Access is "forcing" the next new record to the end of the record set! - great just what i was after!

And there can be now no duplication because you can't have a primary key field "autonumber" duplicate.

I'll keep testing, but for now it seems OK.
(If anyone else has a similair problem, looks like this is the fix!)

Cheers,

Pete.

Over time, this may not always remain true. You really should order your resultset to guarantee your idea of the "last" record is consistent.