This is tricky however I would love some help to do this, My design is quite complex however I will try and explain it (I also have a number of other posts pertaining to the design with example databases)
I have the following tables
- tblInspections
- tblEquipment
- tblEquipmentInspectionJunction
(Each piece of equipment can be involved in many inspections and each inspection can have many pieces of equipment)
Furthermore each piece of equipment will be inspected with a totally different check-list where the table structure is
- tblChecklistQuestions (stores the questions for all check-lists)
- tblChecklistAnswers (stores the answers for all check-lists)
- tblResults (stores the results with the checklistID and ChecklistAnswerID foreign keys)
I have almost got my first junction sorted however the second junction is where I have 2 major complex coding questions
1) For this to work the form must have the same field for the answer however this will store different records for the same piece of equipment
I can use something like this
For i = 1 To 45 (whatever number of questions in the checklist)
db.Execute " INSERT INTO [tblChecklistAnswer]" & "([Answer], [Response]) VALUES " _
& "('" & Me.Controls("txtAnswer" & i).Value & "'," & "'" & Me.Controls("cmbResponse" & i).Value & "'" & ");"
Next i
And this works great however this simply enters whatever is input into ChecklistAnswer Table
What I need is similar code that extracts the checklistAnswerID and the ChecklistID from the appropriate tables and then goes through the loop and enters this data in the results table
2) This may be related somewhat as my difficulty is in extracting the data from the tables however for each specific record (same field) I would like to set the default value as that of the last inspection (if the equipment has been inspected before)
Therefore I am trying with DLookup where the code is something like
if inspected before
DLookup from tblChecklistAnswer (answer) where checklistID and checklistAnswerId are matched in tblResults for that specific piece of equipment (complicated in my head even to write this)
This is the hardest part of my database, however it is also extremely essential for the whole database to operate and I will be greatly appreciative of any assistance
Thank you in advance