How to get a form control to display the value of a field in a table and then save the value of another field of the same table ?
Switch the form to Design Mode.
In Design Mode create a new control
- If not already done, activate Design Mode by clicking on the appropriate icon in the Form Design toolbar.
- In the Form Design toolbar, click on the List Control icon, then draw a zone on your form to indicate where the control should be placed.
The control wizard should start up automatically and guide you through the process. For the remaining details, see below.
Use Design Mode to replace an existing form control (e.g. a text box)
- Activate Form Design Mode via the Form Design toolbar.
- Convert an existing control into a list box by right mouse button clicking on the control and choosing Replace with > Listbox from the context menu.
As an example, take a table named
Client containing fields
ClientName) and associated primary key (
ClientId). What we want to achieve is to have the form display the Client Name in a Listbox, but actually use the corresponding ClientID in the underlying table to write that value elsewhere. It is therefore necessary to declare the field where this data is going to be saved, associate a SQL query to the listbox and setup the field of the table to be used for display as well as that for saving the data.
In order to do this, start by displaying the listbox properties of the listbox control (Form Design toolbar, select the control with a mouse button click and choose Properties), then choose the Data tab of the Properties dialog.
Under Data enter the name of the table to be used for saving the data.
Choose Sql as List content type.
Under List Content, type
SELECT ClientName, ClientId FROM Client or click on the button representing follow through dots to use the query creation wizard.
In the above query,
ClientName is the first field returned, and has an indexation number of 0, and
ClientId is the second field returned with an indexation number of 1.
The default list displayed in a Listbox control is always based on the first indexed field, i.e. indexation number 0, so in our present example, with the values from
Since what we actually want is the value of ClientId (with an indexation number of 1) to be recorded, it is necessary to enter the value 1 under Linked field.