DLookup: 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

 

[Allen Browne]

Hi Justin. Your search form returns only the records that match, and (I
presume) does not allow new records. As a result, the Detail section of the
form goes completely blank when there is no match.

Under those conditions, there is a bug in Access such that it cannot figure
out what's happening with the controls. The controls in the Form Header and
Form Footer sections are still visible, and may still have a Value, but
Access cannot find it reliably or understand simple code that ought to work.
This happens in all versions of Access. More info and an example of the
problem:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

While I can confirm that the problem is with Access, not with you, I can't
provide a solution. Particularly if the form's recordsource is a read-only
query, you are stuck. But if the only reason that the form cannot display
the new record is that you set its AllowAdditions property, turn the
property off, and block the new entry by cancelling its BeforeInsert
instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

I figured out a work-around by running me.filteron = false before the dlookup
code. It appeared that the filter being on caused an issue with the dlookup
function.

Hi Justin. Your search form returns only the records that match, and (I
presume) does not allow new records. As a result, the Detail section of the
form goes completely blank when there is no match.

Under those conditions, there is a bug in Access such that it cannot figure
out what's happening with the controls. The controls in the Form Header and
Form Footer sections are still visible, and may still have a Value, but
Access cannot find it reliably or understand simple code that ought to work.
This happens in all versions of Access. More info and an example of the
problem:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

While I can confirm that the problem is with Access, not with you, I can't
provide a solution. Particularly if the form's recordsource is a read-only
query, you are stuck. But if the only reason that the form cannot display
the new record is that you set its AllowAdditions property, turn the
property off, and block the new entry by cancelling its BeforeInsert
instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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