In case you aren't clear on how to repair your form so that the macro will work:
Open "frm_Main" in the Edit/Design mode.
Right click on the empty area to the right of the current column headings and choose Insert Column>Formatted Field to add the column to the table.
Right click on the new column heading and select Column to open the Properties: Formatted Field dialog.
On the Data tab select ID from the list of the available fields shown on the Data field.
On the General tab set both Name and Label to "ID".
Right click on the ID column heading and select Hide Column.
Save and Close the form.
I noticed on frm_NewPt, List Box 1 is not working.
In the Edit/Design mode right click on List Box 1 and select Column to open the Properties: List Box dialog.
On the Data tab I see the the Data Field is set to "Rescue Analgesia".
For Type of list contents select Sql
Click on the ellipsis to the right of List Content to open a query design dialog.
I'm assuming you want to use tblRescue so Add that table to the query.
Select Rescue Therapy for the first column, and rescueKEY for the second column. It is important that they be in that order for the list box to work properly.
Under the Rescue Theapy column select Sort > Ascending.
Close and Save the query, and notice the SQL command for that query now appears as the List Content property.
You might also want to change the column Label on the General tab of the dialog.
There is one other issued that you should address while in the Design mode.
Select the Form Navigator, (The 4th icon from the left along the bottom of the window) to open the Form Navigator dialog.
Right click on SubForm and select Properties to open the Form Properties dialog.
On the Data tab change the Navigation Bar setting from Yes to Parent Form. That way when you select something on the SubForm grid you won't high jack the MainForm navigation bar.
Close and Save the Form.
Now the list box should let you select from the available Rescue Therapy items in tblRescue
You can learn the basics of SQL from one of online tutorials. Just do an internet search for "SQL Tutorial".
Base is just a front end for a HSQLDB database engine providing some GUI capabilities. While an Embedded database using Base is a convenient learning platform it suffers from some issues making it unsuitable for long term use.
[Wizard] Create a new 'split' HSQL 2.x database
You can copy your tables, queries, forms and reports from an Embedded database into a split database using cut and paste.
If you have privacy concerns with your database, HSQLDB does offer data encryption but I've never used it and don't know about its reliability.