Update syntax VBA and XML

[gmazza via AccessMonster.com]

Hey there,
Here's my routine, I'm getting no errors, yet the table is not being updated,
can anyone see anything wrong? EmployeeID is an autonumber and primary key in
the table.
Thanks in advance!

Public Sub OnActionButton(control As IRibbonControl)
On Error Resume Next

Dim sSql As String
Dim db As DAO.Database
Dim strTrial As String
Dim intEmployee As Integer

intEmployee = Forms!Employees!EmployeeID

strTrial = "Trial Approved"

Select Case control.Id
Case "cmdApproval"
If MsgBox("Are you sure you want to approve this trial?", _
vbYesNo + vbDefaultButton2, "Clinical Trial Approval") = vbNo Then
Else
sSql = "Update Employee " _
& "Set ApprovalComment = " & strTrial & " " _
& "Where EmployeeId = " & intEmployee & ";"

CurrentDb.Execute sSql, dbFailOnError

End If
End Select

 

[Albert D. Kallal]

Hey there,
Here's my routine, I'm getting no errors, yet the table is not being
updated,
can anyone see anything wrong? EmployeeID is an autonumber and primary key
in
the table.

I don't think the code is being run at all....

The syntax in your ribbon xml should be:

<button id="button1" label="Buttion1"
onAction="=OnActionButton()"
/>

Note how you MUST precedue the function name with "=" and follow the syntax
as ()

Also, you don't use a sub name, you use a function name.

So,:

Public Function OnActionButton()

' also, note that the public functon does NOT need the iribbon as a
pramemrer. (so, remove that also).

Furthermore, if the form has the focus, then place the code IN THE FORMS
module.

Thus, we have:

Public Function OnActionButton()

if MsgBox("Are you sure you want to approve this trial?", _
vbYesNo + vbDefaultButton2, "Clinical Trial Approval") = vbYes Then
me.ApprovalComment = "Trial Approved"
end if

end function

Since the function is now inside of the form, then you can see it is a lot
less code.....

So in summary:

you don't use a sub, you need to use a function for the on-action

you need to place = and () to surround the function name

you don't pass nor use the ribbon object as your syntax shows

place the function name as public in the form, and you eliminate all of
that sql junk


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

 

[gmazza via AccessMonster.com]

Hey Albert,
Thank you for your help, cleaner code and works perfect!
From a fellow Edmontonian, have a good night!