Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 6th, 2006, 12:57 PM
Authorized User
Join Date: Sep 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Parameter Problems

I have 2 different Queries: 1."Progress - Repair" and 2."Progress - REPAIRS". Query 1 has five parameters in it that I set with Code and then Query 2 uses the result of Query 1 in its selection. However I am not getting any output from Query 2. I think that I am not setting the parameters correctly in Query 1.

1."Progress - Repair" is like the parent query. I use the results of it in 2."Progress - REPAIRS".

Query 1 is kinda long so im only goin to include the HAVING/WHERE Section. I know that the SELECT and FROM are correct.

Here is the SQL for each:
1. "Progress - Repair"
HAVING (((tblFinal.PieceTypeID)=[PieceType])
AND ((NDETypes.NDEType)=[strNDEType])
AND ((tblDefectDetails.RepairIndex)="R1")
AND ((RetestTypes.RetestType)=[strWOW])
AND ((tblDefectDetails.RetestDate)>[beginDate]
And (tblDefectDetails.RetestDate)<[endDate]))

2. "Progress - REPAIRS"
SELECT [Progress - Repair - Query].PieceTypeID AS Expr1, Count([Progress - Repair - Query].WeldNumber) AS WELDS, Sum([Progress -Repair - Query].SumOfDefectLength) AS INCHES
FROM [Progress - Repair - Query]
GROUP BY [Progress - Repair - Query].PieceTypeID;

This is how I set it up in VBA:
  Dim qryREPAIRS As DAO.QueryDef
  Set qryREPAIRS = dbCurrent.QueryDefs("Progress - REPAIRS - Query")

  For intCnt = 1 To 3
      qryREPAIRS.Parameters("PieceType").Value = intPieceTypeI(intCnt)
      qryREPAIRS.Parameters("strNDEType").Value = strNDEType(intCnt)
      qryREPAIRS.Parameters("strWow").Value = strNDEWow(intCnt)
      qryREPAIRS.Parameters("beginDate").Value = dbDate
      qryREPAIRS.Parameters("endDate").Value = dbDate + 6

      Set rs = qryREPAIRS.OpenRecordset

      If Not (rs.EOF And rs.BOF) Then
        ~~ Do Something
        ~~ Do Something Else <== It Always goes to here
  Next intCnt

I Have a feeling that this is where my problem is because I can run these queries independently in the IDE and I get the correct result.

Any help that you could provide would be greatly appreciated.
Old October 10th, 2006, 01:50 PM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Where are you taking the parameters from and how?

Can you run the first query by itself to do some troubleshooting before you try to run the second query off the first?

Old October 10th, 2006, 03:54 PM
Authorized User
Join Date: Sep 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts

This is where the Parameters are comming from :

Dim dbDate As Date
  dbDate = CDate(strDateIn) <= strDateIn is a string that is sent to the routine, holding a date in the form of mm/dd/yyyy

Dim strPieceName(1 To 3) As String
  strPieceName(1) = "Top"
  strPieceName(2) = "Main Body"
  strPieceName(3) = "Bottom"
Dim intPieceTypeID(1 To 3) As Integer
  intPieceTypeID(1) = 2
  intPieceTypeID(2) = 1
  intPieceTypeID(3) = 3
Dim strNDEWow(1 To 3)
  strNDEWow(1) = "MT of MT"
  strNDEWow(2) = "UT of UT"
  strNDEWow(3) = "RT of RT"
Dim strNDEType(1 To 3)
  strNDEType(1) = "MT"
  strNDEType(2) = "UT"
  strNDEType(3) = "RT"

No matter which query I try to run it always askes for the parameters.(When i run from the IDE, when i run from VBA it says "Error, expected more parameters")

Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameter object malfunction - out parameter dash dev C# 2005 6 December 4th, 2007 12:58 PM
Problems with "SelectedValue" in Control Parameter rsearing ASP.NET 2.0 Basics 5 July 11th, 2007 09:30 AM
Problems on using parameter fever168 JSP Basics 1 March 23rd, 2007 07:34 AM
validate.asp problems and logon.asp problems p2ptolu Classic ASP Databases 0 February 16th, 2005 02:34 PM
Parameter Bhavin Crystal Reports 4 September 9th, 2004 10:47 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.