Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Kansas, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FindFirst with a text field?

    I am haveing a problem getting FindFirst to work with a text field. I have an inventory table that has a numeric ID field and a description. I want to be able to use a combo box using the invdesc and FindFirst. Here is the code I am using:

    Private Sub cmbSKUDescription_AfterUpdate()
    Dim rst As Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "tblinventory.invdesc = ' " & Me![cmbSKUDescription] & "'"
    If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
    End Sub

    I am not getting errors, it just doesn't work. I am using DAO and Access 2000

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindFirst with a text field?

    Hi,

    Did you try the dFirst function?
    Your call should look like this:

    strSKUDescription = dfirst("invdesc","tblinventory", "invdesc='" & Me![cmbSKUDescription] & "'")

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Kansas, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindFirst with a text field?

    Bart, I tried the following code:

    Dim strSKUDesc As String
    strSKUDesc = DFirst("invdesc", "tblinventory", "invdesc = ' " & Me![cmbSKUDescription] & "'")

    But I get this error: Run-time error '94': Invalid use of Null

    I don't understand...

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindFirst with a text field?

    Gary,
    I have a solution I use in my own code.
    You have asigned the string the value returned by DFirst. If this value is Null when the procedure is called, you will receive the error "Invalid use of Null" because the value is Null and not a string. To fix this you can either place the value assignment to a position in your code when the value is sure to be a string or change your "Dim" to "As Variant" (which allows nulls). Then it should fine. It just allocates a little more menmory usage.

    Bart, do you have a better solution?

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

    Re: FindFirst with a text field?

    Two things:

    First, FindFirst works in a recordset. You can't use it to find something in a table, only in a recordset.

    Second, if you're going to use a domain aggregate like DFirst, you need to allow for nulls. The simplest way is to wrap the whole thing in the Nz() function like this:

    strSKUDesc = Nz(DFirst("invdesc", "tblinventory", "invdesc = ' " & Me![cmbSKUDescription] & "'"),"")

    That will return an empty string if DFirst doesn't find a match.
    Charlotte

Posting Permissions

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