Edit Multiple Tables from 1 form

[NealeBabb]

I have this database, i use it to keep track of the location each device is at. I want to be able to rename a device at certain locations so i would have to rename the device in tbl_devices, and would have to put todate date in the ReceivedDateFromDepot field in tbl_Location for the old name and then create a new record in tbl_Location with the new device name, the current location and todays date.

With the current code i can do what i need to in tbl_Location but instead of renaming the device it creates a new one with the new device name

Any help wold be appriciated
Thanks

 

[NealeBabb]

Database attached

 

[jdraw]

Why do you need to rename a device? Seems to be a data normalization issue.

See Orange's comments and database in this thread
http://www.accessforums.net/forms/how-swap-records-separate-tables-using-form-24504.html

 

[NealeBabb]

The Devices are all named according to depot, for example
Depot 1 devices might be named
DP1D01, DP1D02, DP1D03 For Depot1 Device 01, 02, 03 etc
And depot 2 devices might be named
DP2D01, DP2D02 etc

So if i take a stock device from the hub and Allocate it to a Depot i want to be able change the name from STOCK1 to DP1D01

 

[NealeBabb]

This is the code i am using at the moment on a form containing all information from the location table
Device ID, Location, SentToDepot, ReceivedFromDepot. (Last two fields both being dates)

Private Sub Command19_Click()
Dim DeviceID As String
Dim NewDeviceID As String
Dim Location As String
DeviceID = Me.[Device ID]
NewDeviceID = Me.NewDeviceID
Location = Me.DepotNumber

Me.ReceivedFromDepot = Date
RunCommand acCmdRecordsGoToNew
Me.[Device ID] = NewDeviceID
Me.SentToDepot = Date
Me.DepotNumber = Location




End Sub

This does what it needs to on the location table but only creates a new Device rather than renaming it. just need a way of changing the DeviceID in the Devices table from the old device it to the new one rather than creating a new record.

 

[NealeBabb]

Ive solved it now Just incase anyone has the same issue this is the code i used

Dim dbDeviceLog As DAO.Database
Dim rstLocation As DAO.Recordset
Dim DeviceID As String
Dim NewDeviceID As String
Dim Location As String

Set dbDeviceLog = CurrentDb
Set rstLocation = dbDeviceLog.OpenRecordset("tbl_Location")




DeviceID = Me.[Device ID]
NewDeviceID = Me.NewDeviceID
Location = Me.DepotNumber
Me.ReceivedFromDepot = Date
DoCmd.RunSQL "UPDATE tbl_Devices SET [Device ID] = NewDeviceID, Comment = Comment & 'Device previously named ' & DeviceID & ', ' WHERE [Device ID] = DeviceID;"

rstLocation.AddNew
rstLocation("DeviceID").Value = NewDeviceID
rstLocation("SentToDepot").Value = Date
rstLocation("DepotNumber").Value = Location
rstLocation.Update