By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,250 Members | 620 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,250 IT Pros & Developers. It's quick & easy.

leaving a combo box blank to search all records

P: 5
I'm a filry new user to Access and a novice when it comes to VBA. I have a form that contains two combo boxes. The first one is populated by a querry of all the Routes, and the second is linked to that first combo box, displaying the towns on that Route.

First query:

FROM tblTable;

Second query:

SELECT DISTINCT tblTable.Rte, tblTable.TownName
FROM tblTable
WHERE (((tblTable.Rte)=forms!frmcombo.comboRte.value));

at the bottom of this form is a command button to launch a seperate form, that corresponds to this rte and town.

I was wondering if there was a way to add an option to display all towns on this route. Either by having the user select "all", or if they leave it blank for the code to bypass the town combo box. Is this possible, and how do I begin coding it?

This is the code for the command button to launch my seperate form:
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Main_Table"
stLinkCriteria = "[Rte]=" & "'" & Me![comborte] & "' AND [TownName] = " & "'" & Me![ComboTown] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmcombo"
Exit Sub

MsgBox Err.Description
Resume Exit_Command5_Click

End Sub
Nov 13 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 5K+
P: 8,740
  1. First, you need to provide an '<ALL>' Option on the 1st Query and have it appear at the top of the Combo Drop Down.
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCT tblTable.Rte FROM tblTable
    2. UNION Select "<ALL>" From tblTable Order By
    3. tblTable.Rte
  2. Specifically check for <ALL> being selected and adjust the Row Source of the 2nd Combo Box accordingly.
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String
    3. If IsNull(Me![comboRte]) Then Exit Sub
    5. If Me![comboRte] = "<ALL>" Then
    6.   strSQL = "SELECT DISTINCT tblTable.Rte, tblTable.TownName " & _
    7.            "From tblTable"
    8. Else
    9.   strSQL = "SELECT DISTINCT tblTable.Rte, tblTable.TownName " & _
    10.            "From tblTable " & _
    11.            "WHERE (((tblTable.Rte)=forms!frmcombo.comboRte.value));"
    12. End If
    14. Me![<2nd Combo Box>].RowSource = strSQL
Nov 13 '08 #2

P: 5
Thank you for the speedy reply, but I'm running into trouble applying a union statment to the second query. I keep geeting a error stating that " the number of columns in the two selected tables or queries of a union query do not match".

The first combo box only has one column (Rte)
The second combo box has both Rte and TownName (the second column is my bound column)

After selecting the route, I want to be able to give the user the option of selecting a particular town, or All Towns on that route.

I dont really need a union statement on the first query, just the second.

I was thinking as an alternative, I could code command button in such a way that when the Town combo box is left blank it only linked the route criteria when it opens the form and when it is filled in it would link both, as it does now. Thus I could bypass that criteria, and not filter by town when its not filled in. I'm not really sure which is the best way to do it, or how to do it either way. So any advice would be greatly appreciated
Nov 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.