cschristian78
Technical User
We start off with a form that allows the user to select the database which resides on a SQL Server, Portfolio within the database, Peril, Country and State. These selections are used to create specific tables needed to run additional queries. After these selections are made,
I then allow the user to name the [SET] how ever they want and to create multiple [SETS]. At this point, they select the run button and I have set queries that I have created but I have them in a vb code file. When running the query, I just call the string the was created on the code file. For example, the following query is in a code file:
'CA DOI Zones'
Public strCADOIZones As String = "INSERT INTO [zz_Results Master] (Name3, Risks, TIV, [Set], Type, [Index], Peril) " & _
"SELECT Profdata.dbo.Cresta([Cresta])AS [CA DOI Zone], Count(LOC.LOCID) AS Risks, Sum(Val) AS TIV, [Set] AS [Set], 'CA DOI Zone' AS Type, [Index], Peril AS Peril " & _
"FROM (loc INNER JOIN qrySumLoccvg_Old ON loc.LOCID = qrySumLoccvg_Old.LOCID) LEFT JOIN [CA DOI Zones] ON loc.CRESTA = [CA DOI Zones].[CA DOI Zone], setdesc " & _
"WHERE [SET] = '" & dsSetDesc.Tables("SetDesc").Rows(RDCount).Item(1) & "'" & _
"GROUP BY [CRESTA], [Set], [Index], [Peril] " & _
"ORDER BY [SET], [NAME3], [Index]"
When trying to run this query, I get an error when it compiles the WHERE clause. This is because the dataset is only accessible within my form. Is there a way to reference a specific row in a dataset when using code files?
Thanks for the help,
Scott
I have about 15 or so queries that I need to run. In each query, the user selects the peril, country and statecode which within the interface. Based off of the selected criteria, My problem is when creating the loop which runs through the queries, I need to
I then allow the user to name the [SET] how ever they want and to create multiple [SETS]. At this point, they select the run button and I have set queries that I have created but I have them in a vb code file. When running the query, I just call the string the was created on the code file. For example, the following query is in a code file:
'CA DOI Zones'
Public strCADOIZones As String = "INSERT INTO [zz_Results Master] (Name3, Risks, TIV, [Set], Type, [Index], Peril) " & _
"SELECT Profdata.dbo.Cresta([Cresta])AS [CA DOI Zone], Count(LOC.LOCID) AS Risks, Sum(Val) AS TIV, [Set] AS [Set], 'CA DOI Zone' AS Type, [Index], Peril AS Peril " & _
"FROM (loc INNER JOIN qrySumLoccvg_Old ON loc.LOCID = qrySumLoccvg_Old.LOCID) LEFT JOIN [CA DOI Zones] ON loc.CRESTA = [CA DOI Zones].[CA DOI Zone], setdesc " & _
"WHERE [SET] = '" & dsSetDesc.Tables("SetDesc").Rows(RDCount).Item(1) & "'" & _
"GROUP BY [CRESTA], [Set], [Index], [Peril] " & _
"ORDER BY [SET], [NAME3], [Index]"
When trying to run this query, I get an error when it compiles the WHERE clause. This is because the dataset is only accessible within my form. Is there a way to reference a specific row in a dataset when using code files?
Thanks for the help,
Scott
I have about 15 or so queries that I need to run. In each query, the user selects the peril, country and statecode which within the interface. Based off of the selected criteria, My problem is when creating the loop which runs through the queries, I need to