Crosstab Parameters

F

FGM

Windows 2000; Access 2002
Hi,
I have spent many days looking for an answer to the following. Read every
question I can find on here. This is where I have been able to get from what
I have read.

In a crosstab you must assign an explicit parameter by adding it to the
Query Parameters dialog box. Under Parameter column I put [Get ProjID] and
under type long integer.

This is my code:
Public Sub Test()

Const newTablename = "fgm-new"
Dim dbs As DAO.Database
Dim strSql As String

DoCmd.SetWarnings False
Set dbs = CurrentDb
strSql = "SELECT DISTINCTROW * "
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"

On Error Resume Next ' Delete table if it exists
DoCmd.DeleteObject A_TABLE, newTablename
DoCmd.RunSQL strSql

dbs.Close
End Sub

What I want to do is have a loop feeding in a different ProjID each loop.
Right now this will come in and ask me for a ProjID and when I key in the ID
it works. How do I feed in the ProjID in the code automatically? Can I put
in a Where clause and refer to the Parameter? What is the code for it?

any help would be most appreciated. If you need more information let me know.
 
J

John W. Vinson

Windows 2000; Access 2002
Hi,
I have spent many days looking for an answer to the following. Read every
question I can find on here. This is where I have been able to get from what
I have read.

In a crosstab you must assign an explicit parameter by adding it to the
Query Parameters dialog box. Under Parameter column I put [Get ProjID] and
under type long integer.

This is my code:
Public Sub Test()

Const newTablename = "fgm-new"
Dim dbs As DAO.Database
Dim strSql As String

DoCmd.SetWarnings False
Set dbs = CurrentDb
strSql = "SELECT DISTINCTROW * "
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"

On Error Resume Next ' Delete table if it exists
DoCmd.DeleteObject A_TABLE, newTablename
DoCmd.RunSQL strSql

dbs.Close
End Sub

What I want to do is have a loop feeding in a different ProjID each loop.
Right now this will come in and ask me for a ProjID and when I key in the ID
it works. How do I feed in the ProjID in the code automatically? Can I put
in a Where clause and refer to the Parameter? What is the code for it?

any help would be most appreciated. If you need more information let me know.
Click to expand...

There is indeed a Parameters collection in a querydef object, and you can
access it from code. It's best to use the Querydef execute method rather than
RunSQL - for a parameter query it's probably essential.

Dim dbs As DAO.Database
Dim qdf As DAO.Querydef
On Error GoTo Proc_Error
Set dbs = CurrentDb
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"
Set qdf = db.CreateQuerydef("", strSQL) ' create a temp query
qdf.Parameters(0) = Me!ProjID
<as above for other parameters, you can also use
qdf.Parameters("[Enter ProjID:]")>
qdf.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
<deal with errors appropriately>
Resume Proc_Exit
End Sub
 
F

FGM

Thank you John for your answer to my post. I tried your answer but it does
not create a table? It does run without an error. What do you think I am
doing wrong?
Thanks again.

Public Sub Test()

Const newTablename = "fgm-new"
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSql As String
On Error GoTo Proc_Error
Set dbs = CurrentDb
On Error Resume Next ' Delete table if it exists
DoCmd.DeleteObject A_TABLE, newTablename

strSql = "SELECT DISTINCTROW * "
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"
Set qdf = db.CreateQueryDef("", strSql) ' create a temp query
qdf.Parameters(0) = 7
'<as above for other parameters, you can also use
'qdf.Parameters("[Enter ProjID:]")>
qdf.Execute dbFailOnError

dbs.Close


Proc_Exit:
Exit Sub
Proc_Error:
'<deal with errors appropriately>
Resume Proc_Exit


End Sub



John W. Vinson said:
Windows 2000; Access 2002
Hi,
I have spent many days looking for an answer to the following. Read every
question I can find on here. This is where I have been able to get from what
I have read.

In a crosstab you must assign an explicit parameter by adding it to the
Query Parameters dialog box. Under Parameter column I put [Get ProjID] and
under type long integer.

This is my code:
Public Sub Test()

Const newTablename = "fgm-new"
Dim dbs As DAO.Database
Dim strSql As String

DoCmd.SetWarnings False
Set dbs = CurrentDb
strSql = "SELECT DISTINCTROW * "
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"

On Error Resume Next ' Delete table if it exists
DoCmd.DeleteObject A_TABLE, newTablename
DoCmd.RunSQL strSql

dbs.Close
End Sub

What I want to do is have a loop feeding in a different ProjID each loop.
Right now this will come in and ask me for a ProjID and when I key in the ID
it works. How do I feed in the ProjID in the code automatically? Can I put
in a Where clause and refer to the Parameter? What is the code for it?

any help would be most appreciated. If you need more information let me know.
Click to expand...

There is indeed a Parameters collection in a querydef object, and you can
access it from code. It's best to use the Querydef execute method rather than
RunSQL - for a parameter query it's probably essential.

Dim dbs As DAO.Database
Dim qdf As DAO.Querydef
On Error GoTo Proc_Error
Set dbs = CurrentDb
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"
Set qdf = db.CreateQuerydef("", strSQL) ' create a temp query
qdf.Parameters(0) = Me!ProjID
<as above for other parameters, you can also use
qdf.Parameters("[Enter ProjID:]")>
qdf.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
<deal with errors appropriately>
Resume Proc_Exit
End Sub
Click to expand...
 
F

FGM

Hi again,
found out that in one place it was db and another dbs. It appeared to be
running because no error messages and so it just closed. Important to have
err.description
thanks a million.... it sure was great to get it to work...

fgm


FGM said:
Thank you John for your answer to my post. I tried your answer but it does
not create a table? It does run without an error. What do you think I am
doing wrong?
Thanks again.

Public Sub Test()

Const newTablename = "fgm-new"
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSql As String
On Error GoTo Proc_Error
Set dbs = CurrentDb
On Error Resume Next ' Delete table if it exists
DoCmd.DeleteObject A_TABLE, newTablename

strSql = "SELECT DISTINCTROW * "
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"
Set qdf = db.CreateQueryDef("", strSql) ' create a temp query
qdf.Parameters(0) = 7
'<as above for other parameters, you can also use
'qdf.Parameters("[Enter ProjID:]")>
qdf.Execute dbFailOnError

dbs.Close


Proc_Exit:
Exit Sub
Proc_Error:
'<deal with errors appropriately>
Resume Proc_Exit


End Sub



John W. Vinson said:
Windows 2000; Access 2002
Hi,
I have spent many days looking for an answer to the following. Read every
question I can find on here. This is where I have been able to get from what
I have read.

In a crosstab you must assign an explicit parameter by adding it to the
Query Parameters dialog box. Under Parameter column I put [Get ProjID] and
under type long integer.

This is my code:
Public Sub Test()

Const newTablename = "fgm-new"
Dim dbs As DAO.Database
Dim strSql As String

DoCmd.SetWarnings False
Set dbs = CurrentDb
strSql = "SELECT DISTINCTROW * "
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"

On Error Resume Next ' Delete table if it exists
DoCmd.DeleteObject A_TABLE, newTablename
DoCmd.RunSQL strSql

dbs.Close
End Sub

What I want to do is have a loop feeding in a different ProjID each loop.
Right now this will come in and ask me for a ProjID and when I key in the ID
it works. How do I feed in the ProjID in the code automatically? Can I put
in a Where clause and refer to the Parameter? What is the code for it?

any help would be most appreciated. If you need more information let me know.
Click to expand...

There is indeed a Parameters collection in a querydef object, and you can
access it from code. It's best to use the Querydef execute method rather than
RunSQL - for a parameter query it's probably essential.

Dim dbs As DAO.Database
Dim qdf As DAO.Querydef
On Error GoTo Proc_Error
Set dbs = CurrentDb
strSql = strSql & "INTO [" & newTablename & "] "
strSql = strSql & "FROM [qryReceiverAnswers_Crosstab];"
Set qdf = db.CreateQuerydef("", strSQL) ' create a temp query
qdf.Parameters(0) = Me!ProjID
<as above for other parameters, you can also use
qdf.Parameters("[Enter ProjID:]")>
qdf.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
<deal with errors appropriately>
Resume Proc_Exit
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create new table in backend through VBA 2
VBA Code Help 3
Passing Parameter From Form mismatched criteria 10
export vba not working 0
Problem trying to open a recordset 5
Invalid Operation on a querydef 2
VBA works in orignal but fails in copy of database 1
Ken Snell's Multiple sheet export 2

Top