Issues with forms and autofill in Base

edit

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 ...