• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

Continuous Records

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
Jass Saini
Asked:
Jass Saini
  • 11
  • 7
  • 2
  • +1
1 Solution
 
HainKurtSr. System AnalystCommented:
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
 
Rey Obrero (Capricorn1)Commented:
questions..
why do have repeating records?

without seeing your db schema, i don't know what to suggest.
0
 
Rey Obrero (Capricorn1)Commented:
<we are not magicians... >   ;-)  i lost my crystal ball longtime ago
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Jass SainiAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Jass SainiAuthor Commented:
So both forms should have PEC and CostCen in order to link??
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Jass SainiAuthor Commented:
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
 
Jass SainiAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Jass SainiAuthor Commented:
I used the Form wizard..Why...
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Jass SainiAuthor Commented:
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
 
Jass SainiAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Jass SainiAuthor Commented:
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
 
Jass SainiAuthor Commented:
So on the subform there should be repeat records..The same record mulitple times???
0
 
Jass SainiAuthor Commented:
Sorry I am throwing so much at you..
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Jass SainiAuthor Commented:
Thanks..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 11
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now