Update Multiple Rows per single Record

[onmyway]

Hi guys,

I have a challenge:

How can one modify this code to update a database (Sheet1) with multiple rows for a single record?

Details:
The UserForm has a "Detail/Description" section that will be the same for each "Row/Record". it is like a header section.
The "Detail/Description section must save with every row.
When one saves the record, the database must be updated with as many "Rows/"line items" as were captured. I.e., Each "line item" must create a new row in Sheet1.
Only when a "Row/line Item" has data must it be saved as a new row in the sheet. I.e, empty rows must not create unnecessary lines.

i have attached a sample workbook.

Thank you in advance for your input!

[Richard Buttrey]

Hi,

You'll need to upload a workbook with some data in it.

[onmyway]

Herewith. Added some sample data.

My actual application is much more complex and much bigger.

thanks for the help!

[onmyway]

Hi,

You'll need to upload a workbook with some data in it.

LOL - accidentally saved my initial sheet without macros! hope this is better.

[Norie]

Are you going to have a set of textboxes for each row?

[onmyway]

yes...I think so.

I KNOW there must be a better, easier method, I just don't know how or what.

[Richard Buttrey]

May I suggest a simpler method, see attached.

I often use this technique since it minimises the need for VBA. I also build in formula checks on the record entry row, e.g. COUNTA() to check whether all cells are complete and then read the value of that check cell into the macro and if it's not complete offer an message box and exit the sub.

[Norie]

If there's only ever going to be 3 rows then using textboxes is probably fine but if you want to have a dynamic number of rows I would suggest a listbox.

See the attached.

[onmyway]

Hi Richard,

I really love your method. The challenge I have, is that it must be captured via the UserForm, and it must be able to capture multiple records/rows at at time.

The sample workbook I attached, is a very small part of the complete application. This is only one of the UserForm functions.

[onmyway]

If there's only ever going to be 3 rows then using textboxes is probably fine but if you want to have a dynamic number of rows I would suggest a listbox.

See the attached.

i can't tell you what a big smile I have on my face right now.

You answered, what was going to be, my next question as well.

Very nice, thank you to ALL!

[onmyway]

Hi Norie,

One possible challenge: I need to be able to save the data of the textboxes to various places in the row, and not necessarily next to each other. I.e., TextBox1 data might be (row, 15), textBox2 data might be (row, 22).

How can i change these two lines of code to allow for that?

Please Login or Register  to view this content.

[onmyway]

Another question: Will I be able to "reverse" the data flow, by having the UserForm updated with the saved data from the Sheet? I.e., If this single record with multiple lines needs to be edited via the UserForm? And, will i be able to edit that data and re-save it? quite complex...

[Norie]

You would need to totally rewrite the code really but it wouldn't be too complicated.

The main difference would be that rather than a mass dump of the data from the listbox, as is happening here,

Please Login or Register  to view this content.

you would loop through the rows of the listbox.

Please Login or Register  to view this content.

[onmyway]

Great, thank you, Norie.

[onmyway]

Hi Norie,

My apologies for asking this question here, but it is very closely related to your previous response. Please let me know if I must start a new post for this.

How can this piece of code be edited to allow for TextBox names other than TextBox1; TextBox2; TextBox3 .... Names such as txtQTY; txtName etc.:

Please Login or Register  to view this content.

Thank you

[Norie]

You could just replace the loop with a separate statement for each textbox that assigns the value to the appropriate column in the listbox.

Or you could put the names, or the controls themselves in an array in the order they would appear in the listbox and loop through that.

Here's a (very bad) example.

Please Login or Register  to view this content.

[onmyway]

Hi Norie,

Would you mind showing me this method:

Please Login or Register  to view this content.

Thanks!

[Norie]

What are the names of your textboxes and which column in the listbox would they go in?

[onmyway]

It is various types of fields (taken from my Save function):

ws.Cells(iRow, 14).Value = Me.cbxRecordLocation1.Value
ws.Cells(iRow, 15).Value = Me.txtRecordRing1.Value
ws.Cells(iRow, 16).Value = Me.txtRecordHole1.Value
ws.Cells(iRow, 17).Value = Me.txtRecordDepth1.Value
ws.Cells(iRow, 21).Value = Me.cbxProductionCodes1.Value
ws.Cells(iRow, 24).Value = Me.cbxProductionDetail1.Value
ws.Cells(iRow, 18).Value = Me.txtRecordPump1.Value
ws.Cells(iRow, 19).Value = Me.txtRecordPerc1.Value
ws.Cells(iRow, 20).Value = Me.txtRecordDescription1.Value

They would go in the order they appear in the list, from top, being position 1 in the listbox, and bottom being listbox position 9

[Norie]

Here you are.

Please Login or Register  to view this content.

[onmyway]

Thank you!