Problems with UserForm_Initialize

[teel73]

I am trying to set the value of a text control when I show my userform. The value that I'm trying to set the control to comes from a variable that's on another user form. So for instance: I have two UserForms ... (UserForm_1) and (UserForm_2)

on UserForm_1, I cycle thru the rows and capture the row number then I set the value of [txtRow] which is a text control on UserForm_2 to the row number and then I show UserForm_2. The UserForm opens with the number 20 in the control named [txtRow]. That works just fine.

Private Sub cmdUpdateDB_Click() 
dim myRow as variant
 
lastrow = Cells(Rows.Count, "b").End(xlUp).Row
 
For I = 20 To lastrow
    myRow = I
    UserForm_2![txtRow].Value = myRow
    UserForm_2.Show
 
Next

But when I try to initialize UserForm_2 it gives me problems. For instance, I have some variables on UserForm_2 that I need to initialize. One of the varialbles I'm trying to set to [txtRow].value which should be 20 that was passed from "myRow" on UserForm_1. But when I run the code it comes back with an error "Type mismatch". Here's my code:

Private Sub UserForm2_Initialize()

Dim wrkJet As Workspace
Dim dbsContractor As Database
Dim rst As Recordset
Dim sql As String
Dim rcArray As Variant
Dim fstName, lstName, myDOB, mySSN, myVendor, midName As String
Dim wktype, wkstatus, recCreated, recCreatedBy, dstore As String
Dim pkey As Variant
Dim lstRow As String
Dim qdfBIrec As String
Dim f As Field
Dim myCounter As Long: myCounter = 0
Dim myArray(999, 999)
Dim myrow As Long
'
myrow = txtRow.Value
'
fstName = Cells(myrow, "b").Value
lstName = Cells(myrow, "f").Value
mySSN = Cells(myrow, "i").Value
myDOB = Cells(myrow, "j").Value
myVendor = Cells(myrow, "k").Value
midName = Cells(myrow, "e").Value

 

[teel73]

OK .. forget my last question. I figured out how to populate my listbox. But now my problem is : I have a for next loop and within that loop I show the userform with the listbox. After the user completes an action on that userform I need to go back to continue with the "next" row in my for next. How do I transfer back to finish out the For Next code ?

            'SETUP UserForm lstBox ListBox values
            
            lstBox![ExistingName].Value = "A BI record may already exist for " & ctrfname & " " & ctrlname & ".  Please check the names below to verify."
            
            lstboxSql = "SELECT tblContractorDataOnly.Id, tblContractorDataOnly.FName, tblContractorDataOnly.LName, tblContractorDataOnly.DOB, Right([tblContractorDataOnly].[Social Security],4) AS SSN, tblContractorDataOnly.[Company], tblContractorDataOnly.[Data_Store], tblContractorDataOnly.[Status], Format([tblContractorDataOnly].[Restricted],'Yes/No') AS Restricted, tblBackgoundReview.BIStatus, tblBackgoundReview.BICompleted, tblBackgoundReview.ExceptionStatus " _
                & "FROM tblContractorDataOnly INNER JOIN tblBackgoundReview ON tblContractorDataOnly.Id = tblBackgoundReview.Data_ID WHERE (((tblContractorDataOnly.FName)='" & ctrfname & "') AND ((tblContractorDataOnly.LName)='" & ctrlname & "')) OR (((tblContractorDataOnly.DOB)=#" & ctrDOB & "#) AND ((Right([tblContractorDataOnly].[Social Security],4))=" & ctrSSN & ")) ORDER BY tblBackgoundReview.BICompleted  DESC;"
            
            
            
            Set lstboxRst = dbsContractorsBI.OpenRecordset(lstboxSql, dbOpenDynaset)
            
            
            
            lstBox!ListBox1.ColumnCount = lstboxRst.Fields.Count
            For Each f In lstboxRst.Fields
                myArray(0, myCounter) = f.Name
                myCounter = myCounter + 1
            Next

            lstboxRst.MoveLast
            lstboxRst.MoveFirst
            myCounter = 1
            Do
                myArray(myCounter, 0) = lstboxRst![ID]
                myArray(myCounter, 1) = lstboxRst![FName]
                myArray(myCounter, 2) = lstboxRst![LName]
                myArray(myCounter, 3) = lstboxRst![DOB]
                myArray(myCounter, 4) = lstboxRst![SSN]
                myArray(myCounter, 5) = lstboxRst![Company]
                myArray(myCounter, 6) = lstboxRst![Data_Store]
                myArray(myCounter, 7) = lstboxRst![Status]
                myArray(myCounter, 7) = lstboxRst![Restricted]
                myArray(myCounter, 8) = lstboxRst![BIStatus]
                myArray(myCounter, 9) = lstboxRst![bicompleted]
                myArray(myCounter, 10) = lstboxRst![ExceptionStatus]
                lstboxRst.MoveNext
                myCounter = myCounter + 1
            Loop Until lstboxRst.EOF = True
            lstBox!ListBox1.List() = myArray
            
            
            'lstboxRst.Close
            'dbsContractor.Close
            'Set lstboxRst = Nothing
            'Set dbsContractor = Nothing
            
            lstBox.Show