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

    Unanswered: Listbox to display records on a continuous form?

    Hello,

    I have 2 tables linked via a one to many relationship.
    tblUsers (pk = userID) and tblReferrals (pk = referralID)

    I have a form set to "continuous" and the record source set to tblReferrals. So the form shows all the records from tblReferrals.

    I have added a listbox at the top of the form and set the row source to tblUsers.

    I would like to be able to select a row from the listbox (eg userID #1) and have the form display only the records from tblReferrals for user #1.

    Can anyone give me some tips / advice on how to achieve this?

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    I suggest to make a Form1 on the "tblUsers" (without a list box),
    and a Form2 on the "tblReferrals". Form1 is a MainForm, and Form2
    put in Form1 as a SubForm. Link them via "UserID" (in the Relationships,
    also via "LINK CHILD FIELDS" and "LINK MASTER FIELDS" properties.
    Then add a field (Combo Box) FIND RECORD in the MainForm.

    Look at "DemoSubA2000.mdb" (attachment, zip).

    Adapt it in your mdb.
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Thankyou for replying, I can use a form and subform (and make the subform a continuous form). But I was hoping there would be a method where a subform was'nt required.

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    If you have got a relationships tblUsers : tblReferrals, 1 : more, in that case
    you must have a record in the side 1 if you want to have a record on the side
    more, but, it is not required to have a record on the side more.

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    I want a single form (set to continouous) which will show referrals (from tblReferrals) and I want to place a listbox at the top of the form which shows users (from tblUsers). I want to select a user and have the corresponding referrals for that user displayed on the form.

    I can do it as suggested by using a subform, but (maybe im just being picky) would like to know how to achive it without the need of a subform.

  6. #6
    Join Date
    Oct 2010
    Posts
    51
    Quote Originally Posted by moss2076 View Post
    I want a single form (set to continouous) which will show referrals (from tblReferrals) and I want to place a listbox at the top of the form which shows users (from tblUsers). I want to select a user and have the corresponding referrals for that user displayed on the form.

    I can do it as suggested by using a subform, but (maybe im just being picky) would like to know how to achive it without the need of a subform.
    Yeah I just tried a split form and a continuous form. Subform is the best way.
    Last edited by bdabaum; 10-29-10 at 10:25.

  7. #7
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Ok, make a Form on the tblReferrals, (continouous), and put a Combo box (instead of List box), Find record. Link the Combo box on UserID field.

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    I dont think I have ever used a filter, what/how could it help achieve my aim?

  9. #9
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Do make as you want !

  10. #10
    Join Date
    Oct 2010
    Posts
    51
    Quote Originally Posted by moss2076 View Post
    I dont think I have ever used a filter, what/how could it help achieve my aim?
    Well I don't know what your table structure looks like but you would tie the combo box/list box AfterUpdate or Click event to create a filter on the form.
    Me.Filter = "ReferalAgent = '" & Me.lstbox.Column(i) & "'"
    Me.FilterOn = True

    Where i is the column with your related field.

  11. #11
    Join Date
    Aug 2004
    Posts
    364
    The table structure is simply -

    tblUsers
    userID(pk)
    userName
    userAddress

    tblReferrals
    referralID(pk)
    referralDate
    referralTime
    userID

    one userID can have many referralID's.

  12. #12
    Join Date
    Oct 2010
    Posts
    51
    Quote Originally Posted by moss2076 View Post
    The table structure is simply -

    tblUsers
    userID(pk)
    userName
    userAddress

    tblReferrals
    referralID(pk)
    referralDate
    referralTime
    userID

    one userID can have many referralID's.
    Then,
    'assuming that your ListBox has every field from tblUsers
    'and are in the same order.
    Me.Filter = "userID = '" & Me.UserList.Column(0) & "'"
    Me.FilterOn = True

  13. #13
    Join Date
    Aug 2004
    Posts
    364
    If i put the code -
    Code:
     Me.Filter = "userID = '" & Me.List10.Column(0) & "'"
    Me.FilterOn = True
    into the listbox onclick or after update I get a run time error 2001 "You cancelled the previous operation"

  14. #14
    Join Date
    Oct 2010
    Posts
    51
    Quote Originally Posted by moss2076 View Post
    If i put the code -
    Code:
     Me.Filter = "userID = '" & Me.List10.Column(0) & "'"
    Me.FilterOn = True
    into the listbox onclick or after update I get a run time error 2001 "You cancelled the previous operation"
    What other operation are you doing when you click the listbox value? Have you tried putting it in debug mode and stepping through it?

  15. #15
    Join Date
    Aug 2004
    Posts
    364
    No, there is no other code in operation when I click the listbox.

    I have stepped through it, and both lines come up with "true" in the immediate box.

    The record source of the continuous form is tblReferrals, and the rowsource of the list box is tblUsers. The listbox and the form both have userID as the related field.

    I must be doing something else incorrect or missing something else out.

Posting Permissions

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