jboula
asked on
Access 2007 How to execute SQL statement in Report event (On Open) in VB
First, my knowledge of VB and Access is close to none.
I want execute an INSERT SQL statement when an Access report is opened.
I have created my Access report. Then I have created a method for the On Open event of the report that looks like this:
Private Sub Report_Open(Cancel As Integer)
CurrentDb.Execute("INSERT INTO dbo_example VALUE('hello')")
End Sub
However, if I try to open the report I get the Run-time error 3151 saying that ODBC connection failed.
The Report is linked to a Record Source that is a query. The report works fine if I remove this method. The error is really thrown at that line (CurrenDb.Execute)
How can I get an opened database connection from the Report's event?
Thanks in advance.
I want execute an INSERT SQL statement when an Access report is opened.
I have created my Access report. Then I have created a method for the On Open event of the report that looks like this:
Private Sub Report_Open(Cancel As Integer)
CurrentDb.Execute("INSERT INTO dbo_example VALUE('hello')")
End Sub
However, if I try to open the report I get the Run-time error 3151 saying that ODBC connection failed.
The Report is linked to a Record Source that is a query. The report works fine if I remove this method. The error is really thrown at that line (CurrenDb.Execute)
How can I get an opened database connection from the Report's event?
Thanks in advance.
ASKER
I want to keep a record in the database each time a report is opened/printed by the user. This is sort of an audit trail.
jboula,
Can you run this query on it's own?
For one thing, I cannot see what "Field" you are trying to insert your value into.
The Syntax for an Insert Query is:
INSERT INTO YourTable (YourField)
VALUES (YourValue)
An example in your case might look something like this:
INSERT INTO tblReportRunCounter (RunNumber)
VALUES (5)
JeffCoachman
Can you run this query on it's own?
For one thing, I cannot see what "Field" you are trying to insert your value into.
The Syntax for an Insert Query is:
INSERT INTO YourTable (YourField)
VALUES (YourValue)
An example in your case might look something like this:
INSERT INTO tblReportRunCounter (RunNumber)
VALUES (5)
JeffCoachman
ASKER
The query was just an example, it is not the problem. The point was just that I wanted to execute a SQL statement from the report on open event. I think a found an alternative way to do it which seem to work:
DoCmd.RunSQL ("INSERT INTO dbo_Rapports (RAMQ_ID, CreatedAt) VALUES ('XXXX12345678', NOW())")
The only annoying thing is that it shows a warning message to the user: "Your about to append X records to the database". Anyway to suppress that message?
DoCmd.RunSQL ("INSERT INTO dbo_Rapports (RAMQ_ID, CreatedAt) VALUES ('XXXX12345678', NOW())")
The only annoying thing is that it shows a warning message to the user: "Your about to append X records to the database". Anyway to suppress that message?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Perhaps there is a better way.