form-based query

[deetron]

I'm using a form with a combo box list to enable a form-based query.
I have customers in a list, so the query allows me to select one customer at a time to return a appropriate fields based on that customer.

Is there a way to say that if no customer name is shown in a combo box (ie. it is blank) to run a query on ALL customers?

Thanks!

 

[kevinnichols78]

This shouldn't be too hard if handled with code. Have the user select a value in the combobox and then click a button. When the button is clicked something like this will take place:


Public Sub Button_Click()
Dim dbs As Database, rst As Recordset, strSQL As String
Dim customerName as string
combobox1.setfocus
customerName = combobox1.text
if customerName = "" then
strSQL = "SELECT * FROM Table1"
else
strSQL = "Select * from table1 where customer = '"+ customerName+"'"
end if
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
'HERE YOU HAVE YOUR DATA, DO WITH IT WHAT YOU WANT.
rst.Close
End Sub


Something like that should work.

Kevin

 

[deetron]

I'm not sure if this will this work with a form-based query? I have an unbound combo boxes tied to query parameters. So the query is always expecting a Customer parameter, ie. [Forms]![myFORM]![cboCustomer]. If I need to select ALL Customers, then there gotta be a way to remove this parameter - leaving it blank will void the query.

 

[deetron]

I have added a toggle button "All" to disable cboCustomer control and select ALL customers as opposed to individual ones in a list. I have an OK button at the bottom of the form that executes the query as follows:

Private Sub cmdOK_Click()
    DoCmd.Requery
    DoCmd.OpenQuery "qryTEST", acViewNormal, acReadOnly
End Sub

Q1: For some reason DoCmd.Requery doesn't requery a datasheet when I change control fields on the form and press OK to rerun the query. Any clue what I'm doing wrong here?

Q2: Since it's a form-based query I need to remove parameter [Forms]![frmTEST]![cboCustomer] from the qryTEST when I toggle button ("All") is depressed.
I have tried the following function, which I put in On Click control of the toggle button, but with no luck

Public Function dnSelectAll(frm As Form, ctlToggle As Control)

Dim ctlData As Control
Dim dbs As Database, rst As Recordset, strSQL As String
Dim strCustomer As String

cboCustomer.SetFocus
strCustomer = cboRegion.Text

Set ctlData = frm(ctlToggle.Tag)
Set strCustomer = ctlData.Text

If ctlToggle.Value Then
' If the toggle button is depressed,
' 1) disable control; 2) run a new SELECT query
ctlData.Enabled = False
strSQL = "SELECT * FROM qryTEST"
Else
' If toggle button unpressed, restore defaults
ctlData.Enabled = True
strSQL = "SELECT * FROM qryTEST WHERE Customer = '" + strCustomer + "'"
End If

' Set dbs = CurrentDb
' Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
' rst.Close
End Function

Please.

 

[deetron]

neither code seems to work... i'm I on right track here