Problem referencing a control on a tabbed subform

[HillTJ]

Hi, another newby question. I have the following routine on the onchange event of a combobox on a subform called 'FRM_Inspect_Record subform' which is on the main form called 'FRM_Equipment'.

It works fine, although i do notice upon inspection that [Anniversary_Date] is the control source rather than the control name which is 'Text33'. As a secondary question, is this correct logic?

Anyway, back to my main problem. I have now copied 'FRM_Equipment' to a tabbed navigation form and get error 2450 'Calibration cannot find the referenced form 'FRM_Equipment'.'

Clearly my referencing is now incorrect but I'm confused.

Please help. Appreciate it Thanks.

Private Sub Calibration_Frequency_AfterUpdate()

Dim ID As Long
Dim Temp_Date As Date
Dim Temp_Unit As Integer
Dim Temp_Frequency As Integer

On Error GoTo ErrorHandler

   ID = Nz(DMax("InspectID", "Qry_Inspect_Equipment"), 0)
        If ID = 0 Then
            Me.[Anniversary_Date] = Date
            Exit Sub
        End If
   
   Temp_Date = DLookup("[Anniversary_Date]", "TBL_Inspect_Record", "InspectID=" & ID)
   Temp_Unit = DLookup("[Calibration_Unit]", "TBL_Inspect_Record", "InspectID=" & ID)
   Temp_Frequency = DLookup("[Calibration_Frequency]", "TBL_Inspect_Record", "InspectID=" & ID)
      
Select Case Temp_Frequency

    Case 1 ' Annually
    
        Me.[Anniversary_Date] = DateAdd("yyyy", [Temp_Unit], [Temp_Date])

    Case 2 'Monthly
        Me.[Anniversary_Date] = DateAdd("m", [Calibration_Unit], [Temp_Date])

    Case 3 'Weekly (Weekdays)
        Me.[Anniversary_Date] = DateAdd("ww", [Calibration_Unit], [Temp_Date])

    Case 4 ' Daily
        Me.[Anniversary_Date] = DateAdd("d", [Calibration_Unit], [Temp_Date])
                     

    Case Else
End Select
  
ExitError:
    Exit Sub
    
ErrorHandler:

  Select Case Err.Number
    Case 9999
        Resume Next
    Case 999
        Resume Next
    Case Else
        Call LogError(Err.Number, Err.Description, "Calibration_Frequency_AfterUpdate()")
        Resume ExitError
    End Select
 
End Sub

 

[Deleted member 147267]

Before I dig through my syntax document, are you aware that a navigation form only holds 1 form at a time? Thus if you expect to do something on form 1 and you go "what was that value on form 2 again?" and you flip back to form 2, form 1 is closed. When you go back to form 1, it is reloaded.

I keep such syntaxes in a reference document usually because I have no interest in using some Access features, hence the syntax doesn't stick in my memory. I just have it for forum help. Just wanted to make sure you are going to stick with this design first.

P.S. why On Change event as you posted, but your code is for After Update? You realize On Change event fires upon every keystroke in a control? Are you typing in this combo or just selecting?

 

[June7]

That procedure is not OnChange event, it is AfterUpdate.

Would give control more meaningful name than Text33 such as tbxAnnDt.

If referencing field name works then it is ok but there may be situations when it will fail and vice versa.

You are using Navigation Form, not Tab Control? I don't see any reference to FRM_Equipment.

What is the combobox RowSource?

Have to reference subform container. On a Navigation Form, Access defaults to NavigationSubform as the container name. Review https://www.accessforums.net/showthread.php?t=32053

 

[Deleted member 147267]

I have now copied 'FRM_Equipment' to a tabbed navigation form

June7 - this was not clear enough? But thanks for showing how to deal with navigation subform syntax while I was trying to make sure OP wanted to stick with that approach. You can take over here.

 

[June7]

June7 - this was not clear enough? But thanks for showing how to deal with navigation subform syntax while I was trying to make sure OP wanted to stick with that approach. You can take over here.

What's wrong with rephrasing info to make sure I have understanding?

 

[HillTJ]

Hi, I've reverted to a form with a tabbed control. Nav forms did not seem to work for me. & yes June7 it is on 'afterupdate' event. My bad in original post. All worked fine till I moved my form. It's obviously the Me.[Anniversary_Date] part is the culprit. I've unsuccessfully fumbled about now would like help. Tabbed Control is on a form called 'Main_Form', tab is called 'Calibration'. Primary form is 'FRM_Equipment' & Subform is called 'FRM_Inspection_Record subform'. The control is on that form & called 'Text33', but [Anniversary_Date] works.

Hope I've made myself clear. Appreciate it.

 

[June7]

No, I am still confused. You said error message is FRM_Equipment cannot be found, yet I do not see that form referenced in posted code. So where is form referenced? Is it combobox RowSource?

 

[HillTJ]

June7, i put a debugprint after end select to print [anniversary_date]. I know this returned the correct result before i moved the form. Now i get a 'null' returned!.

 

[June7]

So where is [anniversary_date] value coming from? I don't understand how that value connects to the error message you cited.

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

 

[arnelgp]

Private Sub Calibration_Frequency_AfterUpdate()

Dim ID As Long
Dim Temp_Date As Date
Dim Temp_Unit As Integer
Dim Temp_Frequency As Integer

On Error GoTo ErrorHandler

   ID = Nz(DMax("InspectID", "Qry_Inspect_Equipment"), 0)
        If ID = 0 Then
            Me.[Anniversary_Date] = Date
            Exit Sub
        End If
   
   Temp_Date = DLookup("[Anniversary_Date]", "TBL_Inspect_Record", "InspectID=" & ID)
   Temp_Unit = DLookup("[Calibration_Unit]", "TBL_Inspect_Record", "InspectID=" & ID)
   Temp_Frequency = DLookup("[Calibration_Frequency]", "TBL_Inspect_Record", "InspectID=" & ID)
      
Select Case Temp_Frequency

    Case 1 ' Annually
    
        Me.[text33] = DateAdd("yyyy", [Temp_Unit], [Temp_Date])

    Case 2 'Monthly
        Me.[text33] = DateAdd("m", [Calibration_Unit], [Temp_Date])

    Case 3 'Weekly (Weekdays)
        Me.[text33] = DateAdd("ww", [Calibration_Unit], [Temp_Date])

    Case 4 ' Daily
        Me.[text33] = DateAdd("d", [Calibration_Unit], [Temp_Date])
                     

    Case Else
End Select
  
ExitError:
    Exit Sub
    
ErrorHandler:

  Select Case Err.Number
    Case 9999
        Resume Next
    Case 999
        Resume Next
    Case Else
        Call LogError(Err.Number, Err.Description, "Calibration_Frequency_AfterUpdate()")
        Resume ExitError
    End Select
 
End Sub

 

[HillTJ]

arnelgp, It worked as stated but not after I copied to a tabbed form. If your simple recommendation works, then I'm embarrassed, but move ahead. I guess you've figured that now I'm up to developing some kind of tabbed user interface. Getting close to completion. Cheers. Will let you know.

 

[HillTJ]

arnelgp, tried it, no difference.

 

[HillTJ]

Hi, please see zip file attached. Please don't laugh at my attempt at this database. I encourage any critique/recommendations. View attachment Calibration V6.zip. Problem occurs on 'Main_Form', 'Calibration tab', 'Inspection Record' subform after selecting "Frequency". Didn't until I moved it.

Appreciate any help & suggestions if someone has time to review.

 

[June7]

Advise not to use spaces nor punctuation/special characters (underscore is only exception but I don't use them) in naming convention. Frequency_Active(Y/N) would be better as FrequencyActiveYN.

I disabled error handler and debugger stops on line:

ID = Nz(DMax("InspectID", "Qry_Inspect_Equipment"), 0)

That query has a parameter [Forms]![FRM_Equipment]![FRM_Inspect_Record subform]![EquipmentID] that is failing because FRM_Equipment is now on Main_Form, therefore it cannot be found. All query does is filter table with this one parameter - no join, no calcs. I never use dynamic parameterized queries. I would include EquipmentID in DMax() WHERE argument and just query table.

ID = Nz(DMax("InspectID", "TBL_Inspect_Record", "EquipmentID=" & Me.EquipmentID), 0)

 

[arnelgp]

remove filter from query qry_inspect_equipment, instead add "where" condition on your Dmax() on code.

remove references to [FRM_Equipment], instead use Me.Parent to referece the parent form.

 

[HillTJ]

Thanks for the prompt reply. Will let you know how I go. Really appreciate it.

 

[HillTJ]

That worked. Thanks. Now it gives an error message 2450 :Calibration cannot find the referenced form "FRM_Equipment" upon firing the after update event of text37 (which invokes the 'send a task to outlook' code. Clearly, it's to do with the referencing since I moved the form. Appreciate your assistance.

 

[June7]

Because FRM_Equipment is now on Main_Form. Fix the reference path:

Forms!Main_Form.[FRM_Equipment].[Form]![FRM_Inspect_Record subform].[Form]![Inspector] = Nz(GetUserName, "")
Forms!Main_Form.[FRM_Equipment].[Form]![FRM_Inspect_Record subform].[Form]![PostedFlag] = True

Also fix references in ValidateFields and CreateTask. Use Find tool to search for FRM_Equipment.

 

[HillTJ]

June7, if you have the time, I need some more help. I substituted in your recommendation, but the code crashes when it goes to the ValidateFields() sub routine. Error to do with referencing. Clearly, the referencing hasn't gelled with me! Could you (or anyone with the time) look @ it Please??


I've attached the code below;

Private Sub CreateTask()
      
    Dim olApp As Outlook.Application
    Dim olTask As Outlook.TaskItem
    Dim txt_duedate As String
    Dim ScheduleDate As String
    Dim Temp_Site As String
    Dim Temp_Location As String
    
    Dim iTask As Integer, iImportance As Integer
        
    Set olApp = New Outlook.Application
    Set olTask = olApp.CreateItem(olTaskItem)
      
    On Error GoTo ErrorHandler
    
    Select Case [Calibration_Frequency]

    Case 1 ' Annually
        ScheduleDate = DateAdd("yyyy", Me.[Calibration_Unit], Me.[Text33])

    Case 2 'Monthly
        ScheduleDate = DateAdd("m", Me.[Calibration_Unit], Me.[Text33])

    Case 3 'Weekly (Weekdays)
        ScheduleDate = DateAdd("ww", Me.[Calibration_Unit], Me.[Text33])

    Case 4 ' Daily
        ScheduleDate = DateAdd("d", Me.[Calibration_Unit], Me.[Text33])

    Case Else
    End Select
       
    iTask = olTaskNotStarted
    iImportance = olImportanceNormal
    'Temp_Site = DLookup("[Site_Name]", "TBL_Site", "[SiteID] =" & Forms![FRM_Equipment]![SiteID])
    Temp_Site = DLookup("[Site_Name]", "TBL_Site", "[SiteID] =" & Forms![main_form].Form![FRM_Equipment].[SiteID])
    'Temp_Location = DLookup("[Location]", "TBL_Location", "[LocationID] =" & Forms![FRM_Equipment]![LocationID])
    Temp_Location = DLookup("[Location]", "TBL_Location", "[LocationID] =" & Forms![main_form].Form![FRM_Equipment].[LocationID])
    
    Debug.Print [Temp_Site], [Temp_Location]
                 
    With olTask
                              
        .Subject = Forms![main_form].[FRM_Equipment].[Equipment_Desc] & " " & "Identification #:" & Forms![main_form].[FRM_Equipment].[Serial_ID]
        .Body = "This Item is Due for Calibration & is located at " & [Temp_Site] & "." & "It was last used in the " & [Temp_Location] & vbNewLine & vbNewLine & "[Task generated via Microsoft Access Calibration Database on " & Now() & "]"
        .StartDate = Format(ScheduleDate, "yyyy-mm-dd")
        .DueDate = Format(DateAdd("d", 15, ScheduleDate), "yyyy-mm-dd")
        .Categories = "Calibration"
        .ReminderSet = True
        .ReminderTime = ScheduleDate
        .Owner = GetUserName  '"Terry" 'me.Text35
        '.Display
    End With
  
    Set olTask = Nothing
    Set olApp = Nothing
    
    Exit Sub

    
ExitError:
    Exit Sub
    
ErrorHandler:
  Select Case Err.Number
    Case 9999
        Resume Next
    Case 999
        Resume Next
    Case Else
        Call LogError(Err.Number, Err.Description, "CreateTask()")
        Resume ExitError
    End Select
End Sub


Private Sub ValidateFields()
    
    Dim s As String
    bOk = False
    bStartDate = False
    bSubject = False
    bOwner = False
    bDueDate = False
    
    'test for subject...
    
    On Error GoTo ErrorHandler:
    
    'If Forms![FRM_Equipment]![Equipment_Desc] = Empty Or IsNull(Forms![FRM_Equipment]![Equipment_Desc]) Or Len(Forms![FRM_Equipment]![Equipment_Desc]) = 0 Then
    If Forms![main_form]![FRM_Equipment].[Equipment_Desc] = Empty Or IsNull(Forms![main_form]![FRM_Equipment].[Equipment_Desc]) Or Len(Forms![main_form]![FRM_Equipment].[Equipment_Desc]) = 0 Then
    s = s & vbNewLine & "'Subject'"
    
        
    Else
        bSubject = True
    End If
               
    'test for Start Date...
    If Me.Text33 = Empty Or IsNull(Me.Text33) Or Len(Trim(Me.Text33)) = 0 Then
        s = s & vbNewLine & "'Start Date'"
    Else
        bStartDate = True
    End If

    'test for due Date is after start date...
    If Me.Text37 <> Empty Or Not IsNull(Me.Text37) Or Len(Trim(Me.Text37)) > 0 Then
        If CDate(Me.Text37) < CDate(Me.Text33) Then
            s = s & vbNewLine & "'Due Date must be later than Start Date'"
            Else
            bDueDate = True
        End If
   
        Else
        bDueDate = True
    End If
    If bSubject And bStartDate And bDueDate Then
        bOk = True
        
    Else
        MsgBox "The following fields are mandatory and need completing:" & vbNewLine & _
            s, vbExclamation, "Unable to save record"
    End If
ExitError:
    Exit Sub
    
ErrorHandler:
  Select Case Err.Number
    Case 9999
        Resume Next
    Case 999
        Resume Next
    Case Else
        Call LogError(Err.Number, Err.Description, "Private Sub ValidateFields()")
        Resume ExitError
    End Select
    End Sub

 

[June7]

I have never seen = Empty However, the compiler is accepting it and not even seeing it as an undeclared variable. Interesting. Conventional code is:

= ""

or

= vbEmptyString

However, those versions do not correct issue. It appears Empty is an intrinsic constant I was never aware of.

The issue is referencing controls versus referencing fields of subform because the reference goes through subform container control.

To reference a control name use Form qualifier:

Forms![Main_form]![FRM_Equipment].Form.[Equipment_Desc]

To reference a field use ! (bang) instead of dot with or without Form qualifier:

Forms![Main_form]![FRM_Equipment]![Equipment_Desc]

In this case because you are referencing bound controls that are named same as fields, either should work.

Now getting "Invalid Use of Null" error.

    'test for due Date is after start date...
    If Me.Text37 <> Empty Or Not IsNull(Me.Text37) Or Len(Trim(Me.Text37)) > 0 Then
        If CDate(Me.Text37) < CDate(Me.Text33) Then

Text33 is null and CDate errors on Null. Nothing can be compared to null, not even another null because null is not anything. Handle possibility of null and simplify the condition.

If Nz(Me.Text37, "") <> Empty Then
If CDate(Me.Text37) < CDate(Nz(Me.Text33, Date)) Then

Or don't allow the process to even start if there is no ScheduleDate.

You should disable error handler for debugging then step through code. Comment the On Error GoTo lines and set a breakpoint. Refer to link at bottom of my post.