I do not see the 2 new tables? I still have Main table, Testing type,TR status, Users, Engine family
I do not see the 2 new tables? I still have Main table, Testing type,TR status, Users, Engine family
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.
You can PM me if you need further help.
Good Reading https://docs.microsoft.com/en-gb/off...on-description
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.
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
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
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
See
The evils of Lookup fields
About calculated table fields
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, but since the PK field "ID" was renamed to "TestResultID" ( I would name it "TestResultID_PK), the field "ID" cannot be found....... sooooooo _>> ERROR!Code:[ID]+10000
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.
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