• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data not being written to database

M

Maneesh Massey

Member
Hi,

I am working on an application using VBA in Excel 2013. I have a few userforms that collect information from the user and write it back to the worksheet in their respective columns.
There are essentially 5 important forms that do the job. Of the 5 forms, one is to add new agents, and the rest 4 forms take in information and write it to the worksheet based on certain criteria since the agent commenced his work and the agent's work done in the previous year. 2 forms write it to the left side (since the agent commenced his/her work) of the Agent_Detail worksheet while the remaining 2 forms send information to the right side (work done by agent in the previous year) of the Agent_Detail worksheet.

The problem is, somehow, only 2 rows of data are being written to the database for the Work Commenced side of the Agent_Detail worksheet. The previous year's part of the data has no problem at all, with all the entries being written to the database. Why is this happening ? I fail to understand the reason.

Please help me get this right. Appreciate all help. I've attached the file with this post.
Thanks a lot!
Regards,
Maneesh
 

Attachments

  • UserFormBETA.xlsm
    325.7 KB · Views: 8
Hi Maneesh,

Which sub writes the data to the worksheet?

And please can you provide a sample workbook with data just after this problem occurs.

Thanks,

Peter
 
PeterB said:
Hi Maneesh,

Which sub writes the data to the worksheet?

And please can you provide a sample workbook with data just after this problem occurs.

Thanks,

Peter
Click to expand...
Hi Peter,
Thanks for responding and offering to help.
The Sub which is writing data to the worksheet is:

Code: 
Private Sub cmdAdd_Click()

on the frmDetailPreviousYear userform. This also depends on whether the user wants to add more data to the worksheet. If the user wants to enter more data into the worksheet, then answer=VbYes code will open up another form, frmAddMoreDetailPreviousYear or frmAddMoreDetailCommenced form. If the user does not want to enter any more data to the worksheet, then answer=VbNo code will execute, which is to say, frmDetailPreviousYear form will open which has an Add More button on it which writes the data to the worksheet.

I don't know where exactly the code goes wrong and why data is not being written to the worksheet. Sometimes all entries are written to the worksheet, while at other times only 2 or 3 or 4 rows of data are added to the worksheet on the Work Commenced side of the worksheet. The Work in Previous Year side of the worksheet has no problems at all. All entries made on the userform for this side go exactly into the worksheet and not even one is missed.

The problem occurs on the Work Commenced side of the code. I have attached sample data, in which you can see that though there are 7 entries per userform, some entries have gone in all the way (from Villageone to VillageSeven) while at other times, only some of the Village entries have been written to the worksheet. (Check Village entries on the Work Commenced side of the worksheet.)

I don't know where and why this is happening. You just have to test it yourself several times with some fictitious data entries and see it for yourself that after a few tries, some rows of data on the Work Commenced side of the worksheet are not written to the worksheet.

I have attached the file here.
Thanks for all your time and help.
~Maneesh
 

Attachments

  • UserFormBETA.xlsm
    330.8 KB · Views: 7
Hi Manesh,

I have entered some test data in the user form and it is correctly writing to the underlying data sheet.

Please can you post details of entries on the UserForms when it fails to write to the data sheet.

I'm sorry but I can't help you unless you can be more specific about when the problem occurs.

Regards,

Peter
 
PeterB said:
Hi Manesh,

I have entered some test data in the user form and it is correctly writing to the underlying data sheet.

Please can you post details of entries on the UserForms when it fails to write to the data sheet.

I'm sorry but I can't help you unless you can be more specific about when the problem occurs.

Regards,

Peter
Click to expand...

Hi Peter,
I've attached the file again with this reply. I entered some fictitious data for the first 2 rows (for PID no.
EAKSONBH000001) while only one entry was written to the database. Then I tested for PID no.
EAKBASTI000002, and made 7 entries (from one to seven) and they all went in very well as you can see in the file attached. Then again I tested the third time, making 7 entries from the userform frmDetailCommenced, and only ONE entry was written to the worksheet (PID no.
EJKAZAMG000003
). (please see the attached file). While all the entries I made for the Work in Previous Year side was written to the worksheet.

If you make use of it several times, it misses writing some data to the worksheet. I cannot understand this behavior of Excel VBA ??

Hope you can still help. Appreciate your time.
Regards,
Maneesh
 

Attachments

  • UserFormBETA.xlsm
    329.9 KB · Views: 15
Hi Maneesh ,

I am sure Peter will respond , but in the meantime why don't you try debugging the problem yourself ?

First , make a complete flow diagram , which will not only help you now , but will be invaluable 6 months from now , when you may no longer remember anything of what is happening in this project !

What is the complete sequence of user data entry , what are the forms involved , what are the validations ,... ; my question is are you still in the testing phase or has this software been handed over to the user ?

If you have an idea of all of the above , put a breakpoint ( by pressing F9 ) on the line which does the add to the database ; each time Excel reaches that line to execute it , program execution will halt , and allow you to examine all code data before pressing F5 will allow it to continue. If you have made 7 new entries , program execution should halt on the line with the breakpoint 7 times ; if it doesn't , trace the route backwards to see why this has not happened.

Narayan
 
NARAYANK991 said:
Hi Maneesh ,

I am sure Peter will respond , but in the meantime why don't you try debugging the problem yourself ?

First , make a complete flow diagram , which will not only help you now , but will be invaluable 6 months from now , when you may no longer remember anything of what is happening in this project !

What is the complete sequence of user data entry , what are the forms involved , what are the validations ,... ; my question is are you still in the testing phase or has this software been handed over to the user ?

If you have an idea of all of the above , put a breakpoint ( by pressing F9 ) on the line which does the add to the database ; each time Excel reaches that line to execute it , program execution will halt , and allow you to examine all code data before pressing F5 will allow it to continue. If you have made 7 new entries , program execution should halt on the line with the breakpoint 7 times ; if it doesn't , trace the route backwards to see why this has not happened.

Narayan
Click to expand...
Hi Narayan,
This is absolutely invaluable information! Thanks for putting it down step by step like you have. I will try to do exactly like you suggested. If, still, I encounter something I cannot solve myself, will you please help me ?
Thanks once again!
Regards,
Maneesh
 
Hi Maneesh ,

No problem ; not just me , you will find there are quite a few very helpful and very knowledgeable forum members.

Post back if you have any difficulty.

Narayan
 
Hi Maneesh,

I have been stepping through the code but haven't seen why it would fail to write to the worksheet.

It would be good if you could follow Narayan's advice and then report back on any issues that you find.

Regards,

Peter
 
You must log in or register to reply here.
Back
Top