Allen Browne said:Simplest thing would be to look directly in the table to see if any of the
records have a null value.
This example assumes that the subform records come from Table2, which has
a numeric field named SubID that matches a value in the MainID field on
the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
CJ said:Hi Groupies
How can I tell if one of the end date values is null on my continuous
subform? If an date is blank I need to disable a button.,
CJ said:Great, thanks Allen!
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Allen Browne said:Simplest thing would be to look directly in the table to see if any of
the records have a null value.
This example assumes that the subform records come from Table2, which has
a numeric field named SubID that matches a value in the MainID field on
the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
CJ said:Hi Groupies
How can I tell if one of the end date values is null on my continuous
subform? If an date is blank I need to disable a button.,
CJ said:So, I tried to implement the code that Allen gave me and then realized
that I don't know which event to put it in.
I have tried it in the Load event of the subform and also the Current
event of the main.
If an End Date in the sub is null, then I need a button on the main
to be disabled.
Simplest thing would be to look directly in the table to see if any
of the records have a null value.
This example assumes that the subform records come from Table2,
which has a numeric field named SubID that matches a value in the
MainID field on the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
How can I tell if one of the end date values is null on my continuous
subform? If an date is blank I need to disable a button.
Allen Browne said:Sounds like you want the Current event of the main form.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
CJ said:So, I tried to implement the code that Allen gave me and then realized
that I don't know which event to put it in.
I have tried it in the Load event of the subform and also the Current
event of the main.
If an End Date in the sub is null, then I need a button on the main
to be disabled.
Simplest thing would be to look directly in the table to see if any
of the records have a null value.
This example assumes that the subform records come from Table2,
which has a numeric field named SubID that matches a value in the
MainID field on the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
How can I tell if one of the end date values is null on my continuous
subform? If an date is blank I need to disable a button.
CJ said:Hi again Allen
I put in an Else msgbox to test the results and I keep getting the Else
result
even though both items on my subform have null End Dates.
Private Sub Form_Current()
Dim strWhere As String
Dim varResult As Variant
With Me.lngWOId
If Not IsNull(.Value) Then
strWhere = "([fsubMonthEndDetails].Form![Work Order ID] = " &
.Value & ")_
And ([fsubMonthEndDetails].Form![End Date] is null) "
varResult = DLookup("[fsubMonthEndDetails].Form![Work Order ID]
",_
"tblWorkOrderDetails", strWhere)
If IsNull(varResult) Then
MsgBox "There is a blank end date"
Else
MsgBox "no blanks"
End If
End If
End With
End Sub
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Allen Browne said:Sounds like you want the Current event of the main form.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
CJ said:So, I tried to implement the code that Allen gave me and then realized
that I don't know which event to put it in.
I have tried it in the Load event of the subform and also the Current
event of the main.
If an End Date in the sub is null, then I need a button on the main
to be disabled.
Simplest thing would be to look directly in the table to see if any
of the records have a null value.
This example assumes that the subform records come from Table2,
which has a numeric field named SubID that matches a value in the
MainID field on the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
How can I tell if one of the end date values is null on my continuous
subform? If an date is blank I need to disable a button.
Douglas J. Steele said:I'm surprised you're not getting an error.
You can't put a reference to a form in a DLookup like that. What's in the
quotes (before the equal sign) can only be a field in the table (or query)
specified in the second parameter of the DLookup (tblWorkOrderDetails in
your case). The same's true of the first parameter.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
CJ said:Hi again Allen
I put in an Else msgbox to test the results and I keep getting the Else
result
even though both items on my subform have null End Dates.
Private Sub Form_Current()
Dim strWhere As String
Dim varResult As Variant
With Me.lngWOId
If Not IsNull(.Value) Then
strWhere = "([fsubMonthEndDetails].Form![Work Order ID] = " &
.Value & ")_
And ([fsubMonthEndDetails].Form![End Date] is null) "
varResult = DLookup("[fsubMonthEndDetails].Form![Work Order
ID] ",_
"tblWorkOrderDetails", strWhere)
If IsNull(varResult) Then
MsgBox "There is a blank end date"
Else
MsgBox "no blanks"
End If
End If
End With
End Sub
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Allen Browne said:Sounds like you want the Current event of the main form.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
So, I tried to implement the code that Allen gave me and then realized
that I don't know which event to put it in.
I have tried it in the Load event of the subform and also the Current
event of the main.
If an End Date in the sub is null, then I need a button on the main
to be disabled.
Simplest thing would be to look directly in the table to see if any
of the records have a null value.
This example assumes that the subform records come from Table2,
which has a numeric field named SubID that matches a value in the
MainID field on the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is
Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
How can I tell if one of the end date values is null on my
continuous subform? If an date is blank I need to disable a button.
CJ said:Hi Doug
OK, so I changed the Dlookup to be:
varResult = DLookup("[Work Order ID]", "tblWorkOrderDetails", strWhere)
But no change in the result. I still get the message of "No Blanks".
So, I added end dates to the two records, closed and opened the form and I
get
the true result "There is a blank end date". This makes me confused about
what
the strWhere statement is doing. Shouldn't things only progress it the End
Date Is Null?
In a nutshell, I can work with this result, I just don't understand it.
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Douglas J. Steele said:I'm surprised you're not getting an error.
You can't put a reference to a form in a DLookup like that. What's in the
quotes (before the equal sign) can only be a field in the table (or
query) specified in the second parameter of the DLookup
(tblWorkOrderDetails in your case). The same's true of the first
parameter.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
CJ said:Hi again Allen
I put in an Else msgbox to test the results and I keep getting the Else
result
even though both items on my subform have null End Dates.
Private Sub Form_Current()
Dim strWhere As String
Dim varResult As Variant
With Me.lngWOId
If Not IsNull(.Value) Then
strWhere = "([fsubMonthEndDetails].Form![Work Order ID] = " &
.Value & ")_
And ([fsubMonthEndDetails].Form![End Date] is null) "
varResult = DLookup("[fsubMonthEndDetails].Form![Work Order
ID] ",_
"tblWorkOrderDetails", strWhere)
If IsNull(varResult) Then
MsgBox "There is a blank end date"
Else
MsgBox "no blanks"
End If
End If
End With
End Sub
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Sounds like you want the Current event of the main form.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
So, I tried to implement the code that Allen gave me and then realized
that I don't know which event to put it in.
I have tried it in the Load event of the subform and also the Current
event of the main.
If an End Date in the sub is null, then I need a button on the main
to be disabled.
Simplest thing would be to look directly in the table to see if any
of the records have a null value.
This example assumes that the subform records come from Table2,
which has a numeric field named SubID that matches a value in the
MainID field on the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is
Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
How can I tell if one of the end date values is null on my
continuous subform? If an date is blank I need to disable a button.
Douglas J. Steele said:And what's strWhere now?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
CJ said:Hi Doug
OK, so I changed the Dlookup to be:
varResult = DLookup("[Work Order ID]", "tblWorkOrderDetails", strWhere)
But no change in the result. I still get the message of "No Blanks".
So, I added end dates to the two records, closed and opened the form and
I get
the true result "There is a blank end date". This makes me confused about
what
the strWhere statement is doing. Shouldn't things only progress it the
End Date Is Null?
In a nutshell, I can work with this result, I just don't understand it.
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Douglas J. Steele said:I'm surprised you're not getting an error.
You can't put a reference to a form in a DLookup like that. What's in
the quotes (before the equal sign) can only be a field in the table (or
query) specified in the second parameter of the DLookup
(tblWorkOrderDetails in your case). The same's true of the first
parameter.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi again Allen
I put in an Else msgbox to test the results and I keep getting the Else
result
even though both items on my subform have null End Dates.
Private Sub Form_Current()
Dim strWhere As String
Dim varResult As Variant
With Me.lngWOId
If Not IsNull(.Value) Then
strWhere = "([fsubMonthEndDetails].Form![Work Order ID] = "
& .Value & ")_
And ([fsubMonthEndDetails].Form![End Date] is null) "
varResult = DLookup("[fsubMonthEndDetails].Form![Work Order
ID] ",_
"tblWorkOrderDetails", strWhere)
If IsNull(varResult) Then
MsgBox "There is a blank end date"
Else
MsgBox "no blanks"
End If
End If
End With
End Sub
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Sounds like you want the Current event of the main form.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
So, I tried to implement the code that Allen gave me and then
realized
that I don't know which event to put it in.
I have tried it in the Load event of the subform and also the Current
event of the main.
If an End Date in the sub is null, then I need a button on the main
to be disabled.
Simplest thing would be to look directly in the table to see if any
of the records have a null value.
This example assumes that the subform records come from Table2,
which has a numeric field named SubID that matches a value in the
MainID field on the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is
Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
How can I tell if one of the end date values is null on my
continuous subform? If an date is blank I need to disable a
button.
CJ said:My code is:
Private Sub Form_Current()
Dim strWhere As String
Dim varResult As Variant
With Me.lngWOId
If Not IsNull(.Value) Then
strWhere = "([fsubMonthEndDetails].Form![Work Order ID] = " &_
.Value & ") And ([fsubMonthEndDetails].Form![End Date] Is Null)
"
varResult = DLookup("[Work Order ID] ","tblWorkOrderDetails", strWhere)
If IsNull(varResult) Then
MsgBox "There is a blank end date"
Else
MsgBox "no blanks"
End If
End If
End With
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Douglas J. Steele said:And what's strWhere now?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
CJ said:Hi Doug
OK, so I changed the Dlookup to be:
varResult = DLookup("[Work Order ID]", "tblWorkOrderDetails", strWhere)
But no change in the result. I still get the message of "No Blanks".
So, I added end dates to the two records, closed and opened the form and
I get
the true result "There is a blank end date". This makes me confused
about what
the strWhere statement is doing. Shouldn't things only progress it the
End Date Is Null?
In a nutshell, I can work with this result, I just don't understand it.
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
I'm surprised you're not getting an error.
You can't put a reference to a form in a DLookup like that. What's in
the quotes (before the equal sign) can only be a field in the table (or
query) specified in the second parameter of the DLookup
(tblWorkOrderDetails in your case). The same's true of the first
parameter.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi again Allen
I put in an Else msgbox to test the results and I keep getting the
Else result
even though both items on my subform have null End Dates.
Private Sub Form_Current()
Dim strWhere As String
Dim varResult As Variant
With Me.lngWOId
If Not IsNull(.Value) Then
strWhere = "([fsubMonthEndDetails].Form![Work Order ID] = "
& .Value & ")_
And ([fsubMonthEndDetails].Form![End Date] is null) "
varResult = DLookup("[fsubMonthEndDetails].Form![Work Order
ID] ",_
"tblWorkOrderDetails", strWhere)
If IsNull(varResult) Then
MsgBox "There is a blank end date"
Else
MsgBox "no blanks"
End If
End If
End With
End Sub
--
Thanks for taking the time!
CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Sounds like you want the Current event of the main form.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
So, I tried to implement the code that Allen gave me and then
realized
that I don't know which event to put it in.
I have tried it in the Load event of the subform and also the
Current event of the main.
If an End Date in the sub is null, then I need a button on the main
to be disabled.
Simplest thing would be to look directly in the table to see if
any
of the records have a null value.
This example assumes that the subform records come from Table2,
which has a numeric field named SubID that matches a value in the
MainID field on the main form:
Dim strWhere As String
Dim varResult As Variant
With Me.Parent!MainID
If Not IsNull(.Value) Then
strWhere = "([SubID] = " & .Value & ") AND ([EndDate] Is
Null)"
varResult = DLookup("SubID", "Table2", strWhere)
If Not IsNull(varResult) Then
MsgBox "There is a blank end date."
End If
End If
End With
How can I tell if one of the end date values is null on my
continuous subform? If an date is blank I need to disable a
button.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.