error 2427 - Expression that has no value

[Guest]

I've been trying to figure out this problem and I just can't.

I'm working with a database that I didn't create but am trying to improve
upon, but I don't want to restructure the forms, so I'm working with what I
have - otherwise I'd build it a different way and probably not have this
problem.

I created VB code that filters a subform, which is based off of table [tbl
total receipts] where [posting date] = [SelectedDate] (which is a control on
the main form). If it were up to me, I'd just run a search, but others want
it this way.

So I created a field in the header of the subform, [part_search], that
filters the subform to the entry in [part_search] and then I have a
DoCmd.GoToControl to take the cursor to the entry point I want data entry to
begin in the subform.

All of that is working fine. Here's my problem:

When an entry made in [part_search] doesn't exist in the subform, I get the
error that DoCmd.GoToControl cannot find the field in the subform. So i
created a Dlookup to verify if [part_search] = ["an entry in subform"] and
DoCmd.GoToControl if true.

I even got this to work. But here's the odd thing. I can make one entry in
[part_search] that doesn't exist in the subform, and the code runs fine. But
after that first entry that doesn't exist, I get the 2427 error, "you entered
an expression that has no value," and it refers to the DLookup code. Any
help would be appreciated.

Here's the code:

Private Sub part_search_AfterUpdate()
Dim strWhere As String


With Me.part_search
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
Dim part_verify As String

part_verify = Nz(DLookup("Material", "[tbl total receipts]",
"Material = '" & part_search & "' AND " & [Posting Date] & " = " & Forms![frm
receipt audit]!SelectedDate))
If IsNull(part_verify) = False Then
DoCmd.GoToControl "[receipt verified]"
End If
strWhere = "[Material] = '" & .Value & "'"
Me.Filter = strWhere
Me.FilterOn = True
End If
End With


End Sub

 

[Jeff L]

I believe you have to tell Nz what to do when it encounters a null and
you didn't do that.

part_verify = Nz(DLookup("Material", "[tbl total receipts]",
"Material = '" & part_search & "' AND " & [Posting Date] & " = " &
Forms![frm
receipt audit]!SelectedDate),"")
If IsNull(part_verify) = "" Then
blah, blah, blah

Hope that helps!

 

[Guest]

I believe, by default, NZ's return if null, is null, if isnullvalue is left
blank. I tried what you mentioned, and the code wouldn't even run at all,
stating the If isnull(part_verify) = "" is a type mismatch.

I also tried "NO" as the Nz isnullvalue, but I still get the same 2427 error
after 1 entry that doesn't exist in the subform. Before then, it runs smooth
as ever - it's just after that first entry doesn't exist in the subform that
it starts giving the 2427 error whether the [part_search] exists in the
subform or not.

I'm dumbfounded.

 

[Jeff L]

Upon looking at your DLookup a little closer, you are missing single
quotes around the Forms![frm receipt audit]!SelectedDate. So it should
be:

part_verify = Nz(DLookup("Material", "[tbl total receipts]",
"Material = '" & part_search & "' AND " & [Posting Date] & " = '" &
Forms![frm receipt audit]!SelectedDate & "'"))

 

[Guest]

THanks for all your help so far.

Unfortunately this error is very elusive.

I had the part_search and part_verify data appear in my form as a test, and
they're both showing the right data, so it appears the DLookup is working
properly. But for some reason Access will throw up that error after the
first Dlookup finds nothing. Maybe it has something to do with the filter?
After the filter is applied, do you think it may cause some kind of problem
with Dlookup and I need to make sure the filter is set to false (turned off)
before allowing the dlookup to run again? I'm stabbing in the dark, so I'll
give it a try.

I also tried this, without success. It errors at the same moment.

part_verify = UCase$(Nz(DLookup("Material", "[tbl total receipts]",
"Material = '" & part_search & "' AND " & [Posting Date] & " = " &
datesearch)))
part_verify_test.Value = part_verify
part_search_test.Value = .Value
If part_verify = UCase$(.Value) Then

DoCmd.GoToControl "[receipt verified]"

 

[Guest]

Eureka!

I got it working. I was right about the filter. It was the filter causing
the error. I guess when the filter was applied to the subform, it was making
the [tbl total receipts] only show the part(s) that matched the filter? I
set a if filteron then filteron = false right before the dlookup and now I
have no problems.

Thanks again for your help. Wouldn't have figured that out on my own.