How to make selections in form for creating a table

English
#21

Do not know if i need to open another topic for this question.

I checked and the question has been asked, but in 2013 and the answer was no.

In the form I want to use to fill the promo_distribution_table, I would like calculate two fields and store result in the table as value. reason I would like to store it is because the price of items change over time and so would the value. I can not use the query, as the data does not come from the table but has been entered manually.
Formula => Quantity (entered manually) x price (from table) = Value (this needs to be stored)

I can do the math in a query and get a result for stored values, but can not to store and make the reference to the specific field value:
:
SELECT “Table_Supplier_Items”.“Article_price” * “Table_Promo_Distribution”.“Distribution_Quantity_1” AS “Distribution_Value_1”, “Table_Supplier_Items”.“Article_price”, “Table_Promo_Distribution”.“Distribution_Quantity_1” FROM “Table_Promo_Distribution”, "Table_Supplier_Items

#22

I would like to show the value in form, so person entering it, is aware of the value given away.

#23

@grexup,

The original question is answered and it appears new questions are straying. Any new questions should be asked as new ones.

At this point will give quick answer. Some databases allow computed fields - Firebird is one (embedded comes with LO). This can also be done with macros. This has always been frowned upon as there are typically other ways to handle a situation. You can do this with macros. See my answer with samples here → Base Form data uneditable.

Further questions on this topic should be on a new question.

#24

@grexup,

Another single table sample posted here → List field values

#25

Hello, just a quick one. Been kind a busy so my response is not so quick. Sorry for that. Thank you for the help so far With this example added, I start better to understand how to appraoch my problem. For sure I will come back with another question soon regarding this :-). Cheers

#26

I am trying to grasp the macro from the example and reconstructed it to my needs.

Sub LoadList(oEvent)
    Dim oForm As Object
    Dim oShop_country As Object
    Dim oShop_name As Object
    Dim sSelectedItem As String
    Dim sSQL As String
    Dim bClearList As Boolean
    oForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
    oShop_country = oForm.getByName("lb_oShop_country")
    oShop_name = oForm.getByName("lb_oShop_name")
    sSelectedItem = oShop_country.SelectedValue
    bClearList = 0
    If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
	sSQL = "Select ""Shop_country"" || ' ' || ""Shop_name"", ""Shop_id"" FROM ""Table_Customer"" WHERE ""Shop_country"" = '" & sSelectedItem & "'"
	oShop_name.ListSource = array(sSQL)
	oShop_name.refresh()
    If bClearList Then
    	While oShop_name.ItemCount > 0
    		oShop_name.removeItem(0)
    	wend
    End If
End Sub
#27

again sorry for the mess. I can’t seem to get attachment, i see no paperclip symbol.

Anyway so from 1 table customer, I would like to select Shop_country in 1st box and Shop_name (or if possible the concatenate Shop_name+Shop_place) in the2nd box.
I can make the selection in each box, but when the macro runs, I first stopped me at the line with oShop_country, so I figured the reference to DIM oShop_country is the box as it is declared as an object not a string?
I decided to give the boxes the labelname: lb_oShop_country & lboShop_name
after running the macro it stops me at the line sSelectedItem. I do not know why.

#28

Another question i have that in the example the query for the listbox Tint, the selected value Color is “Red”, I copied that and set it to Austria. So without macro I see only the Austrian shops. I assume that the in the macro the linestarting with sSQL can overrule the that query’s set value?

#29

Code:

Sub LoadList(oEvent)
    Dim oForm As Object
    Dim oShop_country As Object
    Dim oShop_name As Object
    Dim sSelectedItem As String
    Dim sSQL As String
    Dim bClearList As Boolean
    oForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
    oShop_country = oForm.getByName("lb_oShop_country")
    oShop_name = oForm.getByName("lb_oShop_name")
    sSelectedItem = oShop_country.SelectedValue
    bClearList = 0
    If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
	sSQL = "Select ""Shop_country"" || ' ' || ""Shop_name"", ""Shop_id"" FROM ""Table_Customer"" WHERE ""Shop_country"" = '" & sSelectedItem & "'"
	oShop_name.ListSource = array(sSQL)
	oShop_name.refresh()
    If bClearList Then
    	While oShop_name.ItemCount > 0
    		oShop_name.removeItem(0)
    	wend
    End If
End Sub
#30

Paperclip icon to add a file is in the toolbar for questions (edit your question). It is not available in a comment.

#31

@grexup stated:

Another question i have that in the example the query for the listbox Tint

Please read all of other post (latest sample) this was already posted there.

You are getting stopped in the code because something is incorrect with your naming. You should have reason to change something. The latest linked answer where you got this code has another link to the Base documentation.

#32

Hello, I got it working :-). Indeed I made here an incorrect naming, this was the problem.This is a relief.
Thanks again for your patience and help. I am going to try to get the concatenate list working, hopefully it will work.

#33

Hello again,

Just 1 issue I still have is that when making selections and moving on, the data selected in first listbox dissapears. How can i avoid this and keep the data in the cell?

Below the macro I use for this, but as it happens later in the process, I wonder if this is macro-related

#34

Sub LoadList_Customer(oEvent)
Dim oForm As Object
Dim oShop_country As Object
Dim oShop_name As Object
Dim sSelectedItem As String
Dim sSQL As String
Dim bClearList As Boolean
oForm = ThisComponent.DrawPage.Forms.getByName(“MainForm”)
oShop_country = oForm.getByName(“lb_CC1”)
oShop_name = oForm.getByName(“lb_CN1”)
sSelectedItem = oShop_country.SelectedValue
bClearList = 0
If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’”
oShop_name.ListSource = array(sSQL)
oShop_name.refresh()
If bClearList Then
While oShop_name.ItemCount > 0
oShop_name.removeItem(0)
wend
End If
End Sub

#35

List box data is tied to the record data - at least in the samples provided. When you move to a new record there is nothing in the selected record and therefore no data to display. If it is an existing record it should display what was previously entered.

#36

Hello again,

Do not know if i should make another question for this, but it concerns the macro code used here. I got it all working thanks again. In the following line of the coding for the macro, i would like to add a sort of data in cell 2 before selecting as this gets overwriiten when selection needs to be altered. I tried adding ORDERBY at several spots, but I run into errors.

If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’”

I tried things like:

sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’ ORDERBY ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress”””

analog to sql in initial query in the second cell:

#37

SELECT “Shop_name” || ’ - ’ || “Shop_place” || ’ - ’ || “Shop_adress”, “Shop_id” FROM “Table_Customer” WHERE “Shop_country” = ‘Austria’ ORDER BY ( “Shop_name” || ’ - ’ || “Shop_place” || ’ - ’ || “Shop_adress” ) ASC

What am I doing wrong?

#38

Any and all further questions should be new. Original question has been answered.

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