Results 1 to 10 of 10
  1. #1
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43

    Duplication in listbox

    Attachment 33453

    I have been posting my Access DB problems in this forum from the past two months and I am very thankful to people who tried to help me. This is a the next stage of my previous thread https://www.accessforums.net/showthread.php?t=71072.
    But I will give a brief introduction. i have a form "Create New Machine" with four cascading listboxes with previous data already available. I am adding a new record say "New Machine" in the first listbox and inserting the existing data into it.
    Some information from the image attached:
    • Machine Sub System A & B belong to Machine System A.
    • Machine Sub System C & D belong to Machine System B.
    • Components A & B belong to Machine Sub System A.
    • Components C & D belong to Machine Sub System B.
    • Components E & F belong to Machine Sub System C.
    • Components G & H belong to Machine Sub System D.

    Now there is a duplication of records in the fourth listbox from selection in third listbox, and also there is pattern.
    Components A & B and Components E & F from Machine Sub System A and Machine Sub System C respectively are showing duplicates.
    Where as Components C & D and Components G & H from Machine Sub System B and Machine Sub System D respectively are working correctly.
    I have also attached the main part of the code.
    I am not able to solve the issue. Any help will be greatly appreciated.



    Please ignore the Duplicate Code and refer to the Updated Code because I feel that there is a problem with the Looping of the If statement that was not captured in the first image.

    Thank you in advance.
    Regards
    Attached Thumbnails Attached Thumbnails Updated Code.jpg   Duplicates Code.jpg   Duplicates.PNG  
    Last edited by Deepak.Doddagoudar; 04-09-2018 at 07:04 AM.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When posting code, please use code tags and not screenshots.
    Code in tags is easier to read and allow forum members the opportunity to edit, which images don't.

    Also your attachment link isn't working.
    I haven't looked at your previous thread as I think the issue is obvious.

    Unless I missed it, you haven't given the row source of the listbox with 'duplicates'.
    However they aren't duplicates as the second column is different for each of the repeated components.
    Remove that column and use SELECT DISTINCT or GROUP BY as appropriate and it should work as you want.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Hello Ridders52,Attachment 33462
    Thank you for your kid suggestions. The row source is the value list for the the second, third and fourth list box.
    Here is the code. I have also attached you the new image to give you a clearer picture. I have now changed the column index. You can see from the image that there is a repitition.

    Private Sub CREATEMACHINE_Click()
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim varItem As Variant
    Dim rs2 As DAO.Recordset
    Dim ctl2 As Control
    Dim rs1 As DAO.Recordset
    Dim ctl1 As Control
    Dim ID As Long
    Dim i As Integer
    Dim l As Integer
    Dim n As Integer
    Dim strWhrMachine1SystemID As String
    Dim strSQL4str As String
    Dim intMachineSystemID As Integer
    Dim rsnewData As DAO.Recordset

    On Error GoTo ErrorHandler

    ID = DMax("[MAchine ID]", "tblmachine")
    Dim sMachineSubsystem As String, varSelectedID11 As Variant
    Dim vMaxMachineSubsystemID As Variant


    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblMachineSystem", dbOpenDynaset, dbAppendOnly)
    Set rs1 = db.OpenRecordset("tblMachineSubSystem", dbOpenDynaset, dbAppendOnly)
    Set rs2 = db.OpenRecordset("tblComponents", dbOpenDynaset, dbAppendOnly)
    Set rsmas = db.OpenRecordset("tblMasterData", dbOpenDynaset, dbAppendOnly)


    Set ctl = Me.listMachineSystem
    Set ctl1 = Me.listMachineSubSystem
    Set ctl2 = Me.listComponents


    For n = 0 To Me.listMachineSystem.ListCount - 1
    If Me.listMachineSystem.Selected(n) Then
    rs.AddNew
    rs!MachineSystem = Me.listMachineSystem.Column(0, n)
    rs![MAchine ID] = ID
    rs.Update
    End If
    Next n

    For i = 0 To Me.listMachineSubSystem.ListCount - 1
    If Me.listMachineSubSystem.Selected(i) Then
    rs.FindFirst "[Machine ID]=" & ID & " AND [MachineSystem]= '" & DLookup("[MachineSystem]", "tblMachineSystem", "[Machine System ID]=" & Me.listMachineSubSystem.Column(2, i)) & "'"


    rs1.AddNew
    rs1![MachineSubsystem] = Me.listMachineSubSystem.Column(0, i)
    rs1![Machine Sytem ID] = rs![Machine System ID]
    rs1.Update

    vMaxMachineSubsystemID = DMax("[Machine System ID]", "tblMachineSystem", "[Machine ID]=" & ID & " AND [MachineSystem]= '" & DLookup("[MachineSystem]", "tblMachineSystem", "[Machine System ID]=" & rs![Machine System ID]) & "'")


    For l = 0 To Me.listComponents.ListCount - 1
    If Me.listComponents.Selected(l) Then
    varSelectedID2 = Me.listComponents.Column(2, l)
    sMachineSubsystem = DLookup("[MachineSubsystem]", "tblMachineSubSystem", "[Machine Subsystem ID]=" & varSelectedID2)


    rs1.FindFirst "[Machine Sytem ID]=" & vMaxMachineSubsystemID & " AND [MachineSubsystem]= '" & sMachineSubsystem & "'"
    If rs1.NoMatch Then
    'MsgBox "no records found"
    Else
    Do While Not rs1.NoMatch
    'MsgBox "I found it!!!"
    rs2.AddNew
    rs2![Components] = Me.listComponents.Column(0, l)
    rs2![Machine Subsystem ID] = rs1![Machine Subsystem ID]
    rs2.Update
    rs1.FindNext "[Machine Sytem ID]=" & vMaxMachineSubsystemID & " AND [MachineSubsystem]= '" & sMachineSubsystem & "'"
    Loop
    End If
    End If
    Next l
    End If
    Next i


    DoCmd.Close


    ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

    ErrorHandler:
    Select Case Err
    Case Else
    MsgBox Err.Description
    DoCmd.Hourglass False
    Resume ExitHandler
    End Select
    End Sub

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Once again the attachment says its an invalid link. However I can see the image in post 1

    Click image for larger version. 

Name:	Capture.PNG 
Views:	17 
Size:	46.3 KB 
ID:	33463

    As already explained, look at the 2nd column.
    The values are different for each pair of components so the results are NOT duplicates
    Edit the listbox row source as already explained
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Click image for larger version. 

Name:	Duplicates 1.PNG 
Views:	25 
Size:	48.1 KB 
ID:	33465Are you able to open the image now?

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes and it's different to the previous version.
    I assume these are cascading combos where each depends on the previous selection.
    Despite all the code you gave earlier, I'm fairly sure you haven't posted the row source for that listbox ...or any other
    Without that info, there is no point trying to advise further.

    For info, I'm busy for the next few hours so hopefully someone else can assist.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    I sincerely apologize for the delayed response. You are right in saying that it is a cascading listbox (not a combobox) with multiselect simple.
    The row source for the first listbox is table Machine and the row source for the second, third and fourth listbox is value list which is derived from the code. I hope this information is enough to help me proceed further. Also I have attached an image of the four tables and the relationship diagram. I apologize again if I have missed any information.Click image for larger version. 

Name:	Combined.jpg 
Views:	18 
Size:	88.3 KB 
ID:	33482Click image for larger version. 

Name:	Relationship.PNG 
Views:	18 
Size:	36.0 KB 
ID:	33483

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Deepak
    Despite numerous screenshots and lots of code, you've omitted the most important item need for anyone to assist you.
    Please post the actual row source sql used for each listbox.

    My guess is that you aren't filtering the row source asked on the selection in the previous list.
    I would start with only the first listbox showing.
    Make a selection and this should make the next listbox visible and filtered based on that selection.
    Repeat for each listbox in turn
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Post #6
    Quote Originally Posted by ridders52 View Post
    <snip>
    Despite all the code you gave earlier, I'm fairly sure you haven't posted the row source for that listbox ...or any other
    Post #8
    Quote Originally Posted by ridders52 View Post
    <snip>
    Please post the actual row source sql used for each listbox.

    My guess is that you aren't filtering the row source asked on the selection in the previous list.
    I would start with only the first listbox showing.
    Make a selection and this should make the next listbox visible and filtered based on that selection.
    Repeat for each listbox in turn
    Colin, you are partially correct. I downloaded the dB (DB12.zip) from the thread (Today, 04:12 AM) https://www.accessforums.net/showthr...189#post394189
    and looked at the relationships, form and code.


    All 4 listboxes are unbound.

    The first listbox is for Machine: row source is a query - not multiselect.
    The other 3 list boxes have the row source as a value list - are Multiselect.
    Code in the associated buttons create the values list in the 3 listboxes.

    You can select only one item in the first listbox. The next 2 list boxes can have multiple selections. If there are multiple selections in the 2nd and 3rd listboxes, there are duplicates in the components listbox. Not sure what purpose of the button "Create Machine" is.


    The image of the relationship window in Post #7 is different than the the relationship in DB12....
    Relationships from DB12
    Click image for larger version. 

Name:	Relationship1.png 
Views:	13 
Size:	77.2 KB 
ID:	33506
    I would suggest reviewing/analyzing the design of the tables/relationships.



    I am so lost and confused I don't think I can help, so I an withdrawing at this point..



    Deepak, good luck with your project.....

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks Steve
    You've confirmed my suspicions about the structure involved or perhaps lack of it.
    Having asked twice unsuccessfully, I'm also dropping out
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Duplication
    By dancoe2004 in forum Queries
    Replies: 2
    Last Post: 08-24-2017, 06:44 PM
  2. Duplication of data
    By Nixx1401 in forum Reports
    Replies: 1
    Last Post: 12-12-2011, 10:05 PM
  3. Query Duplication
    By Lois in forum Queries
    Replies: 1
    Last Post: 11-22-2011, 08:47 AM
  4. duplication problem
    By pdcc in forum Reports
    Replies: 1
    Last Post: 07-16-2011, 09:57 AM
  5. duplication
    By noidea in forum Access
    Replies: 1
    Last Post: 07-31-2009, 06:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums