A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Change field Value using update query



 
 
Thread Tools Display Modes
  #1  
Old July 6th, 2008, 12:12 PM posted to microsoft.public.access.queries
shoeb[_2_]
external usenet poster
 
Posts: 1
Default 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  
Old July 7th, 2008, 05:54 AM posted to microsoft.public.access.queries
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.