Results 1 to 7 of 7
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Can't save record

    I am trying to update the record in tblChangeRequest where controls Priority and HR have changed. I get the following MsgBox:



    Error No. 2046
    Description: The command or action 'SaveRecord' isn't available now.


    On the form frmEmailAORB I have 3 unbound Comboboxs

    Form Record Source:

    Code:
    SELECT DISTINCT [CRNo]+([SubNo]*0.01) AS AONumbers, qrySwitching.UNITS, qrySwitching.MTOEParas, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, Format([DateID],"dddd"", ""mmm d yyyy") AS Dates, tblChangeRequest.Priority, tblChangeRequest.Hr, tblChangeRequest.AOVote, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS DTG
    FROM tblChangeRequest INNER JOIN qrySwitching ON tblChangeRequest.CRID = qrySwitching.CRID
    WHERE (((tblChangeRequest.ActionComplete)=False) AND ((tblChangeRequest.CRNo)<>0))
    GROUP BY [CRNo]+([SubNo]*0.01), qrySwitching.UNITS, qrySwitching.MTOEParas, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, Format([DateID],"dddd"", ""mmm d yyyy"), tblChangeRequest.Priority, tblChangeRequest.Hr, tblChangeRequest.AOVote, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy"), tblChangeRequest.o6Vote
    HAVING (((tblChangeRequest.ChangeRequested)<>"Do not delete") AND ((tblChangeRequest.o6Vote) Is Null))
    ORDER BY [CRNo]+([SubNo]*0.01);
    Unbound Combobox cbxNumber derived from:

    Code:
    SELECT [CRNo]+([SubNo]*0.01) AS CRNumbers
    FROM TblChangeRequest
    WHERE (((TblChangeRequest.O6Vote) Is Null))
    GROUP BY [CRNo]+([SubNo]*0.01), TblChangeRequest.ActionComplete
    HAVING (((TblChangeRequest.ActionComplete)=False));
    Unbound Combobox Priority derived from:

    Row Source: "Flash";"High";"Medium";"Low"
    Row Source Type: Value List
    Bound Column: 1

    Unbound Combobox Hours derived from:

    Row Source: "4";"8";"12";"24";"48";"36";"72"
    Row Source Type: Value List
    Bound Column: 1

    Command Button Cancel: CancelAOOOB
    Command Button Send: SendAOOOB

    VBA:
    Code:
    Option Explicit
    
    Private Sub CancelAOOOB_Click()
          DoCmd.Close acForm, "frmEmailAORB"
          DoCmd.OpenForm "frmStart"
        End Sub
    
    Private Sub cbxNumber_AfterUpdate()
        Me.Filter = "AONumbers=" & Me.cbxNumber
        Me.FilterOn = True
    End Sub
    
    Private Sub SendAOOOB_Click()
    On Error GoTo error
      
      Dim strSubject As String, strBody As String
    
    If IsNull(AONumbers) Then
        
         GoTo Closure
            Exit Sub
         Else
    
    With Me
    
        If Not IsNull(.cbxNumber) And Not IsNull(.Priority) And Not IsNull(.Hours) Then
            strSubject = !Priority & " OOB AO Change Request Number " & !AONumbers & " - " & ![ChangeRequested] & " - " & Tod
            strBody = "Action Officers," & vbCrLf & "This is a follow-on from the AORB/TEWG discussion on CR " _
            & !AONumbers & ".  If needed, please back-brief your O6 for SA, and let me know if there are any issues or concerns. " _
            & "The Change Request priority is " & !Priority & " with " & !Hr & " hours until CR is automatically approved.  " _
            & "Please provide your votes NLT " & !DTG & ". Please call if you have any questions or issues." & vbCrLf & vbCrLf & vbCrLf _
            & "Date Issue Identified: " & Chr(9) & Chr(9) & Chr(9) & !Dates & vbCrLf & vbCrLf & "Priority: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) _
            & !Priority & vbCrLf & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & !AONumbers & vbCrLf & "AO Recommendation: " & Chr(9) _
            & Chr(9) & Chr(9) & !AOVote & vbCrLf & vbCrLf & "Change Requested: " & Chr(9) & ![ChangeRequested] & vbCrLf & vbCrLf _
            & "Unit & Section: " & Chr(9) & Chr(9) & Chr(9) & !UNITS & vbCrLf & "MTOE Para & Bumper Number: " & Chr(9) & ![MTOEParas] & vbCrLf & vbCrLf _
            & "Rationale: " & Chr(9) & Chr(9) & !Rationale & vbCrLf & vbCrLf & "Notes: " & Chr(9) & Chr(9) & Chr(9) & !NOTES & vbCrLf _
            & "Action Items: " & Chr(9) & Chr(9) & !ActionItems & SigBlock
                   
    DoCmd.RunCommand acCmdSaveRecord
                    DoCmd.SendObject acSendNoObject, , acFormatTXT, , , , strSubject, strBody, True
    
        End If
    End With
    
    Closure:
    DoCmd.Close acForm, "frmEmailAORB"
    DoCmd.OpenForm "frmStart"
    
    Exit Sub
    Error_Handler_Exit:
    Exit Sub
    error:
    Select Case Err.Number
        Case 2501
        Err.Clear
    Resume Error_Handler_Exit
        Case Else
        MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
        Err.Clear
    Resume Error_Handler_Exit
    End Select
    End If
    End Sub
    I used to have cbxNumber as the only unbound ComboBox, with Priority row source connected to tblChangeRequest.Priority and Hr to tblChangeRequest.HR with the same Row Source and Row Source Type as above. It would work then.

    Currently the only way to get the default value to show in the combobox was to unbind the field. Then I can get to the emailing portion of the VBA code. otherwise I couldn't select the value in the combobox. cbxnumber works fine.

    I ran into this

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You could try

    If me.Dirty then me.Dirty = False

    instead of the runcommand.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    John_G,
    That has the same effect as when I comment out the doCmd 'DoCmd.SaveRecord. It still doesn't save Priority and HR values to the table

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You said (I think) that the Priority and HR combo boxes were now unbound. If so, that's why they wont save the values to the table.

    I see that your form is bound to a query which has tblchangeRequest as a component. That query contains a Group By clause, which probably makes it not updatable, so any changes you make on the form cannot be saved to the underlying table(s).

    That would explain why the Runcommand acSaveRecord could not be used.

    So, I think the problems you are experiencing are due to the query, and not so much the combo boxes. However, the combo boxes might not have been working properly either, because they had one of the tables in the query as their row source.

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    All I did was change some naming conventions: Below is the original

    Original Email_AORB Form
    Code:
    SELECT [CR_No]+([Sub_No]*0.01) AS CR_Numbers, Chr(9) & Nz([UNIT],"") & Chr(9) & Nz([Section],"") AS UNITS, Nz([MTOE_Para]," ") & Chr(9) & Nz([Bumper_Number],"") AS [MTOE Paras], [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].AO_Vote, [Change Request].O6_Vote, Format([Date_ID],"dddd"", ""mmm d yyyy") AS Dates, [Change Request].Priority, [Change Request].Hr, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS [Time], Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS DTG
    FROM [Change Request]
    GROUP BY [CR_No]+([Sub_No]*0.01), Chr(9) & Nz([UNIT],"") & Chr(9) & Nz([Section],""), Nz([MTOE_Para]," ") & Chr(9) & Nz([Bumper_Number],""), [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].AO_Vote, [Change Request].O6_Vote, Format([Date_ID],"dddd"", ""mmm d yyyy"), [Change Request].Priority, [Change Request].Hr, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy"), Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy"), [Change Request].Action_Complete
    HAVING ((([Change Request].[Change Requested])<>"Do not delete") AND (([Change Request].O6_Vote) Is Null) AND (([Change Request].Action_Complete)=False));
    Unbound Data Tab:
    Code:
    SELECT [CR_No]+([Sub_No]*0.01) AS CR_Numbers, Chr(9) & Nz([UNIT],"") & Chr(9) & Nz([Section],"") AS UNITS, Nz([MTOE_Para]," ") & Chr(9) & Nz([Bumper_Number],"") AS [MTOE Paras], [Change Request].[Change Requested], [Change Request].Rationale, [Change Request].NOTES, [Change Request].Action_Items, [Change Request].AO_Vote, [Change Request].O6_Vote, Format([Date_ID],"dddd"", ""mmm d yyyy") AS Dates, [Change Request].Priority, [Change Request].Hr, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS [Time], [Change Request].Unit, [Change Request].MTOE_Para, [Change Request].Bumper_Number, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS DTG
    FROM [Change Request]
    WHERE ((([Change Request].[Change Requested])<>"Do not delete")  AND (([Change Request].O6_Vote) Is Null) AND (([Change  Request].Action_Complete)=False));
    Original VBA
    Code:
    Private Sub Cmnd_Cancel_Click()
          DoCmd.Close acForm, "Email_AORB"
          DoCmd.OpenForm "Start"
        End Sub
    Private Sub cbxNumber_AfterUpdate()
        Me.Filter = "CR_Numbers=" & Me.cbxNumber
        Me.FilterOn = True
    End Sub
    Private Sub Form_Load()
    If IsNull(CR_Numbers) Then
        MsgBox "There are no OOB CRs to send"
        DoCmd.Close acForm, "Email_AORB"
        DoCmd.OpenForm "Start"
        End If
    End Sub
    Private Sub Send_AORB_OOB_Click()
    On Error GoTo error
      Dim strSubject As String
      Dim strBody As String
      Dim strAddresses As String
        DoCmd.RunCommand acCmdSaveRecord
     
    With Me
    If Not IsNull(.cbxNumber) And Not IsNull(.Priority) And Not IsNull(.Hours) Then
        strSubject = !Priority & " OOB AO Change Request Number " & !CR_Numbers & " - " & ![Change Requested] & " - " & Tod
        strBody = "Action Officers," & vbCrLf & "This is a follow-on from today's AORB/ERB discussion on CR " _
            & !CR_Numbers & ".  If needed, please back-brief your O6 for SA, and let me know if there are any issues or concerns. " _
            & "The Change Request priority is " & !Priority & " with " & !Hr & " hours until CR is automatically approved.  " _
            & "Please provide your votes NLT " & !DTG & ". Please call if you have any questions or issues." & vbCrLf & vbCrLf & vbCrLf _
            & "Date Issue Identified: " & Chr(9) & Chr(9) & Chr(9) & !Dates & vbCrLf & vbCrLf & "Priority: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) _
            & !Priority & vbCrLf & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & !CR_Numbers & vbCrLf & "AO Recommendation: " & Chr(9) _
            & Chr(9) & Chr(9) & !AO_Vote & vbCrLf & vbCrLf & "Change Requested: " & Chr(9) & ![Change Requested] & vbCrLf & vbCrLf _
            & "Unit & Section: " & Chr(9) & Chr(9) & Chr(9) & !UNITS & vbCrLf & "MTOE Para & Bumper Number: " & Chr(9) & ![MTOE Paras] & vbCrLf & vbCrLf _
            & "Rationale: " & Chr(9) & Chr(9) & !Rationale & vbCrLf & vbCrLf & "Notes: " & Chr(9) & Chr(9) & Chr(9) & !NOTES & vbCrLf _
            & "Action Items: " & Chr(9) & Chr(9) & !Action_Items & SigBlock
    DoCmd.Close acForm, "Email_AORB"
    DoCmd.OpenForm "Start"
    DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
    End If
    End With
     
    Exit Sub
    Error_Handler_Exit:
    Exit Sub
    error:
    Select Case Err.Number
        Case 2501
        Err.Clear
    Resume Error_Handler_Exit
        Case Else
        MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
        Err.Clear
    Resume Error_Handler_Exit
    End Select
    Exit Sub
    End Sub
    In this Priority and HR are bound Control boxes and will save when it runs.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    likely the recordset is not updatable for one of the many typical reasons listed here
    http://www.fmsinc.com/Microsoftacces...ble/index.html

    Maybe you haven't changed sources, indexes or linked fields, but you are now assigning empty strings for null values. Are you certain the target table(s) can accept empty strings in all of the fields where you might be updating to empty strings?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Looks like it was qrySwitching that locked the records. I took it out and it seems to work fine.

    Code:
    SELECT [CRNo]+([SubNo]*0.01) AS AONumbers, TblChangeRequest.ChangeRequested, TblChangeRequest.Rationale, TblChangeRequest.NOTES, TblChangeRequest.ActionItems, TblChangeRequest.AOVote, TblChangeRequest.O6Vote, Format([DateID],"dddd"", ""mmm d yyyy") AS Dates, TblChangeRequest.Priority, TblChangeRequest.Hr, Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS [Time], Format(Now()+([Hr]/24),"hhnn dddd"", ""mmm d yyyy") AS DTG, [Unit] & Chr(13) & Chr(10) & [Section] AS Units, [HBVersion] & Chr(13) & Chr(10) & [ApproxPage] AS HBVers, [MTOEPara] & Chr(13) & Chr(10) & [BumperNum] AS MTOEParas, [Requestor] & Chr(13) & Chr(10) & [Sponsor] AS People
    FROM TblChangeRequest
    WHERE (((TblChangeRequest.ChangeRequested)<>"Do not delete") AND ((TblChangeRequest.O6Vote) Is Null) AND ((TblChangeRequest.ActionComplete)=False));

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

Similar Threads

  1. Replies: 3
    Last Post: 09-12-2014, 08:15 PM
  2. Replies: 3
    Last Post: 10-08-2012, 08:40 AM
  3. Replies: 8
    Last Post: 09-27-2012, 11:12 AM
  4. save record
    By ashu.doc in forum Forms
    Replies: 4
    Last Post: 08-13-2012, 08:13 PM
  5. Replies: 4
    Last Post: 05-08-2012, 08:07 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