Custom built SQL string on NON-design master?

[keady2]

Okay so ... my title may explain my problem...I have a design master and then I have several replicas for each district to use outside of my own. The problem is only the design master is able to build a custom SQL string for the reports menu I have built ... the other districts get the error:

Error No. 3027: Description: Cannot update. Database or object is read-only.

Is there a way to build the custom SQL string in databases that are read-only? This is the code I have linked to the RUN REPORT button.

Private Sub Command158_Click()
On Error GoTo ErrorHandler

Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim stdocname As String

strSQL = "SELECT datatbl.*, 'T' & [township] & 'R' & [range] AS [TR] " & _
"FROM datatbl " & _
"WHERE "

If Me.disemployee & "" <> "" Then
strSQL = strSQL & "[datatbl].[name]= '" & Me.disemployee & "' AND "
End If

If Me.distype & "" <> "" Then
strSQL = strSQL & "[datatbl].[function]=" & Me.distype.Column(0) & " AND "
End If

If Me.disgroup & "" <> "" Then
strSQL = strSQL & "[datatbl].[group]='" & Me.disgroup.Value & "' AND "
End If

If Me.disTR & "" <> "" Then
strSQL = strSQL & "('T' & [township] & 'R' & [range])='" & Me.disTR & "' AND "
End If

If Me.discode & "" <> "" Then
strSQL = strSQL & "[datatbl].[project code]= '" & Me.discode & " ' AND "
End If

If Me.txtstartdate & "" <> "" And Me.txtenddate & "" <> "" Then
strSQL = strSQL & "[datatbl].[Dateofwork] Between #" & Me.txtstartdate & _
"# And #" & Me.txtenddate & "#" & " AND "
Else
If Me.txtstartdate & "" <> "" Then
strSQL = strSQL & "[datatbl].[Dateofwork]=# " & Me.txtstartdate & "#" & _
" And "
End If
End If

If Right(strSQL, 5) = "HERE " Then
MsgBox "You did not select any criteria!", vbCritical, _
"Please select CUSTOM criteria"

DoCmd.CancelEvent
Exit Sub
Else
strSQL = Left(strSQL, Len(strSQL) - 5)
End If

Set db = CurrentDb
Set qdf = db.QueryDefs("qryforreport")

qdf.Sql = strSQL

Set qdf = Nothing
Set db = Nothing

If DCount("*", "qryforreport", "[RECORDID] > 1") > 0 Then
stdocname = "CustomReport"
DoCmd.OpenReport stdocname, acPreview
Else
MsgBox "There are no records that match this criteria", vbInformation, _
"No Records Found!"
End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

Thanks for any help on how to make this code run on a non-design master...

Edited 12-Jun-07 by geekgirlau. Reason: insert vba tags

[mattj]

Its' because you are attempting to change the SQL of a stored query. I would try not setting the querydef - rather, just use the SQL string as the recordsource of the report (which can be done on the OnOpen event of the report).
Declare the SQL string variable in a module as a global or public variable, build it as you already are, and then on the report's OnOpen event, you would use
Me.RecordSource = strMyGlobalSQLVariable to set the property.

HTH
Matt

[keady2]

okay ... well that was a lot easier than I thought it would be thanks.. now my only other problem is that report that is now being run of a global SQL statement is entirely based on DSUM() functions which sum totals of points and miles etc off the query (which is no longer being used). So how can I get the DSUM function to use the global SQL string as its recordsource? if i remember correctly the DSUM function MUST use a query or a table. But because it is not design master I cannot assign the Global SQL string as a query definition??

[mattj]

That certaintly rocks the boat... perhaps you could use controls that use the values of the controls displayed on the report - like using "=SUM([Somefield]) in the footer to do sub-totals...

Other than that I am at a loss - perhaps another member may have an idea...

[keady2]

well i got it figured out...i reverted back to using the querydefinition string ... and did not make the query itself replicable...thus making it a local query on each district's copy. that allows it to be run, queries themselves can be changed but not replicable queries because that is changing the entire database.

[mattj]

Glad you got it worked out!

[geekgirlau]

Hi Keady,

When posting code, make sure you use the vba tags (select the code text, then click on the "VBA" button) - I've added them to your post above so you can see the end result.

I would also suggest that you move


Set qdf = Nothing
Set db = Nothing


to immediately below your exit label:


ErrorHandlerExit:
On Error Resume Next
Set qdf = Nothing
Set db = Nothing
Exit Sub



That way even if an error occurs, your objects are being closed.