displaying Multiple records from report 2

[paul123456]

First off my report is a letter with the info like name and address being my records.
I have a query on a report that has the criteria set to

Between [Please enter start ID Number] And [Please enter end ID Number]

this allows the user to display the ID number selected.. What I want to do is have the user be able to type in the exact ID Numbers multiple times. for example it asks please enter id number and the user can enter mutiple IDS like 1, 234, 523, 36434, 2421, 12321, 123. so that these exact id numbers are displayed. right now with the way it is set up if they want one ID number displayed they will have to enter the beginning iD as 1 and the end ID as 1 so that individual ID number is displayed.

Can this be done?

If anyone need more explanation because its not clear please post. Thanks, PAUL

 

[scriverb]

What you have to do here is create a table that the id's are entered into by the User. When the list of id's is complete then by clicking a button your query would match to that list and select the records indicated. This would be done through an inner join between the two tables in the query.

So, create a table with an ID field. Create a new form with an entry process to enter the multiple id's either as individual records in the form or you could setup an unbound form with a subform to enter then in a scrolling subform control. Either way there will be a table with the needed id's when you are finished. Now modify your query by adding this additional table and establishing an inner join between the two tables on id numbers. You can remove the WHERE statement that is there now also. your query will now be selecting only the records with id's in your table.

If you need any further assistance with this just repost and I can help you with it. Bob Scriver

 

[SgtJarrow]

Bob is leading you donw the right path....just a suggestion though. You could create a multiselect list box, which is populated with all the CURRENT IDs in your db, and allow the user to select all the IDs they wish to see. Then, using a view report button, simply pass the selected items to the report as a critera and the report displays those IDs....

Would be a bit easier on the user than having to type in each ID, mistyping one, retyping, etc. The golden rules of KISS and give the users as little manual input as possible should be considered here.

Just my 1/50 of a dollar. -----------------------------------------
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[scriverb]

mstrmage1768: You are certainly right about providing a way to verify and make easy the entry of the id's. My next question was going to be how many records are we talking about. A list box with 5,000 id's would not really be practical. I was thinking about a subform with a combobox so that the typing of partial id gets them close and then a pick to verify the id. Something like that.

All sorts of choices. Thanks for your Suggestion. If the database is on the smallish size the list box would be appropriate. There are so many different ways of doing this. We just need to ask a few more questions. Bob Scriver

 

[SgtJarrow]

And that post is why bob is the man!

Always helpful and always thinking two steps ahead....Keep up the great work! -----------------------------------------
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[scriverb]

Flattery will get you everywhere!!!

By the way where are you from. I am in Lansing, Michigan. I like to kind of keep up on where everyone is located that I meet at this site. Love those UK guys because they are up and running when I have insomnia early in the morning. Bob Scriver

 

[SgtJarrow]

Tampa, FL.....and I know the meaning of early mornings. I like to get into the office by 6 EST or so....partly because I like the morning, partly because I like not having many others around to bother me, partly to aviod the traffic and also because I primarily deal with banking issues in European countries.

I have always valued your suggestions and comments. When I first began using this site, I was far from where I am today, and it was people like you and some of the other "experts" here that helped me learn much of what I now know. Thanks to you, the other experts, and Tek-Tips.! -----------------------------------------
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[CosmoKramer]

I agree most times it is necessary to be able to edit and verify entries, but I was able to have a parameter list entered on a dialog box be utilized by a query. If I remember correctly I got the code off of Microsoft's web site.

Copy & paste these two functions into a new module:

      '************************************************************
      'Declarations section of the module.
      '************************************************************

      Option Explicit

      '============================================================
      ' The GetToken() function defines the delimiter character.
      '============================================================

      Function GetToken (stLn, stDelim)
         Dim iDelim as Integer, stToken as String
         iDelim = InStr(1, stLn, stDelim)
         If (iDelim <> 0) Then
            stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
            stLn = Mid$(stLn, iDelim + 1)
         Else
            stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
            stLn = &quot;&quot;
         End If
         GetToken = stToken
      End Function

      '============================================================
      ' The InParam() function is the heart of this article. When
      ' the query runs, this function causes a query parameter
      ' dialog box to appear so you can enter a list of values.
      ' The values you enter are interpreted as if you
      ' had entered them within the parentheses of the In() operator.
      '============================================================
      Function InParam (Fld, Param)
         Dim stToken as String
         'The following two lines are optional, making queries
         'case-insensitive
         Fld = UCase(Fld)
         Param = UCase(Param)
         If IsNull(Fld) Then Fld = &quot;&quot;
         Do While (Len(Param) > 0)
            stToken = GetToken(Param, &quot;,&quot;)
            If stToken = LTrim$(RTrim$(Fld)) Then
               InParam = -1
               Exit Function
            Else
               InParam = 0
            End If
         Loop
      End Function

In your query, add an expression field something like this:

IDList: InParam([IDNumber],[Enter list of IDs using commas in between])

When run, the user is prompted to enter the list of IDs and only records matching the list entered should be returned.

 

[paul123456]

First off thanks for the posts people, i agree with mstrmage that people that post help me learn what i know. I do have about 20,000 records with IDs, so although mstrrmage has a good idea i think that for my situation i'm gonna go with scriverb's idea. I just got a little bit confused but nothing a little reading can't solve. If i have any questions i'll post very soon, im gonna get started on this within the next hour or so.

Thnx again for posting. Thanks, PAUL

 

[paul123456]

eww didn't see u there CosmeKramer, looks very interesting. i think i'll try that one fist.

THnx Thanks, PAUL

 

[SgtJarrow]

Awsome CosmoKramer.....Thanks! ****************************
Only two things are infinite, the
universe and human stupidity, and
I'm not sure about the former.
(Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[scriverb]

CosmoKramer: Great idea. One addition though. The new column in the query must have a criteria entry of True to select only the records idicated in the list. Otherwise, they all get returned with a value of 0 or -1.

Nice stuff though. Bob Scriver

 

[CosmoKramer]

Thanks Bob, you're right, I did leave that out.....

 

[paul123456]

You guys are getting a little hardcore for me, can you tell me were i would place the criteria entry of True to select only the records idicated in the list?

Thnx Thanks, PAUL

 

[CosmoKramer]

In the design view of your query, enter True on the Criteria row for that expression field.....

 

[paul123456]

Im getting confused by entering in the expression field? do u mean put in IDList: InParam([ID],[Enter list of IDs using commas in between]) in the criteria of the query? if so how can i had True to that?
Thanks, PAUL

 

[CosmoKramer]

No prob,

IDList: InParam([ID],[Enter list of IDs using commas in between])

goes in the Field: row,

and

True

goes in the Criteria row.

 

[paul123456]

OMG this works perfectly........thnx CosmoKramer. Thanks, PAUL