I have two tables:
Tbl_Computer
fields: Computer_ID, Make, Model, Type, DatePurchased, AssignEmp
Tbl_Employee
fields: Employee_ID, First_Name, Last_Name, Department
Inserting the data is no problem but what I want to do is create a form that is for assigning a computer to an employee. So the Employee_ID will be inserted in the Tbl_Computer in the AssignEmp column.
I created a form with two fields Computer_ID (dropdown list) and Employee_ID (dropdown list) with an "Assign" button. I assigned a macro to run when the "Assign" button is pressed. My problem is how to pass the fields in the form to the macro and then what is the syntax to use those values in the UPDATE statement. The "Assign" button macro is as follows:
Code: Select all
******************************************************************************************
******************************************************************************************
Sub Assign_Computer
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("Terminals")
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL="UPDATE ""Tbl_Computer"" SET ""AssignedEmp"" =(Employee) WHERE ""Computer_ID"" = (Computer)"
Stmt.executeUpdate(strSQL)
Conn.close()
End Sub
******************************************************************************************
******************************************************************************************