Can I eliminate the Yes/No Append message box?

[magmike]

I have a button that adds a new record to a table (see code below). Of
course, before doing it, a message box pops up telling me I am about
to append 1 row(s) with a yes no button.

Is there a way to code the button, so that the Yes/No message box does
not happen or to automatically send Yes to that function?

Thanks in advance!

the button code:
Private Sub AddStatus_Click()
Dim stSQL As String
Dim stControl As String
stSQL = "INSERT into Status (StatusCompany, StatusCode) values ([ID],
[StatusCodeTemp])"
stControl = "StatusCodeTemp"
DoCmd.RunSQL stSQL
DoCmd.GoToControl stControl
DoCmd.RunCommand acCmdRefresh
End Sub

magmike

 

[Douglas J. Steele]

The best way is

Private Sub AddStatus_Click()
Dim stSQL As String
Dim stControl As String

stSQL = "INSERT into Status (StatusCompany, StatusCode) values ([ID],
[StatusCodeTemp])"
stControl = "StatusCodeTemp"

CurrentDb.Execute stSQL, dbFailOnError
Me.Controls(stControl).SetFocus
DoCmd.RunCommand acCmdRefresh

End Sub

 

[magmike]

The best way is

Private Sub AddStatus_Click()
Dim stSQL As String
Dim stControl As String

  stSQL = "INSERT into Status (StatusCompany, StatusCode) values ([ID],
[StatusCodeTemp])"
  stControl = "StatusCodeTemp"

  CurrentDb.Execute stSQL, dbFailOnError
  Me.Controls(stControl).SetFocus
  DoCmd.RunCommand acCmdRefresh

End Sub

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

I have a button that adds a new record to a table (see code below). Of
course, before doing it, a message box pops up telling me I am about
to append 1 row(s) with a yes no button.

Is there a way to code the button, so that the Yes/No message box does
not happen or to automatically send Yes to that function?

Thanks in advance!

the button code:
Private Sub AddStatus_Click()
Dim stSQL As String
Dim stControl As String
stSQL = "INSERT into Status (StatusCompany, StatusCode) values ([ID],
[StatusCodeTemp])"
stControl = "StatusCodeTemp"
DoCmd.RunSQL stSQL
DoCmd.GoToControl stControl
DoCmd.RunCommand acCmdRefresh
End Sub

magmike- Hide quoted text -

- Show quoted text -

When I used the new code, I got the following error:

Run-time error '3061':

Too few perameters. Expected 2.

When I click on debug, the CurrentDb line is highlighted.

What are we missing?

 

[Douglas J. Steele]

Sorry, I didn't look closely enough at your SQL.

What are [ID] and [StatusCodeTemp]?

To get their values (rather than their names) into the SQL statement, you
need something like:

stSQL = "INSERT into Status (StatusCompany, StatusCode) " & _
"Values (" & [ID] & ", '" & [StatusCodeTemp] & "')"

There, I'm assuming StatusCompany is a numeric field, and StatusCode is a
text field. Check the quotes in the Values line. Exagerated for clarity,
that's

"Values ( " & [ID] & ", ' " & [StatusCodeTemp] & " ' ) "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I used the new code, I got the following error:

.Run-time error '3061':

Too few perameters. Expected 2.

When I click on debug, the CurrentDb line is highlighted.

What are we missing?

The best way is

Private Sub AddStatus_Click()
Dim stSQL As String
Dim stControl As String

stSQL = "INSERT into Status (StatusCompany, StatusCode) values ([ID],
[StatusCodeTemp])"
stControl = "StatusCodeTemp"

CurrentDb.Execute stSQL, dbFailOnError
Me.Controls(stControl).SetFocus
DoCmd.RunCommand acCmdRefresh

End Sub

 

[magmike]

Sorry, I didn't look closely enough at your SQL.

What are [ID] and [StatusCodeTemp]?

To get their values (rather than their names) into the SQL statement, you
need something like:

stSQL = "INSERT into Status (StatusCompany, StatusCode) " & _
  "Values (" & [ID] & ", '" & [StatusCodeTemp] & "')"

There, I'm assuming StatusCompany is a numeric field, and StatusCode is a
text field. Check the quotes in the Values line. Exagerated for clarity,
that's

  "Values ( " & [ID] & ", ' " & [StatusCodeTemp] & " ' ) "

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

When I used the new code, I got the following error:

.Run-time error '3061':

Too few perameters. Expected 2.

When  I click on debug, the CurrentDb line is highlighted.

What are we missing?

The best way is
Private Sub AddStatus_Click()
Dim stSQL As String
Dim stControl As String
stSQL = "INSERT into Status (StatusCompany, StatusCode) values ([ID],
[StatusCodeTemp])"
stControl = "StatusCodeTemp"
CurrentDb.Execute stSQL, dbFailOnError
Me.Controls(stControl).SetFocus
DoCmd.RunCommand acCmdRefresh
End Sub- Hide quoted text -

- Show quoted text -

That did it. Actually, though, both fields are number fields.