From a UserForm, Update data command button is not working

[johnw993]

I feel dumb asking/posting this question, but I just can't get it resolved. Your input will be greatly appreciated.
I have created a UserForm with about 90 fields, comprised of textboxes, comboboxes and checkboxes.
In this UserForm, the user can select a name in the 1st field (the txtfname box) and click on "Find" and the form populates with the correct data, in all boxes. This is working fine.
The user can also select to Clear the form and enter new data by clicking on "Add New Record", and this is also working well, as new records are added to the database.
The search features in the UserForm, to "Find Next" or "Find Previous" records are also working swimmingly.
What is not working is the "Update" button. (see code below).
It doesn't do anything, or when it does, it creates a new record by overwriting the headers in the database sheet.
Any suggestions?

Thank you.

Please Login or Register  to view this content.

[MarvinP]

Hi John,

Based on your description of what it does, overwrites the header....

I'd suggest that your userform shows data from the rows and columns in your database. If you open the userform and your cursor is on cell B7 then that might be the row (row 7) that it thinks it is to write your userform data.

I believe you need to somewhere in your userform set the row that is the active/selected cell on the worksheet.
Something like
currentrow = ActiveCell.Row or

Step thought your code and see what value Currentrow is equal to and you will find where your data is going.
If you find next or previous, soon after that you need to tell the userform what row you are on.... Set currentrow at that time.

Also, I think you can reduce/improve your code using a "With" construct like the below

Please Login or Register  to view this content.

I hope this helps.

[johnw993]

Thanks Marvin, for your feedback.
The issue is, the user can't select an active row on the worksheet "DB", because they will not be able to see the worksheet (which is hidden and acts like a database). The UserForm has to find that row, based on the selection made in the "fname" field.
I think it would help you to clarify if I could show you all the code for my userform, but it exceeds the 15,000 characters allowed on this forum.
Once I fix the Update issue, I'd like to learn more about, and try the "With" construct as you suggested, and simplify and clean up my code.

In the meantime, am eagerly awaiting your reply.

[jaslake]

Hi John

Create a new (can be hidden) textbox (call it say tbNameRow) on your User Form and populate it with Row Number of the Name found by the Find Code.

Then add this Code

Please Login or Register  to view this content.

[Norie]

Where/how/when are you setting the value of currentrow?

[johnw993]

Thank you JasLake, this solved the problem!

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

[johnw993]

JasLake, I have a different question:
I'm struggling with replacing the DTPicker function in my WorkBook and UserForms. I found and downloaded your frmCalendarv.102.zip file and have installed it in the workbook and it is working great! in all the worksheets.
How can I make this work the same way in UserForms?
I think I may be able to figure it out by experimenting, but it would be helpful if you have an 'easy' and elegant answer.

Thank you!
John

[jaslake]

Hi John

A couple things...

Firstly, this is NOT my Code...I copped it from the Internet...probably here but I'd not swear to it.
http://excel-it.com/excel_datepickers.html

Secondly, this is a new Issue and should be the Subject of a new Thread such that you'll get attention from all the helpers on this Forum. Not many will look at a SOLVED Thread. You may PM me with a Link to your new Thread if you wish.

And thirdly, if you truly wish help with the Date Picker issue, you'll need to upload a sanitized version of your File so we can see what you have and what you need.

Alternately, here's a link that demonstrates the Calendar Control on a User Form (Post #3).
http://www.excelforum.com/excel-prog...userforms.html

Let me know how I can help.