update only current record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running an update query that updates a field in my table from a
calculation of two other fields. The problem is that I only want to update
the currently selected record on my form when invoked, not all records on my
table. How can I make it so that I only update the currently selected
record? Any help is greatly appreciated. Thanks.
 
Include the primary key in the update query and on your form. It can be not
visible on your form. Put the following expression in the criteria of the PK
in your query:
Forms!NameOfYourForm!NameOfPKTextbox

Use the Click event of a control in the record on your form to run the
Update query.
 
I see what you're saying, but I can't seem to get it to work right. when I
include it in my update query, I end up getting the message that I'm about to
update 0 rows. no errors or anything though. can you maybe give me an
example. "hiddenbinder" is the name of my hidden text box whose control
source is tblMain.ID. Heres how I'm using it:
Private Sub saveme_Click()
Dim SQL As String

SQL = "UPDATE tblMachine INNER JOIN tblMain ON tblMachine.ID =
tblMain.tblMachine_ID SET " & _
"tblMain.Average = [Jobs]/[Hours] " & _
"WHERE (((tblMachine.ID)= [Forms]![frmMain]![hiddenbinder]));"

DoCmd.RunSQL SQL
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub
 
Assuming that tblMachine.ID is a number field then try the following.

"UPDATE tblMachine INNER JOIN tblMain ON tblMachine.ID =
tblMain.tblMachine_ID SET " & _
"tblMain.Average = [Jobs]/[Hours] " & _
"WHERE tblMachine.ID = " & [Forms]![frmMain]![hiddenbinder]

If tblMachine.Id is a text field then try
"UPDATE tblMachine INNER JOIN tblMain ON tblMachine.ID =
tblMain.tblMachine_ID SET " & _
"tblMain.Average = [Jobs]/[Hours] " & _
"WHERE tblMachine.ID = """ & [Forms]![frmMain]![hiddenbinder] & """"

The idea is to get the value of [Forms]![frmMain]![hiddenbinder] and add it into
the query. I'm not sure what the two lines after your RunSQL statement are
supposed to accomplish. So I won't advise you on whether they are needed or not.
I see what you're saying, but I can't seem to get it to work right. when I
include it in my update query, I end up getting the message that I'm about to
update 0 rows. no errors or anything though. can you maybe give me an
example. "hiddenbinder" is the name of my hidden text box whose control
source is tblMain.ID. Heres how I'm using it:
Private Sub saveme_Click()
Dim SQL As String

SQL = "UPDATE tblMachine INNER JOIN tblMain ON tblMachine.ID =
tblMain.tblMachine_ID SET " & _
"tblMain.Average = [Jobs]/[Hours] " & _
"WHERE (((tblMachine.ID)= [Forms]![frmMain]![hiddenbinder]));"

DoCmd.RunSQL SQL
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub

PC Datasheet said:
Include the primary key in the update query and on your form. It can be not
visible on your form. Put the following expression in the criteria of the PK
in your query:
Forms!NameOfYourForm!NameOfPKTextbox

Use the Click event of a control in the record on your form to run the
Update query.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...
Click to expand...
 
Back
Top