Delete/update record using Excel form

[adelkam]

Hi,

I am half way through the problem!!
I developed a user form to populate records through a list box. The user selects a record from the list box.
The selected record shows up at the bottom of the form allowing the user to make changes.
I need to program the two command buttons to update the record in the database, and o delete the record from the database.
Both buttons highlighted in "yellow" in the attached file (below)
I appreciate your help.. Thanks

DataEntry.xlsm

[MarvinP]

Hi adelkam,

See if the creation of a Global Variable helps tie your listbox to the rows of the sheet. I think I've fixed your code. You may need to format the date in the update click.

[adelkam]

Thanks MarvinP

It appears that the delete only works, The update does not work..
The update should be reflected in the "Database" worksheet, then it will be shown in the list box automatically (as the delete). For some reason it does not work at my end..
I added more code to the update button for the remaining text boxes. I uploaded the file again, I appreciate if you can review..

Thanks again

375461-delete-update-record-using-excel-form-userform-tie-to-dnr-so-lists-match-REV2.xlsm

[MarvinP]

OK, after a lot of study and trial and error here is what I find.

As soon as a single cell is changed on the worksheet the code jumps to the lstProjects_Click() event. This then puts all the old values in the Frame6 cells.

I believe this is a problem with Event Scope. Read http://www.cpearson.com/excel/Scope.aspx

The idea is that the code in a userform is supposed to only work in/on that userform. Good programmers write code in Modules and then have the Event Code in the userform call the Module code.

I have another idea that I hate to suggest but may work for you. It is called the Form.. Dialog/Icon that is already in Excel and simply not displayed on the toolbar, as it is an older tool.
http://www.addintools.com/documents/...data-form.html

I worked on this for about an hour and couldn't ferret it out. Sorry.

[snb]

An alternative:
http://www.snb-vba.eu/VBA_Userform_in_database_en.html

[Vikas_Gautam]

This is done...!!!!

I have explained my technique in the code itself. So do check it to understand.

Check the attached file:-

[Vikas_Gautam]

In brief:-
What I have done is to bypass Listbox_Click event while updating the record. I have done this by using an Extra variable(Ind) at Module Scope to hold the Row No. Under update. When Listbox_Click Event gets active, it check if current row num matches with Ind. If same then bypasses the event (means exits Listbox_click Sub).