unbound field in query

[pyromaniac]

I have a form that has 2 combo boxes so far (brand and model). I need model to filter when brand is chosen and I need to pull the data for a query. when the bounded column is 1, my combo box filter works. when bounded column is 2 my query works.
Is there a way to leave it at 1 and make the query look into unbound column 2?

 

[Minty]

If the combo boxes are being used to filter results neither should be bound to to your data.

If you are talking about using the second combo column as the criteria in your query you can only bind a query to the bound combo column. The only other way would be to update a hidden text box on your form and use that as the criteria in your query.

If I've misunderstood your question, try uploading a picture with pointers, or your database with suitable explanation of how to replicate your problem.

 

[Ranman256]

No. If cbo2 is not bound, the you can't pick a model. It as to be bound.

 

[pyromaniac]

I went back and edited it to say column but you replied too quickly. I found this on a SO page that I'm going to try.

YourVariable = Me.cmbEmps.Column(2)

if that fails I will make a picture.

 

[Minty]

If you are building the query in VBA then yes, you can easily refer to the columns, as you have found the code to do above. (I assumed you where trying to apply criteria to a saved query)

You could also use a QueryDef, but coding that is more complicated if you are new to all this.

 

[pyromaniac]

I think I posted a screenshot of layout. I'm trying the following code to filter the second combobox.

Dim BrandsSource As String
    BrandsSource = "SELECT [tblModels].[Model_ID].Column(2)," & _
                   " [tblModels].[Model_Name]," & _
                   " [tblModels].[Brand_ID] " & _
                   "FROM tblModels " & _
                   "WHERE [Brand_ID] = " & Me.cboBrands.Value
    Me.cboModels.RowSource = BrandsSource
    Me.cboModels.Requery

now, it's not working but I think I'm on right path... just figuring out brackets and stuff in MS Access

 

[pyromaniac]

ok, thinking out loud... I should have been in the WHERE not the SELECT.

 

[Minty]

Nearly - but as I said you can't refer to column(anything) IN the query string itself. I also think you are confusing yourself slightly.

BrandsSource = "SELECT [COLOR="Red"][tblModels].[Model_ID].Column(2)[/COLOR],"

What are you trying to use this bit of the sql for? It makes no sense. If you are trying to restrict the model_ID to the combo then it should be in the where clause.

 

[pyromaniac]

yea, I noticed that too... Sorry, JS background. SQL confuses me:banghead:

 

[Minty]

Try switching between SQL Server and Access... It's too similar sometimes

 

[pyromaniac]

Time to admit the stupid simplicity of my error. Being base 0 and needing the ID, I was searching Column 2 which did not exist when I needed to be looking at 0.

Dim BrandsSource As String
    BrandsSource = "SELECT [tblModels].[Model_ID]," & _
                   " [tblModels].[Model_Name]," & _
                   " [tblModels].[Brand_ID] " & _
                   "FROM tblModels " & _
                   "WHERE [Brand_ID] = " & Me.cboBrands.Column(0)
    Me.cboModels.RowSource = BrandsSource
    Me.cboModels.Requery

 

[pyromaniac]

I tried adding a select all into this but get a FROM error any idea what is causing this?

Dim BrandsSource As String
If Me.cboBrands.Value = " All" Then
Me.cboModels.Value = Null
BrandsSource = "SELECT [tblModels].[Model_ID]," & _
" [tblModels].[Model_Name]," & _
" [tblModels].[Brand_ID] " & _
"FROM tblModels " & _
"UNION" & _
"SELECT 0, ' All'" & _
"FROM tblModels "
Me.cboModels.RowSource = BrandsSource
Me.cboModels.Requery
Else
Me.cboModels.Value = Null
BrandsSource = "SELECT [tblModels].[Model_ID]," & _
" [tblModels].[Model_Name]," & _
" [tblModels].[Brand_ID] " & _
"FROM tblModels " & _
"WHERE [Brand_ID] = " & Me.cboBrands.Column(0)
Me.cboModels.RowSource = BrandsSource
Me.cboModels.Requery
End If

 

[pyromaniac]

Added some spaces before quotes and an extra 0 in the select statement.

 BrandsSource = "SELECT [tblModels].[Model_ID], " & _    "[tblModels].[Model_Name], " & _    "[tblModels].[Brand_ID] " & _    "FROM tblModels " & _    "UNION " & _    "SELECT 0, ' All', 0 " & _    "FROM tblModels "