[Solved] Update Base table from Form

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
kbdelta
Posts: 2
Joined: Fri Sep 01, 2017 6:33 pm

[Solved] Update Base table from Form

Post 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

******************************************************************************************
******************************************************************************************
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.
Last edited by Hagar Delest on Wed Sep 06, 2017 5:43 pm, edited 2 times in total.
Reason: tagged [Solved].
OpenOffice 4.1.3
Windows 10 Pro
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Update Base table from Form

Post 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.
Attachments
Demo12ListBox.odb
(12.53 KiB) Downloaded 239 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
kbdelta
Posts: 2
Joined: Fri Sep 01, 2017 6:33 pm

Re: Update Base table from Form

Post by kbdelta »

Thank you UnklDonald418. This works for me and is easier.
OpenOffice 4.1.3
Windows 10 Pro
Post Reply