Change field Value using update query
shoeb,
to run an update query using vba, set it up something like this:
Dim strSQL as String
strSQL = "UPDATE Device " _
& "INNER JOIN tblassignment " _
& "ON Device.d_ID =tblassignment.a_deviceId " _
& "SET Device.d_statusId = 2 " _
& "WHERE tblassignment.a_deviceId = " & Me.cmbdevice & ""
CurrentDb.Execute strSQL, dbFailOnError
After the query runs, you need to requery the form to show the new value in
the form.
Me.Requery
Jeanette Cunningham -- Melbourne Victoria Australia
"shoeb" wrote in message
...
Hi,
I am stuck into a situation where i need to update a value in a field
of a table.
The situation is like this.
I have a table "Device" and "tblassignment" with one to many
relationship. The primary key d_ID of tbl.device is linked to
a_deviceID of tbl.deviceassignment.
There is form called "frmassignment" for table "tblassignment". There
is one combo box(cmbdevice) which shows the device.Serialnum and
device.modelnum whose value is stored in tblassignment.a_deviceID.
What i need is when an item is selected in this combo box and form
completely filled and when i click the ADD button field
device.d_status should change the value to 2.
I have created an update query for this and am calling from the vba
module screen for event onclick() for the button
Code:
stDocName = "qrydevicenotfree"
DoCmd.OpenQuery stDocName, acNormal, acEdit
the update query "qrydevicenotfree" is :
Code:
UPDATE Device INNER JOIN tblassignment ON Device.d_ID =
tblassignment.a_deviceId SET Device.d_statusId = 2
WHERE (((tblassignment.a_deviceId)=[form]![frmassignment].
[cmbdevice]));
Need a desperate help for this
regards
shoeb
Reply With Quote
|