How to copy a listbox record to a related table, when original record not yet saved

Getting Started
1

I haver an interesting problem. I have a one to meany relationship between two sqlite database tables.
I have a screen where I add data to the master record, but this also captures data and copies it to a listbox.
I would like to save both the listbox data (details) and the master record data on closing the window. The problem I have is that if I save the master data first, the listbox data seems to disappear. If I save the listbox data first, I have no ID from the master record needed to provide the relationship between master record and details.
Is there a better way to do this?
Any help would be appreciated.

2

save the master first, and the listbox datas shouldn’t disapear before you save them.
you must save everything before closing the window that holds all the datas.

3

Merci Jean-Yves
Unfortunately, the listbox appears to disappear. But I will try again in different parts of the code.

What I have done is used the lost focus event on one field to save the master record, then reload the record and display the new ID. I can then use this value to save all the list. items in the listbox, making sure that the master ID is copied to the appropriate field in the dependent table. Something like this…

If IDField.Text = “” Then // this is a new recored and has yet to be saved to table
Var MRN As Text = MRNField.Value.ToText
Try
myDatabase.ExecuteSQL(“INSERT INTO HHSx (MRN) VALUES (‘MRN’)”) // just store one value
// the database table has auto increment for ID
Var rowID As Int64
rowID = myDatabase.LastRowID
IDField.Text = myDatabase.LastRowID.ToText
loadRecord(Str(rowID))
Catch error As DatabaseException
MsgBox("Insert failed. Error: " + error.Message)
End Try
End If

4

I would look closely at loadRecord(Str(rowID)). Does that reload the listbox? I would also question whether that call is necessary at this stage in the process.

5

Hi Tim
Thanks for the advice. The loadRecord(Str(rowID)) gets the master record reloaded. This then makes the ID for the master record available store into the dependent record so that it is linked to the master record and can be displayed.
I hope that clarifies. In any case, I have it working now. Master record as a window with a listbox of dependent records displayed on the same window, able to be displayed as they are added and saved at the same time as the master record is saved to its table.