Option Group / Combo Box with Integers (1 Viewer)

Hi ! I'm new in VBA and Access. I'm trying to create a Equipment database. I want to filter the search based on a search box, a combo box and an option group box. I already build the search box and the option group box for one parameter. However, when I try to create either a combo box or an option group to a numeric parameter I get an error message saying that I have a syntax error on the FROM clause. Is there anyone who can help me?
The code that I'm using to the combo box is the following :

Private Sub cc_deq_combo_box_AfterUpdate()


Dim cc_deq As String

cc_deq = "SELECT LISTA_EQ.ID, LISTA_EQ.[No INV], LISTA_EQ.DESCRICAO, LISTA_EQ.MARCA, LISTA_EQ.MODELO, LISTA_EQ.[Num_SERIE / MATRICULA] " _
& "FROM LISTA_EQ" _
& "WHERE ([CC_DEQ] = " & Me.cc_deq_combo_box & ")" _
& "ORDER BY [No INV]"

Me.subInfoResults.Form.RecordSource = cc_deq
Me.subInfoResults.Form.Requery


End Sub


Thanks,

Argel

Don't recreate a record source, just filter.

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

You're missing spaces in the SQL-string, marked wit red, I've added them.

Ranman256 said:

Don't recreate a record source, just filter.

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

Code:Copy to clipboard

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

Click to expand...

Thanks for the help but that's not what I really want... I want that the subform changes automatically with the combo box.

JHB said:

You're missing spaces in the SQL-string, marked wit red, I've added them.

Code:Copy to clipboard

cc_deq = "SELECT LISTA_EQ.ID, LISTA_EQ.[No INV], LISTA_EQ.DESCRICAO,  LISTA_EQ.MARCA, LISTA_EQ.MODELO, LISTA_EQ.[Num_SERIE / MATRICULA] " _
    & "FROM LISTA_EQ [B][COLOR=Red]"[/COLOR][/B] _
    & "WHERE ([CC_DEQ] = " & Me.cc_deq_combo_box & ") [B][COLOR=red]" [/COLOR][/B]_
    & "ORDER BY [No INV]"

Click to expand...

Thank you! Problem solve! Silly mistake ...

You were halfway there using a string for the SQL.
If you had used a Debug.Print cc_deq you would have seen your error.