Having problem with SQL in VBA

[Arvin Villodres]

I tried to make event logs of my own and i tried doing
this. I embeded a SQL in VBA of the AfterUpdate event in a
text box.

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO tblEventLog ( [Date],
Event, USERID, TERMID, [Time] ) SELECT Date() AS
[Date], 'ADDED CODE' AS Event, [Forms]![Switchboard]!
[txtSBUID] AS [USER], [Forms]![Switchboard]![txtSBTID] AS
TERMINAL, Time() AS [Time];"
.CommandType = adCmdText
.Prepared = True
Set rst = .Execute
End With

An error occurs after I run this event.

Run-time error '-2147217904(80040e10)':
No Value given for one or more required parameters

I think something wrong with 'ADDED CODE' AS Event part.
I don't know how to correct this error.
Will you be kind enough to help me.
Thanks

 

[TC]

Not to answer your question, but- a textbox AfterUpdate event is not a good
place to audit changes! What if the user changes the textbox, tabs out of it
(thus causing your audit), then presses Esc a few times to discard the
change? Now you've logged a change that never occurred.

You should really do the logging in Form_BeforeUpdate, or Form_AfterUpdate,
by comparing the new values to the old values saved on disk. Then you know
exactly what changed, regardless of what sequence of actions the user took
in the UI.

HTH,
TC
(off for the day)

 

[Graham R Seach]

Arvin,

1. You don't need a recordset, since you're not returning any records.
2. SQL Server doesn't know anything about Acess forms, so you need to
extricate them from the query.
3. SQL Server doesn't have a Time() function, so you need to extricate that
as well.

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO tblEventLog ( [Date],
Event, USERID, TERMID, [Time] ) SELECT Date() AS
[Date], 'ADDED CODE' AS Event, '" & Forms!Switchboard!
txtSBUID & "' AS [USER], '" & Forms!Switchboard!txtSBTID & "' AS
TERMINAL, '" & Time() & "'AS [Time];"
.CommandType = adCmdText
.Prepared = True
.Execute
End With

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Treebeard]

You could also create a query

INSERT INTO tblEventLog ( [Date], Event, USERID,
TERMID, [Time] ) SELECT Date() AS
[Date], 'ADDED CODE' AS Event, [Forms]![Switchboard]!
[txtSBUID] AS [USER], [Forms]![Switchboard]![txtSBTID] AS
TERMINAL, Time() AS [Time];

When you test the query, make sure that the Switchboard form is open.

Then just execute the query from code like this:

Dim dbs As ADODB.Connection
Set dbs = CurrentProject.Connection
dbs.Execute "YourQueryName"

Jack