outpulsing data of a form in vertical format, not horizontal

  • Thread starter Thread starter dave
  • Start date Start date
That works good, but now my problem is the questions I
want aren't like 1-10....they are like 1, 4, 5-9, 14,
etc...how do I get it to outpulse those specefic questions?

-----Original Message-----



Try using an SQL statement instead. e.g.
Click to expand...
DB.openrecordset ("Select * from
 
Dave said:
If i use select *, won't that list all the fields that are
in my table? Each one will have only 8 or 9 or thet total
30 or so from the table. Or do I replace the * with the
fields I want? If that is the case, how do I seperate
them? (meaning do I put them all in paranthees split by a
comma?) thanks!
Click to expand...

D'Oh! Of course

A good trick is to create a query and then copy the SQL code from it. :)
Or you can just use the straight query of course, but make sure you don't
give it the same name as a table.

An example is,
Dim strSQL as string

strSQL = "Select [blah], [blah2] etc. " & _
strSQL = strSQL & " from [tablename]"

Then use DB.Openrecordset (strSQL, dbopendynaset, dbseechanges)

It starts getting tricky if you have criteria in the query - then you need
to start using querydefs.
 
Dave said:
That works good, but now my problem is the questions I
want aren't like 1-10....they are like 1, 4, 5-9, 14,
etc...how do I get it to outpulse those specefic questions?
Click to expand...

bugger - that's what I feared!! Time to call in the querydefs. :)

Okay - create a query with the criteria you want (e.g. if the question
numbers are all in one field, try something like: "In (1,4,5,6,7,8,9,14)"
in the criteria section under the question number.

Then copy the SQL code (like in the previous message I posted).

Now - you'll need to add code similar to this:

Dim qryDEF as DAO.querydef
Dim param as DAO.parameter

Set db = CurrentDb()
Set qryDEF = DB.OpenRecordset("", strSQL)

For Each param In qryDEF.Parameters
param.Value = Eval(param.Name)
Next

Set RS = db.OpenRecordset(strSQL, dbOpendynaset, dbSeeChanges)

etc.

Hope it helps!!!
Rebecca
 
That seems like everything will work once I grind it
out...here is my current code:

Dim xl As Object, wksheet As New Excel.Worksheet
Dim strPath
Dim i As Integer
Dim strSQL As String
Dim qryDEF As DAO.QueryDef
Dim param As DAO.Parameter

strSQL = "Select [Description], [ckt id], [a loc], [z
loc], [Access Hours],[ Contact Name]" & _
strSQL = strSQL & " from [Issues]"

Set DB = CurrentDb()
Set qryDEF = DB.OpenRecordset("", strSQL)


For Each param In qryDEF.Parameters
param.Value = Eval(param.Name)
Next

Set RS = DB.OpenRecordset(strSQL, dbOpenDynaset,
dbSeeChanges)
strPath = "c:\downloads\access\test" & ".xls"
Windows.Application.Workbooks.Open FileName:=strPath
Workbooks.Application.ActiveWorkbook.Activate

For i = 1 To 10
Cells(i, 2) = RS.Fields(i).Value
Next

ActiveWorkbook.Close
RS.Close
Set RS = Nothing
Set DB = Nothing
On Error GoTo Err_Command183_Click

Dim stAppName As String

stAppName = "Excel.exe c:\downloads\access\test.xls"
Call Shell(stAppName, 1)

Exit_Command183_Click:
Exit Sub

Err_Command183_Click:
MsgBox Err.Description
Resume Exit_Command183_Click


Right now, I am receiving an error message on the
following:

Set DB = CurrentDb()

The error message states DB isn't an active variable..I
have never received that message before on the
DB...help! :)
 
so is it working yet??

Rebecca

Rebecca Smith said:
bugger - that's what I feared!! Time to call in the querydefs. :)

Okay - create a query with the criteria you want (e.g. if the question
numbers are all in one field, try something like: "In (1,4,5,6,7,8,9,14)"
in the criteria section under the question number.

Then copy the SQL code (like in the previous message I posted).

Now - you'll need to add code similar to this:

Dim qryDEF as DAO.querydef
Dim param as DAO.parameter

Set db = CurrentDb()
Set qryDEF = DB.OpenRecordset("", strSQL)

For Each param In qryDEF.Parameters
param.Value = Eval(param.Name)
Next

Set RS = db.OpenRecordset(strSQL, dbOpendynaset, dbSeeChanges)

etc.

Hope it helps!!!
Rebecca
Click to expand...
 
no, its not working :(

here is my current code:

Dim xl As Object, wksheet As New Excel.Worksheet
Dim strPath
Dim i As Integer
Dim strSQL As String
Dim qryDEF As DAO.QueryDef
Dim param As DAO.Parameter

strSQL = "Select [Description], [ckt id], [a loc], [z
loc], [Access Hours],[ Contact Name]" & _
strSQL = strSQL & " from [Issues]"

Set DB = CurrentDb()
Set qryDEF = DB.OpenRecordset("", strSQL)


For Each param In qryDEF.Parameters
param.Value = Eval(param.Name)
Next

Set RS = DB.OpenRecordset(strSQL, dbOpenDynaset,
dbSeeChanges)
strPath = "c:\downloads\access\test" & ".xls"
Windows.Application.Workbooks.Open FileName:=strPath
Workbooks.Application.ActiveWorkbook.Activate

For i = 1 To 10
Cells(i, 2) = RS.Fields(i).Value
Next

ActiveWorkbook.Close
RS.Close
Set RS = Nothing
Set DB = Nothing
On Error GoTo Err_Command183_Click

Dim stAppName As String

stAppName = "Excel.exe c:\downloads\access\test.xls"
Call Shell(stAppName, 1)

Exit_Command183_Click:
Exit Sub

Err_Command183_Click:
MsgBox Err.Description
Resume Exit_Command183_Click


Right now, I am receiving an error message on the
following:

Set DB = CurrentDb()

The error message states DB isn't an active variable..I
have never received that message before on the
DB...help! :)
 
Back
Top