Funky Syntax Error

[TheDrescher]

Hey all,

I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

 

[Douglas J. Steele]

Don't know whether it's the issue, but you appear to be inconsistent. In one
place, you've got Fld Category (with a space), in the other, you've got
FldCategory (no space). Which is it?

If there is a space in the name, you need to put square brackets around the
field name:

Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & _
"WHERE [Fld Category] = """ & Nz(Me.Category, "") & """"

 

[Marshall Barton]

I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

 

[TheDrescher]

Doug,

Sorry for the confusion, there is no space in the name, the field is simply
'Category'. With the space in I get the error from the original post. With
it shortened to
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE FldCategory =
""" & Nz(Me.Category, "") & """"
I recieve this notice:
The record source 'SELECT * FROM tblSubCats WHERE FldCategory = "1"
specified on this form or report does not exist
I've checked my spelling to make sure that wasn't an issue, is there
something else I'm missing? Thanks!

Don't know whether it's the issue, but you appear to be inconsistent. In one
place, you've got Fld Category (with a space), in the other, you've got
FldCategory (no space). Which is it?

If there is a space in the name, you need to put square brackets around the
field name:

Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & _
"WHERE [Fld Category] = """ & Nz(Me.Category, "") & """"

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Hey all,

I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories).
I've
created the required tables (named Categories and SubCats) and have set
the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld
Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

.

 

[TheDrescher]

Marshall,

Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

 

[Marshall Barton]

It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?
--
Marsh
MVP [MS Access]

Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

 

[TheDrescher]

Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?
--
Marsh
MVP [MS Access]

Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

.

 

[Marshall Barton]

That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.
--
Marsh
MVP [MS Access]

Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

 

[Dirk Goldgar]

That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

I would guess that would work, but I see no point in the Nz(). If
Me.Category is Null, then

"Category = """ & Nz(Me.Category, "") & """"

will give the exact same result as

"Category = """ & Me.Category & """"

I notice in an earlier post that a possible value for Category is "1". To
TheDrescher: Is Category a numeric field, or is it a text field? Your code
implies that it's a text field, but if it's numeric, then this is more
appropriate:

Me.SubCatBox.RowSource = _
"SELECT * FROM SubCats WHERE Category = " & _
Nz(Me.Category, -1)

The Nz() in this case is to ensure that the statement doesn't fail if
Me.Category is Null, since now we don't have quotes around the value.

 

[TheDrescher]

Marshall,

It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.
--
Marsh
MVP [MS Access]

Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

.

 

[TheDrescher]

Dirk,

Originally it was a text field listing specific categories (EX: Billing,
General Inquiry, etc). Per your post I tried the code you suggested and
changed a couple of the category fields in the SubCats table to their
correspoding numerical values (by ID in the categories table). On testing, I
recieved this: 'Data type mismatch in criteria expression'. I get this
regardless of whether the category is a number or text.

 

[Marshall Barton]

It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

"the value I entered in 'Category' is invalid for the field"
does not sound like a a query error message. Maybe "it"
does not refer to the query.

If the message was from the query and said somthing about a
data type mismatch, then see Dirk's reply. As Dirk said,
the data type (number, text, etc) is essential to getting
the query to work,

If the message appeared when you tried to select an item in
the list, please provide more details.

The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

Now you are losing me. It doesn't make sense to play around
with the BoundColumn property. It needs to be exactly what
your program requires it to be. Most likely it should be
the number that corresponds to the ID field in the query's
field list. If BoundColumn is 1, then I would expect the
query to be:
"SELECT ID, Category FROM . . .

--
Marsh
MVP [MS Access]

That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.

Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

:
It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

 

[TheDrescher]

Marshall,

The latter is correct. I have Dirk's code in the After Update section for
the 'Categories' field. That information pulls up fine in form view, but I
get the error message when I click on the SubCatBox field to select the
corresponding subcategory. This is when I get the "data type mismatch in
criteria expression" notification. This error shows up regardless of whether
the data in the Category field of the SubCats table is text or numerical.

It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

"the value I entered in 'Category' is invalid for the field"
does not sound like a a query error message. Maybe "it"
does not refer to the query.

If the message was from the query and said somthing about a
data type mismatch, then see Dirk's reply. As Dirk said,
the data type (number, text, etc) is essential to getting
the query to work,

If the message appeared when you tried to select an item in
the list, please provide more details.

The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

Now you are losing me. It doesn't make sense to play around
with the BoundColumn property. It needs to be exactly what
your program requires it to be. Most likely it should be
the number that corresponds to the ID field in the query's
field list. If BoundColumn is 1, then I would expect the
query to be:
"SELECT ID, Category FROM . . .

--
Marsh
MVP [MS Access]

That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.


TheDrescher wrote:
Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

:
It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

.

 

[Dirk Goldgar]

Dirk,

Originally it was a text field listing specific categories (EX: Billing,
General Inquiry, etc). Per your post I tried the code you suggested and
changed a couple of the category fields in the SubCats table to their
correspoding numerical values (by ID in the categories table). On
testing, I
recieved this: 'Data type mismatch in criteria expression'. I get this
regardless of whether the category is a number or text.

I don't understand exactly what you did, but I didn't intend for you to
change any data, nor the design of any table. The issue is not whether the
Category field happens to be containing a number, but whether it is
*defined* as a number or not. I think we need to know more about how you
have this set up.

What is the recordsource of your form? What are the names and data types --
as shown in table design view -- of the fields in that table?

What are the names and data types -- as shown in table design view -- of the
fields in table SubCats?

What are the Row Source, Control Source, and Bound Column properties of the
Category combo box (or list box, if that's what type of control it is) on
the form?

What are the Control Source and Bound Column properties of the SubCatBox
combo box (or list box, if that's what type of control it is) on the form?

 

[Marshall Barton]

Post the SubCatBox control's RowSource query along with the
ControlSource, BoundColumn, ColumnCount and ColumnWidths
properties. Also provide the data type of each field in the
row source query and the box's control source field (in its
table).
--
Marsh
MVP [MS Access]

The latter is correct. I have Dirk's code in the After Update section for
the 'Categories' field. That information pulls up fine in form view, but I
get the error message when I click on the SubCatBox field to select the
corresponding subcategory. This is when I get the "data type mismatch in
criteria expression" notification. This error shows up regardless of whether
the data in the Category field of the SubCats table is text or numerical.

It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

"the value I entered in 'Category' is invalid for the field"
does not sound like a a query error message. Maybe "it"
does not refer to the query.

If the message was from the query and said somthing about a
data type mismatch, then see Dirk's reply. As Dirk said,
the data type (number, text, etc) is essential to getting
the query to work,

If the message appeared when you tried to select an item in
the list, please provide more details.

The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

Now you are losing me. It doesn't make sense to play around
with the BoundColumn property. It needs to be exactly what
your program requires it to be. Most likely it should be
the number that corresponds to the ID field in the query's
field list. If BoundColumn is 1, then I would expect the
query to be:
"SELECT ID, Category FROM . . .

:
That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.


TheDrescher wrote:
Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

:
It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

 

[TheDrescher]

SubCatBox Controls:
RowSource: SELECT SubCats.ID, SubCats.SubCategory FROM SubCats ORDER BY
[SubCategory];
ControlSource: SubCategory
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

SubCats Table:
Column 1, "ID", numerical
Column 2, "SubCategory", text

There is a thrid column in the SubCats table which has the Category listed
as text as well, but I didn't include it in the SubCatsBox listing

Post the SubCatBox control's RowSource query along with the
ControlSource, BoundColumn, ColumnCount and ColumnWidths
properties. Also provide the data type of each field in the
row source query and the box's control source field (in its
table).
--
Marsh
MVP [MS Access]

The latter is correct. I have Dirk's code in the After Update section for
the 'Categories' field. That information pulls up fine in form view, but I
get the error message when I click on the SubCatBox field to select the
corresponding subcategory. This is when I get the "data type mismatch in
criteria expression" notification. This error shows up regardless of whether
the data in the Category field of the SubCats table is text or numerical.

TheDrescher wrote:
It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

"the value I entered in 'Category' is invalid for the field"
does not sound like a a query error message. Maybe "it"
does not refer to the query.

If the message was from the query and said somthing about a
data type mismatch, then see Dirk's reply. As Dirk said,
the data type (number, text, etc) is essential to getting
the query to work,

If the message appeared when you tried to select an item in
the list, please provide more details.


The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

Now you are losing me. It doesn't make sense to play around
with the BoundColumn property. It needs to be exactly what
your program requires it to be. Most likely it should be
the number that corresponds to the ID field in the query's
field list. If BoundColumn is 1, then I would expect the
query to be:
"SELECT ID, Category FROM . . .


:
That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.


TheDrescher wrote:
Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

:
It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

.

 

[Marshall Barton]

Since the Category field in the SubCats table is a text
field (strange, it should be the category id), you need to
extract that from the combo box's list.

I now think your code should be:

Me.SubCatBox.RowSource = "SELECT ID, SubCategory FROM
SubCats WHERE Category=""" & Me.Category.Column(1) & """"

If you had the category id in the SubCats table then it
would have been as Dirk said. The reason you should use the
category id is because you can change the category text in
the category table without having to also change it in the
SubCats table.
--
Marsh
MVP [MS Access]

SubCatBox Controls:
RowSource: SELECT SubCats.ID, SubCats.SubCategory FROM SubCats ORDER BY
[SubCategory];
ControlSource: SubCategory
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

SubCats Table:
Column 1, "ID", numerical
Column 2, "SubCategory", text

There is a thrid column in the SubCats table which has the Category listed
as text as well, but I didn't include it in the SubCatsBox listing

Post the SubCatBox control's RowSource query along with the
ControlSource, BoundColumn, ColumnCount and ColumnWidths
properties. Also provide the data type of each field in the
row source query and the box's control source field (in its
table).

The latter is correct. I have Dirk's code in the After Update section for
the 'Categories' field. That information pulls up fine in form view, but I
get the error message when I click on the SubCatBox field to select the
corresponding subcategory. This is when I get the "data type mismatch in
criteria expression" notification. This error shows up regardless of whether
the data in the Category field of the SubCats table is text or numerical.

:>
TheDrescher wrote:
It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

"the value I entered in 'Category' is invalid for the field"
does not sound like a a query error message. Maybe "it"
does not refer to the query.

If the message was from the query and said somthing about a
data type mismatch, then see Dirk's reply. As Dirk said,
the data type (number, text, etc) is essential to getting
the query to work,

If the message appeared when you tried to select an item in
the list, please provide more details.


The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

Now you are losing me. It doesn't make sense to play around
with the BoundColumn property. It needs to be exactly what
your program requires it to be. Most likely it should be
the number that corresponds to the ID field in the query's
field list. If BoundColumn is 1, then I would expect the
query to be:
"SELECT ID, Category FROM . . .


:
That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.


TheDrescher wrote:
Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

:
It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

.

 

[TheDrescher]

Marsh,

The code seems to go in but when I select the category it pulls up the
..Column(1) segment of the code and states Compile Error: Method or data
member not found. This code is all on one line, correct? when I tried to
bring it over in two, it gave me syntax errors in VBA

Since the Category field in the SubCats table is a text
field (strange, it should be the category id), you need to
extract that from the combo box's list.

I now think your code should be:

Me.SubCatBox.RowSource = "SELECT ID, SubCategory FROM
SubCats WHERE Category=""" & Me.Category.Column(1) & """"

If you had the category id in the SubCats table then it
would have been as Dirk said. The reason you should use the
category id is because you can change the category text in
the category table without having to also change it in the
SubCats table.
--
Marsh
MVP [MS Access]

SubCatBox Controls:
RowSource: SELECT SubCats.ID, SubCats.SubCategory FROM SubCats ORDER BY
[SubCategory];
ControlSource: SubCategory
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

SubCats Table:
Column 1, "ID", numerical
Column 2, "SubCategory", text

There is a thrid column in the SubCats table which has the Category listed
as text as well, but I didn't include it in the SubCatsBox listing

Post the SubCatBox control's RowSource query along with the
ControlSource, BoundColumn, ColumnCount and ColumnWidths
properties. Also provide the data type of each field in the
row source query and the box's control source field (in its
table).


TheDrescher wrote:
The latter is correct. I have Dirk's code in the After Update section for
the 'Categories' field. That information pulls up fine in form view, but I
get the error message when I click on the SubCatBox field to select the
corresponding subcategory. This is when I get the "data type mismatch in
criteria expression" notification. This error shows up regardless of whether
the data in the Category field of the SubCats table is text or numerical.

:>
TheDrescher wrote:
It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

"the value I entered in 'Category' is invalid for the field"
does not sound like a a query error message. Maybe "it"
does not refer to the query.

If the message was from the query and said somthing about a
data type mismatch, then see Dirk's reply. As Dirk said,
the data type (number, text, etc) is essential to getting
the query to work,

If the message appeared when you tried to select an item in
the list, please provide more details.


The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

Now you are losing me. It doesn't make sense to play around
with the BoundColumn property. It needs to be exactly what
your program requires it to be. Most likely it should be
the number that corresponds to the ID field in the query's
field list. If BoundColumn is 1, then I would expect the
query to be:
"SELECT ID, Category FROM . . .


:
That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.


TheDrescher wrote:
Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

:
It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.
.

.

 

[Marshall Barton]

You really need to use precise wording. A field is a column
in a table or query. The things on forms and reports are
controls. Your "box" is either a single select list box or
a combo box, but you have never said which. And please use
the exact names of the fields and controls without
generalizing a control by using its control source (bound
field) instead of its Name property.

And when you are having troouble with code, we really need
to see a Copy/Paste of the code. It is rare when the code
we suggest can be copied and pasted and then expected to
work as is. You need to study it to understand what it's
doing and why the responder thought is would help solve a
problem.

In this case, it sounds like the category "box" is named
something other than Category.
--
Marsh
MVP [MS Access]

The code seems to go in but when I select the category it pulls up the
.Column(1) segment of the code and states Compile Error: Method or data
member not found. This code is all on one line, correct? when I tried to
bring it over in two, it gave me syntax errors in VBA

Since the Category field in the SubCats table is a text
field (strange, it should be the category id), you need to
extract that from the combo box's list.

I now think your code should be:

Me.SubCatBox.RowSource = "SELECT ID, SubCategory FROM
SubCats WHERE Category=""" & Me.Category.Column(1) & """"

If you had the category id in the SubCats table then it
would have been as Dirk said. The reason you should use the
category id is because you can change the category text in
the category table without having to also change it in the
SubCats table.

SubCatBox Controls:
RowSource: SELECT SubCats.ID, SubCats.SubCategory FROM SubCats ORDER BY
[SubCategory];
ControlSource: SubCategory
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

SubCats Table:
Column 1, "ID", numerical
Column 2, "SubCategory", text

There is a thrid column in the SubCats table which has the Category listed
as text as well, but I didn't include it in the SubCatsBox listing

:
Post the SubCatBox control's RowSource query along with the
ControlSource, BoundColumn, ColumnCount and ColumnWidths
properties. Also provide the data type of each field in the
row source query and the box's control source field (in its
table).


TheDrescher wrote:
The latter is correct. I have Dirk's code in the After Update section for
the 'Categories' field. That information pulls up fine in form view, but I
get the error message when I click on the SubCatBox field to select the
corresponding subcategory. This is when I get the "data type mismatch in
criteria expression" notification. This error shows up regardless of whether
the data in the Category field of the SubCats table is text or numerical.

:>
TheDrescher wrote:
It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

"the value I entered in 'Category' is invalid for the field"
does not sound like a a query error message. Maybe "it"
does not refer to the query.

If the message was from the query and said somthing about a
data type mismatch, then see Dirk's reply. As Dirk said,
the data type (number, text, etc) is essential to getting
the query to work,

If the message appeared when you tried to select an item in
the list, please provide more details.


The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

Now you are losing me. It doesn't make sense to play around
with the BoundColumn property. It needs to be exactly what
your program requires it to be. Most likely it should be
the number that corresponds to the ID field in the query's
field list. If BoundColumn is 1, then I would expect the
query to be:
"SELECT ID, Category FROM . . .


:
That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.


TheDrescher wrote:
Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

:
It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

 

[TheDrescher]

Marsh,

Sorry about the confusion. My position has recently been expanded to
include programming in Access. I haven't worked in Visual Basic in many,
many years so I'm a bit rusty with terminology and coding. I have a very
"Excel" mindset when it comes to programming because that's pretty much
exclusively what they've had me using for the past 5 years. I went back
through the code and was able to get everything to fit by making sure the
fields were correct and changing one to numerical instead of text-based.
Thanks again for all your assistance and patience

You really need to use precise wording. A field is a column
in a table or query. The things on forms and reports are
controls. Your "box" is either a single select list box or
a combo box, but you have never said which. And please use
the exact names of the fields and controls without
generalizing a control by using its control source (bound
field) instead of its Name property.

And when you are having troouble with code, we really need
to see a Copy/Paste of the code. It is rare when the code
we suggest can be copied and pasted and then expected to
work as is. You need to study it to understand what it's
doing and why the responder thought is would help solve a
problem.

In this case, it sounds like the category "box" is named
something other than Category.
--
Marsh
MVP [MS Access]

The code seems to go in but when I select the category it pulls up the
.Column(1) segment of the code and states Compile Error: Method or data
member not found. This code is all on one line, correct? when I tried to
bring it over in two, it gave me syntax errors in VBA

Since the Category field in the SubCats table is a text
field (strange, it should be the category id), you need to
extract that from the combo box's list.

I now think your code should be:

Me.SubCatBox.RowSource = "SELECT ID, SubCategory FROM
SubCats WHERE Category=""" & Me.Category.Column(1) & """"

If you had the category id in the SubCats table then it
would have been as Dirk said. The reason you should use the
category id is because you can change the category text in
the category table without having to also change it in the
SubCats table.


TheDrescher wrote:
SubCatBox Controls:
RowSource: SELECT SubCats.ID, SubCats.SubCategory FROM SubCats ORDER BY
[SubCategory];
ControlSource: SubCategory
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"

SubCats Table:
Column 1, "ID", numerical
Column 2, "SubCategory", text

There is a thrid column in the SubCats table which has the Category listed
as text as well, but I didn't include it in the SubCatsBox listing

:
Post the SubCatBox control's RowSource query along with the
ControlSource, BoundColumn, ColumnCount and ColumnWidths
properties. Also provide the data type of each field in the
row source query and the box's control source field (in its
table).


TheDrescher wrote:
The latter is correct. I have Dirk's code in the After Update section for
the 'Categories' field. That information pulls up fine in form view, but I
get the error message when I click on the SubCatBox field to select the
corresponding subcategory. This is when I get the "data type mismatch in
criteria expression" notification. This error shows up regardless of whether
the data in the Category field of the SubCats table is text or numerical.

:>
TheDrescher wrote:
It seems I'm trading error for error. Utilizing the code you gave me it
comes back saying the value I entered in 'Category' is invalid for the field.
I was using SELECT since there are 15 categories and 128
category/subcategory combinations. If this is an inefficient way to code it,
how would I list the field specifically?

"the value I entered in 'Category' is invalid for the field"
does not sound like a a query error message. Maybe "it"
does not refer to the query.

If the message was from the query and said somthing about a
data type mismatch, then see Dirk's reply. As Dirk said,
the data type (number, text, etc) is essential to getting
the query to work,

If the message appeared when you tried to select an item in
the list, please provide more details.


The 'Category' field pulls from the 'Categories' table with two columns,
"ID" and "Category" so:
ColumnCount = 2
ColumnWidths = 0";1"
Bound Column = 1(gives response above), 2 (returns VBA code in Category box
and returns "Syntax error in WHERE clause")

Now you are losing me. It doesn't make sense to play around
with the BoundColumn property. It needs to be exactly what
your program requires it to be. Most likely it should be
the number that corresponds to the ID field in the query's
field list. If BoundColumn is 1, then I would expect the
query to be:
"SELECT ID, Category FROM . . .


:
That's what I thought. Try it this way:

Me.SubCatBox.RowSource = "SELECT * FROM SubCats WHERE
Category = """ & Nz(Me.Category, "") & """"

It is not good to use SELECT *, especially in a combo/list
box row source. Instead, list the specific fields needed
for the control's list. Don't forget that the control's
BoundColumn, ColumnWidths and ColumnCount properties must
agree with the listed fields.


TheDrescher wrote:
Me.SubCatBox.RowSource = "SELECT * FROM tbl[SubCats] " & "WHERE
Fld[Category]" = """ & Nz(Me.Category, "") & """""

:
It sounds like the = sign got out of the quotes. What does
the code look like after the changes to fix the names?

TheDrescher wrote:
Thanks for the heads up. After utlizing the example you provided, when I go
to choose the sub-category it states that the Record Source 'False' does not
exist. is there some additional parameter I need to specify?

:
TheDrescher wrote:
I'm coding a portion of the form that populates a set of options in one
field (SubCatBox) based on the value selected in another (Categories). I've
created the required tables (named Categories and SubCats) and have set the
'After Update' event procedure for the Categories field to equal:

Private Sub Categories_AfterUpdate()
Me.SubCatBox.RowSource = "SELECT * FROM tbl SubCats " & "WHERE Fld Category
= """ & Nz(Me.Category, "") & """"

End Sub

I keep getting a 'Syntax Error (missing operator) in query expression
'FldCategory = "1"' . How would I correct this? Thanks!

any time you use a name that contains a space or other
funky characters, you MUST enclose the name in [ ]

....="SELECT * FROM [tbl SubCats] " & "WHERE [Fld Category]
= """ & Nz(Me.Category, "") & """"

In general, you should not use names that require the [ ]
Something like tblSubCats and fldCategory are just as
menaingful and avoid the problem you posted.

.