There are multiple issues here.
1. Customer details.
For the customer details, the best practice would be to store only the primary key ID in the child table, in this case Customer ID
. You have a form and subform here, which is great, you're a lot of the way there. To finish you will need to create a CustomerID
field in the MainForm
, make this the link with the SubForm
and then insert a macro to make it reload on changes:
i) create a new list box CustomerID
field on the MainForm
-- this must not be on the SubForm
because the recordset is different. To see which form is hosting the control, use the Form Navigator
(
) For useability, select the option Dropdown list
in the control box for the list box.
ii) make the data source the field CustomerID
field, which on the MainForm
will be from table All Bookings New
iii) Make the list source for the List Box
a query with the first column however you want the name to appear on your list, for example CONCAT(CONCAT("LastName", ', '), "FirstName")
, and the second column CustomerID
. Thus: SELECT CONCAT(CONCAT("LastName", ', '), "FirstName") AS "nm", "CustomerID" FROM Customers ORDER BY "nm" ASC
iv) Make the Bound Column
in the control dialog 1
meaning it will update the data set with the value in the second column, CustomerID
(zero being the first column). Now, when you return to the row, it will display the name in the list box.
This will not update LastName
etc in the child table Bookings
, nor is the best practice to do that. Instead, when you want to retrieve, you will JOIN
the tables together on the CustomerID
.
To make the form work the way you want, next fix the link the SubForm
in a manner similar to the JOIN
statement, which you already have sort of done. The relationship must be on the CustomerID
field only, not also the name fields, as those are not going to be updated on both tables. In addition, the Control
for the LastName
etc on the SubForm
must be a Text Box
or else it will not display anything, because there is no list. Right mouse click and select Change To
in edit mode.
When you do these things, the List Box
will select a CustomerID
somewhat invisibly and will display a name. The other Text Box
controls will browse to the correct row on the Customer
table SubForm
There is one last thing for this operation. When you change the ListBox
it will not immediately recalculate the SubForm
link. If you really, really want it to work this way, you will need to use a Macro
and Reload()
the form, which also will require saving the change and browsing back to the same row after Reload
. That is, in the Changed
event on the List Box
control you would have a macro where CustomerListID
is the new list:
Sub ReloadMainForm
FormContainer = ThisComponent.DrawPage.Forms
MainForm = FormContainer.GetByName ...
(more)
the problem is you want to use a
FirstName
control serve as the link to a subform with customer information. Technically possible if you move theFirstName
control to theMainForm
and then change theSubForm
properties. However, you can't really identify your customers uniquely just by first name.try instead using a
ListBox
on theMainForm
that binds on theCustomerID
but that displays the text from the name columns usingCONCAT("LastName", "FirstName")
.The use of a
ListBox
on aMainForm
to anchor and change the contents ofSubForm
also may require strategic use ofreload
for theSubForm
, whenever the name in theListBox
changes. That is, it would require use of macros.I have done forms that use functionality like this, but it always winds up taking much more
macro
coding than anticipated.