Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 45
  1. #16
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Is OOBNumber a combobox? The rowsource for a combobox is either a query, table, SQL statement, or a value list.

    If the rowsource never changes, just type the SQL into the combobox rowsource property. If not, you can type a query name as the rowsource property and use the code I've provided previously to create the custom query on the fly.


    It seems odd to me that you would have the rowsource SQL for a combobox in an unbound textbox. How does the SQL get into the unbound textbox in the first place?
    Could you upload a zipped copy of your DB so that I could better understand what you have so far, so that I can make informed suggestions on helping you reach your goals?

  2. #17
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by davegri View Post
    Is OOBNumber a combobox? The rowsource for a combobox is either a query, table, SQL statement, or a value list.

    If the rowsource never changes, just type the SQL into the combobox rowsource property. If not, you can type a query name as the rowsource property and use the code I've provided previously to create the custom query on the fly.
    It seems odd to me that you would have the rowsource SQL for a combobox in an unbound textbox. How does the SQL get into the unbound textbox in the first place?
    Could you upload a zipped copy of your DB so that I could better understand what you have so far, so that I can make informed suggestions on helping you reach your goals?
    OOBNumber is a textbox.

    See attached file:
    testing.zip

  3. #18
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Attachment 24972
    Take a look at this. It incorporates AOVote from frmSetup into a new query, qRecSourceODBChanges. This query is now the recordsource for frmOOBChangesSelect.
    To see it work, click a button on frmSetup, then click 'Continue' (a new button).
    Then open qRecSourceODBChanges to see its content.

  4. #19
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Attachment 24973
    OOBNumber is a listbox, not a textbox. That makes a lot of difference.
    See the attached db. It will populate OOBNumber.

  5. #20
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Dave,
    I used the first one. I didn't work completely at first. No numbers showed up in the OOBNumber control. I corrected some of my syntax for AOVote/O6Vote and got it working for the most part. Next is figuring out how to save the updated Priority & HR control to the table on the correct row(s).

    I'll take a look at the second one to see the differences.

  6. #21
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Attachment 24989

    I've combined the two forms into one and put the criteria controls on the form so that you can see them.
    The AOSelects textbox is locked. It's probably not a good idea for the users to be typing in SQL criteria content - to easy to screw up the query. Those strings should be set in code, away from user tampering. If you need more criteria combinations, add more command buttons to handle new options.

  7. #22
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I have a form for the buttons already. The database is used by me only. The output is used by a lot of people. Attached is my updated version. Its meeting most of what I need, Now I need to be able to write to the table, the records where the Priority and Hr controls are changed. Then put it all in the email as text. I have somewhat of a version for that. See EmailAORB.

    Thompyt.zip

  8. #23
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    That looks pretty nice. You have buttons calling forms yet to be developed, so that's work to do.
    I have never used macros, so if you have problems in that area, I can't help. I like VBA better because you can search the code project-wide to find things. You can't do that with macros and they can really hide their actions.

  9. #24
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Davegri,
    I finally got time to play with the code and set it up with a few tweaks here and there. Now I have it going to the report and emailing a PDF with the correct subject line and signature. What I am trying to do now is to have the text version of the report with some wording added. I have been using the following in my old email as a strbody & strsubject:
    Code:
    Public Const txtMail As String = "Action Officers," & vbCrLf & "This is a follow-on from the AORB/TEWG discussion on CR " _
            & !OOBNumber & ".  If needed, please back-brief your O6 for SA, and let me know if there are any issues or concerns. " _
            & "The Change Request priority is " & !Priority & " with " & !Hr & " hours until CR is automatically approved.  " _
            & "Please provide your votes NLT " & !DTG & ". Please call if you have any questions or issues." & vbCrLf & vbCrLf _
            & "In order to update the email list, please update, request removal, or add recipients to the sender." & vbCrLf & vbCrLf _
            & "Date Issue Identified: " & Chr(9) & Chr(9) & !Dates & vbCrLf & vbCrLf & "Priority: " & Chr(9) & Chr(9) & Chr(9) _
            & !Priority & vbCrLf & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & !OOBNumber & vbCrLf & "AO Recommendation: " _
            & Chr(9) & Chr(9) & !AOVote & vbCrLf & vbCrLf & "Change Requested: " & Chr(9) & Chr(9) & ![ChangeRequested] & vbCrLf & vbCrLf _
            & "Unit & Section: " & Chr(9) & Chr(9) & !Units & vbCrLf & "MTOE Para & Bumper Number: " & Chr(9) & ![MTOEParas] & vbCrLf & vbCrLf _
            & "Rationale: " & Chr(9) & Chr(9) & !Rationale & vbCrLf & vbCrLf & "Notes: " & Chr(9) & Chr(9) & Chr(9) & !NOTES & vbCrLf _
            & "Action Items: " & Chr(9) & Chr(9) & !ActionItems & SigBlock

    I was thinking I could put this in a module as a textMail and reference to it in the .Body line. How do I reference the output of the report or Query to get the same data?

    Thanks.

  10. #25
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I am trying this:
    Code:
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT OOBNumber,ChangeRequested,FROM qRecSourceOOBChanges ORDER BY Level, CRID ASC")
    
    strHdrMail = "This is a follow-on from the AORB/CCB/TEWG discussion on CR" & !OOBNumber & ". If needed, please back-brief your O6 for SA, " _
            & "and let us know if there are any issues or concerns. The Change Request priority is " & !Priority & " with " & !Hr & "" _
            & "hours until CR is automatically approved (GO OOB Excepeted). Please provide your votes NLT " & !DTG & ". Please call if you have " _
            & "any questions or issues. " & vbCrLf & vbCrLf
    
    While Not rs.EOF
    
    strBdyMail = "Date Issue Identified: " & Chr(9) & Chr(9) & !Dates & vbCrLf & vbCrLf & "Priority: " & Chr(9) & Chr(9) & Chr(9) _
            & !Priority & vbCrLf & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & !OOBNumber & vbCrLf & "AO Recommendation: " _
            & Chr(9) & Chr(9) & !AOVote & vbCrLf & vbCrLf & "Change Requested: " & Chr(9) & Chr(9) & ![ChangeRequested] & vbCrLf & vbCrLf _
            & "Unit & Section: " & Chr(9) & Chr(9) & !Units & vbCrLf & "MTOE Para & Bumper Number: " & Chr(9) & ![MTOEParas] & vbCrLf & vbCrLf _
            & "Rationale: " & Chr(9) & Chr(9) & !Rationale & vbCrLf & vbCrLf & "Notes: " & Chr(9) & Chr(9) & Chr(9) & !NOTES & vbCrLf _
            & "Action Items: " & Chr(9) & Chr(9) & !ActionItems & vbCrLf & vbCrLf
     
     rs.MoveNext
    Wend
     rs.Close
    
    With objOutlookMsg
       .Subject = NIE & " - " & Label & " - " & Tod
       .Body = strHdrMail & strBdyMail1 & SigBlock
    But I get a Compile Error: Invalid or unqualified reference

    on !OOBNumber.

    I take it that
    Set rs = CurrentDb.OpenRecordset("SELECT OOBNumber,ChangeRequested,FROM qRecSourceOOBChanges ORDER BY Level, CRID ASC") would be the culprit. How do I reference the Query qRecSourceOOBChanges correctly to get the data?

    Thanks

  11. #26
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have a comma before FROM that shouldn't be there - should have a space:

    Bad
    Code:
    Set rs = CurrentDb.OpenRecordset("SELECT OOBNumber,ChangeRequested,FROM qRecSourceOOBChanges ORDER BY Level, CRID ASC")
    Good
    Code:
    Set rs = CurrentDb.OpenRecordset("SELECT OOBNumber, ChangeRequested FROM qRecSourceOOBChanges ORDER BY Level, CRID ASC")

  12. #27
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ssanfu,
    I have tried:
    Set rs = CurrentDb.OpenRecordset("SELECT OOBNumber FROM qRecSourceOOBChanges ORDER BY Level, CRID ASC")

    Same issue.

    I tried
    FROM query.qRecSourceOOBChanges
    FROM query!qRecSourceOOBChanges
    FROM qRecSourceOOBChanges

  13. #28
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    is strBdyMail1 misspelled?

    Code:
    With objOutlookMsg
       .Subject = NIE & " - " & Label & " - " & Tod
       .Body = strHdrMail & strBdyMail1 & SigBlock

  14. #29
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I used your db from Post #22.

    I tried creating a new query to see if the query had errors. It would not execute.

    First problem: One of the fields in the table, "Level", is a reserved word. To use it in a query, you must enclose it in brackets. (Personally, I think "Level" is a poor name - "Level" of what???)
    So I tried the query like this
    Code:
    SELECT OOBNumber FROM qRecSourceOOBChanges ORDER BY [Level], CRID ASC
    Second problem: I got another error: "Enter Parameter - CRID".
    The error is in the "ORDER BY" clause.
    The query "qRecSourceOOBChanges" does not have a field/column named "CRID"; you cannot order by (sort) on a nonexistent field. Therefore the query bombs.

    Fix "qRecSourceOOBChanges" and try again....

  15. #30
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks ssanfu,
    I'll check it out when I get to work and can play with it again. I did add CRID to the query, but did not know about [Level] being reserved word.

    Davegri, I did catch that misspelling after posting. Thanks

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

Similar Threads

  1. Replies: 22
    Last Post: 09-08-2015, 04:43 PM
  2. Command buttons & images displayed on a form
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 10-15-2013, 09:34 AM
  3. Command Buttons Stopped Working on Form
    By genest11 in forum Reports
    Replies: 12
    Last Post: 02-06-2013, 06:59 AM
  4. Form Command buttons
    By chazcoral2 in forum Forms
    Replies: 6
    Last Post: 05-01-2012, 08:10 AM
  5. Command Buttons on form
    By bespra in forum Forms
    Replies: 10
    Last Post: 11-12-2011, 01:50 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