real time query parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've created a table containing people's names, addresses, phone numbers,
email addresses and other information. I'd like to be able to create a
query/report to pull a list of specific records from this table. At the time
I run the report I like to input the names of people in the table and
generate a report showing the records for those people all on one sheet of
paper. I'd expect the list of input names woud be no more than 20 names.

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.
 
Hi Casey,

This was a great lead. I scanned it briefly. It covers several cases of
dynamic queries but I don't believe it covers the the case I'm trying to do.
I'd like to free form add names to a list and generate a report with just
those names.
 
I believe that using the methods on that page you can still do that. Instead
of using a Combo box as suggested by the author, try using a List Box to list
all of the names that you have in your Database. Then, allow multi-select
under the properties so that a user and select 0-all of the names in your
database by just clicking on the name of the person they wish to be on the
report. Then after you have all of them selected you should be able to hit
your button to apply the filter or however you have it set up to put out a
report with just those selected names. The author had information on how to
set up a list box for that on the previous tutorial named Access and SQL Part
5: More Instant Queries. I hope this helps, if not then maybe I can look into
it more closely but I think that this should be what you are looking for.
Goodluck again!
Hi Casey,

This was a great lead. I scanned it briefly. It covers several cases of
dynamic queries but I don't believe it covers the the case I'm trying to do.
I'd like to free form add names to a list and generate a report with just
those names.
I tried to do the same thing on the current database I am designing. I came
across a website that explains how to do this really well. Go to
Click to expand...
[quoted text clipped - 10 lines]
Click to expand...
 
Hey Casey,

I'll look a bit closer. Thanks. I'm not that familiar with List Boxes but
from what you've described it sounds like it will work great.

Thanks again.

Casey via AccessMonster.com said:
I believe that using the methods on that page you can still do that. Instead
of using a Combo box as suggested by the author, try using a List Box to list
all of the names that you have in your Database. Then, allow multi-select
under the properties so that a user and select 0-all of the names in your
database by just clicking on the name of the person they wish to be on the
report. Then after you have all of them selected you should be able to hit
your button to apply the filter or however you have it set up to put out a
report with just those selected names. The author had information on how to
set up a list box for that on the previous tutorial named Access and SQL Part
5: More Instant Queries. I hope this helps, if not then maybe I can look into
it more closely but I think that this should be what you are looking for.
Goodluck again!
Hi Casey,

This was a great lead. I scanned it briefly. It covers several cases of
dynamic queries but I don't believe it covers the the case I'm trying to do.
I'd like to free form add names to a list and generate a report with just
those names.
I tried to do the same thing on the current database I am designing. I came
across a website that explains how to do this really well. Go to
Click to expand...
[quoted text clipped - 10 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.
Click to expand...
Click to expand...
Click to expand...
 
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.



bbig80524 said:
Hey Casey,

I'll look a bit closer. Thanks. I'm not that familiar with List Boxes but
from what you've described it sounds like it will work great.

Thanks again.

Casey via AccessMonster.com said:
I believe that using the methods on that page you can still do that. Instead
of using a Combo box as suggested by the author, try using a List Box to list
all of the names that you have in your Database. Then, allow multi-select
under the properties so that a user and select 0-all of the names in your
database by just clicking on the name of the person they wish to be on the
report. Then after you have all of them selected you should be able to hit
your button to apply the filter or however you have it set up to put out a
report with just those selected names. The author had information on how to
set up a list box for that on the previous tutorial named Access and SQL Part
5: More Instant Queries. I hope this helps, if not then maybe I can look into
it more closely but I think that this should be what you are looking for.
Goodluck again!
Hi Casey,

This was a great lead. I scanned it briefly. It covers several cases of
dynamic queries but I don't believe it covers the the case I'm trying to do.
I'd like to free form add names to a list and generate a report with just
those names.

I tried to do the same thing on the current database I am designing. I came
across a website that explains how to do this really well. Go to
[quoted text clipped - 10 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.
Click to expand...
Click to expand...
Click to expand...
 
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.
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,
Click to expand...
[quoted text clipped - 28 lines]
Click to expand...
 
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 said:
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.
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,
Click to expand...
[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.
Click to expand...
Click to expand...

--
Casey
College Student

Message posted via AccessMonster.com
Click to expand...
 
Casey,

Problem is solved. I am querying on a number but the code is surrounding the
list items with a single quote , ' . Removing this single quote fixed the
problem. Solution does exactly what I wanted. Thanks for your help.

bbig80524 said:
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 said:
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.
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.
Click to expand...

--
Casey
College Student

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
I'm glad I could point you in the right direction and glad that everything is
working for you! Take it easy and goodluck on the rest of the project.
Casey,

Problem is solved. I am querying on a number but the code is surrounding the
list items with a single quote , ' . Removing this single quote fixed the
problem. Solution does exactly what I wanted. Thanks for your help.
The code is:
Click to expand...
[quoted text clipped - 62 lines]
Click to expand...
 
Back
Top