Results 1 to 8 of 8
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Can't find referenced field error

    Qry.Open_CRs
    Code:
    SELECT Chng_ReqQry.Levelz, Chng_ReqQry.CR_ID, Chng_ReqQry.CR_Numbers, Chng_ReqQry.Levels, IIf(([Chng_ReqQRY].[Sub_No]=0),Format([Chng_ReqQRY].[Date_ID],"dd mmm yy"),"") AS Date_IDs, Chng_ReqQry.Status, Chng_ReqQry.Change_Type, Chng_ReqQry.[HB Vers], Chng_ReqQry.Units, Chng_ReqQry.[MTOE Paras], Chng_ReqQry.People, Chng_ReqQry.[Change Requested], Chng_ReqQry.Rationale, Chng_ReqQry.NOTES, Chng_ReqQry.Action_Items, Chng_ReqQry.Action_Complete, Chng_ReqQry.NIE, Chng_ReqQry.AO_Vote, Chng_ReqQry.Hr, [Times Deferred III].[Days Open]
    FROM [Times Deferred III] INNER JOIN Chng_ReqQry ON [Times Deferred III].CR_ID = Chng_ReqQry.CR_ID
    WHERE (((Chng_ReqQry.Action_Complete)=False))
    ORDER BY Chng_ReqQry.Levelz DESC;
    Email VBA



    Code:
    Private Sub Send_Open_Click()
     On Error GoTo ErrorMsgs
     Dim objOutlook As Outlook.Application
     Dim objOutlookMsg As Outlook.MailItem
     Dim objOutlookAttach As Outlook.Attachment
     Dim objOutlookRecip As Outlook.Recipient
     Dim strBody, strAddresses, strSubject As String
     Dim rs As DAO.Recordset
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Set rs = CurrentDb.OpenRecordset("SELECT CR_Numbers from Open_CRs")
    If IsNull(Me.CR_Numbers) Then
    With objOutlookMsg
       .Subject = "Tomorrow's CR's - " & Format(Date + 1, "dd mmm yyyy")
        .Body = "There are no Change Request actions for tomorrows CMB." 
       .Display
        DoCmd.Close acReport, "Open by Level"
    Exit Sub
    End With
    Else
    With objOutlookMsg
    Then email with CR_Numbers

    It can't find CR_Numbers, where am I referencing wrong. IsNull(CR_Numbers) should be OK if there is an empty field.

    Thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    What you re doing here is creating a recordset.
    Set rs = CurrentDb.OpenRecordset("SELECT CR_Numbers from Open_CRs")

    But I do not see you using it anywhere within the code you posted.


    Then you have
    If IsNull(Me.CR_Numbers) Then

    But you do not have an End If.

    I suggest you comment out <On Error GoTo ErrorMsgs> and step debug. What you have posted here does not seem to be all of the code.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    It isn't all of the code, only the pertinent.

    What would I use so that I can use the If isnull(Field) and it would see it. I was thinking the RS would allow this. If it isn't then I can remove it.

    I already have done the ' on the error line. Stops at: If IsNull(Me.CR_Numbers) Then

    with the error code included I get: 2465 MS Access can't find the field "CR_Numbers" referred to you in your expression.

    This is with the Dim & set out for the RS.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Thompyt View Post
    ...I already have done the ' on the error line. Stops at: If IsNull(Me.CR_Numbers) Then...
    Then Me.CR_Numbers is probably out of scope. What is the value of Me.CR_Numbers when the exception occurs? You should be able to hover over Me.CR_Numbers ot type ?Me.CR_Numbers in the immediate window and hit your Enter key to determine the value.

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Figured it out:
    Code:
     Dim CRNUM As Variant
    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    CRNUM = DLookup("[CR_Numbers]", "[Open_CRs]", "CR_Numbers")
    If IsNull(CRNUM) Then
    With objOutlookMsg
       .Subject = "Tomorrow's CR's - " & Format(Date + 1, "dd mmm yyyy")

  6. #6
    DrGUI is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Parts Unknown
    Posts
    23
    Instead of Me.CR_Numbers maybe you want to use CR_Numbers from your recordset, rs!CR_Numbers

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thompyt,

    It would be helpful if you gave us all the code in this routine. You know your environment and what is pertinent to you is very subjective. We don't know the issue or environment, so are trying to "guess" based on what you show and tell us. What exactly is this code suppose to do in plain English?

    Also, this doesn't do what you think
    Code:
    Dim strBody, strAddresses, strSubject As String
    In Access you have to explicitly Dim your variables. If you don't, they default to Variant

    If you want these to be string, you would use

    Code:
    Dim strBody As String, strAddresses As String, strSubject As String
    OR

    Code:
    Dim strBody As String
    Dim strAddresses As String
    Dim strSubject As String

  8. #8
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Code is to generate an email to conditions of CR_Number. If it is null, then the email will be generated without an attachment, and will send out a different message body. If there is a CR_Number, then it will generate a pdf report and another message body.

    Its figured out with a dim varient and a dlookup to the query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-05-2014, 07:55 AM
  2. Replies: 1
    Last Post: 04-12-2013, 06:11 AM
  3. Replies: 12
    Last Post: 09-14-2012, 04:59 PM
  4. Replies: 1
    Last Post: 08-19-2012, 09:55 PM
  5. Replies: 11
    Last Post: 06-13-2012, 08:14 AM

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