form-based query

deetron

New Member
Joined
Dec 17, 2004
Messages
43
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

kevinnichols78

Board Regular
Joined
Aug 30, 2004
Messages
112
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

New Member
Joined
Dec 17, 2004
Messages
43
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. :confused:
 

deetron

New Member
Joined
Dec 17, 2004
Messages
43
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:

Code:
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

:rolleyes: Please.
 

Forum statistics

Threads
1,147,846
Messages
5,743,521
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top