User form won't update correctly

Since I don't know your workbook like you do, the explanation you give me needs a little refining.

If you could tell me specifically what to do, what to type, which button to push etc. and then what I will see and what you think I should be seeing, it would be helpful.

Go to the start sheet tab. Click on start/ update job. User form will open up. Move are form out of the way so you can see the data table on sheet !. In the yellow box next to apartment #, get rid of the question mark and write 125. Click on the button labeled find previous apartment #. The user forum Will populate. Write anything you want to in the description/ comments yellow box. Click on update record command button. Look at where the data gets updated to on sheet one. it puts It in the top row. I would like the data only user form to get copied to the correct row that has the existing information already in it in this case it would be row Number three. The button, find next APT # does the same thing. I need whatever it is populated on the form, to go into its respective row on the data sheet When, update record button is pressed. Not that it should matter but I'm using Mac Office 2011 thanks again

Okay try this code for the update record button.

Let me know if you need help with anything else.
Please do like you did above. Tell me how to recreate the issue, what the problem is and what you want to have happen.

Please Login or Register  to view this content.

Thanks for the rep. points.

Please Login or Register  to view this content.

Let me know if you need anything else.

So tell me what all you want the form to do.

Usually a button labeled next and previous will just move from record to record without regard to any particular information, meaning something like an apartment number. Wherever you are in a database, then hitting next will move to the very next record, not the next one with the same apartment number, and hitting previous will move to the record just before it, again the very next record before, not the one with the same apartment number.

If you are on the very first record and you hit previous, nothing happens, hitting next on the last record nothing happens.

So just let me know what you want to do, keeping in mind what do you want the behavior to be if you have no records, more than two records, etc.

Maybe just a single button that searches for the first instance of a match, then the next, then the next, informing you when you are back to the first one again.

Okay, you realize you have buttons labeled find next record and find previous record as well as find previous apt# and find next apt# I think that's where the confusion lies.

No problem I'm looking at your code, give me a while to study it.

One think I noticed right away is you have a variable at the top of the module.

Please Login or Register  to view this content.

Then in one of your procedures you have:

Please Login or Register  to view this content.

If you intend these to be the same variable, they are not.
By having Dim lastrow in your cmdPrevious procedure it's a separate variable that can only be used within the procedure that it's declared in. Changing its value does not change the variable at the top. As soon as you hit the end of the procedure the variable is destroyed. The variable at the top is not changed from it's previous value.

If you knew this and it's what you intended, I would advise again it as it's confusing. Make those in your sub procedures a different name so as to avoid confusion.

Okay, here's what I found and here's what I have done.
You have a public variable at the top of the module called currentrow.
When you initialize the user form you are setting this variable to 2.
When you click the previous apartment button and the code runs, the first thing it does is figure out which is the last row in sheet1 and assigns that number to the variable lastrow. Since you declared last row inside that sub procedure it has no effect on the same variable named lastrow at the top of the module.

The next thing that happens is you are looping from the value of lastrow to 2 backwards via step -1.
The issue is this:
you are using currentrow as a counter.
For currentrow = lastrow to 2 step -1

You do not exit this loop anywhere, so the loop will always run down to 2, therefore currentrow will always have a value of 2 when this loop ends.

The other thing is, you call this previous apartment number, but this procedure always loops from the bottom of the sheet up and will in actuality always find the first record, no matter how many are on the sheet. So I don't know what your intention is, but it's always going to find the first record.

Your command next apartment number procedure loops from the first row to the last and will always find the last record no matter how many are there. The way it's set up I would label the first button command first apartment # and the second one command last apartment #.

If you want the first apartment # I would loop from the top and exit the loop as soon as it finds that number and for the last one I would loop from the bottom and exit when it finds the first apartment which would be the last if there's more than 1 apartment number or the first if there's only 1.

So what I did was this. I used a counter c for the loop and changed the current row number to equal c, but only when there's a match. Therefore the command add procedure can use the current row number to update the correct record on the sheet.

You should study the code to see what I did and then you will see how you might have to change other code.

If you have any questions let me know.

Please Login or Register  to view this content.

You can use the existing buttons. Find next apartment # searches from the beginning. The other button allows you to go backwards from where ever you are in the forward search process.

Add this code:

Please Login or Register  to view this content.

Swap out the existing code with this:

Please Login or Register  to view this content.

I attached the file just in case.
Type 125 where the question mark is and then use the find next apartment # button to go through the records forward and the other button to back up. Type another number into the box and start all over again.