Re: real time query parameters

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

The code is:

Option Compare Database
Option Explicit

Private Sub cmdApply_Click()
Dim varItem As Variant
Dim strJerNum As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptPlayerInfo") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string from lstJerNum listbox
For Each varItem In Me.lstJerNum.ItemsSelected
strJerNum = strJerNum & ",'" & Me.lstJerNum.ItemData(varItem) _
& "'"
Next varItem
If Len(strJerNum) = 0 Then
strJerNum = "Like '*'"
Else
strJerNum = Right(strJerNum, Len(strJerNum) - 1)
strJerNum = "IN(" & strJerNum & ")"
End If
' Build Filter String
strFilter = "[JerNum]" & strJerNum
' Apply the filter and switch it on
With Reports![rptPlayerInfo]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemove_Click()
On Error Resume Next
' Switch the filter off
Reports![rptPlayerInfo].FilterOn = False
End Sub

"Casey via AccessMonster.com" wrote:

Double check your VBA coding. Do all of your variables match? If you want, go
ahead and post your VBA code here and I can look through it, although I am
not expert on this matter I may be able to debug it to help you out, either
that or someone else in my office might be able to help me out come Friday.
The most common result of this error is when variable types do not match
somewhere along the way.

bbig80524 wrote:
Hi Casey,

I adopted the code for the list dialog box to my application. It almost
works. I am getting an error when I try to run the filter: "Data Type
Mismatch in Criteria Expression". I've looked at everything I can think of.
The list is a query from the same table as the report so I'm confused about
the data type mismatch. I don't know where this would be defined other than
in field tables.

Any ideas?

Thanks.

Hey Casey,

[quoted text clipped - 28 lines]
I understand I could do this with a custom form but it isn't clear how this
would work. Any help you could provide would be appreciated.

--
Casey
College Student

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200604/1

.

Relevant Pages

  • RE: Set Filter from Startup form to another form
    ... Private Sub Form_Load ... Dim strPrg As String ... Private Function StartProgramAs String ... Now all that is necessary is to set the filter for each form where you want ...
    (microsoft.public.access.formscoding)
  • RE: Query form coding
    ... I have tried to include the query when using the wizard to design the report ... Dim strSource As String ... ' Remove Filter ...
    (microsoft.public.access.formscoding)
  • Re: Filter for Report not working?????
    ... Do I need to release the filter or close the TableFame? ... contFilter DynArrayString ... Your first example based on ContractNo ... I decided to go with a table in PRIV for the report. ...
    (comp.databases.paradox)
  • RE: Query form coding
    ... When I select a name the report displays with only the commas from ... Dim strSource As String ... ' Remove Filter ... For Each varItem In Me.namelst.ItemsSelected ...
    (microsoft.public.access.formscoding)
  • Re: Filter for Report not working?????
    ... cno2, cField string ... contFilter DynArrayString ... I surround the value with quotes to make it a string otherwise the filter ... I decided to go with a table in PRIV for the report. ...
    (comp.databases.paradox)