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

    I do not see the 2 new tables? I still have Main table, Testing type,TR status, Users, Engine family

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

    Sorry wrong db uploaded

    There is an error on startup "Cannot find ID" which I do not know where it is coming from. Click OK and Form will open.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #18
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    So some things I am noticing is that the Testrequest ID is can be different than the Result ID. That might lead to some confusion down the road. For the EPA TR all the Info does not need to be displayed as the person requesting will not know that info. The layout that you have for Epa TR is what I would consider for our engineer to fill out based on what is submitted.

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

    The Coloured Controls are the PK & FK ot both Tables .

    These are of no interest to the user and would be hidden
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #20
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    The ID Number was going to be used to assign a TR number, But if I need to create a auto number besides that to have TR number for the requester to later search that is fine

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

    As mentioned previously the Autonumber field should NOT be used for anything.

    It will automatically assign a unique ID for each record.

    If you want to add a TestRequestNumber then you can add this field to the Table and Manually enter a number for each Request.

    The normal method is to add a Unique TestRequestNumber automatically by using the following Before Update of the Form

    Me.TestRequestNumber = Nz(DMax("TestRequestNumber ","tblTestRequests"),0) + 1
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #22
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by pelle15 View Post
    whats wrong with having look up fields in data table? Calculated Fields?
    See
    The evils of Lookup fields
    About calculated table fields



    Quote Originally Posted by pelle15 View Post
    ....and what do you mean the main table is not normilized?
    What is Normalization? (there are 5 Parts)
    =======================
    What Is Normalization, Part I: Why Normalization?




    @mike60smart, the reason there is a error when the form opens is because of the calculated field, "TR Number" in the table "tblTestRequest".
    The calculation is
    Code:
    [ID]+10000
    , but since the PK field "ID" was renamed to "TestResultID" ( I would name it "TestResultID_PK), the field "ID" cannot be found....... sooooooo _>> ERROR!



    I took the dB that mike60smart posted and made more changes. There is still references to "Main Table" in the code and a few other errors, but in general it works.
    I couldn't change a few fields because of the Linked table "Engine family".

    I think "tblResults" could be "Normalized" a bit more, but since I am not sure what fields should be grouped together, I left it alone. Maybe I'll look at it in a few days.


    Taking the PK field (Autonumber) adding 10,000 to it and saving it as TR Number is no different that using the Autonumber field.
    More on the Autonumber type:

    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key


    Autonumbers--What they are NOT and What They Are
    Microsoft Access Tables: Primary Key Tips and Techniques



    Some suggestions I follow:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.
    Attached Files Attached Files

  8. #23
    pelle15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    11
    Ok, Thanks for the help guys. I have made some good progress over the last couple days and have a question about the automated email. when I use the Me.StatusID it returns a number that corrisponds to the text value that I want. How Do I get it to input the variable text? I have the code below

    Private Sub Send_Status_Update_Click()


    ' Dim db As DAO.Database
    ' Dim rs As DAO.Recordset

    Dim emailTo As String
    Dim emailSubject As String
    Dim emailText As String


    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean


    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
    Set outApp = CreateObject("Outlook.Application")
    outlookStarted = True
    End If


    ' Set db = CurrentDb
    ' Set rs = db.OpenRecordset("SELECT

    'Do Until rs.EOF


    ' emailTo = Trim(rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value) & _
    ' " <" & rs.Fields("EmailAddress").Value & ">"


    emailTo = "e@textron.com"

    emailSubject = "Test Excel Email"
    ' If IsNull(rs.Fields("FirstName").Value) Then
    ' emailSubject = emailSubject & " for " & _
    ' rs.Fields("FirstName").Value & " " & rs.Fields("LastName").Value
    ' End If

    emailText = "Your Status Has Changed to " & Me.StatusID.Value

    ' emailText = Trim("Hi " & rs.Fields("FirstName").Value) & "!" & vbCrLf

    ' If rs.Fields("IsVIP").Value Then
    ' emailText = emailText & "Here is a special offer only for VIP customers!" & _
    ' "Only this month: Get the foo widget half price!" & vbCrLf
    ' End If

    ' emailText = emailText & _
    ' "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. " & _
    ' "Maecenas porttitor congue massa. Fusce posuere, magna sed " & _
    ' "pulvinar ultricies, purus lectus malesuada libero, sit amet " & _
    ' "commodo magna eros quis urna."



    Set outMail = outApp.CreateItem(olMailItem)
    outMail.To = emailTo
    outMail.Subject = emailSubject
    outMail.Body = emailText
    outMail.Send


    ' rs.MoveNext
    ' Loop

    ' rs.Close
    ' Set rs = Nothing
    ' Set db = Nothing

    If outlookStarted Then
    outApp.Quit
    End If

    Set outMail = Nothing
    Set outApp = Nothing

    End Sub

Page 2 of 2 FirstFirst 12
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