2
Here is what I am trying to do. Kindly, help me.
1. I'm creating a query dynamically based on User input using VBA
(strSQL and DotSQL in the code below)
2. Executing the created query with in VBA
3. Writing to a table (tblRandom) the values from the recordset.
--------------------------------
Private Sub append_record_C lick()
Dim strSQL As String
Dim dotsql As String
Dim inp As Recordset
Dim test As Recordset
Dim how_many As Integer
Dim i As Integer
Dim bat_cnt As Integer
Dim cnn1 As New Connection
Dim companyID As Integer
Dim employeeID As Integer
cnn1.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source = D:\temp\DRUG_AL COHOL_DATA.MDB; "
'************** *************** *************** *************** ************
'This is the code if the test group is NOT For the 200* Consortium
'************** *************** *************** *************** ************
strSQL = "SELECT TOP " & how_many & " tblEMP.COMPANY_ ID, tblEMP.EMPLOYEE _ID, Rnd(tblEMP.EMPL OYEE_ID) AS RANDOM_NO " & _
"FROM tblEMP " & _
"WHERE ((tblEMP.COMPAN Y_ID = " & Me.COMPANY_ID & ") AND (tblEMP.INACTIV E_DATE Is Null) " & _
"and ((tblEMP.DOT_CL ASS) IS NULL OR (tblEMP.DOT_CLA SS) <> ""DOT"")) " & _
"ORDER BY Rnd(tblEMP.EMPL OYEE_ID);"
'DoCmd.RunSQL "CREATE TABLE tblTest ([EMPLOYEE_ID] " & _
'"CONSTRAINT ndxStaffID PRIMARY KEY, [FirstName] TEXT(25), " & _
'"[LastName] TEXT(30), [BirthDate] DATETIME);"
'************** *************** *************** *************** ************
'This is the code if the test group IS For the 200* Consortium
'************** *************** *************** *************** ************
dotsql = "SELECT TOP " & how_many & " tblCOMPANY.COMP ANY_ID, tblEMPLOYEE.EMP LOYEE_ID, Rnd(tblEMPLOYEE .EMPLOYEE_ID) AS RANDOM_NO" & _
" FROM (tblCOMPANY INNER JOIN tblCOMPANY_TEST ON tblCOMPANY.COMP ANY_ID = tblCOMPANY_TEST .COMPANY_ID) " & _
"INNER JOIN tblEMPLOYEE ON tblCOMPANY.COMP ANY_ID = tblEMPLOYEE.COM PANY_ID" & _
" WHERE (((tblCOMPANY_T EST.TEST_GROUP_ NAME)= " & Me.CONSORTIUM_N AME & ") AND ((tblEMPLOYEE.D OT_CLASS)= ""DOT"") " & _
"AND ((tblEMPLOYEE.I NACTIVE_DATE) Is Null)) " & _
"ORDER BY Rnd(tblEMPLOYEE .EMPLOYEE_ID)"
Set inp = New ADODB.Recordset
inp.CursorType = adOpenKeyset
inp.LockType = adLockOptimisti c
‘'************* *************** *************** *************** ************
' If the test is for the 200* Consortium, then use the dot sql statment, else use the other
'************** *************** *************** *************** ***********
If Me.COMPANY_ID.V alue = 122 Then
inp.Open dotsql, cnn1
Else
inp.Open strSQL, cnn1
End If
Set test = New ADODB.Recordset
test.CursorType = adOpenKeyset
test.LockType = adLockOptimisti c
test.Open "tblQRTLY_TESTI NG", cnn1
'New Declarations
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim InsertSQL As String
Dim cnCh5 As ADODB.Connectio n
Set cnCh5 = New ADODB.Connectio n
cnCh5.Open cnn1
'Dim the objects
Dim tdf As ADOX.Table
Dim idx As ADOX.Index
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConne ction = CurrentProject. Connection
Set tdf = New ADOX.Table
With tdf
.Name = "tblRandom"
Set .ParentCatalog = cat
.Columns.Append "", adInteger
.Columns
‘-------------------------------------------------------------------------------------
‘Loop thorough the Recordset and build a new table
‘-------------------------------------------------------------------------------------
i = 1
inp.MoveFirst
Do Until inp.EOF
MsgBox (" In loop")
test.AddNew
test!COMPANY_ID = inp!COMPANY_ID
MsgBox test!COMPANY_ID
test!EMPLOYEE_I D = inp!EMPLOYEE_ID
companyID = inp!COMPANY_ID
employeeID = inp!EMPLOYEE_ID
MsgBox test!EMPLOYEE_I D
test!TEST_GROUP = Me!TEST_GROUP
test!TEST_CATEG ORY = Me!TEST_CATEGOR Y
If Me!TEST_CATEGOR Y = "BAT" Then
test!TEST_REASO N = "Breath Alcohol"
Else
test!TEST_REASO N = "Random"
End If
test!QUARTER = Me!QUARTER
test!YEAR = Format(Now(), "YYYY")
test!SAMPLE_DAT E = Date
‘-----------------------------------------------------
‘SQL to execute to write into tblRandom
‘-------------------------------------------------------
InsertSQL = "INSERT INTO tblRandom ([EMPLOYEE_ID], [COMPANY_ID]) " & _
"VALUES (" & test!EMPLOYEE_I D & "," & test!COMPANY_ID & ");"
Set cmdCommand.Acti veConnection = cnCh5
cmdCommand.Comm andText = InsertSQL
cmdCommand.Exec ute
test.Update
inp.MoveNext
i = i + 1
Loop
MsgBox "You have successfully selected " & how_many & " Employees for " & Me!TEST_CATEGOR Y & " Testing!"
'Set rsRandom = New ADODB.Recordset
'With rsRandom
' .CursorType = adOpenKeyset
' .CursorLocation = adUseClient
' .LockType = adLockOptimisti c
' .Open "tblRandom" , cnCh5
'End With
inp.Close
test.Close
Set inp = Nothing
Set test = Nothing
End Sub
