SQL syntax question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am getting an "object doesn't support this method or property." error when
I use the following code to build a query. The code did not throw this error
until I tired to reference the specific column in the combobox (so the cbo
reference initially did nor include the column property). Any help to
correct?

FROM tblFeedbackCollection WHERE (((Left([screenID],3))='" &
[cboCourseFilter].[column(2)] & "')
 
Does the combo box have 2 columns or 3 columns? It is zero-
based, i.e. you have column 0, column 1, ...
Geof Wyght
 
Yes, the combobox has 3 columns; I want to restrict my result set using the
three-character representation that is shown in the 2nd visible column (the
third column overall); I did just notice a post on here by one of the MVP's
who said that a function must be used in my situation since a query cannot
see any column in the combobox other than the bound (in my case hidden first
column, refrence number 0) column.

Agree?

Geof Wyght said:
Does the combo box have 2 columns or 3 columns? It is zero-
based, i.e. you have column 0, column 1, ...
Geof Wyght
-----Original Message-----
I am getting an "object doesn't support this method or property." error when
I use the following code to build a query. The code did not throw this error
until I tired to reference the specific column in the combobox (so the cbo
reference initially did nor include the column property). Any help to
correct?

FROM tblFeedbackCollection WHERE (((Left([screenID],3)) ='" &
[cboCourseFilter].[column(2)] & "')
.
Click to expand...
Click to expand...
 
Yes, the value of the combo box is the bound field.
But there isn't any rule that says that the first column (column 0) has to
be the bound column! Or that you can't have a two column (or even a one
column) row source for the combo box with the three-character field as the
bound column.

Is the combo box unbound?
Is the combo box only being used as a parameter for the query?

So try changing the rowsource for the combo box so the three-character
column is the bound column (don't forget to change the column count and the
column widths); then the WHERE clause for the query would be:

FROM tblFeedbackCollection
WHERE Left([screenID],3) ='" & Me.[cboCourseFilter] & "'"

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



bjnova said:
Yes, the combobox has 3 columns; I want to restrict my result set using the
three-character representation that is shown in the 2nd visible column (the
third column overall); I did just notice a post on here by one of the MVP's
who said that a function must be used in my situation since a query cannot
see any column in the combobox other than the bound (in my case hidden first
column, refrence number 0) column.

Agree?

Geof Wyght said:
Does the combo box have 2 columns or 3 columns? It is zero-
based, i.e. you have column 0, column 1, ...
Geof Wyght
-----Original Message-----
I am getting an "object doesn't support this method or property." error when
I use the following code to build a query. The code did not throw this error
until I tired to reference the specific column in the combobox (so the cbo
reference initially did nor include the column property). Any help to
correct?

FROM tblFeedbackCollection WHERE (((Left([screenID],3)) ='" &
[cboCourseFilter].[column(2)] & "')
.
Click to expand...
Click to expand...
Click to expand...
 
I just thought of another way...

Add a text box to the form and hide it. In the AfterUpdate event of the
combo box put:

Me.[HiddenTextBox] = [combobox].column(2)

The Where clause for the query would be:

FROM tblFeedbackCollection WHERE Left([screenID],3) ='" & Me.[HiddenTextBox]
& "'"

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


bjnova said:
Yes, the combobox has 3 columns; I want to restrict my result set using the
three-character representation that is shown in the 2nd visible column (the
third column overall); I did just notice a post on here by one of the MVP's
who said that a function must be used in my situation since a query cannot
see any column in the combobox other than the bound (in my case hidden first
column, refrence number 0) column.

Agree?

Geof Wyght said:
Does the combo box have 2 columns or 3 columns? It is zero-
based, i.e. you have column 0, column 1, ...
Geof Wyght
-----Original Message-----
I am getting an "object doesn't support this method or property." error when
I use the following code to build a query. The code did not throw this error
until I tired to reference the specific column in the combobox (so the cbo
reference initially did nor include the column property). Any help to
correct?

FROM tblFeedbackCollection WHERE (((Left([screenID],3)) ='" &
[cboCourseFilter].[column(2)] & "')
.
Click to expand...
Click to expand...
Click to expand...
 
I laughed at your sig line...! It's been many years since my last course in
Latin, but that sig really hit the old humerus...
 
If you are building the query string in VBA, you should be able to reference the
column property of the combobox.

Me.CboCourseFilter.Column(2)

Note the difference

[cboCourseFilter].[column(2)]

Drop the brackets around Column(2).


Yes, the value of the combo box is the bound field.
But there isn't any rule that says that the first column (column 0) has to
be the bound column! Or that you can't have a two column (or even a one
column) row source for the combo box with the three-character field as the
bound column.

Is the combo box unbound?
Is the combo box only being used as a parameter for the query?

So try changing the rowsource for the combo box so the three-character
column is the bound column (don't forget to change the column count and the
column widths); then the WHERE clause for the query would be:

FROM tblFeedbackCollection
WHERE Left([screenID],3) ='" & Me.[cboCourseFilter] & "'"

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

bjnova said:
Yes, the combobox has 3 columns; I want to restrict my result set using the
three-character representation that is shown in the 2nd visible column (the
third column overall); I did just notice a post on here by one of the MVP's
who said that a function must be used in my situation since a query cannot
see any column in the combobox other than the bound (in my case hidden first
column, refrence number 0) column.

Agree?

Geof Wyght said:
Does the combo box have 2 columns or 3 columns? It is zero-
based, i.e. you have column 0, column 1, ...
Geof Wyght
-----Original Message-----
I am getting an "object doesn't support this method or
property." error when
I use the following code to build a query. The code did
not throw this error
until I tired to reference the specific column in the
combobox (so the cbo
reference initially did nor include the column
property). Any help to
correct?

FROM tblFeedbackCollection WHERE (((Left([screenID],3))
='" &
[cboCourseFilter].[column(2)] & "')
.
Click to expand...
Click to expand...
Click to expand...
 
Thanks to each of you

SteveS said:
I just thought of another way...

Add a text box to the form and hide it. In the AfterUpdate event of the
combo box put:

Me.[HiddenTextBox] = [combobox].column(2)

The Where clause for the query would be:

FROM tblFeedbackCollection WHERE Left([screenID],3) ='" & Me.[HiddenTextBox]
& "'"

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


bjnova said:
Yes, the combobox has 3 columns; I want to restrict my result set using the
three-character representation that is shown in the 2nd visible column (the
third column overall); I did just notice a post on here by one of the MVP's
who said that a function must be used in my situation since a query cannot
see any column in the combobox other than the bound (in my case hidden first
column, refrence number 0) column.

Agree?

Geof Wyght said:
Does the combo box have 2 columns or 3 columns? It is zero-
based, i.e. you have column 0, column 1, ...
Geof Wyght
-----Original Message-----
I am getting an "object doesn't support this method or
property." error when
I use the following code to build a query. The code did
not throw this error
until I tired to reference the specific column in the
combobox (so the cbo
reference initially did nor include the column
property). Any help to
correct?

FROM tblFeedbackCollection WHERE (((Left([screenID],3))
='" &
[cboCourseFilter].[column(2)] & "')
.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top