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_Click()
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=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = D:\temp\DRUG_ALCOHOL_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.EMPLOYEE_ID) AS RANDOM_NO " & _
"FROM tblEMP " & _
"WHERE ((tblEMP.COMPANY_ID = " & Me.COMPANY_ID & ") AND (tblEMP.INACTIVE_DATE Is Null) " & _
"and ((tblEMP.DOT_CLASS) IS NULL OR (tblEMP.DOT_CLASS) <> ""DOT"")) " & _
"ORDER BY Rnd(tblEMP.EMPLOYEE_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.COMPANY_ID, tblEMPLOYEE.EMPLOYEE_ID, Rnd(tblEMPLOYEE.EMPLOYEE_ID) AS RANDOM_NO" & _
" FROM (tblCOMPANY INNER JOIN tblCOMPANY_TEST ON tblCOMPANY.COMPANY_ID = tblCOMPANY_TEST.COMPANY_ID) " & _
"INNER JOIN tblEMPLOYEE ON tblCOMPANY.COMPANY_ID = tblEMPLOYEE.COMPANY_ID" & _
" WHERE (((tblCOMPANY_TEST.TEST_GROUP_NAME)= " & Me.CONSORTIUM_NAME & ") AND ((tblEMPLOYEE.DOT_CLASS)= ""DOT"") " & _
"AND ((tblEMPLOYEE.INACTIVE_DATE) Is Null)) " & _
"ORDER BY Rnd(tblEMPLOYEE.EMPLOYEE_ID)"
Set inp = New ADODB.Recordset
inp.CursorType = adOpenKeyset
inp.LockType = adLockOptimistic
‘'************************************************ **********************
' If the test is for the 200* Consortium, then use the dot sql statment, else use the other
'************************************************* *********************
If Me.COMPANY_ID.Value = 122 Then
inp.Open dotsql, cnn1
Else
inp.Open strSQL, cnn1
End If
Set test = New ADODB.Recordset
test.CursorType = adOpenKeyset
test.LockType = adLockOptimistic
test.Open "tblQRTLY_TESTING", cnn1
'New Declarations
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim InsertSQL As String
Dim cnCh5 As ADODB.Connection
Set cnCh5 = New ADODB.Connection
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.ActiveConnection = 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_ID = inp!EMPLOYEE_ID
companyID = inp!COMPANY_ID
employeeID = inp!EMPLOYEE_ID
MsgBox test!EMPLOYEE_ID
test!TEST_GROUP = Me!TEST_GROUP
test!TEST_CATEGORY = Me!TEST_CATEGORY
If Me!TEST_CATEGORY = "BAT" Then
test!TEST_REASON = "Breath Alcohol"
Else
test!TEST_REASON = "Random"
End If
test!QUARTER = Me!QUARTER
test!YEAR = Format(Now(), "YYYY")
test!SAMPLE_DATE = Date
‘-----------------------------------------------------
‘SQL to execute to write into tblRandom
‘-------------------------------------------------------
InsertSQL = "INSERT INTO tblRandom ([EMPLOYEE_ID], [COMPANY_ID]) " & _
"VALUES (" & test!EMPLOYEE_ID & "," & test!COMPANY_ID & ");"
Set cmdCommand.ActiveConnection = cnCh5
cmdCommand.CommandText = InsertSQL
cmdCommand.Execute
test.Update
inp.MoveNext
i = i + 1
Loop
MsgBox "You have successfully selected " & how_many & " Employees for " & Me!TEST_CATEGORY & " Testing!"
'Set rsRandom = New ADODB.Recordset
'With rsRandom
' .CursorType = adOpenKeyset
' .CursorLocation = adUseClient
' .LockType = adLockOptimistic
' .Open "tblRandom", cnCh5
'End With
inp.Close
test.Close
Set inp = Nothing
Set test = Nothing
End Sub
