Populate listbox

Hi, welcome to the forum
Try this one

I did not look at the search columns but this is step 1

Well maybe this is better
All searches work
Enjoy your weekend

Minor modification but not what you want yet

It searches for all occurrences of what you type, not just the ID, that will need fine-tuning but this was just to show how it can be done too, if you type a year or type reviewed
If you really want it to work with message boxes and so then you'll have to review the code, sections can be reused but you will have to 'attack' it in another way.

I assume you did not write this code yourself.
The listbox only allows one column but the content is built for two.
You should check how to use multi column Listboxes.

The search is the extra function I added, if concatenates the columns as one string to look in to.
I'm going out now but I'll take a look some time this weekend and upload a test.
Meanwhile,
happy coding

That’s the way I started +25 years ago😜

Keebellah good idea

I rewrote most of your code

Have fun

I can imagine all the next questions and wishes you could have...
Adding records, Editing records, Deleting record, Sorting records by ID, Name, Lotnummer, Year etc.
With that in mind a minor addition, and for you brain gymnastics and build-up your coding experience.

This is the way I attack these challenges, I'm sure others have other ways and better ideas, but ... I never learned for this, it's built on experience

Well, thank you very much!

From what i've seen now it looks very nice.
You perhaps know why i can't type in txtProd? I checked if it's locked but it isn't

Can you please tell me in what line of code it determines in how many and which colums it searches for data?

Ofcourse i wanna add more and i would like to try to search on Year.

I have problems with understanding this:

Case Is = "rev"
            If InStr(1, Right(zkTxt, 6), LCase(Me.TxtRev.Value)) = 0 Then GoTo nextC

Guess i have to add something similair if i wanna search on year

I was following this topic.
For 2 reasons, 1.maybe I can learn something. and 2 Topic starter is Dutch, I am from Belgium and I speak Dutch.
So I made my own example.
I always use real tables when I can.
The first search frame is search ID or Productname or Reviewd.
The second search frame is search ID and Productname and Reviewd. I don't think this is necessary but I put it in there as an example.
Furthermore you can add new products, edit products, delete products.
Hope it helps
Have a nice weekend.

Hi, I started for the same reason, the helping part and of course learning from other one's questions.
I prefer real tables but always leave it to the OP to decide.
I'll take a look at your file, sounds good.
Yes, I'm Dutch but grew up outised of Holland, only came to live here during my studies and then settled here after 1987

I checked, my mistake, you can type now
I'll explain the Case = ... lines later.
Have you checked dotchiejack's file? It's very nice and a good approach.

Thank you very much.
Now ID is acting strange, i can't use all numbers in it and if typ "1" in it i can't add any other number than "8"..

Yes, and nice to see 3 dutch people in a thread without a word Dutch

I will see which approach fits my wishes the most. Till now i didn't plan to add and edit records since i wont add records. Other people are doing that at this moment and i don't know yet if they are ready for a change

The code

If InStr(1, Right(zkTxt, 6), LCase(Me.TxtRev.Value)) = 0 Then GoTo nextC

checks if the text you type in this case in the Revision? field exists if 0 it does not exist so the next row is checked

zkTxt is the string that contains all the columns for one row concatenated with a | as separator, that is why with the prd (Product Name) I check the p1 to find the first | and the P2 for the one after that, between p1 and p2 is the data for the product name


see below some the contents for the first six row, the contents of zktxt for each row

|17|Alice Mutton|20 - 1 kg tins|2017|30125|Yes
|3|Aniseed Syrup|12 - 550 ml bottles|2017|58960|
|40|Boston Crab Meat|24 - 4 oz tins|2018|45734|Yes
|60|Camembert Pierrot|15 - 300 g rounds|2019|58977|
|18|PLUS Carnarvon Tigers|15 kg pkg.|2016|87445|Yes
|18|Carnarvon Tigers|16 kg pkg.|2016|95541|Yes

What it foes is when nothing you type matches the last entry is removed, that is what the IF condition does.
The Function returns a true or false based upon the fact that it found a value or not.
18 is the highest ID you have so if you type 19 nothing is there.
When you type 1 you will see all the IDs with a 1 in it when you type 18 you will see two, (duplicate IDs) and if you type 181 mothing happens becasue there is no 181 and the text remains 18

Okay, I made it too sophisticated, I removed some code and it works now, the only thing is that you can keep on typing even if no results aer found, I wanted to avoid that.
Have something else in mind but this just so it works for you now

Okay I see what you mean, I'll check the code, strange, it worked before.
Will get back to you

Okay this way is better and it works (tested)

I like dotchiejack's sample a lot, I took the liberty to make some minor modifications.
No extra functionality, just modified things.

It's complete now if you do not select anything you can type anything in the search box

@Keebellah,
Nice