Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 60
  1. #31
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862

    Sample

    I am not sure what your PK is for your table. You will need to replace PK with the field name for the PK in oyur table.



    Code:
    Dim lngPK As Long
    Dim strName As String
    Dim strCriteria As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT [Last Name], Email FROM T_Inspectors;")
    strCriteria = "[Employee]= '" & rs![Last Name] & "'"
        rs.FindFirst strCriteria
        
            If Not rs.EOF Then
            lngPK = rs![PK]
            strName = rs![Last Name]
            MsgBox "Prime Key: " & lngKey & " Name: " & strName
            
            Else
            MsgBox "No record Found"
            
            End If
        
        
        DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Employee]='" & rs![Last Name] & "'", acWindowNormal
        
        rs.Close
        
    Set rs = Nothing

  2. #32
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Click image for larger version. 

Name:	T_InspectorTable.JPG 
Views:	5 
Size:	12.9 KB 
ID:	14031T_Inspectors

    Click image for larger version. 

Name:	_JobData.JPG 
Views:	5 
Size:	18.7 KB 
ID:	14032T_Job Data Table

    Ok...I'm getting a error for Employee field.
    So see 2 pics. Show you my PK for T_Inspectors first then my PK for Jobs Table. (The report is pulling the email from T_Inspectors table(Last Name, First Name) then linking with job table using Employee)
    See pics...and let me know if we need to adjust the code.

  3. #33
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    So you are getting the message boxes? They are telling you what you want?

    The problem is the .OpenReport?

  4. #34
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Here is some code that uses a date as criteria. Go over the code get an idea of what it is doing. Adjust the date by hardcodin one that will match. I am using a date because you mentioned the Job date thing. Make sure the Job date field is correct.

    Code:
    Dim lngPK As Long
    Dim strName As String
    Dim strCriteria As String
    Dim strDate As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Q_WeeklyEmployeeDispatch_Today, dbOpendynasett")
    strDate = "#1/1/2013#"
    strCriteria = "[job Date]=" & strDate
        rs.FindFirst strCriteria
        
            If Not rs.EOF Then
            lngPK = rs![ID1]
            strName = rs![Employee]
            MsgBox "Prime Key: " & lngKey & " Name: " & strName
            
            Else
            MsgBox "No record Found"
            
            End If
        
        
        DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , strCriteria, acWindowNormal
        
        rs.Close
        
    Set rs = Nothing

  5. #35
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I just noticed a typo

    dbOpendynasett

    should be dbOpendynaset

  6. #36
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Im using code below now. But getting this message. (also after you saw the 2 pics...I'm using ID1 as PK is that correct?
    Click image for larger version. 

Name:	Picture1.png 
Views:	5 
Size:	73.4 KB 
ID:	14033
    Private Sub Command5_Click()

    Dim lngID1 As Long
    Dim strName As String
    Dim strCriteria As String
    Dim strDate As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Q_WeeklyEmployeeDispatch_ Today, dbOpendynaset")
    strDate = "#1/1/2013#"
    strCriteria = "[job Date]=" & strDate
    rs.FindFirst strCriteria

    If Not rs.EOF Then
    lngPK = rs![ID1]
    strName = rs![Employee]
    MsgBox "Prime Key: " & lngID1 & " Name: " & strName

    Else
    MsgBox "No record Found"

    End If


    DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , strCriteria, acWindowNormal

    rs.Close

    Set rs = Nothing

    End Sub

  7. #37
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Question...After reading code. Im trying to send multiple emails. However I don't see anything in the code relating to emailing.
    Is that correct?

  8. #38
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I am sorry. I don't have a good way to test this since I don't have these tables and queries.

    ("Q_WeeklyEmployeeDispatch_ Today, dbOpendynaset") there is a space between today and Dispatch_
    Also the quotes go around the query name, maybe

    ("Q_WeeklyEmployeeDispatch_Today", dbOpendynaset)

  9. #39
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by wnicole View Post
    Question...After reading code. Im trying to send multiple emails. However I don't see anything in the code relating to emailing.
    Is that correct?
    You need to sort out some things before you can move to a loop. We are testing. See if you can get the FIRST record. That is what the message boxes are for. You check.. see if that is good. Then check the report, see if that is good.

  10. #40
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Ok...The message was good. However nothing appear on the Report it was blank.

    Private Sub Command5_Click()

    Dim lngID1 As Long
    Dim strName As String
    Dim strCriteria As String
    Dim strDate As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Q_WeeklyEmployeeDispatch_ Today", dbOpenDynaset)
    strDate = "#1/1/2013#"
    strCriteria = "[Date]=" & strDate


    If Not rs.EOF Then
    lngID1 = rs![ID1]
    strName = rs![Employee]
    MsgBox "Prime Key: " & lngID1 & " Name: " & strName

    Else
    MsgBox "No record Found"

    End If


    DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , strCriteria, acWindowNormal

    rs.Close

    Set rs = Nothing



    End Sub

  11. #41
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Is there a reason you decided to leave this out?


    rs.FindFirst strCriteria

    If you don't search out some criteria there is no point. You need to move around within your recordset. .Findfirst is what you need to get.

    You mentioned you are going after job dates? Find a record that has a date you want and hard code it into strDate

    Then COMPARE what is coming up in the message box to the hardcoded criteria.

    Then compare to the report. Is there a match foe Jan 1st?

  12. #42
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    It worked! I wasn't using the date 10/1/2013. There were no records using the date you used. Sorry didn't pay attention to that.
    So now how do I remove the testing part to run the query.

    Private Sub Command5_Click()

    Dim lngID1 As Long
    Dim strName As String
    Dim strCriteria As String
    Dim strDate As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Q_WeeklyEmployeeDispatch_ Today", dbOpenDynaset)
    strDate = "#10/13/2013#"
    strCriteria = "[Date]=" & strDate
    rs.FindFirst strCriteria

    If Not rs.EOF Then
    lngID1 = rs![ID1]
    strName = rs![Employee]
    MsgBox "Prime Key: " & lngID1 & " Name: " & strName

    Else
    MsgBox "No record Found"

    End If


    DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , strCriteria, acWindowNormal

    rs.Close

    Set rs = Nothing



    End Sub

  13. #43
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    OK give me a minute. Meanwhile, look at the report. Compare the ID1 and make sure everything is matching. The name in the message box the report, etc.

  14. #44
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    I notice that we don't have email sending as well. How I send the report based on the email field in the T_Inspectors table? Based on this query.

  15. #45
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Yes the Prime Key and Name matches.

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

Similar Threads

  1. Code Problems With Variables
    By bidbud68 in forum Programming
    Replies: 6
    Last Post: 01-30-2013, 01:37 PM
  2. SendEmail CC
    By cbrsix in forum Programming
    Replies: 4
    Last Post: 11-08-2012, 03:03 PM
  3. AfterUpdate SendEMail Macro
    By gdgonzal in forum SharePoint
    Replies: 3
    Last Post: 04-13-2012, 01:49 PM
  4. Problems Executing SavedQuery in Code
    By RMittelman in forum Programming
    Replies: 4
    Last Post: 06-18-2011, 02:49 PM
  5. Merge Code problems
    By kfergus in forum Access
    Replies: 1
    Last Post: 06-01-2006, 03:36 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