Pop up table

[KristenD]

I have a table that stores records based on a field in another table/form. Because this field changes multiple times I always want the most current entry in the pop up table/form.

How would I got about making sure when I run queries/reports that only the most current data is taken from this table? Should I add another field to the table that has a date/time and have it =Now? Would this be the best way?

Thank you!!

 

[MarkK]

If I understand you correctly you have a table which contains data that is calculated or drawn from data in a different table. Is that correct?

The solution is don't do this. Store data raw, and in only one place. If you need to process, calculate or format that data, do so at retrieval time. This ensures that at retrieval time, you draw your result from the sole version of the most recent data available.

Cheers,
Mark

 

[KristenD]

Not exactly. This table the records are entered through a pop up form. The form pops up and the data is entered if another field in the main form (based on another table) contains certain information.

For example the form is based on the field EmpStatus. If EmpStatus = "Active" then the form pops (EmpInfo) up and I enter the information. My problem is this status can change several times so I want it to take only the most current entry from the EmpInfo form.

 

[MarkK]

I don't get it.
1) Can you show how your tables are structured? Notation might look like...

tblEmployee
EmployeeID (Primary Key, or PK)
EmployeeTypeID (Foreign Key, or FK)
FirstName
LastName
Status
...

Forms are of secondary concern, since they are just windows to view data in tables. It's the tables that matter.
2) Can you then describe what process needs to occur in respect to that data.
Mark

 

[KristenD]

I have attached the relationships to the tables.
I have a code behind that as well:

Private Sub EmploymentStatus_AfterUpdate()
If Me.EmploymentStatus = "Active" Then
    Me.tblEmpInfo.Visible = True
    
Else
    Me.tblEmpInfo.Visible = False
    
End If
End Sub
Private Sub Form_Current()
If Me.NewRecord Then
    Me.tblEmpInfo.Visible = True
    
Else
    If Me.EmploymentStatus = "Active" Then
        Me.tblEmpInfo.Visible = True
    Else
        Me.tblEmpInfo.Visible = False
    End If
End If
End Sub

I only want to look at the data in tblEmpInfo if the employee is active. If the employee is not active I have no need to see the data. The problem comes the employee becomes active again and the tblEmpInfo has new information entered into it. I only want to see that most recent entry. The employee may have switched jobs or switched craft codes when they became active again. I hope I'm making sense.