Checking Horizontally and vertically using ADO?
For the record, i am using ADODB, with VBA in MS Access 2002
Current Code:
VB Code:
Private Sub personCalculations()
'*******************************************************
'Opens the database and then opens the Workers table
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\TSmith14\Desktop\test.mdb"
cn.Open
Set rs = New ADODB.Recordset
rs.Open "Workers", cn, adOpenKeyset, adLockPessimistic, adCmdTable
'********************************************************
Do While rs.EOF = False
'********************************************************
'Finds the academic payamount by comparing Current Payband from the
'Workers table, to the salary with the payband located in AcademicPayband
If rs.Fields("Group") = "Academic" Then
Set RsPayband = New ADODB.Recordset
RsPayband.Open "AcademicPayband", cn, adOpenKeyset, adLockPessimistic, adCmdTable
Do While RsPayband.EOF = False
If RsPayband.Fields("Payband") = rs.Fields("Current Payband") Then
academictotals = academictotals + RsPayband.Fields("Salary")
End If
RsPayband.MoveNext
Loop
RsPayband.Close
'**********************************************************
ElseIf rs.Fields("Group") = "Support" Then
Set RsPayband = New ADODB.Recordset
RsPayband.Open "Support Paybands", cn, adOpenKeyset, adLockPessimistic, adCmdTable
Do While RsPayband.EOF = False
Loop
RsPayband.Close
End If
rs.MoveNext
Loop
rs.Close
Label8.Caption = academictotals
'********************************************************
End Sub
Okay, well the academicpayband can find the values very easily, because it is just going through the file in sequential order, but now I am trying to do supportpayband, in which i need to deal with a database like this
VB Code:
Fields: Payband, Level1, Level2, Level3, Level4, Level5, Level6
1 $##.## $##.## $##.## $##.## $##.## $##.##
2 $##.## $##.## $##.## $##.## $##.## $##.##
3 $##.## $##.## $##.## $##.## $##.## $##.##
4 $##.## $##.## $##.## $##.## $##.## $##.##
5 $##.## $##.## $##.## $##.## $##.## $##.##
***Sorry for the messy table, but I cant seem to get it right >.<***
Now I need to go through, match the payband, then, match the level, and grab the corresponding value ($##.##) from the table.
I have done multiple attempts at this, but it never seems to grab the value I want.
Could anyone give me some hints on this?
Re: Checking Horizontally and vertically using ADO?
Not to double post or anything, but I fixed my problem (I think, it seems it be working). But the thing is, I dont really know WHY its working... Could someone explain the part I highlighted??
VB Code:
Private Sub personCalculations()
totals = 0
'*******************************************************
'Opens the database and then opens the Workers table
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\TSmith14\Desktop\test.mdb"
cn.Open
Set rs = New ADODB.Recordset
rs.Open "Workers", cn, adOpenKeyset, adLockPessimistic, adCmdTable
rs.MoveFirst
'********************************************************
Do While rs.EOF = False
'********************************************************
'Finds the academic payamount by comparing Current Payband from the
'Workers table, to the salary with the payband located in AcademicPayband
If rs.Fields("Group") = "Academic" And Me![List4] = "Academic" Then
Set RsPayband = New ADODB.Recordset
RsPayband.Open "AcademicPayband", cn, adOpenKeyset, adLockPessimistic, adCmdTable
Do While RsPayband.EOF = False
If RsPayband.Fields("Payband") = rs.Fields("Current Payband") Then
totals = totals + RsPayband.Fields("Salary")
End If
RsPayband.MoveNext
Loop
RsPayband.Close
'**********************************************************
ElseIf rs.Fields("Group") = "Support" And Me![List4] = "Support" Then
Dim strLevel As String
Set RsPayband = New ADODB.Recordset
RsPayband.Open "SupportPaybands", cn, adOpenKeyset, adLockPessimistic, adCmdTable
RsPayband.MoveFirst
Do While RsPayband.EOF = False
strLevel = rs.Fields("Level")
If RsPayband.Fields("Payband") = rs.Fields("Current Payband") Then
If strLevel <> "0" Then
'****************************************************
If rs.Fields("Level") = rs.Fields("Level") Then
'****************************************************
Dim test As String
test = rs.Fields("Level")
totals = totals + RsPayband.Fields(test)
End If
End If
End If
RsPayband.MoveNext
Loop
RsPayband.Close
End If
rs.MoveNext
Loop
rs.Close
Label8.Caption = totals
'********************************************************
End Sub
I don't really get why that selects the proper column for me....
