strSQL = “Select max (”“PartyID”") from ““tblPerson””"
ResultSet = Stmt.executeQuery(strSQL)
PartyID = ResultSet.getInt(1)
Right now this copies the highest value of PartyID directly from tblPerson (source table), to be insterted in a new PartyAddress record later in the macro.
I want this part to instead copy PartyID from the currently selected record on a form, (main form table control). So that i can insert it into a new PartyAddress record later in the macro.
In this case the form name is ‘NewPerson’
the mainform is ‘mainPerson’
the control where the record is selected is ‘tbl.PersonControl’
PartyID is a PK on the table
For context this is the macro im attempting:
Sub AddNewPartyAddress() REM INSERT RECORDS INTO DATABASE TABLE Dim Context Dim DB Dim Conn Dim Stmt Dim Result Dim strSQL As String Dim AddressID as integer Dim PartyID 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() 'create a new record on tblAddress 'probably a better way to create a new record without entering an empty value; find better method strSQL = "INSERT INTO ""tblAddress"" (""Line1"") VALUES ('')" Stmt.executeUpdate(strSQL) 'get the value of AddressID from the new record on tblAddress strSQL = "Select max(""AddressID"") from ""tblAddress""" ResultSet = Stmt.executeQuery(strSQL) ResultSet.next AddressID = ResultSet.getInt(1) 'right now this takes the highest value of PartyID directly from tblPerson (source table). PartyID is the primary key of tblPerson. 'I want this part to instead take the PartyID from the currently selected record on a form strSQL = "Select max (""PartyID"") from ""tblPerson""" 'set filter here for current record on form ResultSet = Stmt.executeQuery(strSQL) ResultSet.next PartyID = ResultSet.getInt(1) 'insert copied values into the new tblPartyAddress record & set date in DateStart strSQL = "INSERT INTO ""tblPartyAddress"" (""DateStart"", ""PartyID"", ""AddressID"") VALUES (CURRENT_DATE, " & PartyID & ", " & AddressID & ")" Stmt.executeUpdate(strSQL) CloseConn: Conn.close("") '<- close the db connection End Sub
Once assigned to a ‘new address’ button, it is intended to:
create a new address record on
copy that records PK (AddressID)
copy the current person PK (partyID)
insert these values to a new record on
tblPartyAddress and set DateStart to
Right now it does this with the exception it applies the highest PartyID available.