Thank you for your kindness. Since the database contains substantial personal information I need to respect, letâs try it this way first. If this is inadequate, I will remove actual personal data, incorporate substitutions, and send you the entire file.
dbPeoplePosition: Data: = Query: qryResidentPositions
dbPosDef: Data = Table: tblDefPositions
Push Button 1: Mouse button pressed: = Standard.Module1.InsertPosition
tblDefPositions: = id_Position/Text Varchar:2, Title/Text Varchar100
tblPositionHolders = Year/Smallint, id_Position/Text Varchar2, id_Person/smallint
qryResidentPositions =
SELECT âtblDefPositionsâ.âTitleâ, âtblPositionHoldersâ.âYearâ, âtblPositionHoldersâ.âid_Positionâ, âtblPositionHoldersâ.âid_Personâ, âtblPeopleâ.âFIRST_NAMEâ, âtblPeopleâ.âLAST_NAMEâ FROM âtblDefPositionsâ, âtblPositionHoldersâ, âtblPeopleâ WHERE âtblDefPositionsâ.âid_Positionâ = âtblPositionHoldersâ.âid_Positionâ AND âtblPeopleâ.âID_Personâ = âtblPositionHoldersâ.âid_Personâ ORDER BY âtblPositionHoldersâ.âYearâ DESC, âtblPeopleâ.âLAST_NAMEâ ASC
Sub InsertPosition
dim oDoc as object
dim oForm as object
dim oPosDefForm as object
dim oPeopleForm as object
dim oSubForm as object
dim strCombo as string
dim strID as string
dim sSQL, sSQL2 as string
DIM oStatement as object
Dim result as object
dim thisYear as string
dim myVar as string
thisYear = Year(Now())
â Open the Form that has the controls
oDoc = ThisComponent.parent '.parent required when run from form event
oForm = oDoc.formdocuments.getbyname(âfrmResidentsPositionsâ)
oForm.open
Wait 500
â Read the value of the combo box
oPosDefForm = oform.Component.getDrawPage().getForms().getByName(âdbPosDefâ)
strCombo = oPosDefForm.getByName(âcmbPositionsâ).Text
oPeopleForm = oform.Component.getDrawPage().getForms().getByName("dbPeople")
strID = oPeopleForm.getByName("fmtID_Person").Text
oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
sSQL = "SELECT ""tblDefPositions"".""id_Position"" FROM ""tblDefPositions"" WHERE ""tblDefPositions"".""Title"" = '" + strCombo + "'"
result = oStatement.executeQuery(sSQL)
result.next
myVar = result.getstring(1)
oform.close
sSQL2 = "INSERT INTO ""tblPositionHolders"" (""tblPositionHolders"".""Year"", ""tblPositionHolders"".""id_Position"", ""tblPositionHolders"".""id_Person"") VALUES ('" + thisyear + "', '" + myVar + "', '" + strID + "')"
result = oStatement.executeQuery(sSQL2)
oform.open
â oPeopleForm.reload()
end sub
First Row of tblDefPositions before running Macro =
id_Position = Bc
Title = Block Captain
After running the form, moving the combo to 'Christmas, clicking on button:
id_position = Bc
Title = Christmas
I saw that much of the macro code was cut off by the window. Fixed. Also, attached sql text from screenshot
â Open the Form that has the controls
oDoc = ThisComponent.parent '.parent required when run from form event
oForm = oDoc.formdocuments.getbyname(âfrmResidentsPositionsâ)
oForm.open
Wait 500
â Read the value of the combo box
oPosDefForm = oform.Component.getDrawPage().getForms().getByName(âdbPosDefâ)
strCombo = oPosDefForm.getByName(âcmbPositionsâ).Text
oPeopleForm = oform.Component.getDrawPage().getForms().getByName("dbPeople")
strID = oPeopleForm.getByName("fmtID_Person").Text
oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
sSQL = "SELECT ""tblDefPositions"".""id_Position"" FROM ""tblDefPositions"" "
sSQL = sSQL + "WHERE ""tblDefPositions"".""Title"" = '" + strCombo + "'"
result = oStatement.executeQuery(sSQL)
result.next
myVar = result.getstring(1)
oform.close
sSQL2 = "INSERT INTO ""tblPositionHolders"" "
sSQL2 = sSQL2 + " (""tblPositionHolders"".""Year"", ""tblPositionHolders"".""id_Position"","
sSQL2 = sSQL2 + " ""tblPositionHolders"".""id_Person"")"
sSQL2 = sSQL2 + " VALUES ('" + thisyear + "', '" + myVar + "', '" + strID + "')"
result = oStatement.executeQuery(sSQL2)
oform.open
â oPeopleForm.reload()
end sub
sSQL2=INSERT INTO âtblPositionHoldersâ (âtblPositionHoldersâ.âYearâ,âtblPositionHoldersâ.âid_Positionâ,âtblPositionHoldersâ.âid_Personâ) VALUES (â2024â, âBcâ, â-43â)
PDAW.odb (72.4 KB)
I removed sensitive data from the file. The issue persists.