Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-17-2017, 04:57 AM   #1
murray83
Newly Registered User
 
Join Date: Mar 2017
Posts: 108
Thanks: 13
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
update records on table via form not in order

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
Attached Files
File Type: zip trailer move idea 1.2.zip (1.03 MB, 12 views)

murray83 is offline   Reply With Quote
Old 06-17-2017, 05:07 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 8,476
Thanks: 215
Thanked 618 Times in 590 Posts
Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about Uncle Gizmo has a spectacular aura about
Send a message via Skype™ to Uncle Gizmo
Re: update records on table via form not in order

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

Sent from my SM-G925F using Tapatalk
__________________
............
I love doing YouTube Videos demonstrating what you can do with MS Access. I'm always looking for ideas for my next video, so I've set this thread up here:-


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Suggest a topic you think would benefit from video instructions... Looking forward to your ideas!...
Uncle Gizmo is offline   Reply With Quote
Old 06-17-2017, 05:31 AM   #3
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 10,395
Thanks: 34
Thanked 785 Times in 702 Posts
The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all
Re: update records on table via form not in order

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over six months and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 06-17-2017, 05:34 AM   #4
murray83
Newly Registered User
 
Join Date: Mar 2017
Posts: 108
Thanks: 13
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: update records on table via form not in order

so the following sql which i have

Code:
DoCmd.RunSQL ("Update main set [ActionedBy] = cmbActionedBy.Value , [EndTime] = Now(), [ActionedID] = txtusername.value where [ID] = txtID.value")
wont cut it you say
murray83 is offline   Reply With Quote
Old 06-18-2017, 09:50 PM   #5
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,497
Thanks: 2
Thanked 320 Times in 316 Posts
Cronk will become famous soon enough
Re: update records on table via form not in order

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
Code:
"Update main set [ActionedBy] = '" & me.cmbActionedBy & "' , [EndTime] = Now(), [ActionedID] = " & ME.txtusername & " where [ID] = " & Me.txtID
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
Cronk is offline   Reply With Quote
Old 06-19-2017, 06:56 AM   #6
murray83
Newly Registered User
 
Join Date: Mar 2017
Posts: 108
Thanks: 13
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: update records on table via form not in order

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

Code:
DoCmd.RunSQL ("Update main set [ActionedBy] = '" & me.cmbActionedBy & "' , [EndTime] = Now(), [ActionedID] = ' & ME.txtusername & ' where [ID] = '" & Me.txtID "')
also do i need to call Me. can i not just put
Code:
txtusername.value
Attached Images
File Type: png huh.png (22.6 KB, 9 views)
murray83 is offline   Reply With Quote
Old 06-19-2017, 07:00 AM   #7
murray83
Newly Registered User
 
Join Date: Mar 2017
Posts: 108
Thanks: 13
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: update records on table via form not in order

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

murray83 is offline   Reply With Quote
Old 06-19-2017, 07:38 AM   #8
Minty
Hacker (at Golf that is)
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 3,677
Thanks: 65
Thanked 980 Times in 956 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: update records on table via form not in order

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
Code:
strVariable =  "Fred " & "Was Here"
would make strVariable's value = "Fred Was Here"
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-21-2017, 02:57 AM   #9
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,497
Thanks: 2
Thanked 320 Times in 316 Posts
Cronk will become famous soon enough
Re: update records on table via form not in order

This bit is wrong
Code:
[ActionedID] = ' & ME.txtusername & ' where
It should be
Code:
[ActionedID] = " & ME.txtusername & " where
Also unless ID is a text field, the single quotes should be dropped

Cronk is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] How to UPDATE table records from a form. Clayhead Modules & VBA 4 01-03-2016 05:11 AM
Form to update records within a table Johnsonsimon General 3 01-09-2014 08:02 AM
order of records in report deviates from order of records in form Absolute_Beginner Reports 3 09-13-2011 07:26 AM
VBA Rx to switch index of local temp table to update Multiple Items form sort order mdlueck Modules & VBA 14 09-02-2011 10:50 AM
Update table field when customers first order is placed? Garindan Modules & VBA 7 03-17-2011 03:54 AM




All times are GMT -8. The time now is 07:36 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World