passing textbox values between forms

[bigfatpies]

I have a very standard DB called CUSTdb) wth cust name, address, etc,etc... in columns C through J.
(In column A & B, is a unique "record number", and a datestamp)

The above data is populated straight from a simple userform (UFnewcust), (launched by a cmd button called newcust)
When i press the command button, this data goes to the spreadsheets, and then launches the next userform in the process...UFsurvey

when this new form initialises, the unique record number needs to be collected from the CUSTdb... and is ALWAYS going to be on the top right hand corner of every userform I subsequently launch, until the process is finally finished and the record is fully populated.

I will have another "modify existing cust" button to deal with later...

So, in summary, assuming im in this "newcust" process... I will build up the record with several userforms, each adding yet more and more data to the record
ie; UFsurvey will collect and add 5 more columns, then subsequent forms might add 3,4,or u to 10 columns.... and by pressing the command button, I need it to "append" the data ont the record.. NOT below it, but tagged onto the end of the unique "record number"

any help / pointers appreciated...
or am i going about this the wrong way...?
generally, im just going to be using checkboxes which will cause a corresponding textbox on the form to show a £value... if I change the prices I will be editing it "hard" in the code, instead of updating a list... cos that got too difficult...

Later I will use a form that goes in and uses a whole load of vlookups to get data that has £values > 0, and creates a kind of invoice.. that bit I have working with a dummy record that I created....

kind regards

[sailor321]

hi,

why don't you just use ONE userform to populate the record with all data instead of appending stuff?
That would be also possible, but why make it complicated ?

[bigfatpies]

Hi Sailor..

yes.. considered that, but the issue is 2-fold.

first of all, I sit in front of the customer at their home, and go through a kind of presentation, and having one big userform with upwards of 100 options might get really fussy... so having nice neat userform pop up relevent to that specific part of the quote is quite important; ie the name & adress, then the surveys that they will require, then the building info (removing existing stuctures, building work, walling options, drains works, etc etc) then the product options, the fitting options, then electrical, plastering, plumbing, flooring, landscaping... it goes on and on...
so I use each userform to include pictures, and tabstrips to help explain things as I go through each form....

I am happy designing the forms and sending all the data to seperate spreadsheets, but I need a way to glue all the record data togther, so that when I recall it later, it can be summarised, as you say, on one form....

knd r

[romperstomper]

You can pass the values directly from one form to another like this:

Load someotherform
someotherform.textbox1.text = me.textbox1.text
someotherform.show

although I tend to agree that this sounds like a candidate for one form with a multipage.

[bigfatpies]

Rory,
if I used one form, with multipages, how would I call up the specific pages ?

this is how I currently get my unique record number and date...
basically go to last row (that has content) and add 1...

Private Sub UserForm_Initialize()
Dim irow As Long
Dim ws As Worksheet
Me.Width = 340
Set ws = Worksheets("CUSTdb")
'find last row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Me.reg1.Value = ws.Cells(irow, 1) + 1
Me.reg2.Value = Format(Date, "mm/dd/yyyy")


End Sub

How do I set a variable to "lock" that record number throughout the userform data collection so that I can just use textbox_record_num.value = xxxx
where xxx is the formula to go get the record that i am actually working on...



so how do I now call

[romperstomper]

You'd only record the number once using code like the line you just posted. You then switch between pages of the multipage by incrementing its value:

Me.Multipage1.Value = 1

for example would select the second page (the indexing starts at 0).

[sailor321]

if you want to deal with one recordnumber throughout the multipages, just load it into a public variable.
It will keep ist value from different subs, functions, forms or modules - until you change it.

this way you would end up with as many tables as you have multipages with recordnumbers corresponding.

You can add data to the end of a table (say page 1) by searching for the right page and recordnumber. then search for the right column (check for the text of the first line = headers) and paste your data as range. then go to the next multipage-page and do the same, and so on.

This way you can make a complete table from the data, but it will take some time to execute.