docmd.runsql

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I previously had a query that was too complex to be evaluated (apparently).
A nice person on this site told me to construct a 'make table' query.

The results are for a report, so I have made the report have the onopen
event as below:

Private Sub Report_Open(CANCEL As Integer)

Dim self1 As String

If <statement> Then
self1 = "SELECT <variables> INTO <newtable> FROM <table1> INNER JOIN
<table2> ON <table 1 field> = <table 2 field> WHERE <statement 1>;"

Else
self1 = "SELECT <variables> INTO <newtable> FROM <table1> INNER JOIN
<table2> ON <table 1 field> = <table 2 field> WHERE <statement 2>;"

End If

DoCmd.RunSQL self1

With Report
.RecordSource = "SELECT <fields from newtable>
"FROM <newtable>
"WHERE <statement>
"ORDER BY <field> DESC;"
End With
End Sub

It all seems to work except for the DoCmd.RunSQL self1
at which point it just stops and the report is empty. With setwarnings=true
if brings up a warning asking me if it's OK to replace the contents of the
newtable. I click yes, but when Ilook at the contents of newtable they have
not been updated.

Any suggestions?

Thanks in advance for all your help.
 
Further to the description below...

everything works if it is an admin user running the database.

It is only when a non-admin user tries to use it that it doesn't work.

I have made sure all the permissions on the newtable are set to read,
insert, update and delete, but still no luck.

It is a multi user (split) database, but I don't know if this might be
causing the problems.

Thanks all.
 
Okay, I'm completely lost.

Could you post the SQL that the QBE grid generated that caused the
error (too complex)?

Maybe it's me, but it's hard to help given the sketchy description of
the problem and the query/queries involved.

Maybe if you ran the insert statement _before_ you opened the report,
you'd get somewhere, say in the Click event of a button. One way to do
that would be to create a button that opens your report. then _above_
the code that opens the report, put your Insert statement. Something
like:

Private Sub cmdOpenMyReport_Click()
'run my append query
dim strSQL as string
strSQL ="INSERT INTO..." 'append query goes here.

dbengine(0)(0).Execute strSQL
docmd.openReport "MyReport"...
End Sub
 
Hi - thanks for your response,

The query that causes the too complex error is:

SELECT [PERSON].[AKAPID], [SEARCHES].[PERSON ID], [SEARCHES].[CLIENT ID],
[SEARCHES].[date], [SEARCHES].[type], [SEARCHES].[amount] FROM PERSON INNER
JOIN SEARCHES ON [PERSON].[PERSON ID] = [SEARCHES].[PERSON ID] WHERE
((((person.[PERSON ID])=Forms!person!PID)) Or
(((person.AKAPID)=forms!person!exakapid)) Or
(((person.AKAPID)=forms!person!akapid))) and ([searches].[Client ID]) =
[Forms]![security]![CID];

This query failed no matter who the user was.

When I split the above statement into a make table query and referred the
reports .recordsource to the new dataset, it works when being run by an admin
user but not a non-admin - hence why I think it's to do with permissions
rather than code.

The big confusion is the error saying 'you don't have modify permissions'
when I have set all the permissions in the tools>security>user and group
permissions to give full rights to non-admin users.

I have tried what you suggested re: adding a button and running statements
before the report opens, but it doesn't solve the problem.

Cheers
 
Back
Top