DropDown 'all' option?

[cdoyle]

Hi,
I table that is used for options for some dropdown menus throughout my forms that are used to open reports.

On one form, I would like to have a 'all' category appear in my dropdown, but I don't need this on all the other dropdowns that are bound to this table in the database.

Is there a way to add 'all categories' to just this one dropdown that is bound to the table?

I've search the form, but not really sure what to search for, and not finding anything. I would like to keep the rest of the dropdown bound to the table, incase someone updates the content there.

 

[KenHigg]

See if post 22 here will work:

Link


ken

 

[missinglinq]

This RowSource for the combobox works for me:

SELECT [YourTableName].[YourFieldName] FROM [YourTableName] UNION SELECT "(All)" FROM [YourTableName];

 

[KenHigg]

This RowSource for the combobox works for me:

SELECT [YourTableName].[YourFieldName] FROM [YourTableName] UNION SELECT "(All)" FROM [YourTableName];

Then how do you get the 'all' thing to actually work? Don't you have to put an '*' in the equation somewhere?



ken

 

[missinglinq]

Depends on what the OP is trying to do! If you read the first post closely, the OP doesn't actually say what he's trying to accomplish, except that it has to do with running reports. You just have to use standard AfterUpdate code:

Private Sub YourComboBox_AfterUpdate()
If Me.YourComboBox = "(All)" Then
  'Tell Access what to do here for [B]All[/B] selection
End If
End Sub

Linq

 

[cdoyle]

Here's is what I have for the code on the submit button.

Private Sub cmd_addreport_Click()
On Error GoTo Err_cmd_addreport_Click


DoCmd.RunCommand acCmdSaveRecord ' saves the record.

    Select Case Me.cbo_addreports
        Case 1 'Report 1
                stDocName = "r_1"
        Case 2 'Report 3
                stDocName = "r_report2"

(for each report that I want them to be able to run)

      End Select

 DoCmd.OpenReport stDocName, acPreview


Exit_cmd_addreport_Click:
    Exit Sub

Err_cmd_addreport_Click:
    MsgBox Err.Description
    Resume Exit_cmd_addreport_Click
End Sub

So I tried the example that Ken posted, and the dropdown works, 'All' is displayed, but now my code above doesn't work anymore. I get a 'report argumement needed error.

So I tried the example that ling had, and my dropdown doesn't display anything. But if I go into the query builder, and run it from there, it shows all the items from my table, plus 'All'

What do you think I'm doing wrong in either case?

 

[ColinEssex]

The 'solutions' Db on the Access CD has an example of how to do this. (and tons more)

Col

 

[cdoyle]

OK, I got it to somewhat work with this
SELECT Report_ID, Report_Type FROM tbl_report_type UNION Select Null as AllChoice , "All" as Bogus From tbl_report_type ORDER BY Report_Type;

If I bound my dropdown to column 1 all the reports open except the one for all.
I'm guessing I'm going to have to redo/change the code I posted earlier to assign which option opens which report?

 

[cdoyle]

Got it working
I changed the source to this
SELECT Report_ID, Report_Type FROM tbl_report_type UNION Select 99 as AllChoice , "All" as Bogus From tbl_report_type ORDER BY Report_Type;

and then in my onclick of the button, for the 'all' report I changed it's case to 99.

This way, if they decide to change the name of a report or how it's displayed int he dropdown. I don't have to go in and change the code that opens the report.

 

[missinglinq]

Probably could have simply used Case Else for the "all" selection of reports.

 

[cdoyle]

oh wow, you're right that's much easier.
Geeze I wish I could remember this stuff!!!