talytech
02-05-2009, 08:19 AM
I have a huge problem that I can't seem to figure out. I hope I can explain this well. Here goes....
I have two user forms named CSSProcessing and frmLstBox.
On [CSSProcessing], I have a button called "Update" and behind my button is a For Next Loop that loops through the rows on my spreadsheet and checks to see if the data in the cells (which make up one record) already exist in a database that I'm connected to. If the record exists then I show that record in a listbox that's on [frmLstBox]. I setup the listbox values within the For Next Loop then I show [frmLstBox] with the record in the listbox. That seems to work fine.
But my problem is after I open the [frmListBox] and perform whatever action I am going to do, I need to transfer back to the For Next Loop on the [CSSProcessing] to check for the next row.
My question is .. how do I get it to transfer back to the for next loop or is that even possible?
Here is my code on [CSSProcessing] where I set up the listbox that's on [frmLstBox]... Please can somebody tell me what I'm doing wrong.
'CHECK FOR BI RECORD IN CONTRACTORS BI DATABASE
sqlBIRec = "SELECT tblContractorDataOnly.Id, tblContractorDataOnly.LName, tblContractorDataOnly.FName, tblContractorDataOnly.DOB, Right([tblContractorDataOnly].[Social Security],4) AS SSN, tblContractorDataOnly.Company, Format([tblcontractorDataOnly].[Restricted],'Yes/No') AS Restricted, tblBackgoundReview.BIStatus, tblBackgoundReview.BICompleted " _
& "FROM tblContractorDataOnly INNER JOIN tblBackgoundReview ON tblContractorDataOnly.Id = tblBackgoundReview.Data_ID " _
& "WHERE (((tblContractorDataOnly.LName) = '" & ctrlname & "') And ((tblContractorDataOnly.FName) = '" & ctrfname & "') or ((tblContractorDataOnly.DOB) = #" & ctrDOB & "#) And ((Right([tblContractorDataOnly].[Social Security], 4)) = " & ctrSSN & ")) " _
& "ORDER BY tblBackgoundReview.BICompleted DESC;"
Set rstBIContractors = dbsContractorsBI.OpenRecordset(sqlBIRec, dbOpenDynaset)
If rstBIContractors.BOF = True Then
'ADD RECORD TO EXCEPTION DATABASE
qdfNewStr = "INSERT INTO tblExceptions ( FName, LName, Mid, Last4_SSN, DOB, Vendor, chkAFM, chkNPI, chkCD, chkFR, RequestDate, HiringMgr_FName, HiringMgr_Mid, HiringMgr_LName, HiringMgr_userId, Approver_Fname, Approver_Mid, Approver_Lname, Approver_UID, WorkType, BIStatus, Status, DBUpdatedBy, CSO_approver, CSO_Date_approved, DBUpdated, ExceptionApproved ) " _
& "SELECT '" & ctrfname & "' AS Expr1, '" & ctrlname & "' AS Expr2, '" & ctrmid & "' AS Expr3, '" & ctrSSN & "' AS Expr4, #" & ctrDOB & "# AS Expr5, '" & ctrVendor & "' AS Expr6, '" & varchkAFM & "' AS Expr7, '" & varchkNPI & "' AS Expr8, '" & varchkCD & "' AS Expr9, '" & varchkFR & "' AS Expr10, #" & rqstDate & "# AS Expr11, '" & mgrFname & "' AS Expr12, '" & mgrMid & "' AS Expr13, '" & mgrLname & "' AS Expr14, '" & mgruserid & "' AS Expr15, '" & vpFname & "' AS Expr16, '" & vpMid & "' AS Expr17, '" & vpLname & "' AS Expr18, '" & vpUserID & "' AS Expr19, 'Contractor' AS Expr20, 'PENDING-BI CHK' AS Expr21, 'Active' AS Expr22, '" & csoApprover & "' AS Expr23, '" & csoApprover & "' AS Expr24, '" & csoDateApproved & "' AS Expr25, Date() AS Expr26, '" & exceptionAnswer & "' AS Expr27;"
dbsExceptions.Execute qdfNewStr
'GET EXCEPTION ID
rstException.MoveLast
rstException.Index = "UniqueID"
rstException.Seek "=", ctrSSN, ctrDOB
getExceptID = rstException![Ex_RequestID]
'ADD BI RECORD IN CONTRACTORS BI DATABASE
wktype = "RGL"
wkstatus = "Active"
dstore = "CSO"
pkey = Right(ctrSSN, 3) & Format(ctrDOB, "mmdd") & Left(ctrlname, 2)
qdfBIrec = "INSERT INTO tblContractorDataOnly ( FName, LName, Mid, WorkType, Company, [Social Security], DOB, Status, created, createdby, data_store, uniqueID )SELECT '" & ctrfname & "' AS Expr1, '" & ctrlname & "' AS Expr2, '" & ctrmid & "' AS Expr3, '" & wktype & "' AS Expr4, '" & ctrVendor & "' AS Expr16, '" & ctrSSN & "' AS Expr17, '" & ctrDOB & "' AS Expr18, '" & wkstatus & "' AS Expr5, '" & csoDateApproved & "' AS Expr6, '" & csoApprover & "' AS Expr7, '" & dstore & "' AS Expr9, '" & pkey & "' AS Expr10;"
dbsContractorsBI.Execute qdfBIrec
DoEvents
updBIrec = "INSERT INTO tblBackgoundReview ( Data_ID, Recvd_S85, Recvd_S86, Results, Recvd_Drug_screen, S85_comments, S86_comments, results_comments, drugscreen_comments, LastUpdated, LastUpdatedBy, BIStatus, ExceptionStatus, ExceptionCompleted, ExceptionID ) " _
& "SELECT tblContractorDataOnly.Id, 'NONE' AS s85stat, 'NONE' AS s86stat, 'NONE' AS resultstau, 'NONE' AS drugsceenstat, 'MISSING S85' AS s85comment, 'MISSING S86' AS S86comment, 'MISSING BACKGROUND INVESTIGATION' AS resultcomment, 'MISSING DRUG SCREEN' AS drugscreencomment, Date() AS lastUpdated, '" & csoDateApproved & "' AS LastUpdatedby, 'PENDING-BI CHK' AS Bistatus, '" & exceptionAnswer & "' AS ExcStat, Date() AS ExcComplete, '" & getExceptID & "' AS ExcID FROM tblContractorDataOnly WHERE (((tblContractorDataOnly.UniqueID)='" & pkey & "'));"
dbsContractorsBI.Execute updBIrec
Else 'if BI Record found
'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
End If
I have two user forms named CSSProcessing and frmLstBox.
On [CSSProcessing], I have a button called "Update" and behind my button is a For Next Loop that loops through the rows on my spreadsheet and checks to see if the data in the cells (which make up one record) already exist in a database that I'm connected to. If the record exists then I show that record in a listbox that's on [frmLstBox]. I setup the listbox values within the For Next Loop then I show [frmLstBox] with the record in the listbox. That seems to work fine.
But my problem is after I open the [frmListBox] and perform whatever action I am going to do, I need to transfer back to the For Next Loop on the [CSSProcessing] to check for the next row.
My question is .. how do I get it to transfer back to the for next loop or is that even possible?
Here is my code on [CSSProcessing] where I set up the listbox that's on [frmLstBox]... Please can somebody tell me what I'm doing wrong.
'CHECK FOR BI RECORD IN CONTRACTORS BI DATABASE
sqlBIRec = "SELECT tblContractorDataOnly.Id, tblContractorDataOnly.LName, tblContractorDataOnly.FName, tblContractorDataOnly.DOB, Right([tblContractorDataOnly].[Social Security],4) AS SSN, tblContractorDataOnly.Company, Format([tblcontractorDataOnly].[Restricted],'Yes/No') AS Restricted, tblBackgoundReview.BIStatus, tblBackgoundReview.BICompleted " _
& "FROM tblContractorDataOnly INNER JOIN tblBackgoundReview ON tblContractorDataOnly.Id = tblBackgoundReview.Data_ID " _
& "WHERE (((tblContractorDataOnly.LName) = '" & ctrlname & "') And ((tblContractorDataOnly.FName) = '" & ctrfname & "') or ((tblContractorDataOnly.DOB) = #" & ctrDOB & "#) And ((Right([tblContractorDataOnly].[Social Security], 4)) = " & ctrSSN & ")) " _
& "ORDER BY tblBackgoundReview.BICompleted DESC;"
Set rstBIContractors = dbsContractorsBI.OpenRecordset(sqlBIRec, dbOpenDynaset)
If rstBIContractors.BOF = True Then
'ADD RECORD TO EXCEPTION DATABASE
qdfNewStr = "INSERT INTO tblExceptions ( FName, LName, Mid, Last4_SSN, DOB, Vendor, chkAFM, chkNPI, chkCD, chkFR, RequestDate, HiringMgr_FName, HiringMgr_Mid, HiringMgr_LName, HiringMgr_userId, Approver_Fname, Approver_Mid, Approver_Lname, Approver_UID, WorkType, BIStatus, Status, DBUpdatedBy, CSO_approver, CSO_Date_approved, DBUpdated, ExceptionApproved ) " _
& "SELECT '" & ctrfname & "' AS Expr1, '" & ctrlname & "' AS Expr2, '" & ctrmid & "' AS Expr3, '" & ctrSSN & "' AS Expr4, #" & ctrDOB & "# AS Expr5, '" & ctrVendor & "' AS Expr6, '" & varchkAFM & "' AS Expr7, '" & varchkNPI & "' AS Expr8, '" & varchkCD & "' AS Expr9, '" & varchkFR & "' AS Expr10, #" & rqstDate & "# AS Expr11, '" & mgrFname & "' AS Expr12, '" & mgrMid & "' AS Expr13, '" & mgrLname & "' AS Expr14, '" & mgruserid & "' AS Expr15, '" & vpFname & "' AS Expr16, '" & vpMid & "' AS Expr17, '" & vpLname & "' AS Expr18, '" & vpUserID & "' AS Expr19, 'Contractor' AS Expr20, 'PENDING-BI CHK' AS Expr21, 'Active' AS Expr22, '" & csoApprover & "' AS Expr23, '" & csoApprover & "' AS Expr24, '" & csoDateApproved & "' AS Expr25, Date() AS Expr26, '" & exceptionAnswer & "' AS Expr27;"
dbsExceptions.Execute qdfNewStr
'GET EXCEPTION ID
rstException.MoveLast
rstException.Index = "UniqueID"
rstException.Seek "=", ctrSSN, ctrDOB
getExceptID = rstException![Ex_RequestID]
'ADD BI RECORD IN CONTRACTORS BI DATABASE
wktype = "RGL"
wkstatus = "Active"
dstore = "CSO"
pkey = Right(ctrSSN, 3) & Format(ctrDOB, "mmdd") & Left(ctrlname, 2)
qdfBIrec = "INSERT INTO tblContractorDataOnly ( FName, LName, Mid, WorkType, Company, [Social Security], DOB, Status, created, createdby, data_store, uniqueID )SELECT '" & ctrfname & "' AS Expr1, '" & ctrlname & "' AS Expr2, '" & ctrmid & "' AS Expr3, '" & wktype & "' AS Expr4, '" & ctrVendor & "' AS Expr16, '" & ctrSSN & "' AS Expr17, '" & ctrDOB & "' AS Expr18, '" & wkstatus & "' AS Expr5, '" & csoDateApproved & "' AS Expr6, '" & csoApprover & "' AS Expr7, '" & dstore & "' AS Expr9, '" & pkey & "' AS Expr10;"
dbsContractorsBI.Execute qdfBIrec
DoEvents
updBIrec = "INSERT INTO tblBackgoundReview ( Data_ID, Recvd_S85, Recvd_S86, Results, Recvd_Drug_screen, S85_comments, S86_comments, results_comments, drugscreen_comments, LastUpdated, LastUpdatedBy, BIStatus, ExceptionStatus, ExceptionCompleted, ExceptionID ) " _
& "SELECT tblContractorDataOnly.Id, 'NONE' AS s85stat, 'NONE' AS s86stat, 'NONE' AS resultstau, 'NONE' AS drugsceenstat, 'MISSING S85' AS s85comment, 'MISSING S86' AS S86comment, 'MISSING BACKGROUND INVESTIGATION' AS resultcomment, 'MISSING DRUG SCREEN' AS drugscreencomment, Date() AS lastUpdated, '" & csoDateApproved & "' AS LastUpdatedby, 'PENDING-BI CHK' AS Bistatus, '" & exceptionAnswer & "' AS ExcStat, Date() AS ExcComplete, '" & getExceptID & "' AS ExcID FROM tblContractorDataOnly WHERE (((tblContractorDataOnly.UniqueID)='" & pkey & "'));"
dbsContractorsBI.Execute updBIrec
Else 'if BI Record found
'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
End If