Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-16-2019, 10:26 PM   #16
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 170
Thanks: 69
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
Re: Problem referencing a control on a tabbed subform

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

HillTJ is offline   Reply With Quote
Old 09-17-2019, 08:26 PM   #17
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 170
Thanks: 69
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
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.
HillTJ is offline   Reply With Quote
Old 09-17-2019, 09:53 PM   #18
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
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.

June7 is offline   Reply With Quote
Old 09-21-2019, 07:45 PM   #19
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 170
Thanks: 69
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
Re: Problem referencing a control on a tabbed subform

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;

Code:
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
HillTJ is offline   Reply With Quote
Old 09-22-2019, 12:02 AM   #20
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Problem referencing a control on a tabbed subform

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.
Code:
    '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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 09-22-2019 at 12:48 AM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
HillTJ (09-22-2019)
Old 09-22-2019, 11:11 PM   #21
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 170
Thanks: 69
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
Re: Problem referencing a control on a tabbed subform

June7, thanks. Will get back to you & let you know how i go. Appreciate it.
HillTJ is offline   Reply With Quote
Old 09-23-2019, 07:29 AM   #22
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 933
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Problem referencing a control on a tabbed subform

A value that is both zero and a zls (zero length string) evaluates to Empty. However, I've never seen this comparison when referring to the value of a control as opposed to a variable of type variant. A variant initializes to a value that is both 0 and a zls.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is online now   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
HillTJ (09-24-2019)
Old 09-23-2019, 11:44 AM   #23
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,162
Thanks: 15
Thanked 1,574 Times in 1,496 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Problem referencing a control on a tabbed subform

In your code you have ScheduleDate and txt_duedate defined as STRINGs and later, you are trying to format them as a date to store them in what is presumably defined as a date and you are also using date functions to operate on them.

1. Change the definition of ScheduleDate to be DateTime
2. Remove ALL format instructions on date fields. Formatting a date field turns it into a STRING! NEVER, EVER format a date EXCEPT for display purposes.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is online now   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
HillTJ (09-24-2019)
Old 09-23-2019, 01:14 PM   #24
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Problem referencing a control on a tabbed subform

My understanding is Format property does not convert date to string. Format function does that.

However, I never set formatting in table. Do this on forms and reports.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
HillTJ (09-24-2019)
Old 09-23-2019, 03:27 PM   #25
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 933
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Problem referencing a control on a tabbed subform

Quote:
Originally Posted by June7 View Post
My understanding is Format property does not convert date to string. Format function does that.
That's my understanding as well.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is online now   Reply With Quote
Old 09-25-2019, 07:25 AM   #26
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,162
Thanks: 15
Thanked 1,574 Times in 1,496 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Problem referencing a control on a tabbed subform

We are looking at code in #19 NOT controls on a form. So yes, the Format property is different from the Format() function but we were talking about code. "Instruction" is code. "property" is settings on a control.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is online now   Reply With Quote
Old 09-25-2019, 11:20 AM   #27
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Problem referencing a control on a tabbed subform

Ah, I misread and I thought you meant to remove formatting from fields in table. Formatting date value in code might be necessary for a non-U.S. database. But I agree, doesn't seem appropriate for this situation since formatting used is not correct for non-U.S. date.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-25-2019, 02:22 PM   #28
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,162
Thanks: 15
Thanked 1,574 Times in 1,496 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Problem referencing a control on a tabbed subform

We weren't talking about formatting fields in tables but now that you bring it up, formatting a field in a table does nothing to prevent "bad" data from being entered. All the format does is hide it. I did talk at length about this in another active thread. There is never a reason that I am aware of where formatting on a table is a good idea.

Formatting a date using the Format() function is frequently performed when working with non-US dates but most often it is done when it shouldn't be and therefore causes more problems than it solves. Formatting is necessary ONLY when you must supply a string value. So, if you are building an SQL statement in VBA, you are building a STRING and all the elements of it will be strings. When you pass a string date to SQL Server, it must be either standard US field order - mdy OR some unambiguous other format. The standard European dmy is ambiguous and so people format it to mm/dd/yyyy.

But, if you use a querydef that references a date in another field or table or a control on a form, you do NOT need to format anything, nor should you.

Select ... From ... Where mydate = Forms!yourform!yourdate

Does not need formatting as long as the control on the form is bound to a datetime data type or if it is unbound and there is a format property that defines the control as a date.

However, the very same query constructed as a string in VBA MUST format the date.

strSQL = "Select ... From ... Where mydate = #" & Format(Forms!yourform!yourdate, 'yyyy/mm/dd') & "#;"
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is online now   Reply With Quote
Old 09-28-2019, 06:14 PM   #29
HillTJ
Newly Registered User
 
Join Date: Apr 2019
Location: Tropical North Australia
Posts: 170
Thanks: 69
Thanked 1 Time in 1 Post
HillTJ is on a distinguished road
Re: Problem referencing a control on a tabbed subform

Hi,

I'm still stuck & getting frustrated despite the efforts of the forum. To reiterate, I have a tabbed main form 'Main_Form' into which I have 4 tabs. one of which is 'Calibration' onto which I've copied 'FRM_Equipment'. I use several of the fields from this form to generate the title for the Activity. I use several of the fields from 'FRM_Inspect_Record subform' a subform on 'FRM_Equipment' to generate values for the repeat date etc which becomes the outlook task. No matter how I try, i cannot seem to get the referencing right. It all worked till I decided to move 'FRM_Equipment' to a tabbed main form. Upon disabling error routines, it errors out after update of text37, where it calls routine 'ValidateFields' and crashes @;

If Forms![Main_Form]![FRM_Equipment]!Form![Equipment_Desc] = Empty Or IsNull(Forms![Main_Form]![FRM_Equipment]!Form![Equipment_Desc]) Or Len(Forms![Main_Form]![FRM_Equipment]!Form![Equipment_Desc]) = 0 Then
s = s & vbNewLine & "'Subject'"

If I comment out this routine, the subroutine returns, only to crash at the next subroutine "CreateTask" with the following;

Temp_Site = DLookup("[Site_Name]", "TBL_Site", "[SiteID] =" & Forms![Main_Form]![FRM_Equipment].Form![SiteID])

Clearly, I haven't gotten the referencing right, no matter what I try. I's probably a 2min fix, but driving me mad, leave alone the other tweaks I've been advised to do.

I really need to finish this project. I would be eternally grateful for any assistance the forum could provide.
HillTJ is offline   Reply With Quote
Old 09-28-2019, 06:53 PM   #30
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,274
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Problem referencing a control on a tabbed subform

Maybe should provide latest version of db for analysis.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 09-28-2019 at 08:33 PM.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Referencing a field from a record in a datasheet on a tabbed control bentheimmigrant Forms 6 09-21-2015 06:41 AM
problem with tabbed control goingnuts Forms 1 09-10-2013 12:40 PM
Problems referencing control on subform after changing source object of subform JFHBIFF Modules & VBA 3 02-28-2009 09:25 PM
Using subform within a tabbed control tomisin General 0 12-23-2007 01:34 AM
Sub forms in a tabbed control - referencing Rats Forms 7 10-22-2006 05:09 PM




All times are GMT -8. The time now is 07:47 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World