Filtering Problem

[MimiSD]

I have a combo box on my form that i would like to use to filter my form. My
form is based upon a query that lists all the information pulled from 4
different tables. The combo box i have is an unbound combo in the header of
the form.
The code i am using is:
Private Sub cboSchlSearch_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
With Me.cboSchlSearch
If IsNull(.Value) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "[SchoolName]= '" & .Value & "'"
Me.Filter = strWhere
Me.FilterOn = True
End If
End With
End Sub

I have used very similar code on a few other forms where it works fine but
for some reason on this one it doesn't. What happens when you make your
selection of what School Name you want then the form goes blank and nothing
displays. I think one thing that may be affecting it would be that the
School Name is not the primary key. but i have the key in the combo box just
not displayed. Is that what is wrong? if so how do i fix it? If not, what
else could be wrong?

 

[Klatuu]

Depends on what is in the bound column.
The .Value will return the value of the bound column. It the school name is
not in the bound column, you will most likely not get a match.

 

[MimiSD]

Ok then how do i fix it?

Depends on what is in the bound column.
The .Value will return the value of the bound column. It the school name is
not in the bound column, you will most likely not get a match.

I have a combo box on my form that i would like to use to filter my form. My
form is based upon a query that lists all the information pulled from 4
different tables. The combo box i have is an unbound combo in the header of
the form.
The code i am using is:
Private Sub cboSchlSearch_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
With Me.cboSchlSearch
If IsNull(.Value) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "[SchoolName]= '" & .Value & "'"
Me.Filter = strWhere
Me.FilterOn = True
End If
End With
End Sub

I have used very similar code on a few other forms where it works fine but
for some reason on this one it doesn't. What happens when you make your
selection of what School Name you want then the form goes blank and nothing
displays. I think one thing that may be affecting it would be that the
School Name is not the primary key. but i have the key in the combo box just
not displayed. Is that what is wrong? if so how do i fix it? If not, what
else could be wrong?

 

[Klatuu]

Change the code so it works correctly.

Actually, I need to know the properties of your combo.
What data is in which columns?
Which column is the bound column?

Ok then how do i fix it?

Depends on what is in the bound column.
The .Value will return the value of the bound column. It the school name is
not in the bound column, you will most likely not get a match.

I have a combo box on my form that i would like to use to filter my form. My
form is based upon a query that lists all the information pulled from 4
different tables. The combo box i have is an unbound combo in the header of
the form.
The code i am using is:
Private Sub cboSchlSearch_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
With Me.cboSchlSearch
If IsNull(.Value) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "[SchoolName]= '" & .Value & "'"
Me.Filter = strWhere
Me.FilterOn = True
End If
End With
End Sub

I have used very similar code on a few other forms where it works fine but
for some reason on this one it doesn't. What happens when you make your
selection of what School Name you want then the form goes blank and nothing
displays. I think one thing that may be affecting it would be that the
School Name is not the primary key. but i have the key in the combo box just
not displayed. Is that what is wrong? if so how do i fix it? If not, what
else could be wrong?

 

[MimiSD]

the first column is the primary key "SchoolID" the second column is the
"SchoolName". If i am correct the first column is labeled as column 0 and
then the second column is column 1. the properties state that the bound
column is column 1

Change the code so it works correctly.

Actually, I need to know the properties of your combo.
What data is in which columns?
Which column is the bound column?

Ok then how do i fix it?

Depends on what is in the bound column.
The .Value will return the value of the bound column. It the school name is
not in the bound column, you will most likely not get a match.

:

I have a combo box on my form that i would like to use to filter my form. My
form is based upon a query that lists all the information pulled from 4
different tables. The combo box i have is an unbound combo in the header of
the form.
The code i am using is:
Private Sub cboSchlSearch_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
With Me.cboSchlSearch
If IsNull(.Value) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "[SchoolName]= '" & .Value & "'"
Me.Filter = strWhere
Me.FilterOn = True
End If
End With
End Sub

I have used very similar code on a few other forms where it works fine but
for some reason on this one it doesn't. What happens when you make your
selection of what School Name you want then the form goes blank and nothing
displays. I think one thing that may be affecting it would be that the
School Name is not the primary key. but i have the key in the combo box just
not displayed. Is that what is wrong? if so how do i fix it? If not, what
else could be wrong?

 

[Klatuu]

Therein lies some confusion in Access. The bound column is column 1, but in
the columns reference, it is actually Column(0). As it is, you are trying to
compare the SchoolID in your combo to the SchoolName in your form. That is
why you are getting nothing.

There are two options. One would be to change the column reference so you
are passing the SchoolName to the filter.

strWhere = "[SchoolName]= '" & .Column(1) & "'"

Now, a better approach would be to use the primary key to do the search

strWhere = "[SchoolID]= '" & .Value & "'"
or if SchoolID is numeric
strWhere = "[SchoolID]= " & .Value

the first column is the primary key "SchoolID" the second column is the
"SchoolName". If i am correct the first column is labeled as column 0 and
then the second column is column 1. the properties state that the bound
column is column 1

Change the code so it works correctly.

Actually, I need to know the properties of your combo.
What data is in which columns?
Which column is the bound column?

Ok then how do i fix it?

:

Depends on what is in the bound column.
The .Value will return the value of the bound column. It the school name is
not in the bound column, you will most likely not get a match.

:

I have a combo box on my form that i would like to use to filter my form. My
form is based upon a query that lists all the information pulled from 4
different tables. The combo box i have is an unbound combo in the header of
the form.
The code i am using is:
Private Sub cboSchlSearch_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
With Me.cboSchlSearch
If IsNull(.Value) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "[SchoolName]= '" & .Value & "'"
Me.Filter = strWhere
Me.FilterOn = True
End If
End With
End Sub

I have used very similar code on a few other forms where it works fine but
for some reason on this one it doesn't. What happens when you make your
selection of what School Name you want then the form goes blank and nothing
displays. I think one thing that may be affecting it would be that the
School Name is not the primary key. but i have the key in the combo box just
not displayed. Is that what is wrong? if so how do i fix it? If not, what
else could be wrong?

 

[MimiSD]

I got it working now thanks!

Therein lies some confusion in Access. The bound column is column 1, but in
the columns reference, it is actually Column(0). As it is, you are trying to
compare the SchoolID in your combo to the SchoolName in your form. That is
why you are getting nothing.

There are two options. One would be to change the column reference so you
are passing the SchoolName to the filter.

strWhere = "[SchoolName]= '" & .Column(1) & "'"

Now, a better approach would be to use the primary key to do the search

strWhere = "[SchoolID]= '" & .Value & "'"
or if SchoolID is numeric
strWhere = "[SchoolID]= " & .Value

the first column is the primary key "SchoolID" the second column is the
"SchoolName". If i am correct the first column is labeled as column 0 and
then the second column is column 1. the properties state that the bound
column is column 1

Change the code so it works correctly.

Actually, I need to know the properties of your combo.
What data is in which columns?
Which column is the bound column?


:

Ok then how do i fix it?

:

Depends on what is in the bound column.
The .Value will return the value of the bound column. It the school name is
not in the bound column, you will most likely not get a match.

:

I have a combo box on my form that i would like to use to filter my form. My
form is based upon a query that lists all the information pulled from 4
different tables. The combo box i have is an unbound combo in the header of
the form.
The code i am using is:
Private Sub cboSchlSearch_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
With Me.cboSchlSearch
If IsNull(.Value) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "[SchoolName]= '" & .Value & "'"
Me.Filter = strWhere
Me.FilterOn = True
End If
End With
End Sub

I have used very similar code on a few other forms where it works fine but
for some reason on this one it doesn't. What happens when you make your
selection of what School Name you want then the form goes blank and nothing
displays. I think one thing that may be affecting it would be that the
School Name is not the primary key. but i have the key in the combo box just
not displayed. Is that what is wrong? if so how do i fix it? If not, what
else could be wrong?