Originally Posted by
ssanfu
Why can't you add the other tables to the query to get the desc fields?
I have tbEvents in the query and I can easily access eventDescription. The problem is that eventDescription contain only general description of the event e.g. "logged in/out". For some events it's not enough, because I want to convey more information. For example eventId = 4 is eventDescription is only "added product". I want it to appear in the history with more information like "added product" & nameOfProduct & " to the project " & nameOfProject and that's the whole point.
Originally Posted by
ssanfu
What is the SQL of the query for "Dane"?
Code:
SELECT tbHistory.Time, [tbUsers].[userName] & " " & [tbUsers].[userSurname] AS UserFullName, translateHistory([tbEvents].[EventId]) AS EventDescription, tbEvents.EventDesc, tbProjects.projectName, tbProducts.productName
FROM (((((tbEvents RIGHT JOIN tbHistory ON tbEvents.EventId = tbHistory.EventId) LEFT JOIN tbUsers ON tbHistory.UserId = tbUsers.UserId) LEFT JOIN tbFormDesc ON tbHistory.formId = tbFormDesc.FormId) LEFT JOIN tbProjects ON tbHistory.projectId = tbProjects.prID) LEFT JOIN tbProducts ON tbHistory.productId = tbProducts.productId) LEFT JOIN tbProjects AS tbProjects_1 ON tbProducts.projectId = tbProjects_1.prID
ORDER BY tbHistory.Time DESC;
Originally Posted by
ssanfu
I prefer to use recordsets to get the data... see the attachment....
I like recordsets as well. I had it written in similar way as you proposed, please take a look:
Code:
Public Function translateHistory(historyId As Long) As StringDim eventId As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tbHistory WHERE HistoryId = " & historyId)
If Not rs.EOF Then
rs.MoveFirst
eventId = rs.Fields("EventId")
Select Case eventId
Case 1 To 2
translateHistory = EventDescription(eventId)
Case 6
translateHistory = EventDescription(eventId) & "w formularzu <b>" & formDescription(rs.Fields("formId")) & "</b>"
Case 3
translateHistory = EventDescription(eventId) & " o nazwie <b>" & prjName(rs.Fields("projectId")) & "</b>"
Case 4
translateHistory = EventDescription(eventId) & " o nazwie <b>" & productName(rs.Fields("productId")) & "</b> do projektu <b>" & prjName(rs.Fields("projectId")) & "</b>"
Case 7
translateHistory = EventDescription(eventId) & " <b>" & prjName(rs.Fields("projectId")) & "</b>"
Case 8
translateHistory = EventDescription(eventId) & " <b>" & prjName(rs.Fields("projectId")) & "</b>"
Case 9
translateHistory = EventDescription(eventId) & " <b>" & productName(rs.Fields("productId")) & "</b>"
Case 10
translateHistory = EventDescription(eventId) & " <b>" & productName(rs.Fields("productId")) & "</b> z projektu <b>" & prjName(rs.Fields("projectId")) & "/<b>"
Case 11
translateHistory = EventDescription(eventId) & " <b>" & stepDescription(rs.Fields("stepId")) & "</b> w ramach produktu <b>" & productName(rs.Fields("productId")) & "</b>"
End Select
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Public Function EventDescription(eventId As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tbEvents WHERE EventId = " & eventId)
If Not rs.EOF Then
rs.MoveFirst
EventDescription = rs.Fields("EventDesc")
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Public Function formDescription(formId As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tbFormDesc WHERE FormId = " & formId)
If Not rs.EOF Then
rs.MoveFirst
formDescription = rs.Fields("FormName")
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Public Function stepDescription(stepId As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tbProjectSteps WHERE stepId = " & stepId)
If Not rs.EOF Then
rs.MoveFirst
stepDescription = rs.Fields("stepDescription")
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
This code works fine. The problem, however, is that for every record from tbHistory it opens/closes 5 recordsets from tables located on a network drive and this has a huge impact of overall performance of this form.. I will have a look how DLookup affects the performance, hopefully it's better.
Windows 7 64bit 
Access 2010 32bit
Reply With Quote
