Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Updating records in main table

    Another issue evolved from the .EOF Thread. It wasn't noticed until the code worked correctly.



    The current issue is saving the control Priority and Hr to the main table. So far all I have been able to do is have it save to the first record in qRecSourceOOBChanges query, no matter the record selected.

    I have made the controls Priority and Hours bound to qRecSourceOOBChanges, and unbound.

    I have tried to save during the For Each varItem In ctl.ItemsSelected set using DoCmd.RunCommand acCmdSaveRecord and even:
    Me.Recordset.Edit
    Me.Recordset("Priority") = Prior
    Me.Recordset("Hr") = Hours
    Me.Recordset.Update

    Then I have tried
    Do While Not rs.EOF
    For Each varItem In ctl.ItemsSelected
    If rs!OOBNumber = CDbl(ctl.ItemData(varItem)) Then
    If Form_frmOOBChangeSelect.Dirty Then
    Form_frmOOBChangeSelect.Dirty = False
    DoCmd.RunCommand acCmdSaveRecord
    End If
    MsgBox CDbl(ctl.ItemData(varItem)) & vbCrLf & Priority & vbCrLf & Hr

    With the message box the Priority and Hr remain the same through each selection
    but do not show up in the Email body, except if the first record is a selected record. It also doesn't show up in the PDF if one of the selected records is not at the same "Voting" level.

    Code:
    Public Sub SelectedOOBChanges_Click()
        
        On Error GoTo Broke
    
        Set objOutlook = CreateObject("Outlook.Application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
        Set ctl = Me.SelectedOOBNumber
        
        If ctl.ItemsSelected.Count = 0 Then
            MsgBox "Nothing was selected"
        Else
               
            For Each varItem In ctl.ItemsSelected
                
                DoCmd.RunCommand acCmdSaveRecord
                StrWhere = StrWhere & "'" & ctl.ItemData(varItem) & "',"
                StrWhere2 = StrWhere2 & " " & ctl.ItemData(varItem) & ","
            
            Next varItem
                                
                StrWhere = Left(StrWhere, Len(StrWhere) - 1)
                StrWhere2 = Left(StrWhere2, Len(StrWhere2) - 1)
    
        Set rs = CurrentDb.OpenRecordset("qRecSourceOOBChanges")
                        
                If IsNull(Me.OOBNumber) Then
                    MsgBox "There are no OOB CRs or no CR was selected."
                    TempVars.RemoveAll
                    Call subCreateQuery(1)
                    DoCmd.Close acForm, "frmEmailAORB"
                    DoCmd.OpenForm "frmStart"
                Else
    
                    DoCmd.OpenReport "rptOOB", acViewReport, , "OOBNumber IN(" & StrWhere & ")"
    
                    StrHdrMail = "This is a follow-on action from the AORB/CCB/TEWG discussion on CR(S)" & StrWhere2 & ". If needed, please back-brief your higher for SA, " _
                                 & "and let us know if there are any issues or concerns. The Change Request priority is " & Priority & " with " & Hr & " hours until " _
                                 & "CR(S)" & StrWhere2 & " is automatically approved (GO OOB Excepted). Please provide your votes NLT " & DTG & "." & vbCrLf & vbCrLf
    
        If rs.BOF And rs.EOF Then
                rs.Close
            Else
                rs.MoveLast
                rs.MoveFirst
            
                Do While Not rs.EOF
                 For Each varItem In ctl.ItemsSelected
                        If rs!OOBNumber = CDbl(ctl.ItemData(varItem)) Then
                            'If Form_frmOOBChangeSelect.Dirty Then
                            '     Form_frmOOBChangeSelect.Dirty = False
                                         
    
                         'End If
                       ' MsgBox CDbl(ctl.ItemData(varItem)) & vbCrLf & Priority & vbCrLf & Hr
                        
                        
                    strBdyMail = strBdyMail & "Date Issue Identified:" & Chr(9) & Chr(9) & rs!Dates & Chr(9) & Chr(9) & "Days Open:" & Chr(9) & rs!DaysOpen & vbCrLf _
                                 & "Priority: " & Chr(9) & Chr(9) & Chr(9) & rs!Priority & vbCrLf _
                                 & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & rs!OOBNumber & vbCrLf & vbCrLf _
                                 & "AO Recommendation: " & Chr(9) & Chr(9) & rs!AOVote & vbCrLf _
                                 & "O6 Recommendation: " & Chr(9) & Chr(9) & rs!O6Vote & vbCrLf & vbCrLf _
                                 & "Change Requested: " & Chr(9) & Chr(9) & rs![ChangeRequested] & vbCrLf & vbCrLf _
                                 & "Unit & Section: " & Chr(9) & rs!Unitss & vbCrLf _
                                 & "MTOE Para & Bumper: " & Chr(9) & Chr(9) & rs![MTOEParass] & vbCrLf & vbCrLf _
                                 & "Rationale: " & rs!Rationale & vbCrLf & vbCrLf _
                                 & "Notes: " & rs!Notes & vbCrLf _
                                 & "Action Items: " & rs!ActionItems & vbCrLf _
                                 & "__________________________________________________________________________" & vbCrLf & vbCrLf
                         End If
                         
                    Next
                    rs.MoveNext
                Loop
                    
                    With objOutlookMsg
                        .Subject = NIE & " - " & Label & " " & StrWhere2 & " - " & Tod
                        .Body = StrHdrMail & strBdyMail & SigBlock
                        DoCmd.OutputTo 3, "rptOOB", acFormatPDF, "C:\Temp\" & NIE & " - " & Label & " " & StrWhere2 & " - " & Tod & ".pdf", , 0
                        .Attachments.Add ("C:\Temp\" & NIE & " - " & Label & " " & StrWhere2 & " - " & Tod & ".pdf")
                        .To = ""
                        .Display
                        Kill "C:\Temp\" & NIE & " - " & Label & " " & StrWhere2 & " - " & Tod & ".pdf"
    
                        DoCmd.Close acForm, "frmOOBChangeSelect"
                        DoCmd.Close acReport, "rptOOB"
                        DoCmd.OpenForm "frmStart"
                    End With
    
                End If   'IsNull(Me.OOBNumber)
            End If           'ctl.ItemsSelected.Count = 0
        End If ' rs.movefirst & last
    
    Broke_Exit:
        On Error Resume Next
    
        Set ctl = Nothing
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
        Set objOutlookAttach = Nothing
        Set rs = Nothing
        Exit Sub
    
    Broke:
        If Err.Number = "287" Then
            MsgBox "You selected No to the Outlook security warning. Rerun the procedure and click Yes to access e-mail addresses to send your message."
        Else
            MsgBox Err.Number & " " & Err.Description
        End If
    
        Resume Broke_Exit:
    
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So far all I have been able to do is have it save to the first record in qRecSourceOOBChanges query, no matter the record selected.
    I'm not exactly sure what you're attempting to do, but I'm pretty sure that there's never a move to a new record, or next, or previous, so
    DoCmd.RunCommand acCmdSaveRecord will always save the same record during that loop. What I think I read is you looping through a listbox - these are not records.
    Also,
    - unless you need the record count, it's pointless to move last then first (I don't see you using the count in any way)
    - Do While Not rs.EOF: this loop will not execute if there are no records, so my personal approach is to wrap everything inside of it that I want to have happen and after that block of code, do something else if it's empty (like message box and exit or whatever is appropriate)
    - chances are, frmStart is already open...
    DoCmd.Close acReport, "rptOOB"
    DoCmd.OpenForm "frmStart"

    You will raise errors if you try to close an object that was not opened due to the path that code execution takes.
    Last edited by Micron; 09-16-2016 at 07:07 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Code:
     If rs.BOF And rs.EOF Then
                rs.Close
            Else
                rs.MoveLast
                rs.MoveFirst
    Is for Errors from what ssanfu informed me. I don't use the count but use it to move through the records. frmStart is closed.

    What my intention is; If I select a record(s) and the status of Priority and HR of each record should reflect that and update in the main table also.

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    No takers?

  5. #5
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    I'll try!
    Give me a second to read through and understand what you're asking as well as update myself on the previous .EOF thread.
    I think I know what the question is, and if so, I have a solution, but I just want to make sure.
    Also, if you have pictures of what you're doing / trying to do, that would make me happy. Haha.

  6. #6
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Okay. This is probably unorthodox and I'll admit that straight up. But tried and tested, it should maybe work.

    I have tried to save during the For Each varItem In ctl.ItemsSelected set using DoCmd.RunCommand acCmdSaveRecord and even:
    Me.Recordset.Edit
    Me.Recordset("Priority") = Prior
    Me.Recordset("Hr") = Hours
    Me.Recordset.Update
    I'm not entirely sure how you are changing things.
    But what if you just manually updated the table with the information you have for each record.
    Something like
    Code:
    Dim db As Database
    Dim strUpdate As String
    
    Set db = CurrentDb()
    
    strUpdate = "update [tblMainTable] set [Priority] = '" & Prior & '" where [WhateverTheFieldNameIsForTheKeyYouUse] = " & Key
    
    db.Execute strUpdate
    
    db.Close
    This is assuming priority is a string cause I don't really know what it is.

    And just loop through that and do it for each one that needs to be updated...

    There's probably definitely a better way to do it, but this is a way that should work.

  7. #7
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Although looking at it again, it seems like your problem is you're just not looping through the records...
    The problem isn't being able to save/update the records...
    Back to the drawing boards I guess.

  8. #8
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Okay, round two:


    For Each varItem In ctl.ItemsSelected

    DoCmd.RunCommand acCmdSaveRecord
    StrWhere = StrWhere & "'" & ctl.ItemData(varItem) & "',"
    StrWhere2 = StrWhere2 & " " & ctl.ItemData(varItem) & ","
    Next varItem
    If I understand this correct, what you're doing is looping through the items in your list box and trying to save the record.

    So I think what it's doing is looping through the listbox stuff and just saving your first record.

    I believe what you also need to do is loop through the records as well, get to the record that matches the updates from the listbox and then update/save the record.

    Let me know if I'm just like, super far off here..

  9. #9
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Tried it. Doesn't work. It looks like ctl as a control is always null. Even when I did:

    Code:
     Me.Recordset.Edit
                Me.Recordset("Priority") = Prty
                Me.Recordset("Hr") = Hrs
                Me.Recordset.Update
                DoCmd.RunCommand acCmdSaveRecord
                StrWhere = StrWhere & "'" & ctl.ItemData(varItem) & "',"
                StrWhere2 = StrWhere2 & " " & ctl.ItemData(varItem) & ","
    If I select all 3 items available

    CRID = 517, 518, 576
    SelectedOOB - 150, 151, 171
    VarItems as run through = 0,1,2

    MSGBox displays for 150
    517

    0
    150,
    MSGBox displays for 151
    517

    1
    150, 151,

    MSGBox displays for 171
    517

    2
    150, 151, 171,

    So it is counting the items
    It is not incrementing the CRID for each selected SelectedOOB
    ctl is always null
    strwhere does reflect the selected items as varitems counts up.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    from #8:
    If I understand this correct, what you're doing is looping through the items in your list box and trying to save the record.
    Wish I had said that - oh wait, I did already. Maybe it was #2?
    I'm not exactly sure what you're attempting to do, but I'm pretty sure that there's never a move to a new record, or next, or previous, so
    DoCmd.RunCommand acCmdSaveRecord will always save the same record during that loop. What I think I read is you looping through a listbox - these are not records.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Quote Originally Posted by Micron View Post
    from #8:
    Wish I had said that - oh wait, I did already. Maybe it was #2?
    It saves the first record. ?? What is the corrective action?

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I really don't know what the CA is. If you go back and read through the answers you've received I think you should realize no on really knows what you're doing. All we seem to know is that you're looping through listbox items and then attempting to save a different record. I for one cannot see anything in your code that I can relate the listbox item to any sort of record or sql statement that would create one, nor do I see a movement to a new/next record. Burt1215 has stated this twice. Of course it will save whatever record you're on with your FIRST use of DoCmd.RunCommand acCmdSaveRecord, but as I and others have been saying, there is no movement to a new record. Nor is there anything that will use the listbox item as any part of a new record (e.g. there is no call to an append or update query; there is no execution of such a sql statement either). It's like a turntable stylus (phonograph needle) stuck in a record - you're just hearing (saving) the same thing over and over again as many times as the count of listbox items. I hope that analogy makes it clear for you (and doesn't date me too much).

    Maybe you should post a zipped copy of your db with clear instructions as to what one is supposed to do and what is supposed to happen when a user does that. If there's no incompatible features in your db I should be able to open it with my 2007 version.

  13. #13
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ok, Gotcha Micron. Attached is a slimmed down version.

    Thompyta.zip

    I select the "CCB OOB" button and it opens a common form, but with specific data pertinent to the CCB Level CR Number. If I select all I get the following for an output to the email body in text. in red is the area of concern:

    Code:
    This is a follow-on action from the AORB/CCB/TEWG discussion on CR(S) 150, 151, 171.01. If needed, please back-brief your higher for SA, and let us know if there are any issues or concerns. The Change Request priority is Medium with 48 hours until CR(S) 150, 151, 171.01 is automatically approved (GO OOB Excepted). Please provide your votes NLT 1131 Saturday, Sep 24 2016.
     
    Date Issue Identified:                    16 August 2016                  Days Open:         21
    Priority:                                       Medium
    CR Number:                                  150
     
    AO Recommendation:                      Approve
    O6 Recommendation:                   
     
    Change Requested:                        Late Add PSYOP Media Production Center (MPC) for AWA 17.1 in support of 17 CA
     
    Unit & Section:                              17 CA                     TBD
    MTOE Para & Bumper:                     TBD                        None
     
    Rationale: 
     
    Notes: 
    Action Items: Awaiting Technical Diagrams
    __________________________________________________________________________
     
    Date Issue Identified:                    16 August 2016                  Days Open:         21
    Priority:                                       Priority is missing        
    CR Number:                                  151
     
    AO Recommendation:                     Approve
    O6 Recommendation:                   
     
    Change Requested:                        Late Add - Fly Away Broadcast System Version 2 (FABS V2) to AWA 17.1 in support of 17 CA
     
    Unit & Section:                              17 CA                     TBD
    MTOE Para & Bumper:                     TBD                        None
     
    Rationale: 
     
    Notes: 
    Action Items: Awaiting Technical Diagrams
    __________________________________________________________________________
     
    Date Issue Identified:                    01 September 2016                         Days Open:         5
    Priority:                                      Priority is missing         
    CR Number:                                 171.01
     
    AO Recommendation:                   Defer
    O6 Recommendation:                   
     
    Change Requested:                      Move Kairos - RIRAK  Paragraph to A/4-27 FA
     
    Unit & Section:                            A/47 BSB A/4-27FA   Kairos
    MTOE Para & Bumper:                   0211                       K1 K2 K3
     
    Rationale: Coordinated move with KAIROS? Unit/G3/BMID/TED
     
    Notes:
    Action Items:
    __________________________________________________________________________

    I am only getting 150 to save to HR ans Priority fields in tblChangeRequest. Micron explained the saveCmd in the previous post. I am looking to save each selected record with the Priority and HR fields in the table via the frmOOBChange Select. I need to update the table records that have been selected only.
    See below graphic:
    Attached Thumbnails Attached Thumbnails tblChangeRequest.PNG  

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Got it to open and compile OK but I had to reset a couple of references to my older versions. I'll need some time to go over what you have and see if I can translate it to what you want but I'm running out of time for today.

    The first thing I noticed is that your CCB EMail form has a record source assigned to it, but not even one control on that form is bound to it. There's what might be the first fundamental problem, but it depends on whether or not a bound control is needed to accomplish the task.

    Question: if this is true
    Code:
     If ctl.ItemsSelected.Count = 0 Then
        MsgBox "Nothing was selected"
    why are you proceeding? Shouldn't there be an exit from the procedure immediately after the message box? Why proceed if the process depends on writing the chosen priority to the table (what I think is the goal) if no CR number has been chosen? If you agree, nothing you have should come before this decision block (i.e. no objects should be set). I may have other questions later, but try to clear this up in the meantime.
    Last edited by Micron; 09-22-2016 at 03:10 PM. Reason: clarification

  15. #15
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I tried it without the If then Else statement. If you don't select something and then click the select button you will get an invalid procedure.

    Thanks for digging into it for me.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Updating records based on another table
    By bondalisha in forum Access
    Replies: 2
    Last Post: 11-19-2015, 12:45 PM
  2. Replies: 3
    Last Post: 10-08-2015, 01:02 PM
  3. updating records to another table.
    By sankar519 in forum Access
    Replies: 1
    Last Post: 06-30-2014, 08:31 AM
  4. Updating main table from temp table AND form value
    By shabbaranks in forum Programming
    Replies: 8
    Last Post: 05-01-2013, 07:18 AM
  5. Replies: 3
    Last Post: 11-04-2012, 09:25 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