Copy a PK value from current record (macro)

[tally]

strSQL = “Select max (”“PartyID”") from ““tblPerson””"
ResultSet = Stmt.executeQuery(strSQL)
ResultSet.next
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
  tblPerson

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
  tblAddress

• copy that records PK (AddressID)

• copy the current person PK (partyID)

• insert these values to a new record on
  tblPartyAddress and set DateStart to
  todays date

Right now it does this with the exception it applies the highest PartyID available.

[parsely]

your question is: how to get a value from a form? You may find this post helpful link to previous question
The MRI extension (extension manager) might be helpful to wander around the objects in your form, to inspect the controls in your specific case.

[tally]

Thank you That gives me something to go off, ill have a play around with the example and see where it leads. cheers

[gkick]

Hi,

unless I miss something in your requirements that’s a lot of code to replace native inbuild functionality.

Assuming your tables are linked, a main sub form will do all of this for you. See example.

people.odb

[tally]

Thank you for this response. I have made this change for my final build. Thank you both.