Listbox contents filtered using another Listbox

[MaaX]

Hi, i have looked through a few similar queries on this forum but have struggled to apply the solutions to my own problem.

My current REPLACES listbox lists all partnumbers in the database. I would like this REPLACES listbox to filter and only show the partnumbers related to a particular customer which has already been selected in an earlier listbox.

I can manage to get this working inside query design by simply adding a customer column and typing in a customer. When i run the query this way the results are restricted to the customer I have entered in the criterion field. However i need the criterion field to be updated according to the CUSTOMER listbox on the form.

So I guess my question is what do I enter into the Customer criterion field?Project Tracking test.odb

[Ratslinger]

Hello MaaX,

This is a matter of modifying the SQL in the list box & refreshing the control when CUSTOMER changes. You can do this with a macro:

Sub CustChange
    oForm1 = ThisComponent.Drawpage.Forms.getByName("MainForm") 
    oField = oForm1.getByName("txtCustomer")
    sSelectedValue = oField.SelectedValue
    sSql = "SELECT DISTINCT ""Item"" FROM ""PartNumber"" WHERE ""Customer"" = '" & sSelectedValue & "' ORDER BY ""Item"" ASC"
    oField = oForm1.getByName("txtReplaces")
    oField.listsource = array(sSql)
    oField.refresh()
End Sub

I would attach this in two events: Item status changed in txtCustomer list box and in After record change on MainForm. This allows for any change in the customer.

The routine itself when executed simply retrieves the current value of the Customer list box, inserts it into the SQL a string. The string is then inserted into the List source of the list box & the control refreshed generating a new list based on the Customer.

This answer only addresses your question and not anything else on your form.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[MaaX]

Brilliant, really appreciated as that works a treat. However you mention that it can be done modifying the SQL in the list box and then refreshing. I can see that the SQL command is there using the SELECT DISTINCT but also this has the sSelectedValue reference which is beyond me at this stage. Is there a way to separate the SQL and macro so i can add SQL direct to List content of the replaces listbox?

[MaaX]

Also looking to add same to the Similar listbox. I tried to copy and paste the above changing out the Replaces for Similar and saving it as CustChange1 but its just ignored it like it has not even been added.

[MaaX]

looks like i have the second one working. I added CustChange1 to txtCustomer listbox after updating. Seems to be working.

[Ratslinger]

Reply to first comment. Because of your form design, the SQL must be part of the macro. This is key since the actual SQL is joined with the selected customer (sSelectedValue). This entire scenario may be possible without any macros if the form was designed differently.

sSelectedValue is fairly simple. The control (txtCustomer) is obtained on form (MainForm) and the current value of the control is places in this variable. It is the appended to the SQL to allow specific selection.

[Ratslinger]

Your secondary comments appear to have resolved the situation. You could have simply added:

oField = oForm1.getByName("txtSimilar")
oField.listsource = array(sSql)
oField.refresh()

before the End Sub line and both controls would be updated within the one macro.

[MaaX]

Yes i have had a few comments about the database design as a whole. Mainly the fact that i should be using more primary/foreign keys. I am still working on the above database but starting another one in parallel. I am looking at a medical database tutorial for help creating my new database. My first issue as always is trying to work out which fields need there own table and which ones can be part of the main table. I think i will look for a newbie section in a forum for these simpler problems.

[andremamp]

Perfect. It worked for me, I tested and I’m very happy with the results, I’m using and external MySQL server as my DB.

Thanks this was easy. I’ve seen other method that involve much more work.

Thanks.