Populate listbox based on a database search using a textbox

Hello,

I have searched on multiple forums to try to find an answer to this solution but cannot find anything that fits what I need.

I have a database which gives the user a list of all stock we currently hold, and information about the stock (company, quantity, item etc)
I already have a userform set up whereby the user can enter the Model Number & it will display the Item, Company & Quantity. This works by searching for the Model Number & then returning the row data associated with that model number into 3 text boxes. The user can also edit the quantity from this userform as well.

What I need is an extension on this userform which allows for more than one item to be displayed with the same model number. As we can have the same model number on multiple rows with different costs.
I know my current userform will not be sufficient for this, as my results are populated into text boxes. What I am thinking is that the user can enter the model number, hit search & then multiple results can be displayed in a listbox, and then somehow the user can select & then edit the quantity.

I have little VBA knowledge, and what I have I have learned online & modified to suit what I need.

Any help/advice would be brilliant.

Thank you.

Hi Guys,

I've attached an example of my workbook. I have also got an Add New Item form, but I've left that off as that's working fine.

Thanks.

Sorry, I've never come across or used a recordset before. I'm unsure of how this would work.

Thanks.

Originally Posted by xlnitwit

In very general terms- making a few assumptions as to what you use currently- while we await a workbook, you would want

Please Login or Register  to view this content.

to populate the listbox. Then to populate your textboxes, something like

Please Login or Register  to view this content.

and reverse that logic to repopulate the listbox.

Sorry, I've never come across or used a recordset before. I'm unsure of how this would work.

Thanks.

Originally Posted by bakerman2

Let's try this for starters.
After search click in listbox to transfer selected item to textboxes

Thank you, that works good. However, it does not bring up any results if I enter a model number which appears more than once in the list.

Originally Posted by Dotreena

Thank you, that works good. However, it does not bring up any results if I enter a model number which appears more than once in the list.

Sorry, I've figured it out. I was entering the model number lowercase & on the list it is uppercase. Is there any way to able to search either?

Originally Posted by bakerman2

Please Login or Register  to view this content.

Brilliant, works perfectly!

I'm having a small problem with the Edit function now, whereby if I select the 2nd option in the list of a model number that appears twice & try to edit the quantity it will edit the quantity for the 1st item in the list, even though the 2nd item for instance is selected & it is the 2nd items details populated in the textboxs on the userform.

I've had a look at the code myself yesterday but can't seem to see why this is happening?

Thanks.

Originally Posted by bakerman2

Try this one.

When they appear in the listbox what is the final column that has numbers in, where is that information coming from? E.g. if i enter hlv5407 they have 3 and 6 in the final column?

Thanks

Originally Posted by bakerman2

Because model numbers can occur more then once I've added the row numbers where they're located.

Please Login or Register  to view this content.

In turn these numbers are used to edit the correct selected item in ListBox.
You can hide that column by setting columnwidths for all columns and setting lastcolmnwidth to zero.

Ok great, I was trying to figure out how to add a column to my database you see and couldn’t get the code to work.

Also do you know how I can get my msgbox to appear if they enter a model number that’s not found. My original if r is nothing then etc is not working,

Thanks

Originally Posted by bakerman2

Ok, try next one.

That's brilliant.

And finally, can you talk me through if I needed to add a new column to my database as I can't figure out what to modify in your code to facilitate it.

Thanks.

Originally Posted by bakerman2

As long as you add coloumns after F column you don't have to change anything.
Otherwise I've marked what you need to change in the code.

You are a true star. Thank you for all your help on this. I'll mark it as solved now.

Originally Posted by bakerman2

Glad to help.

Sorry to re open this,

I've just come to add a column in, and when doing so it then doesn't change the quantity of the item when i try to adjust it.

Thanks

Originally Posted by bakerman2

Post example file with column added.

Attached.

Thanks

Originally Posted by bakerman2

Change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Brilliant, that's worked.

Finally, how do I alter to search for a different column rather than column D which is model number. I'm trying to get it to pick up the 'Item' instead which is column B.

I've changed

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

No luck?

Thanks.

Originally Posted by bakerman2

Please Login or Register  to view this content.

Changing to 2 was good 1st step red part had also to be changed to 1

Ah! Brilliant.

Thank you again, really appreciate it.

Do you know how to return a date in the listbox as UK format and not US?

generally wrap returned value with format required

Please Login or Register  to view this content.

Originally Posted by nigelog

generally wrap returned value with format required

Please Login or Register  to view this content.

Sorry could you give some more detail as to where that would go?

Any other ideas?