Disable button if SQL is null (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
Hello, I have a SQL that works but with one fault. If there isn't any records for dteCompleted then it disables the button and if there is records it enables them. The issue I'm having is if in my qryEmployeeRequirements there is one record that doesn't have a result the button still is enabled when I need it disabled. What needs added to VBA to accommodate for that?

Code:
Public Function RstButton()
 
 'Show/ Hide print button
    Dim dteCompleted As Variant
    Dim strCriteria As String
  
    strCriteria = "EmpID = " & Forms!frmFunctionalDataEntry!frmRequirementsSubform.Form!EmpID & " And FuncID = " & Forms!frmFunctionalDataEntry!frmRequirementsSubform.Form!FuncID
         dteCompleted = DLookup("DateCompleted", "qryEmployeeRequirements", strCriteria)
         
         If (Not IsNull(dteCompleted)) Then
         Forms!frmFunctionalDataEntry!cmdPrintFunction.Caption = "Print completed " & Forms!frmFunctionalDataEntry!lstArea.Column(1) & " report."
         Forms!frmFunctionalDataEntry!cmdPrintFunction.Visible = True
         Forms!frmFunctionalDataEntry!cmdPrintFunction.Enabled = True
    End If
         If (IsNull(dteCompleted)) Then
         Forms!frmFunctionalDataEntry!cmdPrintFunction.Visible = True
         Forms!frmFunctionalDataEntry!cmdPrintFunction.Caption = "Function Not Complete, Printing disabled"
         Forms!frmFunctionalDataEntry!cmdPrintFunction.Enabled = False
    End If
 End Function
 

June7

AWF VIP
Local time
Yesterday, 17:36
Joined
Mar 9, 2014
Messages
5,423
Well, your SQL is not at fault, it's the VBA.

If the form is Datasheet or Continuous and this button is in Detail section, the setting will apply to ALL instances of the button because there really is only one button control. In other words, it cannot be enabled/disabled dynamically per record.
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
The button is in the header and the DateCompleted is in the continuous. If its looking up to see if my qryEmployeeRequirements has any null records then it should work right?
 

June7

AWF VIP
Local time
Yesterday, 17:36
Joined
Mar 9, 2014
Messages
5,423
Where are you calling that function?

DLookup returns value from first record it encounters that meets the criteria. How would it know if there are any other records in the query that don't have value? Why would there be records without DateCompleted for the given criteria?
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
Where are you calling that function?

DLookup returns value from first record it encounters that meets the criteria. How would it know if there are any other records in the query that don't have value? Why would there be records without DateCompleted for the given criteria?

I'm calling it from a module. Next the reason I know its displaying a null record is I open the form and enter the necessary controls then move the form out of the way and open the query qryEmployeeRequirements and it shows the record is indeed missing.
 

June7

AWF VIP
Local time
Yesterday, 17:36
Joined
Mar 9, 2014
Messages
5,423
I expected you would call function from some module. What event?

I don't understand the "record is indeed missing". The DLookup is apparently finding a record that meets the criteria.

Going in circles. If you want to provide db for analysis, follow instructions at bottom of my post.
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
Cant, to much information to remove. Thanks anyway.....
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
I did find out however its only checking the first record and if there isn't is a date on the first record then it works and if there is a date in the first record and the date is missing in the second record then it doesn't work.
 

GinaWhipp

AWF VIP
Local time
Yesterday, 21:36
Joined
Jun 21, 2011
Messages
5,901
So, you have a problems... This will not work on a continuous form because it will only see the first record AND the button will only *react* to that.

You can *hack* this if... Is your Primary Key a part of your recordsource? And, is the Primary Key unique? I know that seems like an odd question but if you are showing the many side the PK may not be unique and then the hack will not work.
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
Gina, below is a SQL that displays the info in question. Can I just add a criteria in the DateCompleted that will not show the records if there isn't a DateCompleted missing and show the records if there isn't a DateCompleted missing.

Code:
SELECT tblFunctionRequirements.FuncID, tblEmployeeAchievements.EmpID, tblEmployeeAchievements.DateCompleted
FROM tblFunctionRequirements INNER JOIN tblEmployeeAchievements ON tblFunctionRequirements.FuncReqID = tblEmployeeAchievements.FuncReqID
WHERE (((tblFunctionRequirements.FuncID)=[Forms]![frmFunctionalDataEntry]![frmRequirementsSubform].[Form]![FuncID]) AND ((tblEmployeeAchievements.EmpID)=[Forms]![frmFunctionalDataEntry]![frmRequirementsSubform].[Form]![EmpID]));

Here there is a PK is FuncReqID would I have to refer to it in the form so it works?
 
Last edited:

GinaWhipp

AWF VIP
Local time
Yesterday, 21:36
Joined
Jun 21, 2011
Messages
5,901
Hmm, did quite get your post but yes, you can just add Criteria to eliminate the records that do not have DateCompleted. Just put Is Not Null or <>"".
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
Hmm, did quite get your post but yes, you can just add Criteria to eliminate the records that do not have DateCompleted. Just put Is Not Null or <>"".


No not eliminate the null records I want to not show any records if DateCompleted has one Null record
 

GinaWhipp

AWF VIP
Local time
Yesterday, 21:36
Joined
Jun 21, 2011
Messages
5,901
I do not understand what you mean by *not show any records* if DateCompleted has one null record. You want a blank line?
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
Sorry was on vacation....

What I mean is if DateCompleted has one null record then don't display any records.
 

isladogs

MVP / VIP
Local time
Today, 01:36
Joined
Jan 14, 2017
Messages
18,186
Run a check
Code:
if DCount("*","QueryName","DateCompleted Is Null")=0 Then
...run your code
End If
 

oxicottin

Learning by pecking away....
Local time
Yesterday, 21:36
Joined
Jun 26, 2007
Messages
851
Thank you isladogs that helped... I got it working with,

Code:
 Public Function RstButton()
 
 'Show/ Hide print button
    Dim dteCompleted As Variant
    Dim strCriteria As String
    Dim strCriteriaNull As String
    
    strCriteriaNull = "EmpID = " & Forms!frmFunctionalDataEntry!frmRequirementsSubform.Form!EmpID & " And FuncID = " & Forms!frmFunctionalDataEntry!frmRequirementsSubform.Form!FuncID & " And DateCompleted IS NULL"
    
    strCriteria = "EmpID = " & Forms!frmFunctionalDataEntry!frmRequirementsSubform.Form!EmpID & " And FuncID = " & Forms!frmFunctionalDataEntry!frmRequirementsSubform.Form!FuncID
         dteCompleted = DLookup("DateCompleted", "qryEmployeeRequirements", strCriteria)
         
         If DCount("*", "qryEmployeeRequirements", strCriteriaNull) = 1 Then
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Visible = True
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Caption = "Function Not Complete, Printing disabled"
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Enabled = False
    
         ElseIf (Not IsNull(dteCompleted)) Then
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Caption = "Print completed " & Forms!frmFunctionalDataEntry!lstArea.Column(1) & " report."
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Visible = True
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Enabled = True
  
         ElseIf (IsNull(dteCompleted)) Then
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Visible = True
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Caption = "Function Not Complete, Printing disabled"
            Forms!frmFunctionalDataEntry!cmdPrintFunction.Enabled = False
         Else
           'result_else
            
    End If
 End Function
 

Users who are viewing this thread

Top Bottom