Full Version: ComboBox to filter report includes "ALL"
UtterAccess Forums > Microsoft® Access > Access Forms
USMCdBA
I need to generate a report that will select individual parts or ALL parts. I did this before with a value list as the record source and a tip from Duane Hookum in using the Nz function, but this time, my record source MUST be the partList table. How can I select "ALL" the partListIDs as the report criteria yet have the flexibility to select only one of them? Here is the code I have tried, but it kontinues giving me an "Extra )" error when I click on the button to preview the report.
I have at this point, converted my table values to a value list for the combo box[cboZeroQtySelectr] however, as this is only in development, it is a small list and when this goes up for production the actual list of parts could exceed 200 line items -- and every time a new part gets added, I don't relish the thought of having to re-build the value list.:
1;"test-1";"Transit Case, Laptop Computer";
2;"test-2";"Computer, Laptop IBM-T43";
3;"test-3";"Transit Case, UPS";
4;"test-4";"Uninterruptible Power Supply";
5;"test-5";"Transit Case, Network Printer";
6;"test-5";"Printer, Network HP4550N";
7;"test-6";"Transit Case, 1TB Hard Drive";
8;"test-7";"1TeraByte External Drive";
;"ALL PARTS";"All Parts";
Private Sub cmdGoBackFillrpt_Click()
On Error GoTo Err_cmdGoBackFillrpt_Click
Dim stDocName, strWare As String
stDocName = "IOWv2(ZeroQTYs)"
strWare = "1=1 "

If Nz(Me.cboZeroQtySelectr, "ALL PARTS") <> "ALL PARTS" Then
strWare = strWare & "AND [HWlstID] = " & Me.cboZeroQtySelectr
End If

DoCmd.OpenReport stDocName, acPreview, , strWare
Exit_cmdGoBackFillrpt_Click:
Exit Sub
Err_cmdGoBackFillrpt_Click:
MsgBox Err.Description
Resume Exit_cmdGoBackFillrpt_Click

End Sub
I hope I have given enough info to follow clearly.
Thanks for looking,.
Kevin
norie
Kevin
ouldn't you programmatically build the value list from the table, adding in the ALL option?
USMCdBA
I'm not sure what you mean: Have code in the form's OnLoad event that runs a query on the table, adds the option of "All" and populates the value list of the combo box? I'm not sure I would know where to start with that. The [HWlstID] is an autonumber LongInteger, and is the PK of lstPartstbl.
- Kevin --
norie
Kevin
hat's what I meant.
You could loop through the recordset and create a string for the value list, then set the Value List property.
That is possible, but I've just remembered another technique I saw recently.
I think it may have involved a UNION query.
I'll see if I can find the thread and post a link.
fkegley
You need a SELECT statement that selects the fields you want in the combo box. PartID and PartDescription, I guess. Then to put the "(All)" you would need something like this:
SELECT PartsTableName.PartID, PartsTableName.PartDescription FROM PartsTableName
UNION
SELECT 0 As PartID, "(All Parts)" As PartDescription FROM PartsTableName
Order SELECT statement that selects the fields you want in the combo box. PartID and PartDescription, I guess. Then to put the "(All)" you would need something like this:
SELECT PartsTableName.PartID, PartsTableName.PartDescription FROM PartsTableName
UNION
SELECT 0 As PartID, "(All Parts)" As PartDescription FROM PartsTableName
ORDER BY PartID;
Base the report on a query that uses the combo box entries as its criteria values.
Field:PartID....................................form reference to combo box
Criteria: form reference to combo box
Or:...............................................0
This will tell the report to fetch all those where the form reference is not 0, if it is 0 then all will be fetched.
USMCdBA
Thanks nonie. Coding is one of my weaker strengths --I hate typing -----its all so cryptic. I kept thinking there was a way to have the query behind the report sort out the null combo box detail --but no.
norie
Kevin
heck Frank's post, that's exactly what I was referring to. frown.gif
USMCdBA
Norie / Frank,
I am doing just that. Thus far, I've gotten the combo box to display the ALL as needed however, upon execution both through the preview report button and directly running the query, no records are returned. There are records and the report works when the individual partIDs are selected in the combo box. I will continue and see what's what where. Thank.
- Kevin --
fkegley
The value that represents "ALL" in the combo box needs to be on the OR row.
USMCdBA
Frank,
I have folled as instructed, but the value 0 brings up no records. I thought for a moment and replaced the ) with <>0 and all the records came up, unfortunately regardless of the combo box setting, ALL the records come up using <> on the Or: line of the criteria row.
fkegley
Here's an example:
USMCdBA
OK Frank,
I see what went wrong now. When you said:
ield:PartID....................................form reference to combo box
Criteria: form reference to combo box
Or:...............................................0
I thought you were stuttering and I didn't realize that the form reference to the combo box was actually an output column of the query. In any event, I have applied and tested this solution: works as needed. Thank you for your patience.
Regards,
-- Kevin
fkegley
I see. I wish I could take credit for the technique, but Peter Schroeder and niesz gave me the idea in the first place. I think it is pretty "cool".
You're welcome. I am glad I could help.
USMCdBA
Yes, it is the dilemma we all have -- Give credit where credit is due... I like to include source data in comments in code, but for queries, form controls & such -- unless I name for example this last "include ALL' combo box
cboFrankKegleyMemorialALLinclusiveUAinspiredSelector , there is no method.
Although in thinking about it, I do have an idea about adding an admin table with fields showing a problem, solution, solutionsource, where & when applied. This way, a quick form could be displayed showing all the people involed in keeping the database running at peak performance...Of course, then we'ld have to hire someone to record and hold that data -- then a weekly report on the new data --then the monthly input data to the client would only reverberate with "WHY do you need help?" The natural answer is 'Because its a Microsoft Product -- and a dxxxxx good one too!'
Its a vicious circle
fkegley
You mean you're not going to name the combo box after me? I would rather it weren't a Memorial however as I am very much alive! laugh.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.