List field values

English
#1

I have 2 list fields 1 is Colour and 2 is Variant. The values for the Colour field come from a table and I have been able to populate it (10 items in the list). However the data source for the Variant field has 200 different values. I want to limit/filter what appears in the Variant list field by the value selected in the Colour field list ie SELECT variant FROM tbl_x WHERE colour = listfield.colour. Hope this makes sense. Please help

@Ratslinger my sincere apologies for not providing enough detail. I’m using MacOS Catalina. I installed LibreOffice and connected it to my SQL Server database. The reason is so that I can use the Forms in LibreOffice Base for viewing and entering data from/into tables. I was trying out the forms for the first time and I added two Combo boxes. Combo Box A is populated by values from the Colour column of the tbl_Colours (10 returned results) - [SELECT DISTINCT “colour” FROM “tbl_Colours”]. For Combo Box B, I want to populate it with values from the Variant column of the tbl_Colours. Unfortunately there are over 200 values in that column as they represent the various shades/tones of the colours in Colour column e.g. RED has 11 variants. What I want to do in Combo Box B, is to populate it with ONLY the variants that correspond to the Colour selected in Combo Box A. For example, if the user selects RED in Combo Box A, then Combo Box B would be populated with only the 11 variants (from tbl_Colours) that have RED in their Colour column. I hope this clearer. My sincere apologies if it’s not. As for macros, I used write quite basic ones in Excel but have no clue as to how to write macros in Base. LibreOffice 6.4

#2

@Adeshonubi,

Your information is limited. Have no idea where the values are coming from, what is be saved and where, and you mention nothing about your environment - specific LO version, OS and database used (and connector if applicable).

A sample would be best - no personal/ confidential information. Edit your question & place there. See → How do I attach a file to my question/answer?

Depending upon the information asked for, this may require using macros. Are you versed in writing Base macros?

#3

@Ratslinger:

CREATE TABLE dbo.tbl_colours (
id INT PRIMARY KEY IDENTITY(1001,1) NOT NULL,
scheme VARCHAR(50) DEFAULT(NULL),
r_id VARCHAR(50) DEFAULT(NULL),
colour VARCHAR(50) DEFAULT(NULL),
variant VARCHAR(50) DEFAULT(NULL),
rgb VARCHAR(50) DEFAULT(NULL),
);

#4

Hello,

Although not certain of what you have in the way of set-up, here is my response to a similar question → How to make selections in form for creating a table

Edit:

Here is a simple sample. One macro - LoadList

Macro is attached to internal form event - After record change

and Color List Box event - Item status changed

Sample ---- DependantListBoxes.odb

This is essentially the same as the sample in the above link path. For further information on LO macros and events, see the documentation found here → Documentation/Publications

How to make selections in form for creating a table
#5

@Ratslinger: Hi, can’t thank you enough…the macro works. Thank You. However there are a couple of snippets that went over my head (as I’m sure you expected :unamused:). Forgive the lack of knowledge: what does the bClearList do? The SQL syntax is a bit confusing; trying to figure out why we a selecting all columns from tbl_colours, use of the double pipes and what the IF bClearList statement. Will do some research and experiment. Thanks again

#6

@Ratslinger:
Hi, thanks again for ur help. Looked at ur macro again today and still can’t decipher certain bits as said previously. More curios is that you set COLOUR = ‘blue’ as the query for the second combo box. That threw me a bit. Will read through the documentation as suggested and hopefully it’ll all make sense. However if it’s not to much trouble, will appreciate an explanation of the points I raised. You’ve shown me it is doable so it’s ok if you aren’t able to further explain. With MS Access, I was able to write macros that interact directly with form controls and values but for whatever reason I’m having a mental block with Base. Thanks for your all help will do some further reading

#7

@Adeshonubi,

Just for your reference, here is a post which contains many links to Base information including macros → To learn LibreOffice Base are there introductions or tutorials?

Normally have included comments in code but this time missed it. bClearList is used to remove all entries (If statement) in the Tint list box if there is no selection made in the Color list box. You can see this result on a new record.

The SQL to be used for the second list box (tint) is making the selection clear with color concatenated to tint. That can be changed at your discretion. The pipes are concatenation in HSQLDB.

The Tint query COLOUR = ‘blue’ is what was placed there by the macro after the last execution. This can change anytime the macro is executed.

#8

The code is actually fairly simple:

Get access to list box controls through internal form. Get selected item from Color LB. Use selected item in SQL for Tint LB. Move SQL into Tint source & refresh. If no color selected, erase all Tint selections.

#9

@Ratslinger:
Many thanks for the commentary - the code snippet makes more sense now (except the oEvent). I implemented your code verbatim (only changing the variable names). However when I run the macro I get this error: [BASIC runtime error. Property or method not found: SelectedValue.] against this line
oSelect = oColor.SelectedValue. This is the entire code:

REM  *****  BASIC  *****

Option Explicit

Sub cbColour(oEvent)
    Dim oDoc As Object
    Dim oForm As Object
    Dim oPrime As Object
    Dim oColour As Object
    Dim oSelect As String
    Dim oSQL As String
    Dim bClearList As Boolean

    oDoc = ThisComponent
    oForm = oDoc.DrawPage.Forms.getByName("MainForm")
    oPrime = oForm.getByName("cbprime")
    oColour = oForm.getByName("cbcolour")
    oSelect = oPrime.SelectedValue
    bClearList = 0
    
    If IsEmpty(sSelectedItem) Then sSelect = 0: bClearList = 1
	oSQL = "Select ""colour"" FROM ""tbl_colours"" WHERE ""prime"" = '" & oSelect & "'"
	oColour.ListSource = ar
#10

@Adeshonubi,

Your code is incomplete. Please note that comments are limited to how much information can be posted into them (noted at bottom right as you enter). Therefore, it is important you verify what you have posted - always and including images or files. If need be you can use multiple comments or you can edit the original question and add data there - but please note that it is edited data.

#11

Also, the error you specify seems to be an erroneous line. Can’t see that as in your code as the selected value is from oPrime for you.

The error line must be in code not yet posted.

#12

@Ratslinger:
My sincere apologies for my ineptitude. I can appreciate your frustration. LO is quite a convoluted version of VBA and the nuances need a bit of getting used to especially for hacks like me. Usually in the VBA IDE when you typr in an object you see all properties and methods attached but with base that’s not the case. I’m a Business Systems Analyst that makes it my business to understand up and downstream requirements; I write a tiny bit of code and pretty good SQL but this Base IDE throWs me. I’m sorry to be such a pian

#13

Before I post the code; logically I would expect to link a macro to an object/ control/event but that’s not evident in the code. I I see oEvent as a parameter in your ListLoad function but don’t know what the event is. Like I implied…I’m not at your level of knowledge

#14

The code:

Option Explicit

Sub cbColour(oEvent)
    Dim oDoc As Object
    Dim oForm As Object
    Dim oPrime As Object
    Dim oColour As Object
    Dim oSelect As String
    Dim oSQL As String
    Dim bClearList As Boolean
#15

Cont -

oDoc = ThisComponent
        oForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
        oPrime = oForm.getByName("cbprime")
        oColour = oForm.getByName("cbcolor")
        oSelect = oColor.SelectedValue
        bClearList = 0
        If IsEmpty(sSelectedItem) Then sSelect = 0: bClearList = 1
    	oSQL = "Select ""colour"" FROM ""tbl_colours"" WHERE ""prime"" = '" & sSelect & "'"
#16

Cont-

oColour.ListSource = array(oSQL)
	oColour.refresh()
    If bClearList Then
    	While oColour.ItemCount > 0
    		oColour.removeItem(0)
    	wend
    End If
End Sub
#17

It appears you have overlooked this in my answer. I did make an effort since you stated your limited knowledge in this area. There are two events listed and this can also be seen in the provided sample.

As a further note, oEvent is sent along with every executed event and contains such as where & what sent the event. The actual variable name can be other than oEvent.

#18

@Adeshonubi,

Your latest code is different from the original code and is definitely errant. This line:

oSelect = oColor.SelectedValue

there is no such variable in your code for oColor. There is for oColour but I believe this line should be:

oSelect = oPrime.SelectedValue
#19

@Ratslinger:
Firstly i want to thank you unreservedly for all your help and your patience. After my last comment to you, i decided to take a step back and remember my basic VBA skills. The first thing i did was to spellcheck as you rightly pointed out (oColour vs oColor). Then i realised that the SelectedValue method/property was throwing up errors because my object was a Combo Box and not a List Box - as soon as i changed it, that error stopped . I also located the Events for both the MainForm and Colour fields and how you assigned them to the macro so I did the same… after the 000th attempt it finally worked - i still can’t believe it!! The only thing is that my concatenation (|| ’ '||) kept causing errors so I omitted it and changed the order of the colics in my SQL query - for some reason MS SQL doesn’t like ||. THANK YOU SIR. Very appreciative of your help. All the very best

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