Saving in Access 2000 (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Hans

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

Thanks

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

RunCommand acCmdSaveRecord 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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

Charlotte you are a genius. As soon as I took out the breakpoint it ran fine.

I am so sorry I hadn’t tried that before and many thanks for all your help.

Reply | Quote

Charlotte you are a genius. As soon as I took out the breakpoint it ran fine.

I am so sorry I hadn’t tried that before and many thanks for all your help.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

RunCommand acCmdSaveRecord 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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

Hans

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

Thanks

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote