If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query for Current Record Only
I have an update query which I want to run when a record is given focus in a
form. But I do not know how to reference this either in the event property or the query itself so that the query does not update all the records in the target table. Can anyone else? |
#2
|
|||
|
|||
Update Query for Current Record Only
Set criteria in the query to something in the form:
criteria: this field = forms!form.thiscontrolontheform Bonnie http://www.dataplus-svc.com Adrian wrote: I have an update query which I want to run when a record is given focus in a form. But I do not know how to reference this either in the event property or the query itself so that the query does not update all the records in the target table. Can anyone else? -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Update Query for Current Record Only
On Tue, 24 Jun 2008 13:40:00 -0700, Adrian wrote:
I have an update query which I want to run when a record is given focus in a form. But I do not know how to reference this either in the event property or the query itself so that the query does not update all the records in the target table. Can anyone else? Not much to go on here. What is the name of the field you wish to update? What is it's datatype? Where do you get the value you wish to update the field to? You don't need a separate query. Each record should have it's own Prime Key unique record number. Let's assume it's named [RecordID], and that it is a Number datatype. Code an event on the form (perhaps the form's current event if you wish to do this as you navigate to each record): Dim strSQL as String strSQL = "Update MyTable Set MyTable.[FieldName] = SomeValue Where MyTable.[RecordID] = " & Me.[RecordID] CurrentDb.Execute strSQL, dbFailOnError If you get an error that Access can't find Execute, make sure you have a reference set to the Microsoft DAO 3.6 Object Library. Open any VBA code window. Click on Tools + References. If you don't see the DAO library checked up near the top of the list, navigate down the list until you find it. Place a check in it's check box. Close the list and save. You'll have to adapt the above syntax to fields of different datatypes. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#4
|
|||
|
|||
Update Query for Current Record Only
Sorry for the lack of detail.
The field I want to update will be 'Status' in table 'Documents', which is a text field. The value I want to update this field to is "Being Edited", but I might change my approach on this where the value is determined by weather a tick box (called 'Edit' in the same table) is checked or not. How would this fit in to the code you provided below? "fredg" wrote: On Tue, 24 Jun 2008 13:40:00 -0700, Adrian wrote: I have an update query which I want to run when a record is given focus in a form. But I do not know how to reference this either in the event property or the query itself so that the query does not update all the records in the target table. Can anyone else? Not much to go on here. What is the name of the field you wish to update? What is it's datatype? Where do you get the value you wish to update the field to? You don't need a separate query. Each record should have it's own Prime Key unique record number. Let's assume it's named [RecordID], and that it is a Number datatype. Code an event on the form (perhaps the form's current event if you wish to do this as you navigate to each record): Dim strSQL as String strSQL = "Update MyTable Set MyTable.[FieldName] = SomeValue Where MyTable.[RecordID] = " & Me.[RecordID] CurrentDb.Execute strSQL, dbFailOnError If you get an error that Access can't find Execute, make sure you have a reference set to the Microsoft DAO 3.6 Object Library. Open any VBA code window. Click on Tools + References. If you don't see the DAO library checked up near the top of the list, navigate down the list until you find it. Place a check in it's check box. Close the list and save. You'll have to adapt the above syntax to fields of different datatypes. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#5
|
|||
|
|||
Update Query for Current Record Only
Since you are sitting on the record and you are actually changing
(clicking) the "edit" tick box all you need to do is: In the afterupdate event of the tick box if me.tickboxname = true then me.Status = "Being Edited" else do something else if you want endif You need to think about WHEN are you going to change the status back to something else otherwise the status will remain "being edited" after you finish the changes. Ron |
#6
|
|||
|
|||
Update Query for Current Record Only
Thanks, What would you do for the following 2 scenarios:- a) If on selecting a record (in a form called "Documents" so the current record has the focus) you wanted to run a select query called "History" for example and display the result in a subform/report. So that as you moved through the record in form "Documents" the results subform/report would change/refresh as you selected a new record? b) Expanding on the answer you gave me below, if i wanted to update the field "status" purely by selecting a record (in a form called "Documents" so the current record has the focus) without ticking the tick box what would I do. ----------------------------------------------------------------------------------------------- "Ron2006" wrote: Since you are sitting on the record and you are actually changing (clicking) the "edit" tick box all you need to do is: In the afterupdate event of the tick box if me.tickboxname = true then me.Status = "Being Edited" else do something else if you want endif You need to think about WHEN are you going to change the status back to something else otherwise the status will remain "being edited" after you finish the changes. Ron |
#7
|
|||
|
|||
Update Query for Current Record Only
For Part A:
Possibility One: If the main form is bound to the table Documents then when you add the subform the wizard will ask for the linking field between the subform and the main form and everything will be automatic. Possibility Two: If the main form is not bound to the table Documents but contains a subform listing the documents then create a txtbox on the main form (visible = false) In the oncurrent event of that subform move the matching key field to that field. manually enter into the history subform in the parent/master - Child fields the two matching fields. For Part B: put the code I mentioned in the first post in the OnCurrent event of the form. If you do this then I think you will have a problem with every record saying "Being Edited" simply because you have looked at the record (You need some way of changeing the status back to whatever is the default status. This is particularly true if you try to use the oncurrent event.) Ron |
#8
|
|||
|
|||
Update Query for Current Record Only
Thanks Ron,
Last question, is there anyway with for Part B to limit the code so that it will only updated the current selected record, I do plan to put something into the event property to change the value back afterwards based on another set of criteria. I just don't want to update every record in the table, just the current one. Thanks for your help with Part A, ---------------------------------------------------------------------------------------------- "Ron2006" wrote: For Part A: Possibility One: If the main form is bound to the table Documents then when you add the subform the wizard will ask for the linking field between the subform and the main form and everything will be automatic. Possibility Two: If the main form is not bound to the table Documents but contains a subform listing the documents then create a txtbox on the main form (visible = false) In the oncurrent event of that subform move the matching key field to that field. manually enter into the history subform in the parent/master - Child fields the two matching fields. For Part B: put the code I mentioned in the first post in the OnCurrent event of the form. If you do this then I think you will have a problem with every record saying "Being Edited" simply because you have looked at the record (You need some way of changeing the status back to whatever is the default status. This is particularly true if you try to use the oncurrent event.) Ron |
#9
|
|||
|
|||
Update Query for Current Record Only
An update of the record you are sitting on in the OnCurrent ONLY
occurs to the record you are sitting on AND to every record you sit on as you go through the records. Remember it will occur on every record you are sitting on unless you limit the code somehow. to change the field back to not "Being Edited" you will have to put the code in the afterupdate event of any and every field that is related to the logic of putting it back or in the before update event of the "Form" not a particular field. While sitting on a record, you can do anything you want to the data of that record. You just have to determine what is that best event to use to trigger the execution of that code. Ron |
Thread Tools | |
Display Modes | |
|
|