Results 1 to 8 of 8
  1. #1
    jon6035 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    California
    Posts
    12

    Value from Recordset not inserting

    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!)

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,817
    Two problems:

    1. You declare variable PersonnelKey but don't set it. Don't need it.
    MMEventtoPersonnel.FKPersonnel = rstSectionList![Personnel.PKPersonnel]

    2. You don't advance to next rstSectionList record within the loop, loop would never end.
    rstSectionList.MoveNext

    Do While Not rstSectionList.EOF
    MMEventtoPersonnel.AddNew
    MMEventtoPersonnel.FKEvents = EventKey
    MMEventtoPersonnel.FKPersonnel = rstSectionList![Personnel.PKPersonnel]
    MMEventtoPersonnel.Update
    rstSectionList.MoveNext
    Loop

    Suggest you learn more about debugging techniques. Click the link at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jon6035 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    California
    Posts
    12
    1. I removed the variable declaration Dim Personnel Key as Integer.

    2. I replaced my Do While Command with the one you provided.

    I am receiving an RunTime Error '3265': Item not found in collection.

    At this line:

    MMEventtoPersonnel.FKPersonnel = rstSectionList![Personnel.PKPersonnel]

    Furthermore, I agree that I need to learn more debugging, and I will check out your link.

    Respectfully,

    Jon

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,817
    Try:
    MMEventtoPersonnel.FKPersonnel = rstSectionList!PKPersonnel
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jon6035 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    California
    Posts
    12
    Just got it, then I saw your post.

    Now my dual key is causing me a problem. I realize I may be making this to complicated, but I need for the records that don't exist i.e. a FKEvent and FKPersonnel to be appended regardless of whether another person from the list has already been entered. I am trying to keep the Many-to-Many table cleaned out. Any idea how I might get it allow the addition of these records. It looks like the entire loop is stopping when it sees the first duplicate entry.

    Thank you for the quick response!

    Would this be considered error trapping?

    So I tried to add Error Handling with an On Error GoTo ErrorHandler in the declarations and the following code:

    ExitHandler:
    Set rstSectionList = Nothing
    Set MMEventtoPersonnel = Nothing
    Set MyDB = Nothing
    Exit Sub

    ErrorHandler:
    Select Case Err
    Case 3022
    MsgBox "Duplicate Data"
    'It at least stops, but I don't get the records that were not previously there.
    rstSectionList.MoveNext

    Case Else
    MsgBox Err.ErrorDescription
    Resume ExitHandler
    End Select

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,817
    If the EventKey and PersonnelKey are unique then writing records should not be an issue. If this table has these two foreign keys as a compound primary key then no way to duplicate the entries.

    Don't really understand 'FKEvent and FKPersonnel to be appended regardless of whether another person from the list has already been entered'. What has 'another' person got to do with writing this record.

    What do you mean by 'keep the Many-to-Many table cleaned out'?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    jon6035 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    California
    Posts
    12
    Then I don't think I am putting the right values in the field [PKPersonnel]. When I look at the PKPersonnel using the same syntax in Query it looks like a small L up in the right hand corner of the field. When I turned the query into a make table and looked at the field values, it was a binary field type. This was for PKPersonnel, although in the source table it is an Integer.

    For the section I am using, I have 11 people in it. If I remove 5, and click the button, then I get the error. Or if I add a person to that section in the personnel table it doesn't pick it up. The only time I don't get the error when no personnel from that section are listed.

    I intended to add to this form a delete query that would remove records that did not have the AttendEvent box selected. This is in an effort to keep the database as small as possible over time. Probably irrelevant. But once an event passes, normally it will not be adjusted (i.e. personnel attending), I don't want to have 100 records for each event, when only 10 people attend the event. To have to query against as I begin to deal with larger and larger groups of people.

    There are a thousand people in my unit, that could possible be entered into the database and then linked to an individual event.

    It may be simpler to look at the attached database. It is still really rough, but I think you will be able to see the problem I am having a little better.

    It also has the report that you helped me with last week, on the frmEventScheduling.

  8. #8
    jon6035 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2011
    Location
    California
    Posts
    12
    I also tried this method and finally got it to work, based on the things that you told me in earlier posts

    DoCmd.RunSQL ("INSERT INTO MMEventtoPersonnel ( FKPersonnel, FKEvents ) " & _
    "SELECT Personnel.PKPersonnel, [Forms]![frmEvents]![PKEvents] " & _
    "FROM LUSection INNER JOIN Personnel ON LUSection.PKLookup = Personnel.SectionLU " & _
    "WHERE Personnel.sectionlu=" & [Forms]![frmEvents]![SectionLU])

    It does exactly what I needed it to.

    Thank you for your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. inserting a table
    By vaseemali in forum Forms
    Replies: 3
    Last Post: 08-21-2011, 02:03 PM
  2. !!Please Please Help!! Inserting my Data
    By dinorbaccess in forum Access
    Replies: 9
    Last Post: 01-08-2011, 05:24 AM
  3. Inserting into a database
    By Cined in forum Programming
    Replies: 1
    Last Post: 04-14-2010, 12:33 PM
  4. Inserting/Updating
    By detlion1643 in forum Access
    Replies: 1
    Last Post: 02-26-2010, 07:25 PM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums