Selecting No on Sys. Mgmt. window

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Add Record to Table button on my form that adds the user's input
from the form to a table. When I click on this button, it produces a Systems
Management window with this message:

"You are about to append 1 row(s). Once you click Yes, you can't use the
Undo command to reservse the changes. Are you sure you want to append the
selected rows?"

I'm not sure what part of my code produces this window. If I click on Yes,
everything is hunky dorey and the record gets added to the table. But if I
click on No, I get a Run-time error '2501': The RunSQL action was canceled.
Here's the On Click code for this button:

Private Sub Add_Button_Click()
'Inserts a record into table
InsertProcess
End Sub

Private Sub InsertProcess()
'Query that inserts the process record
Dim strSQL As String

strSQL = "INSERT INTO T000_Main (Process_ID_Num, Process_Name,
Process_Owner, Process_Type, Program_Type, Description, Program_Names,
Last_Updated, Process_Keyword, Directory, Frequency, DateCreated) VALUES ('"
& Me.Process_ID_Num_Text_Box & "', '" & Me.Process_Name_Text_Box & "', '" &
Me.Process_Owner_Combo_Box & "', '" & Me.Process_Type_Combo_Box & "', '" &
Me.Program_Type_Combo_Box & "', '" & Me.Description_Text_Box & "', '" &
Me.Program_Names_Text_Box & "', #" & Format(Me.Calendar7, "short date") & "#,
'" & Me.Process_Keyword_Text_Box & "', """ & Me.Directory_Text_Box & """, '"
& Me.Frequency_Combo_Box & "', #" & Now & "#)"

DoCmd.RunSQL strSQL

End Sub

I think I might need to have "On Error GoTo" code but I don't know what that
code should be. If the user selects No, I'd like the application's behavior
to close the System Management window and bring the user back to the same
form with the same data they had input.

Thanks for your help in advance!
 
The DoCmd.RunSQL strSQL statement is what's generating the message.

If you really want to give the user the option of stopping the insertion,
try changing your InsertProcess sub to:

Private Sub InsertProcess()
On Error GoTo Err_InsertProcess
'Query that inserts the process record
Dim strSQL As String

strSQL = "INSERT INTO T000_Main (Process_ID_Num, Process_Name,
Process_Owner, Process_Type, Program_Type, Description, Program_Names,
Last_Updated, Process_Keyword, Directory, Frequency, DateCreated) VALUES ('"
& Me.Process_ID_Num_Text_Box & "', '" & Me.Process_Name_Text_Box & "', '" &
Me.Process_Owner_Combo_Box & "', '" & Me.Process_Type_Combo_Box & "', '" &
Me.Program_Type_Combo_Box & "', '" & Me.Description_Text_Box & "', '" &
Me.Program_Names_Text_Box & "', #" & Format(Me.Calendar7, "short date") &
"#,
'" & Me.Process_Keyword_Text_Box & "', """ & Me.Directory_Text_Box & """, '"
& Me.Frequency_Combo_Box & "', #" & Now & "#)"

DoCmd.RunSQL strSQL

End_InsertProcess:
Exit Sub

Err_InsertProcess:
Select Case Err.Number
Case 2501 ' "The RunSQL action was cancelled."
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_InsertProcess
End Select

End Sub

If you don't want them have the choice, change it to:

Private Sub InsertProcess()
'Query that inserts the process record
Dim strSQL As String

strSQL = "INSERT INTO T000_Main (Process_ID_Num, Process_Name,
Process_Owner, Process_Type, Program_Type, Description, Program_Names,
Last_Updated, Process_Keyword, Directory, Frequency, DateCreated) VALUES ('"
& Me.Process_ID_Num_Text_Box & "', '" & Me.Process_Name_Text_Box & "', '" &
Me.Process_Owner_Combo_Box & "', '" & Me.Process_Type_Combo_Box & "', '" &
Me.Program_Type_Combo_Box & "', '" & Me.Description_Text_Box & "', '" &
Me.Program_Names_Text_Box & "', #" & Format(Me.Calendar7, "short date") &
"#,
'" & Me.Process_Keyword_Text_Box & "', """ & Me.Directory_Text_Box & """, '"
& Me.Frequency_Combo_Box & "', #" & Now & "#)"

CurrentDb.Execute strSQL, dbFailOnError

End Sub
 
Thanks, Doug! Works perfect!
--
Sue
Programmer/Data Analyst
Minnesota


Douglas J. Steele said:
The DoCmd.RunSQL strSQL statement is what's generating the message.

If you really want to give the user the option of stopping the insertion,
try changing your InsertProcess sub to:

Private Sub InsertProcess()
On Error GoTo Err_InsertProcess
'Query that inserts the process record
Dim strSQL As String

strSQL = "INSERT INTO T000_Main (Process_ID_Num, Process_Name,
Process_Owner, Process_Type, Program_Type, Description, Program_Names,
Last_Updated, Process_Keyword, Directory, Frequency, DateCreated) VALUES ('"
& Me.Process_ID_Num_Text_Box & "', '" & Me.Process_Name_Text_Box & "', '" &
Me.Process_Owner_Combo_Box & "', '" & Me.Process_Type_Combo_Box & "', '" &
Me.Program_Type_Combo_Box & "', '" & Me.Description_Text_Box & "', '" &
Me.Program_Names_Text_Box & "', #" & Format(Me.Calendar7, "short date") &
"#,
'" & Me.Process_Keyword_Text_Box & "', """ & Me.Directory_Text_Box & """, '"
& Me.Frequency_Combo_Box & "', #" & Now & "#)"

DoCmd.RunSQL strSQL

End_InsertProcess:
Exit Sub

Err_InsertProcess:
Select Case Err.Number
Case 2501 ' "The RunSQL action was cancelled."
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_InsertProcess
End Select

End Sub

If you don't want them have the choice, change it to:

Private Sub InsertProcess()
'Query that inserts the process record
Dim strSQL As String

strSQL = "INSERT INTO T000_Main (Process_ID_Num, Process_Name,
Process_Owner, Process_Type, Program_Type, Description, Program_Names,
Last_Updated, Process_Keyword, Directory, Frequency, DateCreated) VALUES ('"
& Me.Process_ID_Num_Text_Box & "', '" & Me.Process_Name_Text_Box & "', '" &
Me.Process_Owner_Combo_Box & "', '" & Me.Process_Type_Combo_Box & "', '" &
Me.Program_Type_Combo_Box & "', '" & Me.Description_Text_Box & "', '" &
Me.Program_Names_Text_Box & "', #" & Format(Me.Calendar7, "short date") &
"#,
'" & Me.Process_Keyword_Text_Box & "', """ & Me.Directory_Text_Box & """, '"
& Me.Frequency_Combo_Box & "', #" & Now & "#)"

CurrentDb.Execute strSQL, dbFailOnError

End Sub
Click to expand...
 
Back
Top