Updating another record table with command button

[erlan501]

Greetings.. I'm new in access. I had a little problem and I can't solve it.



I had a form that have a command button to add a record, but I need it to change a value on another record from another table. So when I click the add record button not only new record created but it also change the value on another table.

In my case when I add a new record from that form I also want to change another record value from default value "available" to become "Unavailable" automatically.

[maximus]

Well I need a proper understanding of your problem. Can you give me more details about the relationships of your tables and the fields involved. Perhaps you can also give a small example.

[Evgeny]

can you post a sample of your db to look at. Regards

[erlan501]

In example :

I had a table "Link Activation" (ID, Activation date, Port, Terminal Name). The "Port" field related to a table "Terminal Port List"(ID, Port, Status==> Combo box with value="available" and "unavailable").

And I made a form from this "Link Activation" table and add a command button "activate the link" in the form.

What I want is when I hit the command button "activate the link" , the "status" field from Terminal Port List is change, from "available" to become "unavailable".

[maximus]

In example :

I had a table "Link Activation" (ID, Activation date, Port, Terminal Name). The "Port" field related to a table "Terminal Port List"(ID, Port, Status==> Combo box with value="available" and "unavailable").

And I made a form from this "Link Activation" table and add a command button "activate the link" in the form.

What I want is when I hit the command button "activate the link" , the "status" field from Terminal Port List is change, from "available" to become "unavailable".

I assume that the following:
1) When you select a Port on the form record Source Link Activation the Status for the Port in the Terminal Port List will become unavailable form available.

I have tried to replicate the condition:
I have two tables and In Terminal Port List I have the names of the Ports and the status is available.

On the Form I have a Button Names update status with the following Code:
Private Sub Command8_Click()
On Error GoTo Err_Command8_Click
Dim IntRetVal As Integer
Dim strSQL As String
IntRetVal = SysCmd(acSysCmdSetStatus, "Running Query Now")
strSQL = "UPDATE Terminal_Port SET Status ='unavailable' Where ID=" & Me.PortID
CurrentDb.Execute strSQL
IntRetVal = SysCmd(acSysCmdSetStatus, " ")

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

Here in the above Code I have used an update query to update the statues of te port.


You can use RecordSet. Update Status 1 uses RecordSets

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click
Dim strSQL As String
strSQL = "Select * From Terminal_Port Where ID=" & Me.PortID
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF And rs.BOF Then
Exit Sub
Else
rs.Edit
End If
rs!Status = "unavailable"
rs.Update
rs.Close
Set rs = Nothing
Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

Both of the does the same thing.

Attaching a sample mdb for your reference.

Mark the thread solved if this solves your problem.


How to use the sample A startup form will open select a Port from the port combo and Click any of the update buttons see the effect in the tables

[erlan501]

I still can't make it work..I post the file, could someone please help me to make it work??

I want to change the value in the Status field in the Terminal Port List table to become unavailable when I hit the Add Reocrd button.