J
Justin
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
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