add data to a field based on another field

[stuart_roberts]

Hi all, i am new to Access and learning on the go, watching tutorials and trial an error, so if i am not following the correct process, please feel free to tell me.



I have created a Database to track calls on trains that run in service. if they have a technical problem during running time/service, the call has to be logged, i have taken a screen shot of the relationships of the tables, again, i think i have set up the relationships correctly.

on the table [InServiceIssues], there are three fields called "V1,V2,V3" and another three fields called "LastExamV1, LastExamV2, LastExamV3", what i am trying to do is auto fill fields "LastExamV1, LastExamV2, LastExamV3", when the fields "V1, V2, V3" are filled, the fields "V1, V2, V3" are set as a combo box relating to table [ExamData].Vehicle_IDFK, and the fields "LastExamV1, LastExamV2, LastExamV3", will get there data from [ExamData].DateLastExam.


There will be a form created for the user to input the data into the table, but for now i am unsure how to populate the fields "LastExamV1, LastExamV2, LastExamV3" into the table or query if needed

[ranman256]

I dont think you needed 3 vehicle fields, nor 3 last exam fields.
it should be:
1 vehicle table
1 Service table, 1 Vehicle field.
1 Exam table.

the 1 vehicle can now have many Exams.
the 1 vehicle can have many services

[stuart_roberts]

hi sorry should have explained, when the trains go in servive, they are connected together in three's, hence why there are three fields.

[ranman256]

I hope you dont mean LASTEXAM, the exam before this one. You only store the CurrentExamDate.

[stuart_roberts]

its the date of the last exam which was carried out, its for information purposes, to see if the technical issues caused could be related to the previous exam

[ranman256]

Last exam would lookup when the CurrentExamDate happened for the vehicle in the past.
you can't store a previous date on a current record.

[stuart_roberts]

in the tables, which are in the image, [ExamData], there is no CurrentExamDate, right now LastExamDate, is stored as a date,

[ssanfu]

First off, "Date", "Time", "System", "Description" and "Report" are reserved words in Access and shouldn't be used for object names.

I see several problems with your table structure/relationships.
I would advise stopping and reviewing/changing table structures and relationships.... having fields like "V1", "V2", "V3" violate normalization rules.

I don't understand the junction table "FailureClassMap". It doesn't appear to do anything.

[ssanfu]

Deleted - duplicated post somehow........

[stuart_roberts]

hi thanks for the advice, ill make some changes to the field names.

The [FailureClassMap] is to allow me to filter problem codes, ie, so when a failure class is added only the problem code affiliated with that failure class is available in a drop down menu or combo box.

But what i am trying to do at the moment is when for example V1 is filled in with a vehicle number, the field LastExamV1 is auto populated from [ExamData].[DatLastExam] against the corresponding vehicle.

[ssanfu]

Are you keeping a history of exam dates by vehicle in table "ExamData"?

[stuart_roberts]

yes currently i have just the history, but once i set the database up fully, it will house all future exams aswell

[ssanfu]

I don't know your naming convention so:
the control bound to the field "V1", I named "cboV1"
the control bound to the field "V2", I named "cboV2"
the control bound to the field "V3", I named "cboV3"

And the controls bound to the associated date fields are:
"dtLastExamV1 ",
"dtLastExamV2" and
"dtLastExamV3".


In a standard module, add this code:

Option Compare Database   '<- this line should be at the top of EVERY module
Option Explicit           '<- this line should be at the top of EVERY module

Public Function GetLastExamDate(pVehicleID As Long) As Date
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim sSQL As String

    Set d = CurrentDb

    'default return date
    GetLastExamDate = #1/1/100#

    sSQL = "SELECT TOP 1 ExamData.DateLastExam"
    sSQL = sSQL & " FROM ExamData"
    sSQL = sSQL & " WHERE ExamData.DateLastExam <= #" & Date & "# And ExamData.Vehicle_IDFK = " & pVehicleID
    sSQL = sSQL & " ORDER BY ExamData.DateLastExam DESC;"
    '    Debug.Print sSQL

    Set r = d.OpenRecordset(sSQL)
    If Not r.BOF And Not r.EOF Then
        GetLastExamDate = r("DateLastExam")
    End If

    r.Close
    Set r = Nothing
    Set d = Nothing

End Function

In the FORM module, usage would be like:

Private Sub cboV1_AfterUpdate()
        Me.dtLastExamV1 = GetLastExamDate(Me.cboV1)
End Sub


Private Sub cboV2_AfterUpdate()
        Me.dtLastExamV2 = GetLastExamDate(Me.cboV2)
End Sub


Private Sub cboV3_AfterUpdate()
        Me.dtLastExamV3 = GetLastExamDate(Me.cboV3)
End Sub

You could set Conditional Formatting for the last exam date controls to have a red background if the date = #1/1/100#.



(This doesn't change my thoughts on your table designs. I still think you need to redesign them.)