Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31
  1. #16
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    "Incidently, this is NOT doing what you think.
    Dim strBody, strAddresses, strSubject, strMsg As String



    In Access, you must explicitly Dim your variables
    eg
    Dim strBody as string, strAddresses as string, strSubject as string, strMsg As String, otherwise variables become Variants."

    Thanks
    I am a newbie at this. Dipping ever deeper into unknown areas.

  2. #17
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I thought I had it solved. It still errors out on the & as a Type mismatch

  3. #18
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #19
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Last lin at the green &. I could make it go to the first .Subject line if I didn't have any data, but it would stop at the &. I put a ' at that string to let it bypass the .Subject line. That it did fine. but it halted at the next & with the Type mismatch fault on the next instance:

    DoCmd.OutputTo 3, "Daily Actions", acFormatPDF, "C:\Temp\Daily Actions - " & Format(Date, "dd mmm yyyy") & ".pdf", , 0

    This site has helped me a lot, and I have adapted some code to my own needs. I am still new to the coding world.

    Code:
    Public Sub Send_Daily_Click()
     On Error GoTo ErrorMsgs
     Dim rs As Dao.Recordset
     Dim objOutlook As Outlook.Application
     Dim objOutlookMsg As Outlook.MailItem
     Dim objOutlookMsg1 As Outlook.MailItem
     Dim objOutlookMsg2 As Outlook.MailItem
     Dim objOutlookAttach As Outlook.Attachment
     Dim strBody, strAddresses, strSubject, strMsg As String
     Dim CRNUM, DateTypes, FINAL As Variant
      Set rs = CurrentDb.OpenRecordset("SELECT Status,CR_Numbers,[Change Requested]FROM Daily_Actions_Email ORDER BY Status,CR_ID ASC")
      Set objOutlook = CreateObject("Outlook.Application")
      Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
      Set objOutlookMsg1 = objOutlook.CreateItem(olMailItem)
      Set objOutlookMsg2 = objOutlook.CreateItem(olMailItem)
    CRNUM = DLookup("[CR_Numbers]", "[Daily_Actions_Email]")
    DateTypes = DLookup("[DateType]", "[Settings_Qry]")
    FINAL = DLookup("[Final_Vote]", "[Daily_Actions_Email]")
    With DoCmd
         .SetWarnings False
         .OpenQuery "Daily Update"
         .SetWarnings True
    End With
    If IsNull(CRNUM) Then
    With objOutlookMsg
        .Subject = "There were no actioned CR's - " & Format(Date, "dd mmm yyyy")
        .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
                "There were no actioned Change Requests for " & Format(Date, "dd mmm yyyy") & "." & strSigBlock
        .To = "CCB Results"
        .Display
        DoCmd.Close acReport, "Daily Actions"
    Exit Sub
    End With
    Else
    End If
    rs.MoveFirst
    While Not rs.EOF
     strMsg = strMsg & rs!Status & vbCrLf & Chr(9) & "CR  " & rs!CR_Numbers & " - " & rs![Change Requested] & vbCrLf
     rs.MoveNext
    Wend
     rs.Close
    With objOutlookMsg1
       .Subject = "Today's AORB/TEWG/CCB outcome - " & strTod ' Format(Date, "dd mmm yyyy")

  5. #20
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    BTW, with all these Dim and Set Lines, and you saying I need to Dim my strings as separate lines. Is there a function or module that I can use to set these? Or even an error module?

    Thanks

  6. #21
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ???Do you get an error number or message?

    I am using the function I suggested ThomsDate
    I can use it fine.
    some code where I put the function to work. (you can ignore the Public Type stuff) The result is of interest, as is line 30 where the DateofHire gets its value.

    [code]
    Public Type mEmployee
    name As Person
    DateOfHire As Date
    Salary As Currency
    EmployeeNumber As Integer
    End Type

    Sub jNov5()


    'Once you define the type, you can create instances of it just like any other data type, like so:
    Dim OneEmployee As mEmployee
    Dim AllEmployees(500) As Employee

    'To access the members of a UDT, you use the Name.Membernotation. Here are some examples:
    10 OneEmployee.name.FirstName = "Jack"
    20 OneEmployee.name.LastName = "Sprat"
    30 OneEmployee.DateOfHire = ThomsDate
    40 AllEmployees(1).FirstName = "Maria"
    50 AllEmployees(1).LastName = "Sanchez"
    60 Debug.Print OneEmployee.name.FirstName & " " & OneEmployee.name.LastName & " was hired on " & OneEmployee.DateOfHire

    End Sub

    [code]

    Result:

    Jack Sprat was hired on 14/04/2016

  7. #22
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I get a Message

    Click image for larger version. 

Name:	Error.PNG 
Views:	7 
Size:	42.4 KB 
ID:	24309

    I had all the Format(Date..... replaced with StrTod so when I '.Subject the error moved on to the next instance.

    I understand what you are demonstrating, just not understanding why it doesn't work on mine.

  8. #23
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Set is only used with objects.

    Dim is to identify the variable to Access. It is considered best practice to Dim your variable with the appropriate datatype. If you don't, then Access will assign it s type Variant. And Access may do some conversions/adjustments based on what it thinks best in the circumstance. May not be what you intended.
    There are specific data types designed to be used and "make your programming life" a little less chaotic.

    You assign the data type when you Dim the variable.

    It's possible, but highly unlikely in my view, that the dash "-" may be being treated as a "subtraction".

    Do you know about breakpoints and step debugging? With some patience you can probably narrow down the issue.

    Error Handling Info

    It mentions Excel, but it is applicable to Access and vba.

  9. #24
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I'll have to play more with the F8 / F9 error handling. All I get is dings when I try with Access VBA. I did add a watch to it. It says it can't compile at the bottom of the screen (Red Box).


    Click image for larger version. 

Name:	Error2.PNG 
Views:	6 
Size:	52.6 KB 
ID:	24311

    Code:
    Public Function StrTod() As String
       StrTod = Format(Date, "dd mmm yyy")
    End Function
    It should compile. I get no errors on it when I run Debug. I always stop at the & running Debug. I took out the " - " same result. I took out the error routine to get it to stop ata specific line. It stops and Yellow Highlights the "Public Sub Send_Daily_Click()"
    Attached Thumbnails Attached Thumbnails Error2.PNG  
    Last edited by Thompyt; 04-14-2016 at 01:03 PM. Reason: Clarity

  10. #25
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    can you go to the immediate window( Ctrl G)

    Then type

    ?strTOD
    then press enter

  11. #26
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Open VBA went to StrTod Function hit Ctrl G typed ?StrTod

    Outcome: Compile Error Type Mismatch

  12. #27
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Where does function strTod reside?
    Can you take a screen shot of the function definition including the module name?

  13. #28
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    As requested

    Click image for larger version. 

Name:	Error3.jpg 
Views:	6 
Size:	77.8 KB 
ID:	24314

  14. #29
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Got it fixed

    Changed it to:
    Function Tod() As String
    Tod = Format(Date, "dd mmm yyyy")
    End Function

    It didn't like the Str part in the function name or the Variable name.

    Additionally you need to state the variable inside the function in the other modules for it to work. if I used the module name it error-ed out as usual. If I used the Variable name Tod it worked fine. There are a couple of Forms not working quite right, but that's another issue.

    Thanks!!

  15. #30
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Where does function strTod reside?
    Can you take a screen shot of the function definition including the module name?

    I'm attaching my set up.

    UPDATE @ 7:15
    Just saw your latest post. Yes, I'm aware of the function. I don't believe it has anything to do with str.

    I think one of your other attempts with strTOD was still being found and was interfering.

    Anyway, glad you have it resolved.
    Attached Thumbnails Attached Thumbnails ThomsDate_strTOD.jpg  
    Last edited by orange; 04-14-2016 at 05:18 PM. Reason: update to post

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA assistance
    By Kwbrown in forum Programming
    Replies: 3
    Last Post: 03-15-2014, 03:09 PM
  2. Qry Assistance Please
    By buck in forum Access
    Replies: 8
    Last Post: 12-31-2013, 08:52 AM
  3. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  4. Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  5. Need Assistance with Forms
    By JDA2005 in forum Forms
    Replies: 5
    Last Post: 06-30-2009, 03:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums