User Form ListBox - Find Records (ListBox Populating Issue)

Hello:

I'm trying to troubleshoot my code that finds all records that matches data entered into Member# (textbox), and populates a ListBox if more than 1 record is found. Everything works perfectly, the only thing I can't figure out is why, when multiple records are found, the ListBox is not being populated on the correct lines. I've set the properties on the ListBox to indicate column Headers. However, when the data is pasted into the ListBox, it is being pasted onto the 2nd line (after the header) - so it looks something like this:

Header line (nothing is populated)
line 1 - nothing is populated
line 2 - displays header from the datatab
line 3 - displays the first instance of the record found
line 4 - displays the second instance of the record found

I've been racking my brain for days....I'm hoping someone will be able to help me resolve this. THANK YOU IN ADVANCE FOR YOUR ASSISTANCE!


Here is my code:

Please Login or Register  to view this content.

Thank You, JoeFoot! You definitely understood my issue. I made the change to set n = -1, but it looks the data is still off one row. I would expect the header to be in the defined header row (set properties on ListBox to indicate Headers). Below is what the data now looks like:

Header Row (nothing is populated)
line 1 - displays header from the datatab
line 2 - displays the first instance of the record found
line 3 - displays the second instance of the record found

I tried to change n=-1 to n=-2, but I get a "Run-time error '9" Subscript out of range"

I do appreciate your time!!!! thank you!

Originally Posted by LONeillSSC

Thank You, JoeFoot! You definitely understood my issue. I made the change to set n = -1, but it looks the data is still off one row. I would expect the header to be in the defined header row (set properties on ListBox to indicate Headers). Below is what the data now looks like:

Header Row (nothing is populated)
line 1 - displays header from the datatab
line 2 - displays the first instance of the record found
line 3 - displays the second instance of the record found

I tried to change n=-1 to n=-2, but I get a "Run-time error '9" Subscript out of range"

I do appreciate your time!!!! thank you!

Just some additional information - I did try changing the properties on the ListBox for ColumnHeades to "False" - and the data then posts into the ListBox correctly. I'd prefer to have the ColumnHeads if possible, but it seems that this is what is preventing the data from pasting properly - where the headers from the datatab past into Row1, instead of ColumnHead of the ListBox.

OK....I'm getting there....
I figured out how to get the headers from the data tab to actually past into the ColumnHeads on the ListBox - I needed to add the following code:

Please Login or Register  to view this content.

But now I'm getting Run-time error '70: Permission Denied, and it's pointing to this line of the code:
If n > 0 Then Me.ListBox1.Column = a

I'm so close.........I'm sure it's something that's an easy fix that I just can't figure out because of my limited knowledge of VBA....once again....THANK YOU for any help you can provide!!!!

Thanks, Norie....So it sounds like i need to modify my original code posted above?

Hi Norie:

The ListBox will have 31 columns of data - from what I read, if you do headers the way you suggest, as you scroll through the columns, the headers won't scroll as well. I will continue to play with this. Thank you!

OK....I've got my ListBox for the found records working - not how I ideally would like it, but it serves it's purpose of identifying multiple records so the user can select the record to be edited. Now I'm stuck with my Save code. I've tried to recall a record, edit it, and save the changes, but nothing is happening when I hit save - which means it's encountering an error, but I' can't tell where. Here is my code:

Please Login or Register  to view this content.

FORM mod 070115 Sample.xlsm

Here is a sample of my database if this will help.

Still trying to figure this out....

Originally Posted by LONeillSSC

OK....I've got my ListBox for the found records working - not how I ideally would like it, but it serves it's purpose of identifying multiple records so the user can select the record to be edited. Now I'm stuck with my Save code. I've tried to recall a record, edit it, and save the changes, but nothing is happening when I hit save - which means it's encountering an error, but I' can't tell where. Here is my code:

Please Login or Register  to view this content.

Your code stops on following line

Please Login or Register  to view this content.

for the simple reason that you don't assign a value to r at the beginning of your code.

I guess r represents the rownumber of the record you're trying to save changes to, but you have to lookup the rownumber first in your database before you can go further saving your changes.
In order to achieve this every record will need a unique identifier that you can use to retrieve the correct rownumber to save your changes.

Thanks, Bakerman2! I've modified the code to the following, where I set the value of r, but I still get the same issue, nothing is happening:

Please Login or Register  to view this content.

I've made a small example file because you're still missing the point.
View the code, specially the CommandButton1 code(Save Changes).

Ah....I think I understand what you are saying. I will play around with this over the weekend and let you know how I make out. THANK YOU SO MUCH!!! Have a great one!

bakerman2 - THANK YOU SO VERY MUCH!!!! Your solution worked PERFECTLY, with a few minor tweaks!!!! (sorry for the delay - I didn't get to attempting your solution until this morning!) Once again, THANK YOU!!!!

Glad I could help you out.