List box, row source, where syntax help needed

[cowboy]

Just need help with syntax of the Where statement:
SELECT InspectionsDI.ID, InspectionsDI.[MN12] FROM InspectionsDI WHERE (need help here);

I tested this code:
Private Sub Form_Current()
Dim var1 As String
var1 = Me.Filter
End Sub

I put a watch on var1 and it was equal to: [Area] = 'GOL4'

Some of the things I tried:
WHERE InspectionsDI.[Forms!DIForm.filter]
WHERE me.filter
WHERE InspectionsDI.me.filter


WHERE InspectionsDI. & [Forms!DIForm.filter]
WHERE "InspectionsDI." & "[Forms!DIForm.filter]"
WHERE InspectionsDI.[Forms!DIForm.filter]
WHERE me!filter
WHERE InspectionsDI!me.filter

and I tried more things but obviously cant get the syntax of the where statement correct. Any help would be appreciated, thanks.

[TheShabz]

What is "Filter"? A form object? Also, your WHERE statement should be in the form of
WHERE (something) (operator) (something)
like
WHERE name = "joe"
or
WHERE age < 25

the syntax for passing in an object from a form is Forms!myFrm!myObj

[cowboy]

See how I made var1 = me.filter This takes the value of Forms!DIForm.filter
which is equal to: [Area] = 'GOL4' I am not sure if I can use that same syntax in the record source statement

What I want my WHERE statement to be is
WHERE InspectionsDI.[Area] = 'GOL4'

[TheShabz]

perhaps this will increase my understanding. Where is this SQL statement going to go?

[cowboy]

A listbox on the form DIForm under the row source of the properties

I have lots of row sources with similar code like this for a combo box
SELECT [Cover/Frame Fit].[Cover/Frame Fit] FROM [Cover/Frame Fit] ORDER BY [Cover/Frame Fit].ID;

I am just having a tough time with this WHERE statement because it is using the .filter control of the form

[TheShabz]

ahh ok. It's been a long day, I apologize for the confusion.
I think what you would end up doing is creating a new variable to hold the entire WHERE clause to pass in instead of trying to concatenate it in SQL string. Also, I assume you are planning on doing this in VBA, because I dont know another way to do it.

dim var2 as string
dim runSQL as string

var2 = "Inspection." & Me.Filter
runSQL = "SELECT InspectionsDI.ID, InspectionsDI.[MN12] FROM InspectionsDI WHERE " & var2 & ";"

see if that works. If not, I'm not sure how to proceed from there. Personally, I've never found myself trying to do what you're trying. It's a stab in the dark on my part.

[cowboy]

I was hoping there was a way to do it without using VBA so it would update by itself but if VBA is the only way to do it I will just have to put in the form on current and some afterupdates

[TheShabz]

There very well may be a way to do it without VBA. I'm just not aware of one. Perhaps others can chime in. Hope it work sout for you though.

[cowboy]

I made this work with VBA but before I mark solved just one last check anyone know how to do this with just Row Source Syntax?

[maximus]

This what I have done
1) I have a form tabe1 with a combobox and a List box.
2) I select a name in the combobox and it filters the form and the filter condition is also used in the query which the RowSource of the List Box


Private Sub Combo13_AfterUpdate()
Dim strSQl As String
Dim strFilterCondition As String
strFilterCondition = "[P_Name]='" & Combo13 & "'"
DoCmd.ApplyFilter , strFilterCondition
strSQl = "Select * From Table1 Where " & strFilterCondition
Me.List11.RowSourceType = "Table/Query"
Me.List11.RowSource = strSQl
DoCmd.Requery "List11"
End Sub

I have used a string to hold the value of the filtercondition and passed the same on to the SQL that is to become the RowSource of the ListBox. The code is used in the after update event onf the combobox. refer to attached db.

[sesproul]

What is the purpose of this line:

DoCmd.ApplyFilter , strFilterCondition

if you immediately apply the same selection criteria to the Me.List11.Rowsource? Does is limit all the other fields in the form?

[maximus]

DoCmd.ApplyFilter , strFilterCondition

This is used to filter the form. You have got it Right. strFilterCondition is used to both to filter the form and as a criteria for the sql that populates the list box.