Recordset from Access to Excel based on a Query with combo box criteria

[torontolancer]

Hi there how r u .I would really appriciate ur concern regarding checking out this code.
its beind a command button and i am have a combo box selecing a query criteria and then pressing the button to take care recoredset from access to excel.
i have checked the code in any ways its working fine but when i use the combo box criteria
forms!formname!combobox
in the query the code breaks down at

rst.open a """""""""""gives me error one or two parameter values are not entered"""""""""""""""

AT BREAK POINT IN IMMEDIATE WINDOW WHEN U PUT UR MOUSE OVER COMBO IT DOES PASS THE VALUE BUT WHEN I COMES TO
rst.open a
error occurs

I have already tried
a = a + " HAVING (((tblndwpcsvw.CAT) = " & Me.Combo3 & "))"
instead of
a = a + " HAVING (((tblndwpcsvw.CAT) = [Forms]![tblndwpcsvw]![Combo3]))"

thankxxxxxxxxxxxxxx and have a nice day bbye

NOTE:Copied the SQL from query
NOTE:"""""""""""""""""the query involve a crosstab query thats why the explicit parameter is required""""""""""



Private Sub Command1_Click()


Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection
Dim rst As New ADODB.Recordset
rst.ActiveConnection = cnn
Dim a As String

a = " PARAMETERS [forms]![tblndwpcsvw]![combo3] Text ( 255 );"

a = a + " SELECT tblndwpcsvw.CAT, [PROFILING Query].WK, [PROFILING Query].[2004], [PROFILING Query].[2005], [PROFILING Query].[2006]"

a = a + " FROM tblndwpcsvw INNER JOIN [PROFILING Query] ON tblndwpcsvw.PCS2 = [PROFILING Query].CAT"

a = a + " GROUP BY tblndwpcsvw.CAT, [PROFILING Query].WK, [PROFILING Query].[2004], [PROFILING Query].[2005], [PROFILING Query].[2006]"

a = a + " HAVING (((tblndwpcsvw.CAT) = [Forms]![tblndwpcsvw]![Combo3]))"

a = a + " ORDER BY tblndwpcsvw.CAT, [PROFILING Query].WK;"


rst.Open a

Dim xl As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xl = CreateObject("excel.application")

Set xlbook = GetObject("c:\abc.xls")

xl.Visible = True


xlbook.Windows(1).Visible = True

Set xlsheet = xlbook.Worksheets(1)

xlsheet.Range("b3").CopyFromRecordset rst


Set xl = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
rst.Close
Set rst = Nothing

End Sub

[JKing]

Could you post the error number and description for the error you are receiving?

[torontolancer]

Thank you very much for checking out this code.please check below i am receiving this error in the msg box.
talk to u later take care and have a nice day
bbye



Run-time error '-2147217904(80040e10)':
No value given for one or more required parameters.

[torontolancer]

Hi JKING thank u very much i lost lots of time on this SQL but u helped me out by pointing me to work on the error number so i came across this sql and its working now
its passing the value in the immediate window
but now i am getting this error

"syntex error in parameter clause"

the sql i am using now

a = " PARAMETERS " & [Forms]![tblndwpcsvw]![Combo3] & " Text ( 255 )" + " SELECT tblndwpcsvw.CAT, [PROFILING Query].WK, [PROFILING Query].[2004], [PROFILING Query].[2005], [PROFILING Query].[2006]" + " FROM tblndwpcsvw INNER JOIN [PROFILING Query] ON tblndwpcsvw.PCS2 = [PROFILING Query].CAT" + " GROUP BY tblndwpcsvw.CAT, [PROFILING Query].WK, [PROFILING Query].[2004], [PROFILING Query].[2005], [PROFILING Query].[2006]" + " HAVING (((tblndwpcsvw.CAT) = " & Me.Combo3 & "))" + " ORDER BY tblndwpcsvw.CAT, [PROFILING Query].WK;"

rst.Open a

[JKing]

Have you tried running it with just this:

a = "SELECT tblndwpcsvw.CAT, [PROFILING Query].WK, [PROFILING Query].[2004], [PROFILING Query].[2005], [PROFILING Query].[2006]" + " FROM tblndwpcsvw INNER JOIN [PROFILING Query] ON tblndwpcsvw.PCS2 = [PROFILING Query].CAT" + " GROUP BY tblndwpcsvw.CAT, [PROFILING Query].WK, [PROFILING Query].[2004], [PROFILING Query].[2005], [PROFILING Query].[2006]" + " HAVING (((tblndwpcsvw.CAT) = " & Me.Combo3 & "))" + " ORDER BY tblndwpcsvw.CAT, [PROFILING Query].WK;"
 

As you've said it is receiving the value from the combo box. I don't believe you need to specify the control as a parameter in the query. This should solve your error.

Another note without knowing exactly whats in your tables or queries or the purpose behind them I can't say this for certain but it does seem like this query string is unnecessarily long.
Would the following not produce the same results?

a = "SELECT [Profiling Query].CAT, [PROFILING Query].WK, [PROFILING Query].[2004], [PROFILING Query].[2005], [PROFILING Query].[2006]" + " FROM [Profiling Query] WHERE [Profiling Query].CAT = " & Me.Combo3 &  " ORDER BY [PROFILING Query].CAT, [PROFILING Query].WK;"