Adding Message to VB Code

[dwight1]

I have the following code that helps me in passing a parameter to the sp from a form command button.

Can any one tell me how to modify it such that when the user clicks the button it would say
"Do you want to add a record"
If the user clicks Yes then add record/execute the command else do nothing.

Thanks in advance

Dwight

 

[dwight1]

Cont...sorry here is the code..

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim cmd1 As ADODB.Command
Set cmd1 = CreateObject("ADODB.Command")
cmd1.ActiveConnection = cnn
cmd1.CommandType = 4
cmd1.CommandText = "CopyBorrowerData"
Dim prm1 As ADODB.Parameter
Set prm1 = CreateObject("ADODB.Parameter")
Set prm1 = cmd1.CreateParameter(AppID, 4, 1, , Me.Combo8)
cmd1.Parameters.Append prm1
cmd1.Execute

Exit_Command30_Click:
Exit Sub

Err_Command30_Click:
MsgBox Err.Description
Resume Exit_Command30_Click

End Sub

 

[JoeSmoe55]

I have read this over many times and I think the answer I will give is to simple. I think you would already know this therefore i think I am missing what you are asking but here it goes. sorry if this isn't what you asked for.

joe

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim cmd1 As ADODB.Command
Set cmd1 = CreateObject("ADODB.Command")
cmd1.ActiveConnection = cnn
cmd1.CommandType = 4
cmd1.CommandText = "CopyBorrowerData"
Dim prm1 As ADODB.Parameter
Set prm1 = CreateObject("ADODB.Parameter")
Set prm1 = cmd1.CreateParameter(AppID, 4, 1, , Me.Combo8)
cmd1.Parameters.Append prm1

rc = msgbox "Add this record" & prm1, vbyesNo,"Add a Record"
if rc = vbYes then
cmd1.Execute
else
Goto Exit_Command30_Click
end if

Exit_Command30_Click:
Exit Sub

Err_Command30_Click:
MsgBox Err.Description
Resume Exit_Command30_Click

End Sub

 

[rubbernilly]

OK, couple of things...

Put all of your Dim statements at the top of the code. The computer will allocate the space for them automatically, even if they are inside of an If-Then statement on a branch that will never run:

If 1=0 Then
Dim sTest as String
End If

sTest will take resources as soon as the procedure is entered.

Second, the language for a message box is in the MsgBox() function. You can look up the parts of the function in the help file, but here are a couple of ways to work with a MsgBox():

dim iReturn as Integer

iReturn = MsgBox("Do I look pretty?", vbYesNoCancel, "Title For Box")

Select Case iReturn
  Case vbYes
    'code for yes
  Case vbNo
    'code for no
  Case vbCancel
    'code for cancel
End Select

If the options are simpler than that, you can do a check in-line with an If Statement:

If MsgBox("Do I look pretty?", vbYesNo, "Title For Box") = vbYes Then
  'code for yes outcome
End if

So, taking those two points into account - and without reading the rest of your code for compilability, your code should look more like:

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click

Dim prm1 As ADODB.Parameter
Dim cmd1 As ADODB.Command
Dim cnn As ADODB.Connection

If MsgBox("Are you sure you wish to proceed?", vbYesNo, "Confirm Execute) = vbYes Then
    Set cnn = CurrentProject.Connection
    Set cmd1 = CreateObject("ADODB.Command")
    cmd1.ActiveConnection = cnn
    cmd1.CommandType = 4
    cmd1.CommandText = "CopyBorrowerData"
    Set prm1 = CreateObject("ADODB.Parameter")
    Set prm1 = cmd1.CreateParameter(AppID, 4, 1, , Me.Combo8)
    cmd1.Parameters.Append prm1
    cmd1.Execute
End If

Exit_Command30_Click:
    Exit Sub

Err_Command30_Click:
    MsgBox Err.Description
    Resume Exit_Command30_Click
    
End Sub

You might want to also check into how to release ADO objects so that they are properly destroyed at the end of your code. Destroy them both in proper functioning, and in your error handler.