Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Camp Lejeune, NC
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Find? (A2K SR-1)

    I'm really just beginning to understand the capabilities of Access. But how to get there is another story. If I have a tabluar form with all but one field locked, is there a way to put the cursor in the Last Name field and just start typing a name and have it come up?

    Thank you for any direction you can offer.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Find? (A2K SR-1)

    Are you asking how to find a record or do you want the name would be entered automatically in a field ?
    Francois

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Camp Lejeune, NC
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Find? (A2K SR-1)

    Well, like in my Outlook Global Mailing list, I can start typing someone's name and it automatically rises to the top of the list. So, it would be a find command without any windows or queries actually popping up.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Find? (A2K SR-1)

    In the footer or the header, create a unbound combobox (cmbnaam in my samplecode).
    As rowsource for the combo use the ID and the name fields of the table you want to search and set the ID as invisible.
    In the AfterUpdate of the combo box enter similar code :<pre>Private Sub cmbnaam_AfterUpdate()
    ' Find the record that matches the control.
    If Len(Me!cmbNaam) > 0 Then
    Me.RecordsetClone.FindFirst "[nameid] = " & Me![cmbNaam]
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    Me!Naam.SetFocus
    End Sub</pre>

    Set the property AutoExpand of the combo box to yes.
    If the type of your ID is text intead of numeric change the line Me.RecordsetClone.FindFirst "[nameid] = " & Me![cmbNaam] to
    <pre>Me.RecordsetClone.FindFirst "[nameid] = '" & Me![cmbNaam] & "'"</pre>

    This is not exacltly the same as in outlook but the name in the combo will be expanded to the first matching names with the characters you type.
    Francois

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    Camp Lejeune, NC
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Find? (A2K SR-1)

    I'm still quite the rookie. But I got it to work. Thanks a lot.

  6. #6
    New Lounger
    Join Date
    Jun 2002
    Location
    Camp Lejeune, NC
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Find? (A2K SR-1)

    Run into a little snag. I just tried to duplicate what I managed to do so easily yesterday, but it isn't working. I can't type in the combo box at all. The drop down menu will list all the last names in the field, but I can't select them either. I have made sure everything matches, but to no avail.

    Thanks for any help.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto Find? (A2K SR-1)

    What Francois posted is the code typically used for what is called navigation - it allows you to find a particular record based typically on the unique key for that record, and then display the contents of the record on a form. Combo boxes can also be used to display information where a table links to another table to provide a set of fixed choices. In that case, the combo box has to be bound to a field in the data records linked to the form. That kind of combo is useful for providing a set of ranks of military personnel for example, so you don't get typos and so forth. The data stored in the person record would typically be a number pointer to a record in a rank table with the name number as its key, and a field that showed the rank in text format. Let me suggest that you play around with the wizard for combo boxes so you can see how they can be used in different situations. (I stepped in for Francois since he's probably either watching the world cup matches, or is asleep - he lives in Europe.) Hope this helps.
    Wendell

  8. #8
    New Lounger
    Join Date
    Jun 2002
    Location
    Camp Lejeune, NC
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Find? (A2K SR-1)

    Thanks for stepping in. I do have a basic understanding of the combo boxes. What I don't understand is how it was able to look up names with in the query and autocorrect it until I had the name I wanted. For example, I start typing in my last name A-b-e and my name appears in the combo box. Hit enter and it jumps to that record in the tabular form.

    Here is the code that worked yesterday for one form, but not for another form today.

    Private Sub Combo17_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Last Name] = '" & Me![Combo17] & "'"
    Me.Bookmark = rs.Bookmark
    End Sub

    Thanks for the help Wendell.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto Find? (A2K SR-1)

    How did you create your combo box - manually or using the wizard? Is the RowSource for your combo box the same as yesterday? How many columns does you combo have? Is the first row hidden (width=0)?
    Wendell

  10. #10
    New Lounger
    Join Date
    Jun 2002
    Location
    Camp Lejeune, NC
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Find? (A2K SR-1)

    Yesterday, I ended up stumbling onto the wizard and that was easy enough. I tried that today, no luck. The row source is the same field, but a different query. It is just one column, the Last Name. On the first form, I was able to put two different combo boxes, one for Last Name and one for SSN so you could search for either and they work fine.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Auto Find? (A2K SR-1)

    The only thing I can suggest at this point is to check and see if your combo box has the autoexpand property turned on. What actually happens when you type - do no characters even appear, or does it simply not match one of the entries?

    One slight caution - I have seen this technique fail on occasion. It has to do with using ADO rather than DAO, and Microsoft changed the default in the conversion from Access97 to Access 2000. I'm sure that doesn't make much sense to you, but the technique used in Access97 always seems to work, and for some reason this one doesn't always. I think it has to do with RecordSetClones in ADO - but in any event we have switched to applying filters to forms in Access 2000 and 2002 when we use a combo box to find a record. It seems to work reliably all the time, and is not dependent on DAO or ADO. Hope this rambling makes some sense.
    Wendell

Posting Permissions

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