Making a query with SQL and putting it in an access database query

[BPMan]

ok i have tried to get help on this put have not gotten a good answer yet....
i think it is my fault because i have not been very clear........
here is a function in my program.......

Private Function printDetail(check As Boolean)
Dim str As String
Dim result As ADODB.Recordset
Dim old As ADODB.Recordset
Set old = New ADODB.Recordset
Set result = New ADODB.Recordset
str = "SELECT CSI.ID, CSI.Commodity, CSI.[Vehicle Name], CSI.Program, "
str = str & "CSI.[Idea #], CSI.[ED Group], CSI.Description, CSI.[Vehicle Volume], "
str = str & "CSI.[Option Rate], CSI.Usage, CSI.[Potential Savings/part], "
str = str & "CSI.[Potential Savings], CSI.[Actual Savings/part], "
str = str & "CSI.[Actual Savings], CSI.Status, CSI.Risk, CSI.[Part #], "
str = str & "CSI.[Part Name], CSI.Supplier, CSI.[ED Contact], CSI.[Date Sent], "
str = str & "CSI.[Date Changed], CSI.[Date Closed], CSI.[Purchasing #], "
str = str & "CSI.[ECI #], CSI.[Implementation Type], CSI.Comments From CSI "
'ask greg what he wants here???
str = str & "Where (((CSI.Status) = 'B' Or (CSI.Status) = 'C')) "
If Not cboEDGroup.Text = "(All)" Then
str = str & "And CSI.[ED Group] = '" & cboEDGroup.Text & "' "
ElseIf Not cboProgram.Text = "(All)" Then
str = str & "And CSI.[Program] = '" & cboProgram.Text & "' "
End If
str = str & "ORDER BY CSI.[Date Changed]"

If (frmSplash.cnn.State = adStateOpen) Then
result.Open str, frmSplash.cnn, adOpenKeyset, adLockPessimistic
Else
MsgBox "Cannot find database, computer must be on TTC network!"
End
End If

'HERE

End Function

ok the connection is established else where and is too my access database.
so what i am trying to do is the following....
i have a query in my access database called Detail. I want this query to be set to the SQL statement I build in this function. I dont care how it gets done. Then I want to call a Report in my access databse and print it.
how do i do this.....what code should i put where i have typed HERE
thanks

 

[DarenNM]

Better to stick with your original thread: thread222-362013
so people can follow the whole story.

Did the querydef example I just posted there help?

Hope this helps

Daren


Must think of a witty signature

 

[Brewman76]

Speaking from a matter of experience, it would be better to fill a table with the data you want and run the report off the table.

However, the examples in the previous thread work using the DAO CreateQueryDef method. That would save your SQL statement as a stored Access query. This unfortunately isn't an option when using ADO with Access 2000. Using ADO will create a query that is only accessible through ADO and not visible when opening up the query list in Access. This is because Access 2k actually has two database engines and.. well.. I digress since I'm getting off the subject.

I'm still curious why you insist you must store this as a query. Using a temp table and filling that with the data you want to report on would be much easier from a testing and troubleshooting standpoint. How do you propose to design the report if while designing you don't have any fields to connect to? I'm sure it would make whomever may in the future look at or modify your code scratch their head.