Corrupted table

[whatsinaname]

Problem: I have two tables. I have a Macro do an insert into Table 2 and it also writes into Table 1.

The first has a title and a title_id. The second table has the title_id and a person_id.
I have a form with a combo box that lists the titles from the first table, a list that displays the titles for each person (table 1 and table 2 are linked here), and a button.
When I click a button on the form, I execute a Macro that reads the title in the combo box, looks up the title id, and inserts a new record in Table 2. This works.
If I select a different title in the combo box and click to insert it in table 2, I find that the first record in Table 1 has been over-written by the originally selected title in the combo box. Table 1 is not referenced in the insert command.
Version 7.3.2.2
Help, please.

[skyandrews]

I would like to help but need more info or a secure copy of your odb. To upload, just edit your question and press the upload icon. You say the macro writes into Table 1… what data? If the combobox is based on Table 1 then there is no data in the combobox that should be “re -written” to Table 1, thus the overwriting problem. I will start a demo assuming “title” means something like “Supervisor”, etc… and then “Person” is the name of someone. Also if inserting an integer into Table 2 (title_ID), then the combobox should be a listbox instead, with “title” from tbl1 as sql first column, and “title_ID” as second column. The bound column = 1 and the listbox data field property should be blank.

[whatsinaname]

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.

[skyandrews]

Thank you…! Since there is quite a bit more here than your question indicated, it may take a little time to get through. In general, what is the main form’s purpose and workflow? I will post and update this post with suggestions and solutions as I go, if that’s acceptable.

1. When using boolean check boxes based on a table with many yes/no boolean columns, the table columns’ type should instead be integer, not yes/no boolean type. I had major problems with tables using several yes/no boolean columns until I converted them all to integer type. Before changing the column type from yes/no boolean, be sure to first change the data in those columns from True or Yes, or False, or No, to an appropriate integer. “True” or “Yes”=1; “False” or “No”=0; triplestate = -1) All checkbox data value properties on forms must also be changed to the appropriate integer, not “yes” or “no”! The only thing that is different is the appearance of integers in the table instead of the boolean chkboxes. All other checkbox appearances remain unaffected by this important change! Instead of all those columns I will be using a one to many relationship between tblPeople and tblPositionHolders.
2. tblPositionHolders does not have a unique primary key column, recommended.
3. Combo box “cmbPositions” needs to be a list box with bound column = 1, data field property set blank, data list content set to type sql - select title, id_Position from tblDefPositions. The current combobox has Title as data field and query result resulting in the macro trying to insert Title, instead of id_Position, into the recipient table.

[whatsinaname]

Thank you again.

1. Yes, there is more, I only described the section that I was having problems with. I felt that to describe the rest would only clutter the presentation.
2. The database has the setup for removing the yes/no boxes from tblPeople. tblPositionHolders contains the same information with id_Position being the link to tblDefPositions (a table with the same categories as the yes/no headers) and id_Person linking to the people. I didn’t want to remove what is until I got the new format working, and this is where the problem is.
3. I have been appointed to the database position of our HOA. People volunteer for positions (tblPositionHolders) and pay the voluntary Dues (tblDues). Each person is linked to tblProperty, their residence.
4. Each residence is assigned to a sections (tblSections) that has a block captain. Not yet sure if I want to try to incorporate tblBlockCaptains into tblPositionHolders.
5. frmResidentsPositions is intended to select a person from the navigation, a task for which they have volunteered from the combo box, push the button to make the assignment, and see the result in the table.
6. frmPeople is incomplete. frmDues supports the logging of payments. Those who contribute are members for the year. frmResidents supports the printing of reports in various formats.

[skyandrews]

Yes! Thank you! This makes the function of your db much clearer… working on it!

[whatsinaname]

Thank you very much for your kindness and your effort.

[skyandrews]

Quickly fixed (tested) your original odb without name changes so should seemlessly work into your system, with two exceptions. First, the combobox had to be changed to a listbox with bound column=1, the data list content property set to type sql - SELECT “Title”, “id_Positions” FROM “tblDefPositions”, and also the data field set to nothing. Second, the Mainform structure was simplified (see capture). The macro was modified to reflect the changes.
updPDAW.odb (72.7 KB)

[whatsinaname]

Once again, “Thank You” is clearly inadequate. I spent a couple of days, periodically pounding my fist against the table. You saw the solution that I could not see. Thanks again.