Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Multiselect Listbox question again

    Hello,

    I am using a few listboxes and a command button to book a visit for Groups at various Centres.

    The way it works is I select a centre (centreID / tblCentres) from lstCentres and select a group (groupID / tblGroups) from lstGroups, then click the command button which creates a new record with an autonumber in tblVisits. The visits are then displayed in the lstVisits listbox.

    This is working great.

    Next, I have added another listbox (lstUsers) which uses tblUsers as its source. I want to be able to allow multiple users to be named in a group visit to a centre, So in addition to selecting Groups and Centres, I will also be able to add named Users who belong to the selected group who are going to make the visit.

    Many groups have many Users, so I have an intersection table called tblLinkUsersVisits to store the many-to-many relationship records. The intersection table has two fields - userID and visitID.

    I want to make my lstUsers listbox a multi-select listbox, and have tried using the code below to allow me to add multiple userID's into the intersection table tblLinkUsersVisits (along with the visitID which is created when I press the command button to make the booking of groups and centres as described above).

    The code for the command button is currently -
    Code:
    Dim stDocName As String
    Dim stLinkCriteria As String
        
    Dim strSQL        As String
    Dim db            As DAO.Database
    Dim rs            As DAO.Recordset
    Dim ctl           As Control
    Dim varItem       As Variant
    
    stDocName = "visits_frm"
    stLinkCriteria = "[centreID]=" & "'" & Me![LstCentres] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
    
    DoCmd.GoToRecord , , acNewRec
    
    Forms![visits_frm]![groupID] = Forms!tabbed_frm.LstGroups.Column(0)
    
    Forms![visits_frm].Form![subfrmLinkUsersVisits]![userID] = Forms!tabbed_frm.ListUsers.Column(0)
    
    Forms![visits_frm]![centreID] = Forms!tabbed_frm.LstCentres.Column(0)
    
      Set db = CurrentDb()
      Set rs = db.OpenRecordset("tblLinkUserVisits", dbOpenDynaset, dbAppendOnly)
    
      'make sure a selection has been made
      If Me.ListUsers.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 user!"
        Exit Sub
      End If
    
      'add selected value(s) to table
      Set ctl = Me.ListUsers
      For Each varItem In ctl.ItemsSelected
        rs.AddNew
        rs!userID = ctl.ItemData(varItem)
       rs.Update
      Next varItem
    But when I run it I get a message saying a related record is required in tblVisits. So I think it is trying to create a new visitID autonumber. I want the code to take the selected UserID's from lstUsers and the visitID autonumber when the booking is made.

    I may for example select 5 users all for 1 single visit, so the 5 userID's will go into tblLinkUsersVisits along with the same visitID for each record.

    I need help!
    Attached Thumbnails Attached Thumbnails relationships.jpg  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When adding records to tblLinkUserVisits, you need to supply a value for a group (GroupID):
    Code:
      For Each varItem In ctl.ItemsSelected
        rs.AddNew
        rs!userID = ctl.ItemData(varItem)
        rs!GroupID = <GroupID>
       rs.Update
      Next varItem
    Have a nice day!

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post
    When adding records to tblLinkUserVisits, you need to supply a value for a group (GroupID):
    Code:
      For Each varItem In ctl.ItemsSelected
        rs.AddNew
        rs!userID = ctl.ItemData(varItem)
        rs!GroupID = <GroupID>
       rs.Update
      Next varItem
    I dont think that is correct, I am only adding UserId's and VisitId's into tblLinkUserVisits. The groupID is being stored in tblVisits.

    I need to add multiple userID's and the same visitID to tblLinkUserVisits.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Youre right... to a point: It's not the GroupID data that's missing, it's the VisitID data. You must add it to every new row created in the loop. Presently, the loop tries to add rows into the tblLinkUserVisits with a Null (or zero, depending on how the table is defined) VisitID.
    Have a nice day!

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post
    Youre right... to a point: It's not the GroupID data that's missing, it's the VisitID data. You must add it to every new row created in the loop. Presently, the loop tries to add rows into the tblLinkUserVisits with a Null (or zero, depending on how the table is defined) VisitID.
    Yes, that is it, I need to add the visitID to every new row created in the loop...but I dont know how

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    Any thoughts on how I can add the visitID to every new row in the loop in tblLinkUserVisits?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the code I posted but replace GroupID by VisitID.
    Have a nice day!

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post
    Use the code I posted but replace GroupID by VisitID.
    That was the first thing I tried, and what is happening can be seen in the screen grab. I started with an empty tblLinkUserVisits, and the selected UserID's are being added to the link table, but the visitID field aren't.
    Attached Thumbnails Attached Thumbnails tblLinkUserVisits.jpg  

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From where does the VisitID come (control, result of a DLookup function, public variable, etc.)? Also, what's the definition of the table tblLinkUserVisits and what relationships and constraints do you have in your database (if any)?
    Have a nice day!

  10. #10
    Join Date
    Aug 2004
    Posts
    364
    On my main form, the user will select firstly from two listboxes a groupID (from tblGroups) and CentreID (from tblCentres) to be booked into a visit.

    Then, the third listbox on my main form displays the users who belong to groups. As a group visit may or may not involve all users of a group, I need to be able to select the users who are visiting as part of a group for that particular visit.

    The userID (or userID's if the listbox is multiselected) need to be stored in the link table tblLinkUserVisits along with the the same visitID which is created in tblVisits when the command button is pressed. When the user presses the command button on the main form it opens up the visitsFrm and the selected Id's are stored in tblVisits along with the autogenerated visitID, and I need the visitID to go into tblLinkUserVisits as well as the userID(s).

    So I need to get the visitID into tblLinkUserVisits as well as tblVisits.

    The code for my command button on the main form is this -
    Code:
    Dim stDocName As String
        Dim stLinkCriteria As String
        stDocName = "visits_frm"
    
    stLinkCriteria = "[centreID]=" & "'" & Me![List9] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
    DoCmd.GoToRecord , , acNewRec
    
    Forms![visits_frm]![groupID] = Forms!tabbed_frm.ListOne.Column(0)
    
    Forms![visits_frm].Form![tblLinkUserVisitsSubfrm]![userID] = Forms!tabbed_frm.ListTwo.Column(0)
    
    Forms![visits_frm]![centreID] = Forms!tabbed_frm.List9.Column(0)
    
     Dim strSQL        As String
      Dim db            As DAO.Database
      Dim rs            As DAO.Recordset
      Dim ctl           As Control
      Dim varItem       As Variant
      Set db = CurrentDb()
      Set rs = db.OpenRecordset("tblLinkUserVisits", dbOpenDynaset, dbAppendOnly)
    
      'make sure a selection has been made
      If Me.ListTwo.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 user!"
        Exit Sub
      End If
    
      'add selected value(s) to table
      Set ctl = Me.ListTwo
     For Each varItem In ctl.ItemsSelected
        rs.AddNew
        rs!userID = ctl.ItemData(varItem)
        rs!visitID = visitID
       rs.Update
      Next varItem
    Last edited by moss2076; 05-18-10 at 15:24.

  11. #11
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post
    From where does the VisitID come (control, result of a DLookup function, public variable, etc.)? Also, what's the definition of the table tblLinkUserVisits and what relationships and constraints do you have in your database (if any)?
    The database relationships are shown in the post of this thread as an attachment.

    Ive attached the database in a zip file, just open tabbed_frm, select a centre, group and user(s) from the listboxes and click the button. The visits_frm will open displaying the booking info. Then close the form and open the table called tblLinkUserVisits and you will see only 1 visitID has been saved.
    Attached Files Attached Files
    Last edited by moss2076; 05-19-10 at 14:45.

  12. #12
    Join Date
    Aug 2004
    Posts
    364
    Update - I think I have worked out what the problem is, but I need some assistance on how to fix it -

    If, for example in my main form I select a group from lstGroups, a centre from lstCentres and say 3 users from lstUsers then click the command button to create a visit in tblVisits, the 3 users are added to tblLinkUserVisits, but the visitID hasn't been created yet, so that field is remaining empty in tblLinkUserVisits.

    It is only when the visits form (frm_visits) loads that the visitID is created in tblvisits. So the visitID in tblLinkUserVisits is remaining empty.

    So how do I make the visitID be added into tblLinkUserVisits at the same time as when it is created in tblVisits?

  13. #13
    Join Date
    Aug 2004
    Posts
    364
    anyone any ideas?

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. I cannot use the file you attached to your message (database.zip). Winzip signals that it's not a valid archive file or that it is corrupted. WinRar succeeds in opening it but it contains a file: database. (without extension). When I try to open this file with Access (v. 2003 and v. 2007), a message signals that the file is not in a valid database format.

    2. userID must exist in tblUsers and visitID must exist in tblVisits before adding both keys (userID and visitID) to the table tblLinkUserVisit.

    3. The function that creates the associations by adding rows in tblLinkUserVisit must "know" both the userID and visitID keys as well as the number of associations to create.

    4. The code you posted:
    Code:
     For Each varItem In ctl.ItemsSelected
        rs.AddNew
        rs!userID = ctl.ItemData(varItem)
        rs!visitID = visitID
       rs.Update
      Next varItem
    seems to be correct provided that the condition described in (2.) is fulfilled.

    5. I could probably provide a more precise answer if I could have a look at the database, please try to repost it if necessary.
    Have a nice day!

  15. #15
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post

    2. userID must exist in tblUsers and visitID must exist in tblVisits before adding both keys (userID and visitID) to the table tblLinkUserVisit.
    Hello again I tried re-attaching the database, but when I even try to download it, it says it is corrupted. I have uploaded it to here, hopefully it can be downloaded now.

    UserID does exist in tblUsers, but no, visitID does not exist in tblVisits before adding both keys because -

    When the userID(s) are selected in the listbox the main form, the Visits_frm hasnt been opened yet, so the visitID hasn't been created yet - hence that is why I cannot put the visitID in tblUserLinkVisits. The visitID is only created when the command button on the main form is pressed.

    I was hoping there may be a way of still adding the visitID to the link table even though the visitID is created after the command button is pressed. Could the multselect code be moved into the visit_frm's on load event or similar for example?

    If there isn't a way, I will have to alter my form design - basically scrap the main form and just work from the visits_frm so the visitID is always created first and the userID(s) created afterwards.
    Last edited by moss2076; 05-23-10 at 06:53.

Posting Permissions

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