update records on table via form not in order (1 Viewer)

good afternoon

have at the moment a slight brain hurt

as when i confirm a record out of squence, which may happen it updates that record for "Actioned By" but it put's the status on the newest record which is wrong

any help would be great

cheers

ps sorry its zipped but its 7meg

To provide an "Order" a sequence you require you need to use an index.

Sent from my SM-G925F using Tapatalk

murray83, in order to alleviate the brain hurt, you need to know something.

In a table, there IS no sequence. SQL (not only for Access) operates on data in no particular order because it is based on set theory. The idea is that each query is a monolithic operation, acting as though everything happened at once. You and I both know that of course that can't really happen - but the point is that it happens in a way that we can't tell the difference.

Table records are stored in the order that they are inserted or updated, even if there is a prime key. If you started from an empty table and did a bulk load, the records would appear in the order of insertion. But if the table gets updated, the order gets scrambled - particularly if a WHERE clause is in force.

The way to impose order on the dataset is to always go through queries with WHERE clauses to specifically select the desired record or records. This is why Uncle G says you need to use an index. A unique key will let you focus on a single record and update it. Or, if your record contains a unique date, or a combination of date and something else that when take together would make it unique, then you can have selective AND PRECISE updating.

As you point out, if you have to randomly update a record out of sequence when confirming it, if the query doesn't select the proper record you will confirm the wrong record. You are seeing that so you know it is true; you just might not have known why.

Your solution involves WHERE clauses that specify enough to make it clear WHICH record you want to update.

so the following sql which i have


wont cut it you say

I think the issue is more one of construction of your sql string, as opposed to the non ordered structure of data in a table.

The sql string will be

This assumes that ActionedBy is a text field, and ActionedID and ID are numeric fields. If the latter are not numeric, you need to have single quotes in the text.

This would evaluate, depending on your form data, as
Update main set [ActionedBy] = 'Cronk' , [EndTime] = Now(), [ActionedID] = 10 where [ID] = 15

thank you for that, i have changed the quotation marks as suggested but this happens


also do i need to call Me. can i not just put

and what if you dont mind me asking whats the & for

Me. refers to the form object that the code is being run from. By using Me. you will get an intellisense help of the available controls on the form the code is being written for.

& is used to concatenate (add together) strings. So
would make strVariable's value = "Fred Was Here"

This bit is wrong
It should be

Also unless ID is a text field, the single quotes should be dropped

also do i need to call Me. can i not just put
Code:
txtusername.value

In addition to the explanation given by Minty, the "Me." tells Access WHERE the variable is defined and so it reduces the work the compiler needs to do to get the physical memory address of each variable. Once you use the "Me.", the ".value" becomes unnecessary because .Value is the default property of controls and fields. You would only include the property if you wanted to reference something other than the .Value property.