Calling for parameter that doesn't exist anymore

[rankhornjp]

I have a form that list people who are having a drug test.



"Drug Test" is a tab(page) on the "Main" form.
[TestFilter] is a text box

The orginal code looked like this.

 
Private Sub TestFilter_Exit()
Dim sql As String
 
sql = "SELECT TestResults.ID, People.ID, People.pLname AS [Last Name], People.pFname as [First Name], People.pDOB as [DOB], Tests.tDescription as [Test], [classes]![cDate] AS [Scheduled], TestResults.rResults as [Results] " _
    & "FROM qryGetScheduledDrugTests " _
    & "WHERE (((IIf([cDate] = [TestFilter], Yes, No)) = Yes)) " _
    & "ORDER BY People.pLname;"
 
Me.lstTestList.RowSource = sql
End Sub

This worked fine, except you had to put the date in 3 different places cause "qryGetScheduledDrugTests" uses [FromDate] and [ThruDate] as part of the criteria and they are located on the "Reports" tab of the "Main" form. So, I decided that having the extra [TestFilter] box on the form was unnecessary and made the form less intuitive so I deleted it and changed my code to this:

 
Private Sub DrugTest_Click()
Dim sql As String
 
sql = "SELECT TestResults.ID, People.ID, People.pLname AS [Last Name], People.pFname as [First Name], People.pDOB as [DOB], Tests.tDescription as [Test], [classes]![cDate] AS [Scheduled], TestResults.rResults as [Results] " _
    & "FROM qryGetScheduledDrugTests " _
    & "ORDER BY People.pLname;"
 
Me.lstTestList.RowSource = sql
End Sub

Now everytime the the "Main" form loads/refreshes or I switch to the "Drug Test" tab a pop up box asks me for the value of the [TestFilter] parameter. I have searched the whole form, by looking at the property sheet, and all the code, using ctrl+f, for [TestFilter] and came up empty. I don't know why it thinks it's still there.

I have tried adding back [TestFilter] and then changing it to visible="No". This stops the pop up, but then my list wont populate.

If I add a date value to the pop-up the list will populate the test that are equal to the value in the pop-up.


Any suggestions

[orange]

Post the sql for this query
qryGetScheduledDrugTests

[rankhornjp]

 
SELECT DISTINCT [plname] & ", " & [pFname] AS client, People.pDOB, Tests.tDescription, [classes]![cDate] AS rSheduled, Sysvars.FromDate, Sysvars.ThruDate, Classes.cDescription, Classes.CDate, Classes.cTime, Sysvars.ShowInactive, People.pInactive, EnrollmentHeader.ReferralOffice, EnrollmentHeader.ReferralName, People.pFname, People.pLname, People.ID, TestResults.ID, TestResults.rResults
FROM Sysvars, EnrollmentHeader INNER JOIN (((TestResults INNER JOIN Tests ON TestResults.tID = Tests.ID) INNER JOIN People ON TestResults.pID = People.ID) INNER JOIN Classes ON TestResults.cID = Classes.ID) ON (EnrollmentHeader.ProgID = Classes.ProgID) AND (EnrollmentHeader.pID = People.ID)
WHERE (((IIf([cdate]>=[FromDate] And [cdate]<=[thrudate],Yes,No))=Yes) AND ((EnrollmentHeader.DateCompleted) Is Null) AND ((EnrollmentHeader.Terminated) Is Null))
ORDER BY Classes.cDescription;

[rankhornjp]

I deleted the "Drug Test" tab and started over. It works fine now. Using this code:

Private Sub DrugTest_Click()
Dim sql As String
 
sql = "SELECT TestResults.ID, People.ID, People.pLname AS [Last Name], People.pFname as [First Name], People.pDOB as [DOB], Tests.tDescription as [Test], [classes]![cDate] AS [Scheduled], TestResults.rResults as [Results] " _
    & "FROM qryGetScheduledDrugTests " _
    & "ORDER BY People.pLname;"
 
Me.lstTestList.RowSource = sql
End Sub

Not sure I "Solved" anything. Only thing I can gues is it was an error and the [TestFilter] was a ghost in the system.....