I am working on an event tracker.
Can anyone spot what is wrong with my code?
Thanks in advance.
Start Code
Dim MyDB As DAO.Database
Dim rstSectionList As DAO.Recordset
Dim EventKey As Integer
Dim PersonnelKey As Integer
'Stores the current Table.Events Primary Key to insert into table.
EventKey = Me.PKEvents
Set MyDB = CurrentDb()
'http://www.databaselessons.com/dao.php (link to information)
Set rstSectionList = MyDB.OpenRecordset("SELECT Personnel.PKPersonnel, Personnel.sectionlu " & _
"FROM LUSection INNER JOIN Personnel ON LUSection.PKLookup = Personnel.SectionLU " & _
"WHERE Personnel.sectionlu=" & [Forms]![frmEvents]![SectionLU])
Set MMEventtoPersonnel = MyDB.OpenRecordset("MMEventtoPersonnel")
If rstSectionList.EOF And rstSectionList.BOF Then
MsgBox "No names for chosen section."
Else
rstSectionList.MoveFirst
Do While Not rstSectionList.EOF
With rstSectionList
MMEventtoPersonnel.AddNew
MMEventtoPersonnel.FKEvents = EventKey
'This following line is not working. It will not take PKPersonnel from the rstSectionList and put it in the field.
MMEventtoPersonnel.FKPersonnel = PersonnelKey
MMEventtoPersonnel.Update
End With
Loop
End If
rstSectionList.Close
Set rstSectionList = Nothing
End Code
A little more explanation for what I am trying to do.
I have the following tables: Events, MMEventtoPersonnel, Personnel. Each have a numeric Primary Key, and the MMEventtoPersonnel stores a dual primary key for Personnel/Events, so that I won't receive duplicate records in the many-to-many table.
The concept is that when you open the Events form, you choose a section from the cboSectionLU. Then click a button which will that specific list of personnel to the MMEventtoPersonnel Table.
The required Foreign Key comes from the form. EventKey = Me.PKEvents
The required Foreign Key for Personnel I am trying to get from my recordset Personnel.PKPersonnel.
I removed the primary keys for the MMEventtoPersonnel, to test what was actually inserting and found that the EventKey was working, but still not inserting the PKPersonnel into the FKPersonnel field of the MMEventtoPersonnel. (To the tune of almost 3 million records before I stopped it. HaHa!)