Searching Criteria Separted By Commas

[bpitche]

I have a text box in which I would like to enter values separated by commas. All values are equipment numbers which are all the same field in the table. Is there a way to create a sql statement and create the reports for those specific numbers?

 

[ghubbell]

I won't tell you how. Not 'cause I'm nasty but because if you have to do this in a relational database you need another table. Your table should link to your first, carry a "many" relationship to your first (Id number of some sort) and one text field. You may hate me now, but you'll love me later! Gord
ghubbell@total.net

 

[bpitche]

Isn't there a way to create a string variable and build a sql statement by searching for each comma and adding each equipment number to the sql statement?

 

[ghubbell]

Sorry, I got carried away and didn't fully absorb your request...I'll be serious now...You should use something like a list box or list boxes to select the items for report purposes. If you let users type in entries, there is nothing to stop them from typos, which would give you faulty data: A real easy way would be to add a field called Selected (True/False) to each of your items. One list box has all the items Where Selected = False, Another List box right beside has all items where selected = True. In the double-click event of LB1, run an update query that changes the item in LB1 to true. Requery both lists. When you're ready to run the report have it show all items where selected = True. In the reports On close event have it run an update query that updates any of your True selected items to false. There are multiuser issues here but this is a graphically "swell" way to do this and really very easy to build. Gord
ghubbell@total.net

 

[bpitche]

Gord,

Is it possible for you to tell me how I would go about do it? I am really interested in using it. The reports are not that important, just labels. It would make it a lot easier on the one or two people that will be using it.

Thanks much,
Brad Pitcher
The Hoover Company

 

[ghubbell]

Why Sure Brad, a pleasure, and for all others, give me an hour or so and I'll whip up some instructions!













Gord
ghubbell@total.net

 

[bpitche]

Thank you very much Gord!!

 

[ghubbell]

Here we go: Sorry, I'm a slow typer and I confuse myself!

Items table: Add 1 field Yes/no Named "Selected". Default Value for your new field is No . You could set its "Display Control" property to check box if you'd like.

A new blank form in design view. Double-click top left corner (small black square) to "pop up" the forms properties dialog. Find Other tab and set pop up to Yes. Format tab: Record Selectors: No, Navigation buttons: No, Dividing Lines: No, Border Style: Sizable, Min Max Buttons: Min enabled. Done here.

Make sure the wizard button (wand and stars) is depressed on the tool bar.
Choose a listbox on the toolbar and drag out it's shape on to the form. The wiz will ask some questions and what you want as data: from your items table: the ID# and the Description. (You can probably hide the ID# if you want). Store and remember the ID#. This list is not "bound" to anything. Name it "LstAvailable"

You could do the same exact steps again (good exercise) or cheat and copy the LB you just made by clicking once on it, copy/paste. Either way you'll have to rename the second LB "LstSelected".

While we're adding stuff to the form, choose a "command button" from the tool bar, and drag out its shape on the form. The wiz will ask you which report to open.. (you pick your report) and let's name this button "CmdReport".

Around this time click the Save button (floppy disc) on the toolbar and name your form "frmReportChoice" . Close the form.

Now we have to make a query. Choose simple query wizard and get your data from your items table. You'll need the ID# first, the description, and the "selected" field. Name this query "qryItemFalse" and we want to modify its design. In the criteria row under "selected" type in: False . Close and Save. This query is to become the row source for "LstAvailable".

In the "Database window" click on qryItemFalse, choose copy then paste and save as "qryItemTrue". Open qryItemTrue, and change the criteria from False to True . Close and Save. This query is to become the row source for "LstSelected".

Copy/Paste qryItemFalse again. Name it "qryALLUDFalse". Open it in design view. On the tool bar find the "Query type button" (left of ! ) Select "Update Query". Change your "False" criteria to True and notice we just gained a row titled "update to". Type in: False . Close and Save. This query is to change all trues to false.

Copy/Paste "qryALLUDFFalse" and name the copy "qryUDListFalse". To design view! In the criteria
under ID# Type in: [Forms]![frmReportChoice]![LstSelected] . Close and Save. This query will be used when you double-click on the "LstSelected" and will push the item back to available.

Copy/Paste "qryUDListFalse" and name the copy "qryUDListTrue". To design once more! In the criteria
under ID# Type in: [Forms]![frmReportChoice]![LstAvailable] . Remove the "True" under the selected criteria and change the "update to" from False to True. Close and Save. This query will be used when you double-click on the "LstAvailable" and will push the item in to LstSelected.

I think we're done with the queries!

Back to the form in design view:
Double-click on "LstAvailable" and its "properties" dialog should appear. Find the "Row Source" property on the "Data" tab. Remove any stuff thats in its "field" and choose or type in "qryItemFalse"

Double-click on "LstSelected" . Find the "Row Source" . Choose or type in "qryItemTrue".
Now find the "Event" tab and the event named "On Double-Click". in its "field" choose "event procedure". You'll see a small button to the right "..." click that. This opens the forms "Visual Basic" module, and will automatically label a "sub procedure" for you. In between the title and the end sub type in: Movement .Close the module for a moment, back track to "LstAvailable" and do exactly the same thing.

Close the module again and click on the top left corner of the form's screen (small black square). The dialog should now show the Forms properties. Find the event called "On UnLoad". Same as before. bla bla bla.... Movement

This time, stay in the module and paste this in anywhere on its own (not within a subs title and end sub):


Private Sub Movement()
On Error GoTo ErrMove
Dim ctl As Control
Set ctl = Me.ActiveControl
DoCmd.SetWarnings False
Select Case ctl.Name
Case "LstAvailable"
DoCmd.OpenQuery "qryUDListTrue"
Case "LstSelected"
DoCmd.OpenQuery "qryUDListFalse"
Case Else
DoCmd.OpenQuery "qryALLUDFFalse"
End Select
Me.LstAvailable.Requery
Me.LstSelected.Requery

ExitMove:
DoCmd.SetWarnings True
Exit Sub

ErrMove:
If Err.Number = 2474 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryALLUDFFalse"
Else
MsgBox Err.Number & " " & Err.Description, vbInformation, " movement error."
End If
Resume ExitMove
End Sub

Your report: In to design view if you still have any energy! Double-click on left top corner to pop up the reports properties dialog. Find the Record Source property and click on the "..." button. You may be asked if you want to make a query, Yes. Now see if your "selected" field is in the grid. If it is not, double-click or drag it from the table within the query. Type in its criteria True . Close the "query" and you're back to the report. Close and Save.

If I didn't mix up too much and there is a good chance I did (curse away!) this thing should fly. Open your form, see if all your items are in the Available list, double-click on an item and it should move to the selected list. Don't want it there? Double-click on the selected list and it should move back. When you close the form it should clean off any Trues in the table and set them to False. That's providing I didn't get too confused. If it bugs, write back and I'll fix it..... a pleasure, Gord
ghubbell@total.net