Results 1 to 5 of 5

Thread: Form Lookup

  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Lookup

    Is there a way using a form lookup combo box control (named cmbLookup) where a portion of the code currently says:

    rs.FindFirst "[ENum] = "& Str(Me![cmbLookup])

    to have a user input only the rightmost 1 - 5 digits of a 8 digit autonumber.

    For example: the entire ENum field entry I want to find is 69000362. I would like my user to be able to enter 362 and be able to locate the record.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    New Lounger
    Join Date
    Jan 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Lookup

    Hi, Judy.

    You could try what is discussed in a thread a few pages back called "Like statement". (If I knew how to make this a link I would. Could someone let me know? [img]/w3timages/icons/blush.gif[/img]) I am not sure that this would work in .FindFirst. You would have to play with it a little to see if it would.

    HTH

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Form Lookup

    A Like operator will work in a FindFirst criteria string, but I think you have a different problem here. If you're using a combobox control, the value that will be picked up in the expression is the value of the control, not what the user typed in. In that case, there isn't any point in a like operator. Either you have a matching value or you don't. If you don't, FindFirst won't work anyhow.

    The more customary way to use unbound comboboxes to find a record is with something often called recordsetclone navigation. In the afterupdate event of the combobox, you do something like this:

    <pre>Private Sub cmbLookup_AfterUpdate()
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    With rst
    If Not .EOF Then
    .FindFirst "[ENum]='" & [cmbLookup] & "'"
    If Not .NoMatch Then
    Me.Bookmark = .Bookmark
    End If
    End If
    End With
    Set rst = Nothing
    End Sub</pre>


    I would caution you about using Enum as a field name, however. Starting in VBA6/Access 2000, Enum is a reserved word used to define a collection of named constants.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Form Lookup

    Lonnie,

    Find the thread you want and view it in collapsed mode (as opposed to flat). Find the individual message you want and right click it. The properties dialog will includes an Address (URL), which you can highlight and copy to the clipboard with a Ctrl+C. For example, the link for Judy's original post was this: http://www.wopr.com/cgi-bin/w3t/showflat.p...cc&Number=21269. Then in the reply message box, you can either click the tagPanel hotlink above to insert the markup tags, or just type in the markup tags [ url ] address [/ url ]. I've put spaces in the markup tags so they show up as text; you don't have spaces in them normally. Then just paste the address over the placeholder between the tags.
    Charlotte

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Form Lookup

    Hi Lonnie,
    Just for the sake of completeness, I would add to Charlotte's post that you can also use this format:
    [ url=www.whatever.com ]description text here[ /url] so that people will see 'description text here' as the hyperlink rather than the actual url. Again I've added spaces so you can see the tags.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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