Pull excel 2013 DB records into a userform for editing, and re-submitting

[bigfatpies]

Hello everyone.

Im very new to VBA but have managed to cobble some fairly decent userforms together for collecting client data selection during an ordering process.
I was thinking about using a userform to pull in data from the simple excel db records, by maybe selecting an invoice no or customer name...
I have no clue how to go about this.. so appreciate it will be difficult to solve ... so im really looking for any guidelines/tips that i can go off and research...

On my userform Id like to have a textbox (lets say ; Job No)...that I can click on and then use arrow keys (not a scrollable list), so that i can flick through the historical records... .. and as the job number changes, all the other record fields would be pulled in instantly so I can quickly scan through them in a nice format,,,,and if I see ne Id like to change... I can simply manually change the data, and when I submit (with cmd button) it would re-write the correct data back to the DB...

as an example, assume there are just 10 fields per record ..(there are actually 70 or so) : "job No" followed by personal details, address, phone, email etc) so that on my userform (which im happy designing ) I have a listbox or combobox ?? that I can arrow through, and the other fields will be instantly populated... I will later look to expand the functionality so that when the order data is displayed, it references items by looking them up in a pricelist, and showing the order value....

Is this too generic a question ?? if so I apologize... but dont really know where to start... ill probably be able to pick up the gist of the solution pretty quick if someone can shoot me in the right direction !!!

Thanks in advance..

[mehmetcik]

Hi.

Forget about the text or combobox for scrolling through your invoices. use a spin button.
Link that to a textbox that will show your selected invoice
the spin button value will be the row in your database.

all your data should be in textboxes so that they are simple to load and save.
The following is untested but should work.


READ Data from excel to vba.

LR = spinbutton1.value
myarray = range(cells(LR,1),cells(LR,70)).value

Fill Texboxes

For Count = 1 To 70
'Load Text Boxes
Me.Controls("Textbox" & Count).Text = MyArray(1, Count)
Next

Write Texboxes to array

For Count = 1 To 70
'Load Text Boxes
MyArray(1, Count) = Me.Controls("Textbox" & Count).Text
Next

Write Data from Array to Excel

 range(cells(LR,1),cells(LR,70)).value = myarray

To tranfer Spinbutton value to text box.
You need to declare a variable at the top of your module

Dim ChangeFlag as integer

private sub spinbutton1_change()
if changeflag = 1 then exit sub
Changeflag = 1
textbox1.value = Spinbutton1.value
changeflag = 0
end sub

To allow direct input into textbox

private sub Textbox1_change()
if changeflag = 1 then exit sub
Changeflag = 1
Spinbutton1.value = textbox1.value
changeflag = 0
end sub