Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Sel Combo Box (Access 2000)

    I am trying some of the code from one of Woodys writers for Record Selector combo boxes as shown below:

    Private Sub Combo14_AfterUpdate()
    Dim strSearch As String



    'For numeric IDs
    strSearch = "[Emp No] = " & Me![cboSelect]

    'Find the record that matches the control.
    Me.RecordsetClone.FindFirst strSearch
    Me.Bookmark = Me.RecordsetClone.Bookmark

    End Sub

    I have applied this to the after update event of my form field, and put the combo box into the form header as she suggested. What it does is go directly to the Emp No in the form for the name that I select in the combo box, but the rest of the form fields remain on the current record. Lets say, I choose Diana Smith who is Emp 101 from the combo box. The form goes to Emp 101 in the Emp No field, but all other fields remain on the current record say for instance Bob Brown, whose record I happened to be on before I made my choice in the combo box. I am attaching my database in hopes that you can look at the employee form, and look at the field in question (within the header), and tell me what I am missing in the VBA after update code.
    Thank you,
    Nannette M. Padgett
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Record Sel Combo Box (Access 2000)

    <P ID="edit" class=small>(Edited by MarkD on 30-Sep-02 17:03. Edited by MarkD to replace attachment; fixed a couple of small format bugs in combo box.)</P>Your problem is the combo was bound to EMP_NO field. Combos used for record navigation should be UNbound. Combo AfterUpdate code was OK. I modified combo Row Source SQL to concatenate employee first & last names:
    <pre>SELECT Employees.[Emp No], [Last Name] & ", " & [First Name] AS [Employee Name]
    FROM Employees;</pre>

    Also added Form On Current event to "synchronize" the combo w/current record:
    <pre>Private Sub Form_Current()
    Me.cboSelect = Me.Emp_No
    End Sub</pre>

    Plus added "Option" declarations for form module. See revised database in attached ZIP file.

    HTH
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Sel Combo Box (Access 2000)

    Thank you Mark,
    It worked beautifully. I do appreciate your digging into the file and helping so very much. You guys are so good.
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Sel Combo Box (Access 2000)

    Mark,
    Got a problem. How did you get a Row Source line? When I go into the properties of the Unbound text box, there is no row source line to add an SQL query on?
    Help, this is the last trip in my path I believe.
    EDITED
    I got it. Had to change the text box to a combo box first...sorry
    NMpadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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