RunSQL Problem

[darrenhubbard]

Hi All,

I have the following VBA function:

Private Sub TrafficButton_Click()
Param = [Forms]![VAD]![TableSelect]

If IsNull(Param) Then
MsgBox "Please select a table from the drop-down list"
Exit Sub
End If

Param = Trim("[" & Trim(Param) & "]&quot

MsgBox Param

querystring = "SELECT " & Param & ".LastOccurence, Applications.Application, ApplicationNames.[Application Name], " & Param & ".[Server IP], " & Param & ".[Server Port], " & Param & ".Protocol, " & Param & ".[Client IP], " & Param & ".ServerTotal, " & Param & ".ClientTotal, " & Param & ".TotalNumber FROM (" & Param & " LEFT JOIN Applications ON (" & Param & ".[Server Port]=Applications.[Server Port]) AND (" & Param & ".Protocol=Applications.Protocol)) LEFT JOIN ApplicationNames ON (" & Param & ".[Server Port]=ApplicationNames.[Server Port]) AND (" & Param & ".Protocol=ApplicationNames.Protocol) AND (" & Param & ".[Server IP]=ApplicationNames.[Server IP]) ORDER BY [ServerTotal] DESC;"

MsgBox querystring

DoCmd.SetWarnings False
DoCmd.RunSQL Trim(querystring)
DoCmd.SetWarnings True

Exit Sub
End Sub

The function runs when a button on a form is clicked and it takes the name selected from a ComboBox into "Param"

The two msgbox's are there for my debugging purposes and the all the "trim"'s are there because I'm paranoid

For some reason, the DoCmd.RunSQL generates the error message "A RunSQL action requires an argument consisting of an SQL statement".

I've even copied the value of querystring that RunSQL is trying to execute into a query and it runs absolutely fine and as intended.

Anybody got any ideas?

Thanks for all your help,

Darren

 

[darrenhubbard]

Quick update -- if I add an INTO [temptable] into the SQL it then runs fine.

So, I can only assume that RunSQL won't launch a datasheet type view of query it's running (ie it has to "do" something). Does anybody have an alternative that will work?

TIA,

Darren

 

[Carthesis]

Yeah...RunSQL can only (apparently) be used to run an 'action' query. If it just runs a 'SELECT', it just dumps the results into a recordset.

Only problem I have is i'm trying to get it to run a delete query for me, and it doesn't. I keep getting type data mismatch in criteria expression error.

Any ideas anyone?

Private Sub cmdDeleteRecord_Click()

Dim strSQL As String

If Me![Table] = "Incoming" Then
    
     strSQL = "DELETE * FROM [tblIncoming] "
     strSQL = strSQL & "WHERE [tblIncoming].[OWCRefNo]='" & Me![OWCRefNo] & "';"
    
ElseIf Me![Table] = "Outgoing" Then
     
     strSQL = "DELETE * FROM [tblOutgoing] "
     strSQL = strSQL & "WHERE [tblOutgoing].[OWCRefNo]='" & Me![OWCRefNo] & "';"
     
ElseIf Me![Table] = "Drawings" Then
    
     strSQL = "DELETE * FROM [tblDrawings] "
     strSQL = strSQL & "WHERE [tblDrawings].[DwgNo]='" & Me![OWCRefNo] & "';"
     
End If

DoCmd.RunSQL strSQL
With DoCmd
     .SetWarnings True
     .RunSQL strSQL
End With

End Sub

 

[Norris68]

"Does anybody have an alternative that will work?"

How about creating a multi-column listbox and setting its RecordSource property to the SQL string you generated?

 

[MichaelRed]

{f1} term "runsql" first line:

The RunSQL method carries out the RunSQL action in Visual Basic in action queries

It would be faster (and even -perhaps- more accurate-) in many instances to make a more liberal use of the ubiquitous resources provided.








MichaelRed
m.red@att.net

Searching for employment in all the wrong places