Change field Value using update query

[shoeb]

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

 

[Jeanette Cunningham]

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