FindFirst with a text field?

Topic

New Reply

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

Reply | Quote

Replies

Hi,

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

strSKUDescription = dfirst(“invdesc”,”tblinventory”, “invdesc='” & Me![cmbSKUDescription] & “‘”)

Reply | Quote

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…

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote