Results 1 to 12 of 12
  1. #1
    mrfixit1170 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    42

    Click Event to create Records in two tables

    Hey guys,
    I have had a request to change a few features on this database I've been working on. I have been asked to



    provide a way to add more time to a Maintenance Event record.

    I have started with the click event below, it creates a record in the "tbl_AVUM_Scored_Data" the IETM_ID

    has time assigned to it from a table called "tbl_AVUM_Test_BT", these are all fixed time that don't change.

    As a Maintenance Event is reviewed a task from table tbl_AVUM_Test_BT is selected with the assigned times.

    There are instances when a Maintenance Event may have taken longer or shorter depending on the situation,

    the code below is an attempt to create a record for the Maintenance Event and to create a related record with

    the added times for that instance of the task.

    Essentially I am creating a record and an edit for the record at the same time. I am not clear how to

    reference the Record_ID of the Maint Event before it gets created to use it in the tbl_SME_AVUM_Edit table.

    Code:
    tbl_AVUM_SCORED_Data
    Record_ID	AutoNumber
    (Pk) EI_ID	Text
    (Pk) Event_Date	date/time
    (Pk) Event_No	Number
    (Pk) Sys_Code	Text
    IETM_ID		Number
    Code:
    tbl_SME_AVUM_Edit
    (Fk) AVUM_SD_Record_ID	Number
    MOS_ID			Text
    Time			Number
    Comments		Text
    Date Added		date/time
    Code:
    Private Sub btnSubmitSMEUPDATE_Click()
    Dim strItems As String
    Dim intItems As Integer
    Dim varItem As Variant
    Dim var2Item As Variant
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    
    On Error GoTo ErrorHandler
    
    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("tbl_AVUM_Scored_Data", dbOpenDynaset)
    Set rs2 = db.OpenRecordset("tbl_SME_AVUM_Edit", dbOpenDynaset)
        
        Set ctl = Forms!frm_TaskList_Popup!List2
        For Each varItem In ctl.ItemsSelected
        rs1.AddNew
        rs1!EI_ID = Forms!frm_Acft_Score!EI_ID
        rs1!EVENT_DATE = Forms!frm_Acft_Score!EVENT_DATE
        rs1!EVENT_NO = Forms!frm_Acft_Score!EVENT_NO
        rs1!SYS_CODE = Forms!frm_Acft_Score!SYS_CODE
        rs1!IETM_ID = ctl.ItemData(varItem)
        rs1.Update
    
    Next varItem
        
        Set ctl2 = Forms!frm_SME_Update!lboAVUMSMEUpdate
        For Each var2Item In ctl2.ItemSelected
        rs2.AddNew
        rs2!Record_ID = rs1!Record_ID
        rs2!MOS_ID = Forms!frm_SME_Update!cboSMEMOSo1
        rs2!Time = Forms!frm_SME_Update!txboSMETimeo1
        rs2!Comments = rs2!MOS_ID = Forms!frm_SME_Update!txboSMEAVUMComments
        rs2.Update
    
    Next var2Item
    
    ExitHandler:
        Set strItem = Nothing
        Set intItem = Nothing
        Set ctl = Nothing
    Exit Sub
    
    ErrorHandler:
        Select Case Err
            Case Else
            MsgBox Err.Description
            DoCmd.Hourglass False
            Resume ExitHandler
        End Select
    
    End Sub
    I hope this is clear. Please ask questions for clarity.

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One way:

    Code:
          Do While Not rsLocal.EOF
            'add master record
            With rsMaster
              .AddNew
              !Cust_key = rsLocal!CustKey
              !company_cd = "CK"
              !Division = 9
              !Dor_date = rsLocal!DORDate
              !Driver = rsLocal!LeaseDriverNum
              !Tran_type = "I"
              !Total_amt = rsLocal!Amount
              !Added = strUser
              !Added_Date = Now()
              .Update
              .Bookmark = .LastModified
              lngARKey = !AR_Key
            End With
    
            'insert fare amount into detail
            With rsDetail
              .AddNew
              !AR_Key = lngARKey
              !Detail_type = "R"
              !Description = rsLocal!Comments
              !Quantity = 1
              !Amount = rsLocal!Amount
              !Ext_amount = rsLocal!Amount
              .Update
            End With
            rsLocal.MoveNext
          Loop
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mrfixit1170 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    42
    pbaldy,
    Thank you for your response, just so I am clear will I place the "Do While" statement after my "For Each" but before my ".AddNew" statements?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not exactly clear on what you're doing, but if you want to grab the inserted value from the first recordset to use in the second, you'll need to do it right away. The way you've got it, you could insert numerous items with the first recordset before doing the second. If there's a relationship between them, I'd expect it to be done in the same loop. What do the 2 different list boxes represent?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    mrfixit1170 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    42
    pbaldy,
    Thanks again for the reply, I misunderstood your example at first, I see now that what you were showing me was the syntax in red is what I need to add to my code.
    To answer your question concerning the list boxes, List2 is on a form the user selects from a task listing. When the user selects the task a query is triggered to show (In another ListBox) the type of people it takes to completed the task and the time to complete the task for each person. If the user chooses to edit the number of people or add more time I have setup a button marked "SME Edit" to open another page(frm_SME_Update) which takes just the selected task and displays it in a listbox, (lboAVUMSMEUpdate) and with a series of combo boxes and textboxes the user can "add" time to the task. I have created a table (tbl_SME_AVUM_EDIT)to store the "SME Edits" per task using the EI_ID, Event_Date, Event_No, SYS_Code and the IETM_ID (IETM_ID happens to be the task) and the elements in the table, Record_ID(This should tie to the tbl_AVUM_Scored_Data table event for the task.),MOS_ID, Time, Comments.
    So tbl_AVUM_Scored_Data and tbl_SME_AVUM_Edit is related by the Record_ID.
    Thank you again for your help....

  6. #6
    mrfixit1170 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    42

    I think I this is still a work in progress?

    I tried to place the elements into place at your suggestion, however I am getting a "Loop without a DO" error message I am looking that up now, however I am still new at this, so the error may stick out to someone more experienced than I.
    I also added a variable to give the Do while statement an .EOF aurgument. Not sure if this is correct of even necessary but I gave it a shot. Any advice is welcomed and encouraged.
    Thanks for looking

    Code:
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rid As Long
    Dim EM As AccessObject
    
    On Error GoTo ErrorHandler
    
    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("tbl_AVUM_Scored_Data", dbOpenDynaset)
    Set rs2 = db.OpenRecordset("tbl_SME_AVUM_Edit", dbOpenDynaset)
    Set EM = frm_Acft_Score
    
        Do While Not EM.EOF
            Set ctl = Forms!frm_TaskList_Popup!List2
            For Each varItem In ctl.ItemsSelected
                With rs1
                    .AddNew
                    !EI_ID = Forms!frm_Acft_Score!EI_ID
                    !EVENT_DATE = Forms!frm_Acft_Score!EVENT_DATE
                    !EVENT_NO = Forms!frm_Acft_Score!EVENT_NO
                    !SYS_CODE = Forms!frm_Acft_Score!SYS_CODE
                    !IETM_ID = ctl.ItemData(varItem)
                    .Update
                    .Bookmark = .LastModified
                    rid = !Record_ID
                End With
            'Next varItem
            
            'Set ctl2 = Forms!frm_SME_Update!lboAVUMSMEUpdate
            'For Each var2Item In ctl2.ItemSelected
                With rs2
                    .AddNew
                    !Record_ID = rid
                    !MOS_ID = Forms!frm_SME_Update!cboSMEMOSo1
                    !Time = Forms!frm_SME_Update!txboSMETimeo1
                    !Comments = rs2!MOS_ID = Forms!frm_SME_Update!txboSMEAVUMComments
                    .Update
                End With
                EM.MoveNext
            'Next var2Item
        Loop
        
    ExitHandler:
        Set strItem = Nothing
        Set intItem = Nothing
        Set ctl = Nothing
        Set ctl2 = Nothing
        Set rs1 = Nothing
        Set rs2 = Nothing
        Set EM = Nothing
        Me.lboAVUMSMEUpdate.Requery
        Me.lboAVUMSMEUpdate = ""
        Me.cboSMEMOSo1.Requery
        Me.cboSMEMOSo1 = ""
        Me.txboSMETimeo1.Requery
        Me.txboSMETimeo1 = ""
        Me.txboSMEAVUMComments.Requery
        Me.txboSMEAVUMComments = ""
    Exit Sub

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You need a "Next" for this:

    For Each varItem In ctl.ItemsSelected

    I would expect the Do loop to use one of the recordsets. What are you trying to loop there?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    mrfixit1170 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    42
    I've just been reading about the loop's in VBA and I guess I really don't need the loop process, my user will be making a selection one at a time. If another task needs to be selected and edited then the user makes the selections. Thank you again.....I'll post what I have in a bit.

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If they're making a single selection (and the listbox is single select), you don't need the

    For Each varItem In ctl.ItemsSelected

    loops either. That code is for multi-select listboxes. For a single select listbox you can just refer to the listbox and get the selected value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    mrfixit1170 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    42

    Cool it works!

    pbaldy,
    Thank you for your help......I did remove the "For Each" statement and the reference to the EM.EOF. I figured out I really didn't need it.

    Here is the finished code

    Code:
    Dim strItems As String
    Dim intItems As Integer
    Dim varItem As Variant
    Dim var2Item As Variant
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rid As Long
    
    On Error GoTo ErrorHandler
    
    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("tbl_AVUM_Scored_Data", dbOpenDynaset)
    Set rs2 = db.OpenRecordset("tbl_SME_AVUM_Edit", dbOpenDynaset)
    
             Set ctl = Forms!frm_TaskList_Popup!List2
                With rs1
                    .AddNew
                    !EI_ID = Forms!frm_Acft_Score!EI_ID
                    !EVENT_DATE = Forms!frm_Acft_Score!EVENT_DATE
                    !EVENT_NO = Forms!frm_Acft_Score!EVENT_NO
                    !SYS_CODE = Forms!frm_Acft_Score!SYS_CODE
                    !IETM_ID = ctl.ItemData(varItem)
                    .Update
                    .Bookmark = .LastModified
                    rid = !Record_ID
                End With
                    
            Set ctl2 = Forms!Test1_frm_SME_Update!lboAVUMSMEUpdate
                With rs2
                    .AddNew
                    !Record_ID = rid
                    !MOS_ID = Forms!Test1_frm_SME_Update!cboSMEMOSo1
                    !Time = Forms!Test1_frm_SME_Update!txboSMETimeo1
                    !Comments = Forms!Test1_frm_SME_Update!txboSMEAVUMComments
                    .Update
                End With
         
    ExitHandler:
        Set strItem = Nothing
        Set intItem = Nothing
        Set ctl = Nothing
        Set ctl2 = Nothing
        Set rs1 = Nothing
        Set rs2 = Nothing
        
        Me.lboAVUMSMEUpdate.Requery
        Me.lboAVUMSMEUpdate = ""
        Me.cboSMEMOSo1.Requery
        Me.cboSMEMOSo1 = ""
        Me.txboSMETimeo1.Requery
        Me.txboSMETimeo1 = ""
        Me.txboSMEAVUMComments.Requery
        Me.txboSMEAVUMComments = ""
    Exit Sub
    
    ErrorHandler:
        Select Case Err
            Case Else
            MsgBox Err.Description
            DoCmd.Hourglass False
            Resume ExitHandler
        End Select
    
    End Sub
    Thanks again for your help.....

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help. That works? I'd be worried about:

    !IETM_ID = ctl.ItemData(varItem)

    since varItem is never set, but it could be that for a single select listbox it will just grab the selected item, and is more or less ignoring the variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    mrfixit1170 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    42
    pbaldy,
    Thanks again, I just dropped the .ItemData(varItem), still works great.....

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

Similar Threads

  1. On-Click Event Procedure
    By tbassngal in forum Forms
    Replies: 6
    Last Post: 07-20-2011, 07:06 AM
  2. Command button click event
    By R_jang in forum Programming
    Replies: 10
    Last Post: 10-29-2010, 10:13 PM
  3. On Click Event For Button On Form
    By Desstro in forum Forms
    Replies: 3
    Last Post: 08-09-2010, 02:36 PM
  4. On Click Event Procedure
    By MrDean in forum Forms
    Replies: 3
    Last Post: 10-07-2009, 07:16 AM
  5. On-Click event transfer
    By nkenney in forum Forms
    Replies: 16
    Last Post: 03-26-2009, 09:02 PM

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