Code Needed to Save Data from Input Sheets to Database Sheet by Referring to Specific Cell

[Rudidw]

Hi,

I have 2 Input sheets to capture data and I would like to save this data (or overwrite changes made) to the database sheet (DB) when I press the button according to the "Student number" in cell "C5" and I need some code to make this happen please.

So basically what must happen is - I make changes to a specific student's info on the Input sheets and then press the button... This will send (save) the data to the DB sheet and will update any changes that were made by using the student number to select the correct row to update.

I have attached a workbook for reference

Thanks

[torachan]

@Rudidw now the sheets have only a save button, how do you intend retrieving your data into your input sheets.
but before that how do you propose doing you initial input, these need to be thought about as a whole (input/output/retrieval/editing/updating/deleting).
If you do it by just 'glueing' pieces of code together from random sources you land up with a 'dogs breakfast'.
start by ensuring you have given thought to all fields you want in your database - do not waste storage space storing variables that can be calculated on the fly from stored data.
Without knowing the end game it is impossible to code efficiently.
A simple instance is referencing individual data - if retrieval is simply via a listbox there is no need for a search procedure involving the student number, because there will always be a link between the DB row and the listbox listindex - this simplifies coding enormously.

[sintek]

My thoughts exactly...You will need to put more thought into this...I suggest One userform with everything as torachan showed and suggested in your previous post...Just add the filter part to his file...

[Rudidw]

@Rudidw now the sheets have only a save button, how do you intend retrieving your data into your input sheets.
but before that how do you propose doing you initial input, these need to be thought about as a whole (input/output/retrieval/editing/updating/deleting).
If you do it by just 'glueing' pieces of code together from random sources you land up with a 'dogs breakfast'.
start by ensuring you have given thought to all fields you want in your database - do not waste storage space storing variables that can be calculated on the fly from stored data.
Without knowing the end game it is impossible to code efficiently.
A simple instance is referencing individual data - if retrieval is simply via a listbox there is no need for a search procedure involving the student number, because there will always be a link between the DB row and the listbox listindex - this simplifies coding enormously.

Hi torachan and sintek,

Yes I agree what you said above, it can become a mess. I just thought it would be less confusing to ask for help for each function of the userform separately (like delete a button that has been solved) and then piece them all together. As mentioned in my other post the workbook I have been sending here is just an example that I have to try to replicate according to how my main project workbook works. So this is also a bit tough from my side to do as I may not post my actual project workbook for IP reasons.

I tell you what I will do - I will replicate what my project workbook has and does as far as possible with the userform buttons, listboxes, etc and all the code I have received and pieced together up until now to give you guys a bigger picture and then you can do your magic with the coding to have it working efficiently...will this be helpful?

I am just extremely grateful for all the help you guys on the forum give!

[Rudidw]

Hi All,

Ok I have attached a workbook that should give more clarity in a very simple way on the the bigger picture for my help requests.

I have tried to replicate the most important parts of my main project workbook to make the userform to work as intended. This example workbook shared here shows everything I need have done but is just a very "watered down" simple version of my actual project workbook but it functions the same.

So the basic IPO (Input - Process - Output) Model as a Systems Engineer would put it for my main project workbook is as follows:
Input - Multiple Input sheets (about 16 of them) are used to capture 772 input fields over the multiple Input sheets. Unfortunately this is a requirement and cannot be changed.
Process - Data is saved and retrieved from a database sheet ("DB") to and from the multiple Input sheets. There is a single Control sheet (not included/required in example workbook) that is linked to all the Input sheets and all calculations are then done within the Control sheet with all the input variables that are provided by the user. In this example workbook I have included 772 empty columns to replicate main project and these "extra" columns cannot be deleted as they will be used in main project.
Output - Some important Input variables are used and the calculation answers are printed out to a Report.

So this brings in the userform that I need help with. The function of the userform is not to capture any data (this is done on the multiple Input sheets), but to be a quick interface for the user to activate a brand new entry or to search for a specific entry captured previously that is stored in the "DB" by using 3 different ways:
1. Click on "All Students" button and select the entry you want
2. Click on "Current Students" button which will show only current students and select one of those or
3. Do a quick search in the Search field if there is a lot of "Student" entries stored in the "DB" sheet because the number entries (rows) will be forever increasing over time.

When one of the 3 ways mentioned above has been used, The listbox ("Resultbox") will list all entries in the "DB" which have a specific "Student ID" and a few specific columns will be shown in this "Resultbox. When you click on a entry in the "Resultbox" then additional info about that entry will be displayed in Listbox 2, a photo of that entry will be displayed above that in "PicBox" and the "Student Number" displayed in "ProID" box.

The parts of the userform I currently need help with is:
a. When I click the "New Student" button on the userform it must look what the last "Student Number" was in The "DB" "Lastrow", open Input 1 sheet and add the new student number to cell "C5" in Input 1 sheet and clear all existing input fields on Input 1 and Input 2 sheets to prepare for new data capture of the "New Student"
b. On the userform when I select a "Student" in "Resultbox" and click "Load Student Info" button, then it must transfer all info stored on the "DB" for that specific student to the multiple Input sheets. In this example sheet it is 17 input fields that need to be populated but on my project workbook it will be 772 input field over 16 sheets that would be populated. Currently I use a button in the "DB" sheet to do this that uses the activerow function (code is in Module2), but would like to do it with the "Load Student Info" button on the userform.
Not on Userform but on Input 1 sheet:
c. Lastly as per this thread heading - Save data from the multiple Input sheets to "DB" sheet by referring to the specific "Student Number". So if it is a new "Student" then all data will be saved to a new row (last row) and if it is existing student data that was changed then all changes will be saved back to the "DB" on the row of that student with the same student number in column "A" of the "DB".

Thanks for the help!

[sintek]

What 772 entries per student ????
What person can have that much information...

[Rudidw]

What 772 entries per student ????
What person can have that much information...

Haha, I don't actually work with students it is just as an example I use for you guys to try to understand what I am trying to do with my example workbooks I share here. That is why I write "Students" it is not really students or employees etc. I could also have used cars...I'm sure there could be a few thousand entries there when you start drilling down to every type of screw, bolt and nut used per make and model of each car on the road! "Student" is the example I used but could be anything. There is a method to my madness - trust me

[torachan]

I wish you well with your endevours.
However I have managed to maintain my sanity (just) with over 40 years of various programming development before retiring.
I have no intention of finally losing it in my twilight years.
I will pass on this challenge.
Wishing you well.
torachan.

[sintek]

Sadly, this System was not thought out...Just a whole lotta wrong...It is going to require a lot of patch code...

Have no idea why CLASS is not also on Input 1 Sheet as it follows in Column Order on DB Sheet????
For that matter all the columns are inconsistent...bad setup...Which is gonna cause a lot of unnecessary code manipulation...
Anyway this will get you started...I deleted the unused columns as it was inserted to explain your actual file setup anyway...

a. When I click the "New Student" button on the userform it must look what the last "Student Number" was in The "DB" "Lastrow", open Input 1 sheet and add the new student number to cell "C5" in Input 1 sheet and clear all existing input fields on Input 1 and Input 2 sheets to prepare for new data capture of the "New Student"

Please Login or Register  to view this content.

c. Lastly as per this thread heading - Save data from the multiple Input sheets to "DB" sheet by referring to the specific "Student Number". So if it is a new "Student" then all data will be saved to a new row (last row) and if it is existing student data that was changed then all changes will be saved back to the "DB" on the row of that student with the same student number in column "A" of the "DB".

Please Login or Register  to view this content.

b. On the userform when I select a "Student" in "Resultbox" and click "Load Student Info" button, then it must transfer all info stored on the "DB" for that specific student to the multiple Input sheets.

As you have hidden columns in your Listbox...One is not able to reference the columns for this code snippet...
This only does first Input sheet...Amend the code for second Input sheet i.e. change colNumbers and ranges etc

Please Login or Register  to view this content.

Anyway, that is my patch code for you...if the database columns corresponded in continuous order with all the Input sheets, Coding would have been a lot more simplistic

[Rudidw]

Have no idea why CLASS is not also on Input 1 Sheet as it follows in Column Order on DB Sheet????
For that matter all the columns are inconsistent...bad setup...Which is gonna cause a lot of unnecessary code manipulation...

Thanks for the awesome help so far!!! I bet you are gatvol of this now but I feel we are getting so close to a solution now!

We can move a few columns around if you want and if that will make life easier for you with the coding?!

Please Login or Register  to view this content.

Thanks for this!

A problem I picked up and wanted to ask about...I started to delete the DB entries to see what would happen if another user started deleting the entries also. By doing this I started getting errors when there was only one line populated in the DB when I clicked on "Add New Student", "All Students" and "Current Students" buttons at this point. I was able to sort out "Current Students" error by adding a On Error Resume Next line to that code and it displayed it properly in the "ResultBox". I did the same to "All Students" button code and the "ResultBox" layout that was displayed was not right. Is there a simple way to sort out these errors?

With "Add New Student" button it does not enter in a new student number if the DB is empty as it can't determine what the next number should be...Is there a way to create a student number if the DB is empty to get around this?

Please Login or Register  to view this content.

Can you move around the columns in the DB that would work best for you and then show me how the code would look like to "Save" both Input Sheets data to the DB?

Please Login or Register  to view this content.

So I assume that my code in module 2 will not work with you code here?

Anyway, that is my patch code for you...if the database columns corresponded in continuous order with all the Input sheets, Coding would have been a lot more simplistic

I am really interested to see what the code would look like if you did move the columns around as you suggest...so you have free will to do so! Maybe this will sort out all the issues once and for all Please show me the coding for it to work with both input sheets please, so that I can reduplicate what you did for other input sheets in my project workbook...remember you are dealing with a noob here when it comes to VBA coding.

Thanks again for the help and the patience!

[torachan]

I cannot help looking on in amazement.
Solve all your problems with one simple userform and layout your data columns to sequence with your input boxes will enable you to minimise your coding.
You have an example to work with in previous postings.
torachan

[sintek]

Solve all your problems with one simple userform and layout your data columns to sequence with your input boxes will enable you to minimise your coding.

I agree...Go back to the drawing board...I would...

so that I can reduplicate what you did for other input sheets in my project workbook...remember you are dealing with a noob here when it comes to VBA coding.

Even if we did that...You would not be able to replicate for all the other Input Sheets unless they were exactly the same...

As far as your errors...Go back to your previous posts where the code was received and address the errors with the members who provided the code...
This scenario was obviously not taken into account when you asked for assistance with code...

[Rudidw]

I cannot help looking on in amazement.
Solve all your problems with one simple userform and layout your data columns to sequence with your input boxes will enable you to minimise your coding.
You have an example to work with in previous postings.
torachan

I agree...Go back to the drawing board...I would...

Hi...apologies for only replying now - been a hectic week with month end and all!

I understand you are trying to help my situation by trying to "sell" me on the idea to rather use a userform (like torachan's example) to do both data capture and searching, rather than what I have am using now and I appreciate the concern but it is not going to work and is not required for what I am currently working on in my actual project workbook. I have saved your example workbook and I definitely think I can/should use it in another project but I just cant't use it with what I am busy with now.

What you both are suggesting by "go back to the drawing board" or using torachan's userform example is like going to the BMW M5 dashboard (my design) designers after the car has been built and telling them to now go design, manufacture and install a Tesla Model 3 type dashboard (your design) into the BMW M5. It just is not practical now and was not the original requirement.

My userform "dashboard" is supposed to be a simple one with a simple "touch screen" (Resultbox) to do only searching functions and a few buttons to activate other functions, not a Tesla Model 3 dash (like yours) that does search and data capture in one place. To do these type of changes at this stage of my project is just not feasible!

The problem I had was with a button on the userform that did not work but I was able to figure it out and this is now sorted out with the Private Sub cmdLoad_Click() code that sintek provided above...works great thanks!

However my original issue as per this thread heading describes still remains. I don't understand the the code for Sub RoundedRectangle2_Click() and I don't know how to extend it to work with "Input 2" sheet and beyond?

[AliGW]

A bit of friendly and well-meant advice:

There is a limit to the time members will be prepared to invest in your problem. If they feel that satisfying your requirements and ignoring the inevitable pitfalls that ensue would ultimately lead to their having to spend even more time helping you to iron out the problems they, in their wisdom from experience have anticipated, then they may decide to step away and not help at all. That's their prerogative.

I am not saying that they or anybody else will not help you, just that you should perhaps be a bit more willing to compromise, especially when you have been handed a better way of doing things on a plate and for free.

Just bear in mind that this forum is based on goodwill, and that works both ways.

[sintek]

As mentioned...it would be easier to keep column in contiguous order... i.e

Look at Input 1

Student Number is column A in DB
Name is Col B
Last name is Col C
Id - Col D
Tax No Col F
cell number Col I
Email Col J
Address Col G

etc etc ...Because of this one needs to build the populating of the database from non contiguous ranges...

If they were in order i.e A,B,C,D,E,F,G,H,I,J etc one could just populate the row with an array of all the user input sheets...At Once...


This populates DB with info from Input 1 sheet - Semi contiguous

Please Login or Register  to view this content.

Because Input 2 sheet is noyt in order of columns in either the sheet or DB you will have to build an array and loop through it...to populate database...
See code above in

Please Login or Register  to view this content.

So our suggestions were...

Input 1 sheet Have Columns A to J in order
Input 2 sheet have Columns K to R in order
Input 3 sheet have Columns S to Z in order etc etc etc

Then you could easily populate your database row with an array of values and not have to build 2 arrays with the columns of each sheet to loop through...


So coming back to you replicating the code for all your other sheets...YOU will not be able to accomplish this as each input sheet would need it's own different code format...

[Rudidw]

As mentioned...it would be easier to keep column in contiguous order... i.e

Look at Input 1

Student Number is column A in DB
Name is Col B
Last name is Col C
Id - Col D
Tax No Col F
cell number Col I
Email Col J
Address Col G

Thnks for the reply sintek!

I thought it might be best to maybe to give more context so...In the attached pic you can see the layout of sheets 1,2,6,9 of my project workbook and you notice that the layout of each sheet is different and unfortunately needs to be this way for proper data capture that makes sense according to all the different variables that need to be captured per category in every sheet and this does mess with the DB sheet in some instances but not all. Attachment 689313

I have always been using this very simple code to save the input data from the all my various input sheets to the DB, but I always had to delete the row from the DB after all data was exported to the input sheets, and then after any changes were made to the input data sheets then use this code to move all data back to the DB to the last row. but remember my DB table had a few hundred rows to use this code, which was not ideal as you mentioned.

Please Login or Register  to view this content.

So if I can't use your code then I what I was hoping to maybe now is use my existing code but tweak it if possible, because it worked fine but now to include the function to over-wright the existing data in the DB by referring to the entry ID# ("student #), and only create a new line entry when a brand new entry is to be saved to the DB in the next row after the last row entry...is it possible to adjust my code to achieve this?

I came right with your

Please Login or Register  to view this content.

to move the data from the DB to input sheets but to over-wright existing DB data or to create a new line data entry is another story for me

Thanks again for the help!

[sintek]

Good luck then...

[Rudidw]

Good luck then...

Ok thanks anyway I will see if I can figure something out.

Thanks for all the help, I really appreciate it!