Update a subform after select a item

English
,
#1

Hi.

I’ve created (with great difficulty) a macro which checks that the quantity ordered of a product conforms to its packaging. As the packaging is in table T_PRODUCTS (PRD_PKG) and the quantity in table T_ORDERS_SUB (ORD_QTY), I did as I could using the column names for the modulus check:

intColumn1 MOD intColumn2 <> 0

The macro seems to work fine (although I think it could be designed more simply, but I don’t know any other way) but there’s just one problem: when I select a new product in the listbox, it doesn’t update right away in the subform table control (frmProducts) and as the procedure doesn’t have the column to compare, it crashes.

I’ve tried various ways to “update” grdProducts after selection in the listbox (UpdateRow, Reload…) but I can’t get it to work.

Thanks for your help.

dbPrimusOrders.odb (16,4 Ko)

#2

Don’t know what should be an error there. Choosed a new value in the listbox. Added “Quantity” and saved the row (by changing the row). Then gone back and data where there in the subform. But: I don’t know what the subform should save except only one value, because you created ORD_ID and PRD_ID as key for this table. So the product should be only one rows for every order. You won’t need a tablecontrol for only one row.

#3

When order lines are already entered in the frmOrdersSub subform, clicking on each one displays the corresponding product below in the frmProducts subform. But when a new product is entered in a new line, it is not updated immediately in frmProducts. And once the quantity has been entered and tabulated (loss of focus for ORD_QTY), the program crashes:

copy02
copy02831×370 56.9 KB

copy01
copy011025×391 120 KB

Logical, since it can’t check with an empty column.

#4

What do you need all that silly Basic code for?

Store quantities per package and calculate the quatities by the sizes of the sold packages. This works without a single line of macro code.

Do you mean, that you enter some new product to the products table and the new product does not appear in any listbox until you refresh the listbox?
Simple solution: The navigation toolbar has 2 refresh buttons. One reloads the entire (sub-) form, the second one is enabled when a listbox is focussed. It refreshes the listbox only without moving away from the focussed record. If you make it work that far, I can offer a very simple macro to refresh anything anywhere in the same form document.

#5

@Primus : If there isn’t a saved row in the mainform the subform can’t show any value.

#6

defaults3.odb (61.6 KB)

The attached database with embedded HSQL was intended to demonstrate how to preselect default values for new records. Form1 and Form2 can do this without macro code, Form21 is a variant of Form2 utilizing an auto-refresh macro.
I added an additional Form 11 which is a variant of Form1 with an additional subform reflecting all existing records of the same selected category. In this Form11 you enter a category and a date which are inherited by for new record in the upper subform. The second subform (yellow color) igores the date and shows all records of the preselected category. The macro refreshes both subforms when you hit Enter in the criteria form. The Enter key triggers the refresh button which saves the main form record to disk and then refreshes the subforms.
All these subforms still do not show any inexistent records (records not saved to disk). Instead these forms store criteria values in a dedicated row in the table named “Filter”.

Source code in a Writer document with an installation button: https://forum.openoffice.org/en/forum/download/file.php
After installing this macro, Form21 and Form11 will work as expected.
Documentation: Apache OpenOffice Community Forum - [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes - (View topic)

1 Like
#7

I did what I could based on my knowledge in Basic. Since LibreOffice doesn’t support assertions, stored procedure or triggers, I had to use a macro to get what I was looking for.

Not always applicable. When a reseller buys from a supplier, he is dependent on the supplier’s order management system. If the supplier sells products by quantity and decides, for reasons of its own, to invoice by the unit, the reseller has no choice but to adapt its ordering system. It’s also a good exercise for learning how to use macros.

and

frmOrders (mainform) → frmOrdersSub (subform of frmOrders) → frmProducts (subform of frmOrderSub)

I understand that once a product has been selected in frmOrdersSub, it will only be updated in frmProducts once it has been saved in the table, once all the mandatory columns (price, quantity and, consequently, the calculated total) have been filled in. So how do you compare ORD_QTY and PRD_PKG?

Instead of making the comparison between the two values in a subform column, I’d prefer the comparison to be made directly in the table column. But I don’t know how to do that.

It would be a pleasure.

I’ll look defaults3.odb later in the day.

#9

@Primus,
a subform is not the answer because before child records can be created a parent record must exist.
all the info you require is shown in the visible field of your Product list box and can easily be extracted using basic code.
.
the attachment has 2 forms:
“fOrders” uses basic code which demonstrates the above whilst using your form and data.
“fSimpleInput” does not require macros, data input is basic, quick, simple and reliable, quantity errors can be spotted with a single button click.
.
macro code can enhance form usage but is rarely essential and definitely not a first priority, it’s best to follow the rules and keep things simple especially in the early years.
Orders.odb (29.7 KB)

#11

I’m perfectly aware that the sub-form is just a wobbly solution. But not knowing what else to do, I cobbled it together as best I could.

In any case, your file does the job, thank you. I’m completely lost in your code, but it works. To tell you the truth, I had to work quite hard on this verification function because I had done the thing several years ago in VBA for Access and I wanted the same thing with LO Base.

Private Sub VerifyQty_AfterUpdate()
	
	If Me.txtOrdQty MOD Me.PRD_ID.Column(2) <> 0 Then
		Ok = MsgBox("The quantity selected for this product must be a multiple of the packaging.", vbInformation + vbOKOnly, "Caution")
		Me.txtOrdQty.Value = 0
		DoCmd.GoToControl "txtOrdQty"
	End If
	
End Sub

It was quite simple.

#8

Goodday Sir,

I couldn’t read too good the entire topic and I therefore have a question: by the time I was constructing a form I first did not realize the utility of a SubForm connection, but I had to make up my mind and follow the menu-driven SubForm attachment instructions to show a foreign key related table. Did you ever try that way?

#10

Sorry, I don’t know what you mean. Which foreign key do you want the main table to be linked to?

#12

Ok, I did a mistake. Anyway by creating your Form either for data entry or for consultation purpose, in case you need data projection split in two frames (MainForm + SubForm) the wizard usually asks you which field common to both you would link… (it’s an equijoin ).

Feel free to ask me for a odb file as example, if you fancy…

#13

The wizard is close to useless because it can create one form with one subform (always equijoin), and it never creates any listbox.
For relations other than equijoins, you need parameter queries.
Before you can refer to a listbox item, you need to add the item. WIthout macros, you can simply add an additional form where you append new records. After that, you need to refresh all listboxes containing the new item. This is what the second refresh button does when a listbox or combo box is focussed.
I can offer a tiny AutoRefresh macro refreshing anything anywhere within the same form document.
Latest sample: https://ask.libreoffice.org/uploads/short-url/ivwMpt7NZ7xsnjXBkrR7090JR3a.odb

#14

Thanks for your survey Mr. Villeroy: as per your message I probably got misunderstood with Mr. Primus request. I have to admit I didn’t take listbox facility into consideration.

Regards

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.