Post edited for errors @16:21 12/06/21
Hi 
I own a small business and have been working on a database after hitting some limits with spreadsheets.
Ive made up a set of tables with firebird embedded and am in the process of trying to get the forms functional. A couple of them will need macros to work. So im hoping to start the process of figuring things out and learning macros. Final use will be for my business, single user use as we are only small. But i want to simplify the process of creating accounts etc so my wife can use it.
Im trying to create a macro that will:
- create a new record on tbl.party
- insert the current date into the field DateOrigination
- set a value of 1 into the PartyTypeID field
- copy the value of the PartyID field
- create a new record on tbl.Person
- Insert the PartyID value into the PartyID field
Being new to this ive decided to work with sample data and modify it. It hasn’t worked for this code. I only have a very basic understanding of sql and queries so far, hopefully that will improve over the next few weeks; though im admittedly a bit burned out from learning about table design, forms, subforms, basic sql, reports, etc etc all at once.
Im hoping someone could please take a look at the code and put me on the right tracks to get it working?
Im not sure how wrong it is. Help would be greatly appreciated, getting this working would boost morale a great deal as it would allow me to get the core of the database working as intended. If i can get this figured out, the same structure will also serve for a few other similar macros, so it will kill a few birds with one stone.
REM ***** BASIC *****
Sub AddPartyThenPerson()
REM INSERT RECORDS INTO DATABASE TABLE
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Dim PartyID as integer
Dim PersonID as integer
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext") '< get the database context
DB=Context.getByName("FSDatabase") '<- change database name
Conn=DB.getConnection("","") '<- username & password pair
On Local Error GoTo CloseConn
Stmt=Conn.createStatement()
REM Insert current date into DateOrigination field of tblParty, and Insert PartyType 1 into Party value, and insert and return the PartyID value.
strSQL = "INSERT INTO ""tblParty"" (""DateOrigination"") VALUES (=today());""Party"" (""PartyTypeID"") VALUES (1);Call Identity();"
ResultSet = Statement.executeQuery(strSQL)
If Not IsNull(ResultSet) then
ResultSet.next
PartyID = ResultSet.getString(1)
End If
REM Insert the retrieved PartyID value from tblParty into tblPerson.
strSQL = "INSERT INTO ""tblPerson"" (""PartyID"") VALUES (" & PartyID & ");Call Identity();"
ResultSet = Statement.executeQuery(strSQL)
If Not IsNull(ResultSet) then
ResultSet.next
PersonID = ResultSet.getString(1)
End If
Stmt.executeUpdate(strSQL) '<- update the inserted data
Conn.close("") '<- close the db connection
End Sub
Here is a copy of the ODB file
(it is stripped down to the tables related to the question)
Edit2:
Here is an updated file with a demo form, to give you an idea of why i need the code and how the tables work together
FSDatabase.odb
