Passing variable to UserForm textbox effort
I have a button on my spreadsheet. Behind that button I have a variable "I". I is set as a variant.
"I" is set to capture a specific row number on the spreadsheet. So let's say "I" = 20 . I have a userform
named "lstBox" and on that form I have a textbox named "txtRow". I want txtRow to be populated with
the value of "I" when I call it. The code below works fine until I initialize "lstBox".
If there is no code on "lstBox" UserForm_Initialize(), then [txtRow] is populated with 20 but as soon as I put code on UserForm_Initialize() the code below returns a "type mismatch" error on the first line of my code "lstBox![txtRow].Value = I".
Can someone please explain why?
[vba]For I = 20 To lastrow
lstBox![txtRow].Value = I
lstBox.Show[/vba]
Here is the code I have on the UserForm_Initialize()
[vba]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 Variant
myrow = txtRow.Text
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
wktype = "N/A"
wkstatus = "Active"
recCreated = Date
recCreatedBy = Application.UserName
dstore = "CSO"
pkey = Right(mySSN, 3) & Format(myDOB, "mmdd") & Left(lstName, 2)
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsContractor = wrkJet.OpenDatabase("M:\TODAYS_SERVER\Contractor_BE.mdb")
sql = "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) _
='" & fstName & "') AND ((tblContractorDataOnly.LName)='" & lstName & "')) OR _
(((tblContractorDataOnly.DOB)=#" & myDOB & "#) AND ((Right([tblContractorDataOnly].[Social Security],4)) _
=" & mySSN & ")) ORDER BY tblBackgoundReview.BICompleted DESC;"
Set rst = dbsContractor.OpenRecordset(sql, dbOpenDynaset)
ListBox1.ColumnCount = rst.Fields.Count
'header
For Each f In rst.Fields
MsgBox f.Name
myArray(0, myCounter) = f.Name
myCounter = myCounter + 1
Next
'Body
rst.MoveLast
rst.MoveFirst
myCounter = 1
Do
myArray(myCounter, 0) = rst![ID]
myArray(myCounter, 1) = rst![FName]
myArray(myCounter, 2) = rst![LName]
myArray(myCounter, 3) = rst![DOB]
myArray(myCounter, 4) = rst![SSN]
myArray(myCounter, 5) = rst![Company]
myArray(myCounter, 6) = rst![Data_Store]
myArray(myCounter, 7) = rst![Status]
myArray(myCounter, 7) = rst![Restricted]
myArray(myCounter, 8) = rst![BIStatus]
myArray(myCounter, 9) = rst![bicompleted]
myArray(myCounter, 10) = rst![ExceptionStatus]
rst.MoveNext
myCounter = myCounter + 1
Loop Until rst.EOF = True
ListBox1.List() = myArray
rst.Close
dbsContractor.Close
Set rst = Nothing
Set dbsContractor = Nothing
[/vba]
