Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listcount (MS Access 2003)

    I have a listbox, column heads set to none. If the list has one row of data, the listcount returns a zero? Should it not be 1, or am I nodding off. Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listcount (MS Access 2003)

    It should be 1.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listcount (MS Access 2003)

    Thanks Hans, thats what I thought. Now begins the hunt for why I am getting this. I have presumed when MS Update put in my Service Patch 3 they also put in the hotfix patch. I am seeing combo data etc which is supposed to require the hotfix. I am going to look up KB907417 (OTKLOADR) which is in my updatesto see if it's the hotfix. One problem after another. Will let you know what I find. Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listcount (MS Access 2003)

    I found a thread on another forum mentioning that that update was left out of Office 2003 SP3, but it doesn't seem relevant to your problem.

    Access 2003 post-Service Pack 3 hotfix package: December 18, 2007 appears to be more relevant.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listcount (MS Access 2003)

    Just installed the hotfix, good old MS did not include it with my windows update SP3, however listcount still wrong. Must be in my code as I did a dummy form with a listbox and that was correct. The code below is crude but cannot see why its not working correctly.

    First Operation - Loop through a list if it contains names in


    For TY = 1 To Me.AthleteList.ListCount - 1

    If Me.AthleteList.ListCount > 1 Then

    strSQL = "DELETE ID2, AthleteID " & _
    "FROM JUNCTION " & _
    "WHERE ID2=" & Forms!Mainform1!Subform1.Form.ID2 & _
    " AND JUNCTION.AthleteID=" & Forms!Mainform1!Subform1.Form.AthleteList.Column(0 , TY)

    DoCmd.RunSQL strSQL

    Code above removes relevant Junction table links (records)


    Second Operation - Loop through Athlete list to see if Athlete name in any other records, if not delete it

    ' Put Athlete ID from listrow above into a label AID, requery List237 which uses the label data in its criteria

    Me.AID.Caption = AthleteList.Column(0, TY)

    Me.List237.Requery

    If Me.List237.ListCount = 1 Then ' Ensures name is removed from Athlete table to avoid being an orphan


    ' Delete from Athletes Table
    strSQL = "DELETE AthleteID " & _
    "FROM AthleteNames " & _
    "WHERE AthleteID=" & Forms!Mainform1!Subform1.Form.AthleteList.Column(0 , TY)


    DoCmd.RunSQL strSQL

    End If
    End If
    Next

    Don't know if you can make sense of above, if so can you think of a reason why the listcount produces a zero instead of one when only one occasion of name is found used. Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listcount (MS Access 2003)

    If you want to loop through all items in a list box, you should use

    For TY = 0 To Me.AthleteList.ListCount - 1

    instead of

    For TY = 1 To Me.AthleteList.ListCount - 1

    The list index starts counting at 0, not at 1. Apart from that, I don't understand the code.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listcount (MS Access 2003)

    Thanks Hans. I have the listcount running from 1 as the first list has headings. Regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listcount (MS Access 2003)

    I'm afraid I can't help. Sorry.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listcount (MS Access 2003)

    I have not tried this yet, don't know if the SQL is correct syntax, but instead of using the listcount of a list, can a query as below be modified to to identify where there is only 1 record found?

    strSQL = "SELECT JUNCTION.AthleteID, JUNCTION.ID2 " & _
    "FROM JUNCTION " & _
    "WHERE JUNCTION.AthleteID= & forms!Mainform1!Subform1.Form.AthleteList.Column(0 , TY)"

    DoCmd.RunSQL strSQL

    If I can do it in VB it might get round my listcount problem. Regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listcount (MS Access 2003)

    Would

    Dim lngCount As Long
    lngCount = DCount("*", "JUNCTION", "AthleteID=" & forms!Mainform1!Subform1.Form.AthleteList.Column(0 , TY))

    help?

  11. #11
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listcount (MS Access 2003)

    Many thanks. I still get a count of 0, however it makes my code cleaner. Is it any clearer?

    If Me.AthleteList.ListCount > 1 Then

    For TY = 1 To Me.AthleteList.ListCount - 1

    ' Delete from Junction Table
    strSQL = "DELETE ID2, AthleteID " & _
    "FROM JUNCTION " & _
    "WHERE ID2=" & Forms!Mainform1!Subform1.Form.ID2 & _
    " AND JUNCTION.AthleteID=" & Forms!Mainform1!Subform1.Form.AthleteList.Column(0 , TY)

    DoCmd.RunSQL strSQL


    lngCount = DCount("*", "JUNCTION", "AthleteID=" & Forms!Mainform1!Subform1.Form.AthleteList.Column(0 , TY))

    If lngCount = 1 Then

    ' Delete from Athletes Table
    strSQL = "DELETE AthleteID " & _
    "FROM AthleteNames " & _
    "WHERE AthleteID=" & Forms!Mainform1!Subform1.Form.AthleteList.Column(0 , TY)

    DoCmd.RunSQL strSQL

    End If

    End If

    Next

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listcount (MS Access 2003)

    I don't understand why you want to delete if lngCount equals 1 - it means that there's still a junction record for that AthleteID.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listcount (MS Access 2003)

    I think you have answered my problem. In the first part of the code, all references to the record being deleted are removed from the junction table. Therfore, if there was only 1 athlete name in the Athlete Names table that was associated with the record being deleted, the ingcount would return a zero, as the only AthleteID held in the junction table has just been removed!! So maybe I should be looking for an imgcount of zero to delete the name from the names table. Hope I have not confused you further but that seems to have made it clearer at this end. Think I'm right? Regards

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listcount (MS Access 2003)

    Yes, it seems more logical to delete athletes who have 0 related records in the junction table.

  15. #15
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listcount (MS Access 2003)

    Thanks very much for your help once again, very much appreciated. Regards

    Just to cnfirm, all works well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •