Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving in Access 2000 (2000)

    I wonder if anyone can help.

    I am trying to save a record on a form before closing it. I have tried both of these lines of code:

    DoCmd.RunCommand acCmdSaveRecord

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    But I get this message:

    Run time error '2046'
    The command or action 'SaveRecord' isn't available now.

    Has anyone any ideas on what I can try next please.

    Many thanks



    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving in Access 2000 (2000)

    Access saves the current record automatically when the user closes the form, so there is no need for separate code.

  3. #3
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving in Access 2000 (2000)

    The problem I have is that I need to save it before I close it. It's for an asset management database, the database is SQL with Access front end. The window can't be closed until entries have been made to other tables which have referential integrity on the main page. For example, the page I need to save is where the main asset information is entered, before this is closed invoice details must be entered using a different form in the invoices table this means the main asset information must already be saved to the database.

    Have you any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving in Access 2000 (2000)

    I don't think I understand your description. I don't know enough about your databases (SQL Server and Access) to know why the code doesn't work.

  5. #5
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving in Access 2000 (2000)

    I have a sql database with numerous tables for an asset database.

    I have referential integrity set up so that the primary key on most tables is the asset number. Until the asset has been saved in the assets table you cannot add information to any other table e.g. Invoices, History etc.

    Users have to be able to add the invoice information before they close the main asset window, therefore for a new record the asset details must be saved to the database before a new record can be added to any other tables, but the users can't close the asset window until they've entered the information in other tables I therefore have to get the asset information saved to the database.

    Hope that helps.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving in Access 2000 (2000)

    You could put a "Save" command button on the main asset form. Say you name it cmdSave. The On Click event procedure for the button would look like this:

    Private Sub cmdSave_Click()
    RunCommand acCmdSaveRecord
    End Sub

    If clicking this button also generates the error you mentioned, something else is wrong.

  7. #7
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving in Access 2000 (2000)

    Hans

    That was the first thing I tried, see first post.

    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving in Access 2000 (2000)

    But you didn't explain where you used it. I was under the - apparently mistaken - impression that you put the code in the Form_Close or Form_Unload event.

    Before trying other code, can you test if you can you save a record using the menu option Record | Save Record or its keyboard shortcut Shift+Enter?

  9. #9
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving in Access 2000 (2000)

    Sorry I hadn't realised I hadn't said where I was saving it. I have a save button that runs a load of script, primarily to save it and then to enter changes into a history table.

    Both the record save and ctrl and enter work, how can I incorporate that into the code?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving in Access 2000 (2000)

    If the menu option works, the problem is probably in "a load of script". You should try to find out where and why the error occurs, for example by putting a breakpoint at the beginning of the On Click event procedure (click in the first line, then press F9 to toggle a breakpoint). When you click the button, the code will pause at the breakpoint. Use F8 to single step through the code.

  11. #11
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving in Access 2000 (2000)

    The code I have tried is as follows:

    'Form.Recordset.Edit
    ' Form.Recordset.Update

    ' If Me.Dirty Then
    ' DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, , A_MENU_VER20
    ' End

    ' If Me.Dirty Then
    ' DoCmd.RunCommand acCmdSaveRecord
    ' End If
    ' me.Requery

    'RefreshDatabaseWindow

    'DoCmd.RunCommand acCmdSaveRecord


    ' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    This is copied straight from the code hence why it's commented out. I have gone through the process step by step and it always falls over with the error message as shown in the first post when it goes to whichever of the save options I try so I can't actually get to save it. Is there a way of coding in the ctrl+Enter option?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving in Access 2000 (2000)

    <code>RunCommand acCmdSaveRecord</code> is the VBA equivalent of Record | Save Record and of Shift+Enter, so I don't understand why the interactive command would work and the VBA instruction wouldn't. Are you sure there is no other code involved?

  13. #13
    Star Lounger
    Join Date
    Jun 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving in Access 2000 (2000)

    I was concerned that I was missing something basic. The code that is run on this command is:

    Private Sub cmdSave_Click()
    'On Error GoTo Err_cmdSave_Click

    ' Saves the entries made and creates the appropriate history records

    Dim strSQLTelUp As String
    Dim intUpdate As Integer
    Dim objRS As DAO.Recordset
    Dim objDB As Database
    Dim strSQL As String
    Dim strHandset As String
    Dim strHandsetA As String
    Dim strMobile As String
    Dim strSQL1 As String
    Dim objRS1 As Recordset
    Dim strResponse As String
    Dim strSQL2 As String
    Dim strDocName As String
    Dim strSQLTUP As String

    Debug.Print Form.Dirty

    Set objDB = CurrentDb

    RunCommand acCmdSaveRecord

    It falls over on the accmdsaverecord above, the rest of the code runs the insert into history which obviously it can't do until the asset itself has been saved. Can you see any reason why this is happening?

    This is the error message.

    Run-time error '2046':

    The command or action 'SaveRecord' isn't available now.

    I am getting really puzzled.

    Many thanks.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Saving in Access 2000 (2000)

    Since your back-end is in SQL Server, the actual error may be masked by the ODBC driver. If you can't deduce what is causing the error (I presume the Debug.Print indicates the form is indeed dirty), you could try turning on the ODBC tracing function. That's done in the ODBC data source configuration, and you want to be sure to turn it on, run one test and then turn it off. It generates tons of data if you leave it on for more than one or a few transactions. This is just a hunch, but I suspect the SQL Server engine may be complaining that some referential integrity condition hasn't been met. One other possibility is that a trigger in the SQL Server database is gumming up the works.
    Wendell

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving in Access 2000 (2000)

    You need to test for a dirty record before saving, but put your breakpoint after the saverecord line, since that often throws an error when the focus is in the VB editor. If the record isn't dirty when you click the save button, do you need the rest of the code? Is the record on the same form as the button or in a subform?
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •