Parameter Query in VB

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have 9 parameter queries (the parameter refers to one of
three combo boxes on one of two different forms, but only
one parameter per query). What I am doing is to check if
there are records related to each query and then act on
that information. I create a record set (using a function
similiar to Q209203) and test recordcount =0 to return a
true or false.

I would rather not have to create and maintain 9 seperate
functions in VBA, but am having a problem referencing the
correct control. See following code . . .

This is how I currently have the function set (would need
9 functions). Note: strTestQuery is the name of the query
being passed into the function.
Private Function TestYear (strTestQuery as String) as
Boolean
Dim dbTest as DAO.Database
Dim qdfTest as DAO.QueryDef
Dim rstTest as DAO.Recordset
Set dbTest = CurrentDB()
Set qdfTest = dbSample.QueryDefs(strTestQuery)
qdfTest![Forms!frmYearSelect!cboYear] = Forms!
frmYearSelect!cboYear
Set rstTest = qdyTest.OpenRecordset()
If rstTest.RecordCount = 0 Then
TestYear = FALSE
Else
TestYear = TRUE
EndIf
rstTest.Close
qdfTest.Close
dbTest.Close
End Function

What I would like to do is to pass in an extra argument or
two so I can use function for the testing. The problem
comes in on the line . . . qdfTest![strControl] =
strControl.
Private Function TestYear (strTestQuery as String,
strControl as String) as Boolean
Dim dbTest as DAO.Database
Dim qdfTest as DAO.QueryDef
Dim rstTest as DAO.Recordset
Set dbTest = CurrentDB()
Set qdfTest = dbSample.QueryDefs(strTestQuery)
qdfTest![strControl] = strControl
Set rstTest = qdfTest.OpenRecordset()
If rstTest.RecordCount = 0 Then
TestYear = FALSE
Else
TestYear = TRUE
EndIf
rstTest.Close
qdfTest.Close
dbTest.Close
End Function

Any help or guidance would be appreciated; even if it can
not be done. I can provide more information if requested.

Thanks in advance.

John
 
John wrote:
[snip]
What I would like to do is to pass in an extra argument or
two so I can use function for the testing. The problem
comes in on the line . . . qdfTest![strControl] =
strControl.
Private Function TestYear (strTestQuery as String,
strControl as String) as Boolean
Dim dbTest as DAO.Database
Dim qdfTest as DAO.QueryDef
Dim rstTest as DAO.Recordset
Set dbTest = CurrentDB()
Set qdfTest = dbSample.QueryDefs(strTestQuery)
qdfTest![strControl] = strControl
Set rstTest = qdfTest.OpenRecordset()
If rstTest.RecordCount = 0 Then
TestYear = FALSE
Click to expand...
[snip]

Off the top of my head, I think you can do it this way:

qdfTest.Parameters(strControl) = Eval(strControl)
Set rstTest = qdfTest.OpenRecordset()
TestYear = (rstTest.RecordCount > 0)
 
Thanks,

Worked great.
-----Original Message-----
John wrote:
[snip]
What I would like to do is to pass in an extra argument or
two so I can use function for the testing. The problem
comes in on the line . . . qdfTest![strControl] =
strControl.
Private Function TestYear (strTestQuery as String,
strControl as String) as Boolean
Dim dbTest as DAO.Database
Dim qdfTest as DAO.QueryDef
Dim rstTest as DAO.Recordset
Set dbTest = CurrentDB()
Set qdfTest = dbSample.QueryDefs(strTestQuery)
qdfTest![strControl] = strControl
Set rstTest = qdfTest.OpenRecordset()
If rstTest.RecordCount = 0 Then
TestYear = FALSE
Click to expand...
[snip]

Off the top of my head, I think you can do it this way:

qdfTest.Parameters(strControl) = Eval(strControl)
Set rstTest = qdfTest.OpenRecordset()
TestYear = (rstTest.RecordCount > 0)
Click to expand...
 
Back
Top