Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11

    Form Datasheet varables

    Hello,



    Im looking to create a automated system that we process Test requests though. I use a form called "Main TR Dev" to view the test request and change them with the needed data after they are submitted. Once they are filled out I will changed the variable named "Status" to approved/In Test/ect. This is where things get tricky (For me at least), I want to send a automated email to the requester that their Test request has changed status. I have tried querying the current record with no success as im using a data sheet in the footer of the form to select what Test request I want to view. I know I missing a ton of info but do not really know what you guys need to help me out.

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Can you upload a zipped copy of the database?

  3. #3
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    I have attached a zip file with the database
    Attached Files Attached Files

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    There is no need for a Split Form.

    All you need is a standard MainForm with a Combobox in the header which allows you to select a specific TR

    You can also obtain the EMail address by using the following in an Unbound Textbox to make a reference to the Requestor Combobox.

    =[Requestor].Column(2)

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    See frmMainTRDev in the attached
    Attached Files Attached Files

  6. #6
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    The split form gave me easy access to all Test requests at easy glance. This will only be used to change data after submissions by selected personnel. What I need is a Serial Email that is sent on status change

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    I was suggesting a MainForm as the best design because your Main Table is not Normalised.

    On a Specific Day and Number of Requests are Received for Testing

    This indicates at least 3 Tables to replace Main Table




    Not knowing your process we would need a more detailed explanation of your testing process.

  8. #8
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    Process would be be something along the following:
    - User submits TR
    - Email Sent to my group that a new TR has been submitted
    - One in my group gets assigned to the TR and fills out the details needed to complete Testing and changes status to Approved
    - Email sent out to original user that submitted TR that status has changed
    - Once testing has started data will need to be entered at any time
    - Status change Email that Testing is complete with report attached

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Which of the Fields relate specifically to the TR
    Which of the Fields relate specifically to the Testing

  10. #10
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    If you look at the Form "EPA TR" you will see the fields that relate to the TR
    The Rest are additional data that is related to testing

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware that "Date" is a reserved word in Access and shouldn't be used for object names.

    Emailing from Access is a fairly common topic. I did a search using "email from access" and had a lot of hits. Here is one: Sending email automatically from access 2013
    Are you using Outlook?


    I would suggest that you push pause for a while and fix other issues.
    You also have Look up FIELDS in tables, spaces in object names and calculated fields in tables.
    Having ALL PK fields named "ID" is a very poor naming scheme.
    Autonumber PK fields should not be displayed on forms.
    The main table is not normalized - it could be broken up into at least 3 (maybe 4) tables.




    Good luck with your project.

  12. #12
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    So fairly new to access, whats wrong with having look up fields in data table? Calculated Fields?
    Spaces Im starting to understand for coding reasons lol
    and what do you mean the main table is not normilized?

  13. #13
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Whenever you see all data being collected in 1 Table it normally indicates that the table needs to be Normalised.

    Have a read of the following https://docs.microsoft.com/en-gb/off...on-description

  14. #14
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    That only confused me more. I need all the data regarding to the the filed test request to be linked together based on the TR Number. If I was going to Normilize it the only way I could think of doing it would be to have the type of request being the 2nd norm.

  15. #15
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Ok in the attached I have changed your relationship diagram so that we have tblTestRequest linked to tblTestResults

    These 2 Tables are linked by the PK in tblTestRequest to the FK in tblTestResults.

    The Form that opens in startup shows tblTestRequest as the Main Form with tblTestResults as the Subform
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 01-16-2017, 11:46 PM
  2. Replies: 3
    Last Post: 08-18-2013, 09:14 PM
  3. Replies: 6
    Last Post: 11-21-2012, 05:10 PM
  4. Replies: 8
    Last Post: 08-26-2012, 11:11 PM
  5. Replies: 4
    Last Post: 05-04-2012, 03:41 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