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
|
|||
|
|||
Change field Value using update query
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 |
#2
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|