Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box Help (XP)

    1. How or where is the CompanyID specified? You wrote "CompanyID is equal" - but to what?

    2. Is there a particular reason to use a subform? If the list box is unbound (and you were correct in assuming that it should be unbound), it would be easier to put the personnel info directly on the main form instead of in a subform.

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Help (XP)

    If you can post a sample db, instead of a picture, I can show you how to do that. I'm walking out the door right now, otherwise I'd whip one up.

  3. #3
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Box Help (XP)

    <P ID="edit" class=small>(Edited by HansV on 08-Oct-03 12:14. Shrunk wide screenshot in size to reduce horizontal scrolling.)</P>I'm creating a Database that needs to have a List Box that displays just the personnel that work for a certain company. I'm not really familiar with List Boxes, but I've tried to create this anyway. Unfortunately, the List Box displays ALL the Personnel for All the Companies. I know that when I use Combo Boxes, I want a specific value stored in the Foreign Key Field, but this is (obviously) only one record, i.e., Select John, then John gets stored in the Foreign Key Field.

    With a List Box, many values have to be displayed, as (again, obviously), many personnel can work for a company. Therefore, when I add the List Box, I presume that I don't want the values to be stored in a Field. However, as stated, with an Unbound Form, all Records are displayed.

    Can someone please advise me how I can do the following:

    1. Display JUST the Personnel that work for a particular company (i.e., CompanyID is equal)
    2. Get the List Box to work so that when someone clicks a person in the List Box, all information pertaining to that person becomes visible (ie, the Personnel Form is a Subform in the Company Form, and the Subform is set to single, so only 1 Personnel Record's Fields display at a time, but clicking the List Box causes the 1 Record to 'update' to show the corresponding personnel record).

    I've attached an example of the Form I'm using that I hope will explain better what I need.

    Thanks in advance for any help you can provide. Sorry if this is such a basic question! <img src=/S/please.gif border=0 alt=please width=31 height=23>
    Attached Images Attached Images

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Help (XP)

    The CompanyID (actually the BranchID) is the Primary Key in the Branch Table, and the BranchID is the Foreign Key in the Personnel Table, ie, 1 Branch can have many staff.

    I thought I had to use a Subform when I have Joined Table in a Form??? I've seen the Access Wizard do the same thing when you use Data from 2 Tables. Of course, as you say, I presume an Unbound List Box can have data from any source? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    I was also going to use a Subform as users may want to Add to the Personnel Table.

    If it helps, I've attached a sample DB that outlines where I'm at and what I'm trying to do.

    As always, thanks for your help. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Attached Files Attached Files

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

    Re: List Box Help (XP)

    I had misunderstood the situation, I thought that the list box was on the main form.

    You can get the behavior you want (I think) by applying the following modifications. The modified database is attached. (I removed superfluous database objects)

    1. Add code in the On Current event of the main form to update the list box in the subform:

    Private Sub Form_Current()
    Me.frm_personnel_details.Form.List34.Requery
    End Sub

    2. Change the row source of the list box to select only personnel from the branch selected in the main form. This is done by putting [Forms]![tbl_branch]![branch_id] in the criteria line for branch_id. The row source becomes

    SELECT id_personnel, branch_id, name, title FROM tbl_personneldetails WHERE branch_id=[Forms]![tbl_branch]![branch_id]

    3. Write code in the On Click event of the list box to move to the corresponding record in the subform. It would also be nice if navigating to another record in the subform would change the selected item in the list box, but this would cause a problem (click in list box -> move to different record in subform -> change selection in list box). It takes a little trick to avoid this. This is the complete code for the subform:

    Private blnFromList As Boolean

    Private Sub Form_Current()
    If blnFromList = False Then
    Me.List34 = Me.id_personnel
    End If
    End Sub

    Private Sub List34_Click()
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[id_personnel] = " & Str(Nz(Me![list], 0))
    If Not rs.EOF Then
    blnFromList = True
    Me.Bookmark = rs.Bookmark
    blnFromList = False
    End If
    End Sub

    The module-level variable blnFromList is used to signal when the On Current event is caused by a click in the list box. If it is True, the code in On Current is skipped.
    Attached Files Attached Files

  6. #6
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Help (XP)

    Thanks Hans

    As usual, you're a genius!

    Your example works flawlessly, although when I copied and pasted it from your database to mine, it didn't work?!?? I had to just copy the Forms across, and then it worked? I'm extremely grateful but very confused! LOL!

    I do know, however, that I would *NEVER* have figured that out myself! I'm actually surprised more Access Objects don't come with this kind of functionality built-in, similar to the Combo Box Wizard. We can't be the only people who have ever wanted a List Box to work in this way! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Anyway, as usual, thanks again for being so helpful and so brilliant. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

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

    Re: List Box Help (XP)

    I don't know exactly why the database I attached didn't work "out of the box". It may be a references problem, or I may have deleted a bit too much. Anyway, it isn't important.

  8. #8
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Help (XP)

    I probably didn't state that properly. The database you attached worked perfectly.

    However, when I copied & pasted the VBA and Row Source, etc, into my DB, it returned errors?

    I overcame it by simply copying and pasting the whole Forms from your DB to mine, as they were pretty much identical.

    Anywho, it's working, so that's the important thing.

    Thanks again. [img]/forums/images/smilies/smile.gif[/img]

  9. #9
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Help (XP)

    Hi Hans

    Can I ask a question regarding a message I get when I use the Form with the List Box? It says the Record Source does not exist, but yet it goes on to work. I've heard that strange messages like this can be a sign of database corruption . . . do you think this is why the DB is displaying this message, or can you think of another explanation? The List Box doesn't run this query, it's source is different, so I don't understand why this message only appears when the List Box populates for the first time. I know this isn't your problem, but I appreciate your insight.

    On a different note, I thought I would use the kind of Query you wrote to Limit another Combo Box's Fields, but it *SO* doesn't work, and it's doing my head in. It's obviously above me, so I'm hoping you can tell me what I'm doing wrong.

    I have another Form, called 'frm_branch_communications' that has several objects on it: a Communications ID Field; a Branch ID Field (which is bound to Branch Communications Form, i.e., many Communications take place that relate to each Branch); a Staff ID Field (the person in our Company who made contact with someone in the Brach); a Communcations Type Field; and a Personnel ID Field (i.e., the Person in the Branch with whom we communicated).

    Similar to the other Form, I would like that only the Personnel who work for the Branch appear in the Personnel Combo Box. I therefore created a Query that included the BranchID and the Personnel Name, and in the Query I added the Criteria that branchID shoud = the Form's Branch ID:

    Here's the SQL that forms the Source of the Combo Box:
    SELECT tbl_personneldetails.id_personnel, tbl_personneldetails.branch_id, tbl_personneldetails.name FROM tbl_personneldetails WHERE (((tbl_personneldetails.branch_id)=frm_communicati on_details!branch_id));

    However, when I open the Form, the Combo Box has no values, no matter what Record I go to.

    Can you see what I'm doing wrong?

    As always, thanks for any insight you can provide with regard to these 2 issues.
    Attached Images Attached Images

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

    Re: List Box Help (XP)

    1. Database corruption is not the first thing I would think of. I suspect that the SQL from the error message lurks somewhere in the form. If you can't locate it, consider using a find-and-replace utility such as Find and Replace. It's not free, but you can download a free trial version (with a time limitation),

    2. Your SQL is

    SELECT tbl_personneldetails.id_personnel, tbl_personneldetails.branch_id, tbl_personneldetails.name FROM tbl_personneldetails WHERE (((tbl_personneldetails.branch_id)=frm_communication_details!branch_id));

    In the first place, it you want to refer to a form, you must precede its name with Forms!
    In the second place, you stated higher up that the form is called frm_branch_communications, or am I confusing things now?

  11. #11
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Help (XP)

    Thanks Hans.

    I'll look into it, but the thing that surprises me is the that the List Box works perfectly . . . I would have thought that if there were a name error, the List Box wouldn't work? Anyway, I'll scour the objects and code.

    Regarding the Combo Box . . . it works great (now that I added the 'Forms!' in front of the form name) as long as only the Communications form is open. As soon as I open the Main Form on which the Communications Subform is embedded, it prompts me to enter the BranchID number; if I enter the BranchID number, then it works, but obviously Access should be able to tell what the Branch ID Number is, shouldn't it???

    I don't understand why it works on a stand-alone form but not when it becomes a subform? I tried replacing the Subform Name with the Main Form name, but this didn't work either.

    BTW, as you can probably now guess, 'Frm_Communication_Details' is the Form I built to be part of the Main Form 'frm_Branch_Communications'.

    Thanks again for your help.

    I owe you a fortune!

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

    Re: List Box Help (XP)

    The Forms collection contains all open forms. A subform on a main form is not considered to be 'open', so it is not part of the Forms collection. If you want to refer to a control on a subform, you have to include the main form:

    Forms!MainFormName!SubFormName!ControlName

    In your case, this becomes

    Forms!frm_Branch_Communications!frm_communication_ details!branch_id

    See Forms: Refer to Form and Subform properties and controls on the Access web for a comprehensive overview.

  13. #13
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Help (XP)

    Thanks Hans, and thanks for the link. I'll peruse that site--it looks great!

    As always, you're a star! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  14. #14
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box Help (XP)

    I'm going to have to be a royal pain, but this isn't working. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I've set the Control Source for the Combo Box to the following:

    SELECT tbl_personneldetails.id_personnel, tbl_personneldetails.name, tbl_personneldetails.branch_id FROM tbl_personneldetails WHERE (((tbl_personneldetails.branch_id)=Forms!frm_Branc h_Communications!frm_communication_details!branch_ id));

    And all I get is a Blank Combo Box on every form, when I try to select a value.

    It's very frustrating (more so for you than for me).

    I really do want to understand this and to understand what I'm doing wrong.

    From your posts and from checking the Website you linked me to, it would appear that the criteria is correct, but it's obviously not working.

    I'm so sorry to be a pain, but I would really like to get this working.

    As always, thanks in advance for your help.

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

    Re: List Box Help (XP)

    You shouldn't set the Control Source of the combo box to this SQL string, but the Row Source property.

Page 1 of 2 12 LastLast

Posting Permissions

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