[Gambas-user] Using DataBrowser for a table which

[John Rose]

My SQLite3 database contains country & centre tables. The centre table's
primary key is the _id column. The centre table has a country_id column
which is a Foreign Key to _id in the country table. The centre table
contains a column named centre with values such as London, Paris,
Strasbourg. The country table contains a column named country with
values such as Great Britain, France. The Create Table SQL commands are:
Create Table centre (_id Integer Primary Key AutoIncrement,
                     centre Text Not Null,
                     country_id Integer,
                     Foreign Key (country_id) References country (_id)
                                              On Delete Cascade
                                              On Update Cascade);
Create Table country (_id Integer Primary Key AutoIncrement,
                      country Text Not Null);

My FMain form contains a button (ButtonCentreMaintenance) which on
clicking it calls (see code below) a form named FCentreMaintenance which
is used to add/change/delete centres. The FCentreMaintenance form
contains a DataBrowser named DataBrowserCentre which contains a
DataSource named DataSourceCentre.

Public Sub ButtonCentreMaintenance_Click()
  Dim hForm As FCentreMaintenance
  hform = New FCentreMaintenance
  hForm.DataSourceCentre.Connection = MDatabase.DatabaseConnection
  hForm.DataSourceCentre.Table = "centre"
  hForm.DataSourceCentre.Filter = "country_id = " & CountryId
  hForm.DataSourceCentre.Update()
  hForm.Refresh()
  hForm.DataBrowserCentre.Columns = ["centre"]
  hForm.DataBrowserCentre.Labels = ["Centre"]
  hForm.DataBrowserCentre.Editable = True
  hForm.DataBrowserCentre.Update()
  hForm.DataBrowserCentre.Refresh()
  hForm.Show() 
  hForm.DataBrowserCentre.SetFocus()
  Me.Hide()
End

The problem I have is that when I use the 'New' icon in
DataBrowserCentre, it allows me to key in a new centre but when I try to
save it (using the Save icon) it clears it on the form. The cause of
this is the setting of the Filter in the code above (i.e. it only
displays centre for centre rows which are in the particular country
selected by the user when the form is called). What I need to be able to
do is to have the save set the country_id column to the correct value
(rather than 0 as it does now when saving). Any ideas on how to do this?
The only way that I've thought of doing this is to have
DataBrowserCentre also display the column country_id which the user
would set to the correct value when adding a centre.

If you want to run this yourself, I've attached the project & the
database to this message. I used Germany & tried to add Berlin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: JewishHeritage.tar.gz
Type: application/x-compressed-tar
Size: 39599 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20120819/217ee61f/attachment.bin>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: JewishHeritage.db
Type: application/x-sqlite3
Size: 232448 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20120819/217ee61f/attachment-0001.bin>