Having problem with SQL in a VBA

  • Thread starter Thread starter Arvin Villodres
  • Start date Start date
A

Arvin Villodres

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

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
 
Try
.CommandText = "INSERT INTO tblEventLog ( [Date],
Event, USERID, TERMID, [Time] ) SELECT Date() AS
[Date], 'ADDED CODE' AS Event, '" & [Forms]![Switchboard]!
[txtSBUID] & "' AS [USERID], '" &
[Forms]![Switchboard]![txtSBTID] & "' AS
TERMID, Time() AS [Time];"

A good tip when you get this error is to open up the
immediate window and type in Debug.Print cmd.CommandText to
see how the string whether been formed correctly.

Hope This Helps
Gerald Stanley MCSD
 
i'm having trouble using this statement:

INSERT INTO tblEventLog ( [Date], Event, USERID, TERMID,
[Time] )
SELECT Date() AS [Date], "EDITED SUPPLIER CODE" & " " &
[Forms]![frmSuppliers]![txtSUPPLNO] AS Event, [Forms]!
[Switchboard]![txtSBUID] AS [USER], [Forms]![Switchboard]!
[txtSBTID] AS TERMINAL, Time() AS [Time];

how am I suppose to incorporate this in my vb script?

thanks for the reply.

Arvin
-----Original Message-----
Try
.CommandText = "INSERT INTO tblEventLog ( [Date],
Event, USERID, TERMID, [Time] ) SELECT Date() AS
[Date], 'ADDED CODE' AS Event, '" & [Forms]![Switchboard]!
[txtSBUID] & "' AS [USERID], '" &
[Forms]![Switchboard]![txtSBTID] & "' AS
TERMID, Time() AS [Time];"

A good tip when you get this error is to open up the
immediate window and type in Debug.Print cmd.CommandText to
see how the string whether been formed correctly.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
tried to make event logs of my own and i tried doing
this. I embeded a SQL in VBA

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


.
Click to expand...
.
Click to expand...
 
Back
Top