Page 1 of 1

[Solved] Update Base table from Form

PostPosted: Fri Sep 01, 2017 7:01 pm
by kbdelta
I have created some tables in Base and am using the Forms to insert my data. Some of the data in the tables will need to be updated. I was trying to create a form to update the data in the Tables.

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   Expand viewCollapse view
******************************************************************************************
******************************************************************************************
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
******************************************************************************************
******************************************************************************************


I appreciate any help. Thank you.

Re: Update Base table from Form

PostPosted: Sun Sep 03, 2017 11:05 pm
by UnklDonald418
To store a selected Employee_ID in the AssignEmp field of Tbl_Computer doesn't require a macro.
I uploaded a little demonstration that shows how a List box can be used to accomplish that task.
Form_Tbl_Computer allows entry and editing of the data in Tbl_Computer including the AssignEmp column. The form uses a List Box to display the Employee name in the AssignEmp column, but if you look at Tbl_Computer you can see that it is actually storing the Employee_ID as a foreign key. To see the SQL query used to do that look at the Data tab of the List Box Properties dialog for the AssignEmp column.

Re: Update Base table from Form

PostPosted: Wed Sep 06, 2017 4:27 pm
by kbdelta
Thank you UnklDonald418. This works for me and is easier.