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("MainForm")
CustIdCtrl = MainForm.getByName("CustomerIdList")
RowNum = MainForm.Row
CustIDCtrl.Commit()
If MainForm.IsNew() Then
MainForm.InsertRow()
MainForm.Reload()
MainForm.Last
Else
MainForm.UpdateRow()
MainForm.Reload()
MainForm.Absolute(RowNum)
End If
End Sub
Create the macro by going to the main database window, Tools â Macros â Organize, create a new module on the current document, and then save that as a new sub, select it as an event in the form control dialog. That solves problem 1.
2. Date of service.
To set the date of service as the same as a previous record, you will need to access the desired value and update it to the desired field. There are two basic methods: using an unbound control to hold the value temporarily or using a SQL statement. Iâll assume you want the latter. Thus, the macro will be as follows on the event for the MainForm AfterRecordChange or something like that:
Sub SetDateSQL
FormContainer = ThisComponent.DrawPage.Forms
MainForm = FormContainer.GetByName("MainForm")
DateCtrl = MainForm.getByName("datBookingDate")
Conn = MainForm.ActiveConnection()
SQL = Conn.CreateStatement()
QueryStr = "SELECT YEAR(MAX(`BookingDate`)), MONTH(MAX(`BookingDate`)), DAY(MAX(`BookingDate`)) FROM `All Bookings New`"
ResultSet = SQL.executeQuery(QueryStr)
ResultSet.next
MaxDateYr = ResultSet.getString(1)
MaxDateMo = ResultSet.getString(2)
MaxDateDay = ResultSet.getString(3)
Dim adate As New com.sun.star.util.Date
adate.year = MaxDateYr
adate.month = MaxDateMo
adate.day = MaxDateDay
If IsEmpty(DateCtrl.Date) Then
DateCtrl.Date = adate
DateCtrl.Commit()
End If
End Sub
I realize the desired date may or may not be the MAX date â you see where the query goes and can adjust it as you desire. Instead you may want the row with the MAX invoice number and the date for that row-- use a subquery and WHERE statement to achieve that result.
3. Profits Box
Once again, this is not a best practice to store another value that can be calculated from two other recorded values. That said, easy to do. You will need to change the profit control to a unique name, which I have called fmtAmountSpentProfit in the example. I use the actual names of the other controls in the test database, although I observe that they are all bound to bad data source fields and those bindings would need to be corrected in order to save the data to the right place:
Sub CalcAmt
FormContainer = ThisComponent.DrawPage.Forms
MainForm = FormContainer.GetByName("MainForm")
PdInCtrl = MainForm.getByName("fmtUnitPrice")
PdOutCtrl = MainForm.getByName("fmtBillingRate")
ExtrasCtrl = MainForm.getByName("fmtAmountSpent")
ProfitCtrl = MainForm.getByName("fmtAmountSpentProfit")
ProfitCtrl.Value = PdInCtrl.Value - PdOutCtrl.Value - ExtrasCtrl.Value
ProfitCtrl.Commit()
End Sub
Run this sub from the Changed event on each of the controls, or create a button, or pick desired event.
4. Auto Number Invoices
The database already does that because of the setting for AutoNumber in the table.
Conclusion
Those are the questions I see. Feel free to donate to TDF if so moved.
P.S.: It seems like a bad idea to me to save unencrpted financial account information in the database, if that is indeed what I see.
EDIT: Added the .commit() method to the second two macros, which is necessary to make the calculated valued âstickâ in the database.
EDIT 2: For the first macro, added IF logic to deal with the insert row. When you are on the insert row, the write to the database is a different function (InsertRow) and the new row number is just the last one. To deal with the aesthetics of the speed of the changes, you could make the changing controls .EnableVisible = False and then True at the end. Macros can get complicatedâŚ
(if this answers your question, please accept by clicking the check box (
) to the left)
