Ask Your Question

Issues with forms and autofill in Base [closed]

asked 2015-04-09 11:53:27 +0100

MyMaids gravatar image

updated 2016-09-05 16:44:01 +0100

David gravatar image

Please help It was suggested to me over on reddit/r/libre to post this here as you guys are wonderful and might be able to help :p

Hi all, I have been trying to get this sorted for weeks now. Crunch time is upon me. If anyone is willing to help me complete this project that would be awesome. I know most of you help each other just for the enjoyment of it but if someone wanted a few bucks to help me get this done i wouldn't mind, just need to get it finished. I have heard great sories about how good the libre community is and its why ive switched from openoffice and come here for help rather than there. Any help is welcome even if its to tell me this simply isnt possible.

(drop box link to database)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-11 19:12:12.005558


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 the FirstName control to the MainForm and then change the SubForm properties. However, you can't really identify your customers uniquely just by first name.

doug gravatar imagedoug ( 2016-09-05 18:35:21 +0100 )edit

try instead using a ListBox on the MainForm that binds on the CustomerID but that displays the text from the name columns using CONCAT("LastName", "FirstName").

doug gravatar imagedoug ( 2016-09-05 18:37:13 +0100 )edit

The use of a ListBox on a MainForm to anchor and change the contents of SubForm also may require strategic use of reload for the SubForm, whenever the name in the ListBox changes. That is, it would require use of macros.

doug gravatar imagedoug ( 2016-09-05 18:39:38 +0100 )edit

I have done forms that use functionality like this, but it always winds up taking much more macro coding than anticipated.

doug gravatar imagedoug ( 2016-09-05 18:41:52 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2015-04-10 05:03:00 +0100

doug gravatar image

updated 2015-04-10 18:03:46 +0100

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 (image description) 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 ...
edit flag offensive delete link more


Thank you soooo much, iv only had time to test a few of your suggestions but they worked perfectly and I understood very clearly what you wrote. Ill contact you later if I manage to get everything done.

MyMaids gravatar imageMyMaids ( 2015-04-10 11:24:58 +0100 )edit

See edit abut the commit method added to the second two macros.

doug gravatar imagedoug ( 2015-04-10 15:05:02 +0100 )edit

answered 2015-04-10 00:07:30 +0100

Charlie gravatar image

Your images are not clear enough and your text says absolutely nothing. If you want help ask a detailed question.

We can't see what is in your mind!!

edit flag offensive delete link more


Sorry the image was for another platform that can zoom much eaiser than this website, if you click the image all should become alot clearer, but thanks for responding

MyMaids gravatar imageMyMaids ( 2015-04-10 11:10:01 +0100 )edit

Question Tools

1 follower


Asked: 2015-04-09 11:53:27 +0100

Seen: 1,580 times

Last updated: Sep 05 '16