Solved

Continuous Records

Posted on 2014-12-09
22
63 Views
Last Modified: 2014-12-16
I have a query that I ran based off of two tables.  I cannot use the word "DISTINCT" or "GROUP BY" in my query..it will not have updateable fields.   I am currently trying to firgure out how to get rid of my repeating records on my form that is based off the query.  If someone can tell me how to write a SQL statement??
0
Comment
Question by:Jass Saini
  • 11
  • 7
  • 2
  • +1
22 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 40489644
what is your current query?
what is result from this?
what is repeating, and what fields do you want to update...

we are not magicians...
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40489649
questions..
why do have repeating records?

without seeing your db schema, i don't know what to suggest.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40489653
<we are not magicians... >   ;-)  i lost my crystal ball longtime ago
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40489685
Repeating records means that your query is based on parent and child tables (assuming your tables are designed correctly).

So, make a form with the parent table and add a subform to it for the child table. This would allow you to edit your main form and also allow you to edit select records in your subform if you need to.

Mike
0
 

Author Comment

by:Jass Saini
ID: 40489702
My current query show the repeating records as well...I know "we are not magicians".  I am new at access...I have been asking questions as needed and I know that you need more info.

I have two tables Final and OrgLook..My first form starts with the ORG table and I am querying that based off the Properties Sheet for each box....From there  this is my next query

SELECT  Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Org Name:], Final_Table.CostCen, Final_Table.[Fund:], Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.[BC1Change], Final_Table.[TotalBC1], Final_Table.[BC2Change], Final_Table.[TotalBC2], Final_Table.[BC3Change], Final_Table.[TotalBC3], Final_Table.[BC4Change], Final_Table.[TotalBC4]
FROM Final_Table LEFT JOIN dbo_tblOrgLook_master ON (Final_Table.PEC = dbo_tblOrgLook_master.PEC) AND (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter)
WHERE dbo_tblOrgLook_master.Analyst IN('A.Wade') AND dbo_tblOrgLook_master.Org IN('5300') AND dbo_tblOrgLook_master.CostCenter IN('0540000000') AND dbo_tblOrgLook_master.Fund IN('15G0020000') AND dbo_tblOrgLook_master.PEC IN('5020090');

I am taking this query and putting it into a form as I need inputs for Final_table.BC1Change to Final_Table.TotalBC4.

First of all since I can't use the distinct..I get repeating records in the query thus also the form...

And I can't input anything into the fields that I queried for Final_table.BC1Change to Final_Table.TotalBC4
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40489729
1. Make form "frmMaster"
2. Add any fields (you may want to edit or view)
3. Add to "frmMaster" a subform named "sfrmFinal"
4. Add to it fields you want to edit.
5. Link the subform as below.
      - Parent: dbo_tblOrgLook_master.PEC   --> Child: Final_Table.PEC     and
      - Parent: dbo_tblOrgLook_master.CostCenter --> Child: Final_Table.CostCen
0
 

Author Comment

by:Jass Saini
ID: 40489744
So both forms should have PEC and CostCen in order to link??
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40489750
Yes.
 - Parent: dbo_tblOrgLook_master.PEC   --> Child: Final_Table.PEC     and
      - Parent: dbo_tblOrgLook_master.CostCenter --> Child: Final_Table.CostCen


I do not know why your query had left join as shown below:

FROM Final_Table LEFT JOIN dbo_tblOrgLook_master

This seems to indicate that there are some records in Final_Table that may do not exist in dbo_tblOrgLook_master.

What this means to you, the you will not able to view the records from Final_Table (that do not have match in dbo_tblOrgLook_master).
0
 

Author Comment

by:Jass Saini
ID: 40489766
So I have repeating records in my query too.  I did what you wanted and still have repeating records and still can not input.
0
 

Author Comment

by:Jass Saini
ID: 40489771
I think that's where my problem is ...between how I linked the tables and the fact that I don't think the PEC and Costcenter are unique and I have told my boss..
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40489775
Did you really build forms frmMaster and sfrmFinal and linked them so quickly?

By using the subform, you need to trash the query. Instated of query, you will be using form/subform combination.
0
 

Author Comment

by:Jass Saini
ID: 40489777
I used the Form wizard..Why...
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40489782
also, try to change

FROM Final_Table LEFT JOIN dbo_tblOrgLook_master

to

FROM Final_Table INER JOIN dbo_tblOrgLook_master

to see how the query solution is working for you.
0
 

Author Comment

by:Jass Saini
ID: 40489785
Can I copy and Paste my code so you can see the first query and the second one.  I think that's where my problem is
0
 

Author Comment

by:Jass Saini
ID: 40489786
Private Sub chkAllInstitutions_AfterUpdate()
    Dim strQry As String, varItem As Variant
    
    If Me.chkAllInstitutions = True Then
        Me.chkAllOrg.Value = False
        
        Me.lstEmployeeID.Enabled = False
        strQry = "SELECT DISTINCT Org, OrgName, Division FROM dbo_tblOrgLook_master WHERE Division = 'Institutions' ORDER BY Org"
        Me.lstOrg.RowSource = strQry
        strQry = "SELECT DISTINCT CostCenter FROM dbo_tblOrgLook_master ORDER BY CostCenter"
        Me.lstCostCenter.RowSource = strQry
        strQry = "SELECT DISTINCT Fund FROM dbo_tblOrgLook_master ORDER BY Fund"
        Me.lstFund.RowSource = strQry
        strQry = "SELECT DISTINCT PEC FROM dbo_tblOrgLook_master ORDER BY Program"
        Me.lstPEC.RowSource = strQry
    Else
        Me.lstEmployeeID.Enabled = True
        
    End If
End Sub



Private Sub chkAllOrg_AfterUpdate()
Dim strQry As String, varItem As Variant
    
    If Me.chkAllOrg = True Then
        Me.chkAllInstitutions.Value = False
        
        Me.lstEmployeeID.Enabled = False
        strQry = "SELECT DISTINCT Org, OrgName FROM dbo_tblOrgLook_master ORDER BY Org"
        Me.lstOrg.RowSource = strQry
        strQry = "SELECT DISTINCT CostCenter FROM dbo_tblOrgLook_master ORDER BY CostCenter"
        Me.lstCostCenter.RowSource = strQry
        strQry = "SELECT DISTINCT Fund FROM dbo_tblOrgLook_master ORDER BY Fund"
        Me.lstFund.RowSource = strQry
        strQry = "SELECT DISTINCT PEC FROM dbo_tblOrgLook_master ORDER BY Program"
        Me.lstPEC.RowSource = strQry
    Else
        Me.lstEmployeeID.Enabled = True
    End If
End Sub

Private Sub cmdClear_Click()
    ClearlstAnalyst
    ClearlstOrg
    ClearlstCostCenter
    ClearlstFund
    ClearlstPEC
End Sub

Private Sub ClearlstAnalyst()
    Dim varItem As Variant
    
    For Each varItem In Me.lstEmployeeID.ItemsSelected
        Me.lstEmployeeID.Selected(varItem) = False
    Next varItem

End Sub

Private Sub ClearlstOrg()
    Dim varItem As Variant
    
    For Each varItem In Me.lstOrg.ItemsSelected
        Me.lstOrg.Selected(varItem) = False
    Next varItem
    
    Me.lstOrg.RowSource = ""
    
End Sub

Private Sub ClearlstCostCenter()
    Dim varItem As Variant
    
    For Each varItem In Me.lstCostCenter.ItemsSelected
        Me.lstCostCenter.Selected(varItem) = False
    Next varItem
    
    Me.lstCostCenter.RowSource = ""
    
End Sub

Private Sub ClearlstFund()
    Dim varItem As Variant
    
    For Each varItem In Me.lstFund.ItemsSelected
        Me.lstFund.Selected(varItem) = False
    Next varItem
    
    Me.lstFund.RowSource = ""
    
End Sub

Private Sub ClearlstPEC()
    Dim varItem As Variant
    
    For Each varItem In Me.lstPEC.ItemsSelected
        Me.lstPEC.Selected(varItem) = False
    Next varItem
    
    Me.lstPEC.RowSource = ""
    
End Sub

Private Sub cmdClose_Click()
Quit
End Sub


Private Sub Form_Load()
On Error GoTo Err_Handler
   
    Dim strDomainLoginID As String, strPosition As String, strQry As String, strFName As String
    Dim strUnitID As String, StrLName As String, strEmployeeID As String
    Dim oCon As New ADODB.Connection, oRS As New ADODB.Recordset
    
    strDomainLoginID = GetUserName()      'domain login id
    LoginName = strDomainLoginID
        
    Me.lstOrg.RowSource = ""
    Me.lstCostCenter.RowSource = ""
    Me.lstFund.RowSource = ""
    Me.lstPEC.RowSource = ""
    Me.cmdSearch.Enabled = False
    Me.chkAllInstitutions.Visible = False
    Me.chkAllInstitutions.Value = False
    Me.lblAllInstitutions.Visible = False
    Me.chkAllOrg.Visible = False
    Me.chkAllOrg.Value = False
    Me.lblAllOrg.Visible = False
    
                
    If Not OpenDBConnection(oCon, "Ado") Then Err.Raise 65000, , "Can not open db connection"
    
    
    strQry = "SELECT EmployeeID, PositionID, UnitID, FName, LName FROM Employee_tbl WHERE Active = 1 and DomainLogin = '" & strDomainLoginID & "'"
    oRS.Open strQry, oCon, adOpenStatic, adLockReadOnly
    
    strPosition = oRS!PositionID
    strEmployeeID = oRS!EmployeeID
    strFName = oRS!FName
    StrLName = oRS!LName
    strUnitID = oRS!UnitID
    oRS.Close
    
    

    
    If strUnitID = "Data" Or strUnitID = "Tech" Or strUnitID = "LAU" Then
        
        Me.txtWelcome = "Welcome " & strFName & " " & StrLName & ", " & strUnitID
        Me.lstEmployeeID.RowSourceType = "Table/Query"
        Me.lstEmployeeID.RowSource = "SELECT distinct EmployeeID FROM Employee_tbl WHERE active = 1 and UnitID <> 'Data'"
        Me.lstEmployeeID.Requery
        
        Me.chkAllInstitutions.Visible = True
        Me.chkAllInstitutions.Value = False
        Me.lblAllInstitutions.Visible = True
        
        Me.chkAllOrg.Visible = True
        Me.chkAllOrg.Value = False
        Me.lblAllOrg.Visible = True
    Else
        
        Select Case strPosition
            Case "All"
                Me.txtWelcome = "Welcome " & strFName & " " & StrLName & ", " & strUnitID
                Me.lstEmployeeID.RowSourceType = "Table/Query"
                Me.lstEmployeeID.RowSource = "SELECT distinct EmployeeID FROM Employee_tbl WHERE active = 1"
                Me.lstEmployeeID.Requery
                
                Me.chkAllInstitutions.Visible = True
                Me.chkAllInstitutions.Value = False
                Me.lblAllInstitutions.Visible = True
                
                Me.chkAllOrg.Visible = True
                Me.chkAllOrg.Value = False
                Me.lblAllOrg.Visible = True
                
            Case "Budget Analyst"
                Me.txtWelcome = "Welcome " & strFName & " " & StrLName
                strQry = "SELECT distinct M.EmployeeID FROM Employee_tbl E INNER JOIN Employee_tbl M on M.ManagerID = E.ManagerID" & vbCrLf
                strQry = strQry & "WHERE E.EmployeeID = '" & strEmployeeID & "'"
                Me.lstEmployeeID.RowSourceType = "Table/Query"
                Me.lstEmployeeID.RowSource = strQry
                Me.lstEmployeeID.Requery
                
                Me.chkAllInstitutions.Visible = False
                Me.chkAllInstitutions.Value = False
                Me.lblAllInstitutions.Visible = False
                
                Me.chkAllOrg.Value = False
                Me.lblAllOrg.Visible = False
    
            Case "Manager"
                Me.txtWelcome = "Welcome " & strFName & " " & StrLName & ", Mgr"
                                
                Me.lstEmployeeID.RowSourceType = "Table/Query"
                Me.lstEmployeeID.RowSource = "SELECT DISTINCT EmployeeID FROM Employee_tbl WHERE Active = 1 and ManagerID = '" & strEmployeeID & "'"
                Me.lstEmployeeID.Requery
                
                Me.chkAllInstitutions.Visible = True
                Me.chkAllInstitutions.Value = False
                Me.lblAllInstitutions.Visible = True
                               
                Me.chkAllOrg.Visible = True
                Me.chkAllOrg.Value = False
                Me.lblAllOrg.Visible = True
                
            Case Else
                DoCmd.Quit
        End Select
    End If

Exit_Proc:
    Exit Sub

Err_Handler:
    MsgBox "In Form_Load of " & Me.NAME & vbCrLf & Err.Number & "--" & Err.Description
    Resume Exit_Proc
End Sub

Private Sub lstComponent_AfterUpdate()

End Sub

Private Sub lstEmployeeID_AfterUpdate()

    Dim strEmployeeID As String, varList As Variant, iLen As Integer
    Dim strQry As String
    
    If Me.lstEmployeeID.ItemsSelected.Count > 0 Then
        
        strEmployeeID = ""
        Me.lstFund.RowSource = ""
        Me.lstPEC.RowSource = ""
        Me.cmdSearch.Enabled = False
        
        strEmployeeID = GetSelectedEmployeeID
        iLen = Len(Nz(strEmployeeID, ""))
        
        If iLen > 2 Then
            strQry = "SELECT DISTINCT O.Org, O.OrgName" & vbCrLf
            strQry = strQry & "FROM dbo_tblOrgLook_master AS O INNER JOIN Employee_tbl AS E ON O.Analyst = E.EmployeeID" & vbCrLf
            strQry = strQry & "WHERE EmployeeID in (" & strEmployeeID & ") " & vbCrLf
            strQry = strQry & "ORDER BY O.Org "
            Me.lstOrg.RowSource = strQry
            Me.lstOrg.RowSourceType = "Table/Query"
            Me.lstOrg.Requery
            Me.lstCostCenter.RowSource = ""
            Me.lstFund.RowSource = ""
            Me.lstPEC.RowSource = ""
        End If
    Else
        
        Me.lstOrg.RowSource = ""
        Me.lstCostCenter.RowSource = ""
        Me.lstFund.RowSource = ""
        Me.lstPEC.RowSource = ""
    End If
    
End Sub

Private Sub lstOrg_AfterUpdate()
     UpdateCostCenterListing
    
    If Me.lstOrg.ItemsSelected.Count > 0 Then
        Me.cmdSearch.Enabled = True
    Else
        Me.cmdSearch.Enabled = False
    End If
End Sub

Private Sub UpdateCostCenterListing()
    Dim strOrg As String, varList As Variant, iLen As Integer
    Dim strQry As String
    Dim strEmployeeID As String
    
    strOrg = Nz(GetSelectedOrg, "")

    If Me.chkAllInstitutions = True Then
        
        strQry = "SELECT DISTINCT CostCenter FROM dbo_tblOrgLook_master WHERE Inactive = 0 and Org In (" & strOrg & ")"
        Me.lstCostCenter.RowSource = strQry
        Me.lstCostCenter.RowSourceType = "Table/Query"
        Me.lstCostCenter.Requery
        
    
    Else
    
        strEmployeeID = Nz(GetSelectedEmployeeID, "")
        
        If Len(strEmployeeID) < 2 Then
            MsgBox "Please select an employee to display."
        Else
    
                    
            iLen = Len(Nz(strOrg, ""))
            
            If iLen > 2 Then
    '
                strQry = "SELECT DISTINCT CostCenter from dbo_tblOrgLook_master where Inactive = 0 and Analyst IN (" & strEmployeeID & ") and Org IN (" & strOrg & ")"
                Me.lstCostCenter.RowSource = strQry
                Me.lstCostCenter.RowSourceType = "Table/Query"
                Me.lstCostCenter.Requery
            End If
        End If
     End If
End Sub
Private Sub lstCostCenter_AfterUpdate()
     UpdateFundListing
    
    If Me.lstCostCenter.ItemsSelected.Count > 0 Then
        Me.cmdSearch.Enabled = True
    Else
        Me.cmdSearch.Enabled = False
    End If
End Sub
Private Sub UpdateFundListing()
    Dim strOrg As String, varList As Variant, iLen As Integer
    Dim strQry As String
    Dim strEmployeeID As String
    Dim strCostCenter As String
    
    strOrg = Nz(GetSelectedOrg, "")
    strCostCenter = Nz(GetSelectedCostCenter, "")

    If Me.chkAllInstitutions = True Then
        
        strQry = "SELECT DISTINCT Fund FROM dbo_tblOrgLook_master WHERE Inactive = 0 and Org In (" & strOrg & ")"
        Me.lstFund.RowSource = strQry
        Me.lstFund.RowSourceType = "Table/Query"
        Me.lstFund.Requery
        
    
    Else
    
        strEmployeeID = Nz(GetSelectedEmployeeID, "")
        
        If Len(strEmployeeID) < 2 Then
            MsgBox "Please select an employee to display."
        Else
    
                    
            iLen = Len(Nz(strOrg, ""))
            
            If iLen > 2 Then
    '
                strQry = "SELECT DISTINCT Fund FROM dbo_tblOrgLook_master WHERE Inactive = 0  and Analyst IN (" & strEmployeeID & ") and Org IN (" & strOrg & ")and CostCenter IN (" & strCostCenter & ")"
                Me.lstFund.RowSource = strQry
                Me.lstFund.RowSourceType = "Table/Query"
                Me.lstFund.Requery
            End If
        End If
     End If
End Sub
Private Sub lstFund_AfterUpdate()
    UpdatePECListing
    
    If Me.lstFund.ItemsSelected.Count > 0 Then
        Me.cmdSearch.Enabled = True
    Else
        Me.cmdSearch.Enabled = False
    End If
End Sub

Private Sub UpdatePECListing()
    Dim strOrg As String, varList As Variant, iLen As Integer
    Dim strQry As String
    Dim strEmployeeID As String
    Dim strCostCenter As String
    Dim strFund As String
    
    strOrg = Nz(GetSelectedOrg, "")
    strCostCenter = Nz(GetSelectedCostCenter, "")
    strFund = Nz(GetSelectedFund, "")

    If Me.chkAllInstitutions = True Then
     
        
        strQry = "SELECT DISTINCT PEC FROM dbo_tblOrgLook_master where Inactive = 0 and Org In (" & strOrg & ") and CostCenter IN (" & strCostCenter & ")and Fund IN (" & strFund & ")"
        Me.lstPEC.RowSource = strQry
        Me.lstPEC.RowSourceType = "Table/Query"
        Me.lstPEC.Requery
        
    
    Else
    
        strEmployeeID = Nz(GetSelectedEmployeeID, "")
        
        If Len(strEmployeeID) < 2 Then
            MsgBox "Please select an employee to display."
        Else
    
                    
            iLen = Len(Nz(strFund, ""))
            
            If iLen > 2 Then
    '
                strQry = "SELECT DISTINCT PEC FROM dbo_tblOrgLook_master WHERE Inactive = 0 and Analyst IN (" & strEmployeeID & ") and Org In (" & strOrg & ") and CostCenter IN (" & strCostCenter & ")and Fund IN (" & strFund & ")"
                Me.lstPEC.RowSource = strQry
                Me.lstPEC.RowSourceType = "Table/Query"
                Me.lstPEC.Requery
            End If
        End If
     End If
End Sub

Private Sub lstPEC_AfterUpdate()
    
    If Me.lstPEC.ItemsSelected.Count > 0 Then
        Me.cmdSearch.Enabled = True
    Else
        Me.cmdSearch.Enabled = False
    End If
End Sub

Private Function GetSelectedOrg() As String
    GetSelectedOrg = GetListBoxSelectedItem(Me.lstOrg, True, False)
End Function

Private Function GetSelectedEmployeeID() As String
    GetSelectedEmployeeID = GetListBoxSelectedItem(Me.lstEmployeeID, True, False)
End Function

Private Function GetSelectedCostCenter() As String
    GetSelectedCostCenter = GetListBoxSelectedItem(Me.lstCostCenter, True, False)
End Function
Private Function GetSelectedFund() As String
    GetSelectedFund = GetListBoxSelectedItem(Me.lstFund, True, False)
End Function

Private Function GetSelectedPEC() As String
    GetSelectedPEC = GetListBoxSelectedItem(Me.lstPEC, True, False)
End Function



Private Sub Save_Record_Click()
 
        Call querydef
        
        DoCmd.OutputTo acOutputQuery, "Allot_Q", "Excel97-Excel2003Workbook(*.xls)", "", True, "", , acExportQualityScreen
        
 End Sub

Open in new window


To help with page navigation I MOVED THE CODE INTO A CODE BLOCK.
PortletPaul
Topic Advisor
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40489788
re:> I used the Form wizard..Why...
I see.

Q1: what do you see on the main form? Do you see repeats?

Q2: What do you see on the sibform? Do you see repeates? If you do, this is what is expected.

Q3: What is the problem now?

Mike
0
 

Author Comment

by:Jass Saini
ID: 40489789
At the end it call for another query...

Function ClearList(lst As ListBox) As Boolean
    'Purpose:   Unselect all items in the listbox.
    'Return:    True if successful
    'Author:    Allen Browne. http://allenbrowne.com  June, 2006.
    Dim varItem As Variant

    If lst.MultiSelect = 0 Then
        lst = Null
    Else
        For Each varItem In lst.ItemsSelected
            lst.Selected(varItem) = False
        Next
    End If

    ClearList = True

End Function

Public Function SelectAll(lst As ListBox) As Boolean
    'Purpose:   Select all items in the multi-select list box.
    'Return:    True if successful
    'Author:    Allen Browne. http://allenbrowne.com  June, 2006.
    Dim lngRow As Long

    If lst.MultiSelect Then
        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True
        Next
        SelectAll = True
    End If

End Function

Public Sub querydef()
Dim db As Database, qdf As querydef, strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Allot_Q")


Analyst = ""
For Each Item In Forms!AllotSearch_frm!lstEmployeeID.ItemsSelected
    If Analyst = "" Then
        Analyst = "'" & Forms!AllotSearch_frm!lstEmployeeID.ItemData(Item) & "'"
    Else:
        Analyst = Analyst & " , '" & Forms!AllotSearch_frm!lstEmployeeID.ItemData(Item) & "'"
    End If
Next
Analyst = "IN(" & Analyst & ")"


Org = ""
For Each Item In Forms!AllotSearch_frm!lstOrg.ItemsSelected
    If Org = "" Then
        Org = "'" & Forms!AllotSearch_frm!lstOrg.ItemData(Item) & "'"
    Else:
        Org = Org & " , '" & Forms!AllotSearch_frm!lstOrg.ItemData(Item) & "'"
    End If
Next
Org = "IN(" & Org & ")"

CostCenter = ""
For Each Item In Forms!AllotSearch_frm!lstCostCenter.ItemsSelected
    If CostCenter = "" Then
        CostCenter = "'" & Forms!AllotSearch_frm!lstCostCenter.ItemData(Item) & "'"
    Else:
       CostCenter = CostCenter & " , '" & Forms!AllotSearch_frm!lstCostCenter.ItemData(Item) & "'"
    End If
Next
CostCenter = "IN(" & CostCenter & ")"

Fund = ""
For Each Item In Forms!AllotSearch_frm!lstFund.ItemsSelected
    If Fund = "" Then
        Fund = "'" & Forms!AllotSearch_frm!lstFund.ItemData(Item) & "'"
    Else:
        Fund = Fund & " , '" & Forms!AllotSearch_frm!lstFund.ItemData(Item) & "'"
    End If
Next
Fund = "IN(" & Fund & ")"

PEC = ""
For Each Item In Forms!AllotSearch_frm!lstPEC.ItemsSelected
    If PEC = "" Then
        PEC = "'" & Forms!AllotSearch_frm!lstPEC.ItemData(Item) & "'"
    Else:
        PEC = PEC & " , '" & Forms!AllotSearch_frm!lstPEC.ItemData(Item) & "'"
    End If
Next
PEC = "IN(" & PEC & ")"


strSQL = "SELECT  Final_Table.ID, dbo_tblOrgLook_master.Analyst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgName, dbo_tblOrgLook_master.CostCenter, dbo_tblOrgLook_master.Fund, dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.ProgramName, Final_Table.[Org Name:], Final_Table.CostCen, Final_Table.[Fund:], Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.[BC1Change], Final_Table.[TotalBC1], Final_Table.[BC2Change], Final_Table.[TotalBC2], Final_Table.[BC3Change], Final_Table.[TotalBC3], Final_Table.[BC4Change], Final_Table.[TotalBC4]"
strSQL = strSQL & "FROM Final_Table LEFT JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.CostCenter) AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) WHERE dbo_tblOrgLook_master.Analyst " & Analyst & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.CostCenter " & CostCenter & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC

qdf.SQL = strSQL

End Sub

Open in new window

0
 

Author Comment

by:Jass Saini
ID: 40489793
So on the subform there should be repeat records..The same record mulitple times???
0
 

Author Comment

by:Jass Saini
ID: 40489794
Sorry I am throwing so much at you..
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40489798
For lengthy codes like this use "Code" tag like:
rivate Sub chkAllInstitutions_AfterUpdate()
    Dim strQry As String, varItem As Variant
    
    If Me.chkAllInstitutions = True Then
        Me.chkAllOrg.Value = False
        
        Me.lstEmployeeID.Enabled = False
        strQry = "SELECT DISTINCT Org, OrgName, Division FROM dbo_tblOrgLook_master WHERE Division = 'Institutions' ORDER BY Org"
        Me.lstOrg.RowSource = strQry
        strQry = "SELECT DISTINCT CostCenter FROM dbo_tblOrgLook_master ORDER BY CostCenter"
        Me.lstCostCenter.RowSource = strQry
        strQry = "SELECT DISTINCT Fund FROM dbo_tblOrgLook_master ORDER BY Fund"
        Me.lstFund.RowSource = strQry
        strQry = "SELECT DISTINCT PEC FROM dbo_tblOrgLook_master ORDER BY Program"
        Me.lstPEC.RowSource = strQry
    Else
        Me.lstEmployeeID.Enabled = True
        
    End If
End Sub



Private Sub chkAllOrg_AfterUpdate()
Dim strQry As String, varItem As Variant
    
    If Me.chkAllOrg = True Then
        Me.chkAllInstitutions.Value = False
        
        Me.lstEmployeeID.Enabled = False
        strQry = "SELECT DISTINCT Org, OrgName FROM dbo_tblOrgLook_master ORDER BY Org"
        Me.lstOrg.RowSource = strQry
        strQry = "SELECT DISTINCT CostCenter FROM dbo_tblOrgLook_master ORDER BY CostCenter"
        Me.lstCostCenter.RowSource = strQry
        strQry = "SELECT DISTINCT Fund FROM dbo_tblOrgLook_master ORDER BY Fund"
        Me.lstFund.RowSource = strQry
        strQry = "SELECT DISTINCT PEC FROM dbo_tblOrgLook_master ORDER BY Program"
        Me.lstPEC.RowSource = strQry
    Else
        Me.lstEmployeeID.Enabled = True
    End If
End Sub

Private Sub cmdClear_Click()
    ClearlstAnalyst
    ClearlstOrg
    ClearlstCostCenter
    ClearlstFund
    ClearlstPEC
End Sub

Private Sub ClearlstAnalyst()
    Dim varItem As Variant
    
    For Each varItem In Me.lstEmployeeID.ItemsSelected
        Me.lstEmployeeID.Selected(varItem) = False
    Next varItem

End Sub

Private Sub ClearlstOrg()
    Dim varItem As Variant
    
    For Each varItem In Me.lstOrg.ItemsSelected
        Me.lstOrg.Selected(varItem) = False
    Next varItem
    
    Me.lstOrg.RowSource = ""
    
End Sub

Private Sub ClearlstCostCenter()
    Dim varItem As Variant
    
    For Each varItem In Me.lstCostCenter.ItemsSelected
        Me.lstCostCenter.Selected(varItem) = False
    Next varItem
    
    Me.lstCostCenter.RowSource = ""
    
End Sub

Private Sub ClearlstFund()
    Dim varItem As Variant
    
    For Each varItem In Me.lstFund.ItemsSelected
        Me.lstFund.Selected(varItem) = False
    Next varItem
    
    Me.lstFund.RowSource = ""
    
End Sub

Private Sub ClearlstPEC()
    Dim varItem As Variant
    
    For Each varItem In Me.lstPEC.ItemsSelected
        Me.lstPEC.Selected(varItem) = False
    Next varItem
    
    Me.lstPEC.RowSource = ""
    
End Sub

Private Sub cmdClose_Click()
Quit
End Sub


Private Sub Form_Load()
On Error GoTo Err_Handler
   
    Dim strDomainLoginID As String, strPosition As String, strQry As String, strFName As String
    Dim strUnitID As String, StrLName As String, strEmployeeID As String
    Dim oCon As New ADODB.Connection, oRS As New ADODB.Recordset
    
    strDomainLoginID = GetUserName()      'domain login id
    LoginName = strDomainLoginID
        
    Me.lstOrg.RowSource = ""
    Me.lstCostCenter.RowSource = ""
    Me.lstFund.RowSource = ""
    Me.lstPEC.RowSource = ""
    Me.cmdSearch.Enabled = False
    Me.chkAllInstitutions.Visible = False
    Me.chkAllInstitutions.Value = False
    Me.lblAllInstitutions.Visible = False
    Me.chkAllOrg.Visible = False
    Me.chkAllOrg.Value = False
    Me.lblAllOrg.Visible = False
    
                
    If Not OpenDBConnection(oCon, "Ado") Then Err.Raise 65000, , "Can not open db connection"
    
    
    strQry = "SELECT EmployeeID, PositionID, UnitID, FName, LName FROM Employee_tbl WHERE Active = 1 and DomainLogin = '" & strDomainLoginID & "'"
    oRS.Open strQry, oCon, adOpenStatic, adLockReadOnly
    
    strPosition = oRS!PositionID
    strEmployeeID = oRS!EmployeeID
    strFName = oRS!FName
    StrLName = oRS!LName
    strUnitID = oRS!UnitID
    oRS.Close
    
    

    
    If strUnitID = "Data" Or strUnitID = "Tech" Or strUnitID = "LAU" Then
        
        Me.txtWelcome = "Welcome " & strFName & " " & StrLName & ", " & strUnitID
        Me.lstEmployeeID.RowSourceType = "Table/Query"
        Me.lstEmployeeID.RowSource = "SELECT distinct EmployeeID FROM Employee_tbl WHERE active = 1 and UnitID <> 'Data'"
        Me.lstEmployeeID.Requery
        
        Me.chkAllInstitutions.Visible = True
        Me.chkAllInstitutions.Value = False
        Me.lblAllInstitutions.Visible = True
        
        Me.chkAllOrg.Visible = True
        Me.chkAllOrg.Value = False
        Me.lblAllOrg.Visible = True
    Else
        
        Select Case strPosition
            Case "All"
                Me.txtWelcome = "Welcome " & strFName & " " & StrLName & ", " & strUnitID
                Me.lstEmployeeID.RowSourceType = "Table/Query"
                Me.lstEmployeeID.RowSource = "SELECT distinct EmployeeID FROM Employee_tbl WHERE active = 1"
                Me.lstEmployeeID.Requery
                
                Me.chkAllInstitutions.Visible = True
                Me.chkAllInstitutions.Value = False
                Me.lblAllInstitutions.Visible = True
                
                Me.chkAllOrg.Visible = True
                Me.chkAllOrg.Value = False
                Me.lblAllOrg.Visible = True
                
            Case "Budget Analyst"
                Me.txtWelcome = "Welcome " & strFName & " " & StrLName
                strQry = "SELECT distinct M.EmployeeID FROM Employee_tbl E INNER JOIN Employee_tbl M on M.ManagerID = E.ManagerID" & vbCrLf
                strQry = strQry & "WHERE E.EmployeeID = '" & strEmployeeID & "'"
                Me.lstEmployeeID.RowSourceType = "Table/Query"
                Me.lstEmployeeID.RowSource = strQry
                Me.lstEmployeeID.Requery
                
                Me.chkAllInstitutions.Visible = False
                Me.chkAllInstitutions.Value = False
                Me.lblAllInstitutions.Visible = False
                
                Me.chkAllOrg.Value = False
                Me.lblAllOrg.Visible = False
    
            Case "Manager"
                Me.txtWelcome = "Welcome " & strFName & " " & StrLName & ", Mgr"
                                
                Me.lstEmployeeID.RowSourceType = "Table/Query"
                Me.lstEmployeeID.RowSource = "SELECT DISTINCT EmployeeID FROM Employee_tbl WHERE Active = 1 and ManagerID = '" & strEmployeeID & "'"
                Me.lstEmployeeID.Requery
                
                Me.chkAllInstitutions.Visible = True
                Me.chkAllInstitutions.Value = False
                Me.lblAllInstitutions.Visible = True
                               
                Me.chkAllOrg.Visible = True
                Me.chkAllOrg.Value = False
                Me.lblAllOrg.Visible = True
                
            Case Else
                DoCmd.Quit
        End Select
    End If

Exit_Proc:
    Exit Sub

Err_Handler:
    MsgBox "In Form_Load of " & Me.NAME & vbCrLf & Err.Number & "--" & Err.Description
    Resume Exit_Proc
End Sub

Private Sub lstComponent_AfterUpdate()

End Sub

Private Sub lstEmployeeID_AfterUpdate()

    Dim strEmployeeID As String, varList As Variant, iLen As Integer
    Dim strQry As String
    
    If Me.lstEmployeeID.ItemsSelected.Count > 0 Then
        
        strEmployeeID = ""
        Me.lstFund.RowSource = ""
        Me.lstPEC.RowSource = ""
        Me.cmdSearch.Enabled = False
        
        strEmployeeID = GetSelectedEmployeeID
        iLen = Len(Nz(strEmployeeID, ""))
        
        If iLen > 2 Then
            strQry = "SELECT DISTINCT O.Org, O.OrgName" & vbCrLf
            strQry = strQry & "FROM dbo_tblOrgLook_master AS O INNER JOIN Employee_tbl AS E ON O.Analyst = E.EmployeeID" & vbCrLf
            strQry = strQry & "WHERE EmployeeID in (" & strEmployeeID & ") " & vbCrLf
            strQry = strQry & "ORDER BY O.Org "
            Me.lstOrg.RowSource = strQry
            Me.lstOrg.RowSourceType = "Table/Query"
            Me.lstOrg.Requery
            Me.lstCostCenter.RowSource = ""
            Me.lstFund.RowSource = ""
            Me.lstPEC.RowSource = ""
        End If
    Else
        
        Me.lstOrg.RowSource = ""
        Me.lstCostCenter.RowSource = ""
        Me.lstFund.RowSource = ""
        Me.lstPEC.RowSource = ""
    End If
    
End Sub

Private Sub lstOrg_AfterUpdate()
     UpdateCostCenterListing
    
    If Me.lstOrg.ItemsSelected.Count > 0 Then
        Me.cmdSearch.Enabled = True
    Else
        Me.cmdSearch.Enabled = False
    End If
End Sub

Private Sub UpdateCostCenterListing()
    Dim strOrg As String, varList As Variant, iLen As Integer
    Dim strQry As String
    Dim strEmployeeID As String
    
    strOrg = Nz(GetSelectedOrg, "")

    If Me.chkAllInstitutions = True Then
        
        strQry = "SELECT DISTINCT CostCenter FROM dbo_tblOrgLook_master WHERE Inactive = 0 and Org In (" & strOrg & ")"
        Me.lstCostCenter.RowSource = strQry
        Me.lstCostCenter.RowSourceType = "Table/Query"
        Me.lstCostCenter.Requery
        
    
    Else
    
        strEmployeeID = Nz(GetSelectedEmployeeID, "")
        
        If Len(strEmployeeID) < 2 Then
            MsgBox "Please select an employee to display."
        Else
    
                    
            iLen = Len(Nz(strOrg, ""))
            
            If iLen > 2 Then
    '
                strQry = "SELECT DISTINCT CostCenter from dbo_tblOrgLook_master where Inactive = 0 and Analyst IN (" & strEmployeeID & ") and Org IN (" & strOrg & ")"
                Me.lstCostCenter.RowSource = strQry
                Me.lstCostCenter.RowSourceType = "Table/Query"
                Me.lstCostCenter.Requery
            End If
        End If
     End If
End Sub
Private Sub lstCostCenter_AfterUpdate()
     UpdateFundListing
    
    If Me.lstCostCenter.ItemsSelected.Count > 0 Then
        Me.cmdSearch.Enabled = True
    Else
        Me.cmdSearch.Enabled = False
    End If
End Sub
Private Sub UpdateFundListing()
    Dim strOrg As String, varList As Variant, iLen As Integer
    Dim strQry As String
    Dim strEmployeeID As String
    Dim strCostCenter As String
    
    strOrg = Nz(GetSelectedOrg, "")
    strCostCenter = Nz(GetSelectedCostCenter, "")

    If Me.chkAllInstitutions = True Then
        
        strQry = "SELECT DISTINCT Fund FROM dbo_tblOrgLook_master WHERE Inactive = 0 and Org In (" & strOrg & ")"
        Me.lstFund.RowSource = strQry
        Me.lstFund.RowSourceType = "Table/Query"
        Me.lstFund.Requery
        
    
    Else
    
        strEmployeeID = Nz(GetSelectedEmployeeID, "")
        
        If Len(strEmployeeID) < 2 Then
            MsgBox "Please select an employee to display."
        Else
    
                    
            iLen = Len(Nz(strOrg, ""))
            
            If iLen > 2 Then
    '
                strQry = "SELECT DISTINCT Fund FROM dbo_tblOrgLook_master WHERE Inactive = 0  and Analyst IN (" & strEmployeeID & ") and Org IN (" & strOrg & ")and CostCenter IN (" & strCostCenter & ")"
                Me.lstFund.RowSource = strQry
                Me.lstFund.RowSourceType = "Table/Query"
                Me.lstFund.Requery
            End If
        End If
     End If
End Sub
Private Sub lstFund_AfterUpdate()
    UpdatePECListing
    
    If Me.lstFund.ItemsSelected.Count > 0 Then
        Me.cmdSearch.Enabled = True
    Else
        Me.cmdSearch.Enabled = False
    End If
End Sub

Private Sub UpdatePECListing()
    Dim strOrg As String, varList As Variant, iLen As Integer
    Dim strQry As String
    Dim strEmployeeID As String
    Dim strCostCenter As String
    Dim strFund As String
    
    strOrg = Nz(GetSelectedOrg, "")
    strCostCenter = Nz(GetSelectedCostCenter, "")
    strFund = Nz(GetSelectedFund, "")

    If Me.chkAllInstitutions = True Then
     
        
        strQry = "SELECT DISTINCT PEC FROM dbo_tblOrgLook_master where Inactive = 0 and Org In (" & strOrg & ") and CostCenter IN (" & strCostCenter & ")and Fund IN (" & strFund & ")"
        Me.lstPEC.RowSource = strQry
        Me.lstPEC.RowSourceType = "Table/Query"
        Me.lstPEC.Requery
        
    
    Else
    
        strEmployeeID = Nz(GetSelectedEmployeeID, "")
        
        If Len(strEmployeeID) < 2 Then
            MsgBox "Please select an employee to display."
        Else
    
                    
            iLen = Len(Nz(strFund, ""))
            
            If iLen > 2 Then
    '
                strQry = "SELECT DISTINCT PEC FROM dbo_tblOrgLook_master WHERE Inactive = 0 and Analyst IN (" & strEmployeeID & ") and Org In (" & strOrg & ") and CostCenter IN (" & strCostCenter & ")and Fund IN (" & strFund & ")"
                Me.lstPEC.RowSource = strQry
                Me.lstPEC.RowSourceType = "Table/Query"
                Me.lstPEC.Requery
            End If
        End If
     End If
End Sub

Private Sub lstPEC_AfterUpdate()
    
    If Me.lstPEC.ItemsSelected.Count > 0 Then
        Me.cmdSearch.Enabled = True
    Else
        Me.cmdSearch.Enabled = False
    End If
End Sub

Private Function GetSelectedOrg() As String
    GetSelectedOrg = GetListBoxSelectedItem(Me.lstOrg, True, False)
End Function

Private Function GetSelectedEmployeeID() As String
    GetSelectedEmployeeID = GetListBoxSelectedItem(Me.lstEmployeeID, True, False)
End Function

Private Function GetSelectedCostCenter() As String
    GetSelectedCostCenter = GetListBoxSelectedItem(Me.lstCostCenter, True, False)
End Function
Private Function GetSelectedFund() As String
    GetSelectedFund = GetListBoxSelectedItem(Me.lstFund, True, False)
End Function

Private Function GetSelectedPEC() As String
    GetSelectedPEC = GetListBoxSelectedItem(Me.lstPEC, True, False)
End Function



Private Sub Save_Record_Click()
 
        Call querydef
        
        DoCmd.OutputTo acOutputQuery, "Allot_Q", "Excel97-Excel2003Workbook(*.xls)", "", True, "", , acExportQualityScreen
        
 End Sub

Open in new window

0
 

Author Closing Comment

by:Jass Saini
ID: 40503666
Thanks..
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now