Query having trouble pulling last record

[robsworld78]

You might want to consider using Order number as your PK, but making it an ordinary Long Integer instead of autonumber, so that you have control over what it contains.

John

The Order number is the OrderID and it is PK, if I loose the autonumber how do I get it to auto-increment when I create a new order?

[orange]

Can you export the Orders Table to csv and post it?

As for autonumbers - they are really meant for Access's use. They are unique numbers only and can not be guaranteed to be sequential. In fact they are not always positive.
They do serve to uniquely identify a record, and that's all they're intended to do.

If your OrderIds need to be sequential, you could try putting the value in a single record table, then getting that value + 1, and writing it back into the table. This can be an issue in multi-user, but there may be other options. There are many people using sequential numbers in multi-user applications who will have figured something out.

Just saw your last post.. I wouldn't lose the autonumber from the PK. It's just how your using it to find Last. These are 2 issues at the moment.

[robsworld78]

The autonumbers has so far always been in the correct order and getting larger but I do believe you. The system is mult-user. I think I'm going to try and have the new order created via SQL insert so all the orders go in the system the same way and see if that makes a difference. Problem with this if it works there so much that will need changing. But trying it on the one form might not be to hard. I don't even know where to start with what you suggest, I'll do some searching and look into it.

I exported using text file and changed the extension to .csv, access then opened a dialog for what to use as a seperator so it should be good. When I opened it on my computer excel grabbed it and opened it like I see in access.

[orange]

It seems you have some doubts in these processes

I add records to the table via 2 methods, a regular form and on that form I add records using SQL insert. Depending what a user does on the form it might add a single record using just the form or it may trigger 2 SQL insert statements which adds 2 extra records to the table, so when the user is done on the form 3 new records were created and that is also working flawlessly.

It appears that if the last 2 records have the same VendorID and one was inserted via "SQL insert" and the other via the form it chooses the SQL insert record even though it comes before the record that was inserted via the form. (I hope that makes sense)

Its seems if all records go in via the form the query always has the right result and if all records go in via SQL insert the query has the right results but when records are added via both methods it seems to have glitches, however that could be all wrong because its done this from the very beginning I think before I used SQL insert except in less frequency. I got help on the forum once and thought I had it sorted out but the problem still exists.

Have you inserted some debug.print statement in some of these related processes to show the values of different fields (OrderId, Bin Number...) at various points - OR have you stepped thru the code.

Is it possible something is picking up a value and saving it and later writing it out --even though there has been an update while this process was working with a held value????

[orange]

Make sure you have backups, and work on a copy. Don't mess up your production stuff.
Get it tested before moving it into to operational system.

[orange]

I hate to be a pain but could you export it with field names?

[robsworld78]

Yeah I have lots of doubts, I can usually see patterns but I just can't with this. One minute I think yeah its because of the 2 processes but then something shows that says no it isn't.

I'm not good with VBA, just able to get by but most does make sense when I see it, I always get messed up on commas and such. I have watched every step to make sure its going as it should and grabbing the correct number and it is, that's why the forms are a mess and have all those subforms on them, so I can see it happen as it does. As far as I can tell its doing what it should and as I mentioned its done this almost from the beginning.

[robsworld78]

I'm sorry, it should have had that. This one does.

[robsworld78]

You need to find a way of identifying the last record by using the actual data. The "Last" record entered might be the one with the maximum ID value, though since you are using autonumbers that is not 100% reliable.

You both spoke on this and I've been able to make it work using the "Max" instead of "Last". I changed it so it gives the last OrderID for each vendor and have the bin on there as a normal field and it works great. Thanks for the tips guys.

Now what I'm a little concerned about is John's comment on autonumbers not being 100% reliable, what exactly do you mean by that? Do you mean it will use a gap in the ID's at some point. Of course when I start an order and cancel it that "autonumber" was used and I thought not used again. I thought the only way to reset the numbers was to have no records in the table and then do a compact and repair and they will reset back to "1".

I've never seen a lower autonumber then the previous one. How concerned should I be about using it this way? Unfortunately I've only seen autonumbers in the 1000,s not 10,000,s or more.

Orange you mentioned doing the field + 1 but that sounds rather difficult because this will be accessed by many people at the same time.

Thanks again for getting me this far, least it works now as this query is literally the most important query, if the system won't tell what vendor had what last correctly then nothing works.

[orange]

Yes Dmax + 1 will get you incremental numbers but there is a n issue in multiuser. I don't know what the odds are for 2 people and exactly the same instant in time, but here's a reference to the concept.
http://www.databasedev.co.uk/automat...ent_value.html

As for autonumbers, they are unique, and intended for access. They are not necessarily sequential, may have gaps, and some have found negatives.

Here is some info on autonumbers http://allenbrowne.com/ser-40.html

PBaldy talks of a bullet proof method in this post. Paul is well respected, I'd try his method.
http://www.access-programmers.co.uk/...d.php?t=203472

[robsworld78]

Thanks for the info thats great. I'm posting another video shortly about something else in the access forum if you're interested in a challenge.