Combo Boxes

[Tom]

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

 

[fredg]

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";

 

[Tom]

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";

 

[fredg]

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";

It's always best to copy and paste the actual code you used, not, as I
suspect, re-type it here in the message. I have no idea if all of the
errors were all in your code or just in the re-typing.
I can only see this message and you have...

1) A space missing ... tblParts.[Descrip]from ... in front of the
word "from".
It needs to be there. It should be ...tblParts.[Descrip] from ....

2) You added a double quote after the word tblParts.
"Select tblParts.[Descrip]from tblParts " ....
That last quote should NOT be there.

3) You are missing a single quote.
.... where tblParts.[PartNmbr] = "& ...
should be
where tblParts.[PartNmbr] = ' " & (remove the space between the single
and double quote)

So, to re-write your code:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = ' " & me.[cboParts] & " ' ";

(it should all be on one line)
Remove the spaces between the single and double quotes.

 

[Tom]

Hi Fred

I copied the code exactly as you typed it and removed the spaces between the
single and double quotes at both locations.

If I keep the code on one line, I get a compile error at the Where statement
telling me it expected an end of statement.

If I break the line at the where, I get a compile error at the first single
quote "Expcted: expression".

What is it telling me??

Thanx

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

On Fri, 7 Dec 2007 15:06:01 -0800, Tom wrote:

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";

It's always best to copy and paste the actual code you used, not, as I
suspect, re-type it here in the message. I have no idea if all of the
errors were all in your code or just in the re-typing.
I can only see this message and you have...

1) A space missing ... tblParts.[Descrip]from ... in front of the
word "from".
It needs to be there. It should be ...tblParts.[Descrip] from ....

2) You added a double quote after the word tblParts.
"Select tblParts.[Descrip]from tblParts " ....
That last quote should NOT be there.

3) You are missing a single quote.
.... where tblParts.[PartNmbr] = "& ...
should be
where tblParts.[PartNmbr] = ' " & (remove the space between the single
and double quote)

So, to re-write your code:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = ' " & me.[cboParts] & " ' ";

(it should all be on one line)
Remove the spaces between the single and double quotes.

 

[fredg]

Hi Fred

I copied the code exactly as you typed it and removed the spaces between the
single and double quotes at both locations.

If I keep the code on one line, I get a compile error at the Where statement
telling me it expected an end of statement.

If I break the line at the where, I get a compile error at the first single
quote "Expcted: expression".

What is it telling me??

Thanx

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

:

On Fri, 7 Dec 2007 15:06:01 -0800, Tom wrote:

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";

It's always best to copy and paste the actual code you used, not, as I
suspect, re-type it here in the message. I have no idea if all of the
errors were all in your code or just in the re-typing.
I can only see this message and you have...

1) A space missing ... tblParts.[Descrip]from ... in front of the
word "from".
It needs to be there. It should be ...tblParts.[Descrip] from ....

2) You added a double quote after the word tblParts.
"Select tblParts.[Descrip]from tblParts " ....
That last quote should NOT be there.

3) You are missing a single quote.
.... where tblParts.[PartNmbr] = "& ...
should be
where tblParts.[PartNmbr] = ' " & (remove the space between the single
and double quote)

So, to re-write your code:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = ' " & me.[cboParts] & " ' ";

(it should all be on one line)
Remove the spaces between the single and double quotes.

It's telling you that I should have re-checked my post before I sent
it. Sorry.

The semicolon goes inside the last quote ... " ' ;"
"Select .... etc....
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"

 

[Tom]

Hello again Fred.

Still getting an "Expected End of Statement" on the "Where". This is a copy
of the code... It's all on one line.... Hope we're getting closer.


Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";

End Sub

Hi Fred

I copied the code exactly as you typed it and removed the spaces between the
single and double quotes at both locations.

If I keep the code on one line, I get a compile error at the Where statement
telling me it expected an end of statement.

If I break the line at the where, I get a compile error at the first single
quote "Expcted: expression".

What is it telling me??

Thanx

On Fri, 7 Dec 2007 16:02:00 -0800, Tom wrote:

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

:

On Fri, 7 Dec 2007 15:06:01 -0800, Tom wrote:

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


It's always best to copy and paste the actual code you used, not, as I
suspect, re-type it here in the message. I have no idea if all of the
errors were all in your code or just in the re-typing.
I can only see this message and you have...

1) A space missing ... tblParts.[Descrip]from ... in front of the
word "from".
It needs to be there. It should be ...tblParts.[Descrip] from ....

2) You added a double quote after the word tblParts.
"Select tblParts.[Descrip]from tblParts " ....
That last quote should NOT be there.

3) You are missing a single quote.
.... where tblParts.[PartNmbr] = "& ...
should be
where tblParts.[PartNmbr] = ' " & (remove the space between the single
and double quote)

So, to re-write your code:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = ' " & me.[cboParts] & " ' ";

(it should all be on one line)
Remove the spaces between the single and double quotes.

It's telling you that I should have re-checked my post before I sent
it. Sorry.

The semicolon goes inside the last quote ... " ' ;"
"Select .... etc....
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"

 

[fredg]

Hello again Fred.

Still getting an "Expected End of Statement" on the "Where". This is a copy
of the code... It's all on one line.... Hope we're getting closer.

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";

End Sub

Hi Fred

I copied the code exactly as you typed it and removed the spaces between the
single and double quotes at both locations.

If I keep the code on one line, I get a compile error at the Where statement
telling me it expected an end of statement.

If I break the line at the where, I get a compile error at the first single
quote "Expcted: expression".

What is it telling me??

Thanx

:

On Fri, 7 Dec 2007 16:02:00 -0800, Tom wrote:

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

:

On Fri, 7 Dec 2007 15:06:01 -0800, Tom wrote:

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


It's always best to copy and paste the actual code you used, not, as I
suspect, re-type it here in the message. I have no idea if all of the
errors were all in your code or just in the re-typing.
I can only see this message and you have...

1) A space missing ... tblParts.[Descrip]from ... in front of the
word "from".
It needs to be there. It should be ...tblParts.[Descrip] from ....

2) You added a double quote after the word tblParts.
"Select tblParts.[Descrip]from tblParts " ....
That last quote should NOT be there.

3) You are missing a single quote.
.... where tblParts.[PartNmbr] = "& ...
should be
where tblParts.[PartNmbr] = ' " & (remove the space between the single
and double quote)

So, to re-write your code:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = ' " & me.[cboParts] & " ' ";

(it should all be on one line)
Remove the spaces between the single and double quotes.

It's telling you that I should have re-checked my post before I sent
it. Sorry.

The semicolon goes inside the last quote ... " ' ;"
"Select .... etc....
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"

"Select tblParts.[Descrip]from tblParts"
|
You didn't leave a space between [Descrip] and from
"Select tblParts.[Descrip]from tblParts"
|
and you added a " after tblParts.

where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";
|
You seem to have added an additional semicolon at the end.

where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"
is all you need.

To re-state the above:
1) you need a space between the words [Descrip] and from
2) Remove the quote after tblParts
3) Remove the final ; (leave the one within the "';")

 

[Tom]

That solved one problem....

Now I get "Run Time Error" 2465. Can't find the field referred to in your
espression.

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = '" & Me.[cboParts] & "';"

End Sub

Hello again Fred.

Still getting an "Expected End of Statement" on the "Where". This is a copy
of the code... It's all on one line.... Hope we're getting closer.

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";

End Sub

On Sat, 8 Dec 2007 10:49:01 -0800, Tom wrote:

Hi Fred

I copied the code exactly as you typed it and removed the spaces between the
single and double quotes at both locations.

If I keep the code on one line, I get a compile error at the Where statement
telling me it expected an end of statement.

If I break the line at the where, I get a compile error at the first single
quote "Expcted: expression".

What is it telling me??

Thanx

:

On Fri, 7 Dec 2007 16:02:00 -0800, Tom wrote:

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

:

On Fri, 7 Dec 2007 15:06:01 -0800, Tom wrote:

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


It's always best to copy and paste the actual code you used, not, as I
suspect, re-type it here in the message. I have no idea if all of the
errors were all in your code or just in the re-typing.
I can only see this message and you have...

1) A space missing ... tblParts.[Descrip]from ... in front of the
word "from".
It needs to be there. It should be ...tblParts.[Descrip] from ....

2) You added a double quote after the word tblParts.
"Select tblParts.[Descrip]from tblParts " ....
That last quote should NOT be there.

3) You are missing a single quote.
.... where tblParts.[PartNmbr] = "& ...
should be
where tblParts.[PartNmbr] = ' " & (remove the space between the single
and double quote)

So, to re-write your code:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = ' " & me.[cboParts] & " ' ";

(it should all be on one line)
Remove the spaces between the single and double quotes.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


It's telling you that I should have re-checked my post before I sent
it. Sorry.

The semicolon goes inside the last quote ... " ' ;"
"Select .... etc....
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"

"Select tblParts.[Descrip]from tblParts"
|
You didn't leave a space between [Descrip] and from
"Select tblParts.[Descrip]from tblParts"
|
and you added a " after tblParts.

where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";
|
You seem to have added an additional semicolon at the end.

where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"
is all you need.

To re-state the above:
1) you need a space between the words [Descrip] and from
2) Remove the quote after tblParts
3) Remove the final ; (leave the one within the "';")

 

[fredg]

That solved one problem....

Now I get "Run Time Error" 2465. Can't find the field referred to in your
espression.

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = '" & Me.[cboParts] & "';"

End Sub

Hello again Fred.

Still getting an "Expected End of Statement" on the "Where". This is a copy
of the code... It's all on one line.... Hope we're getting closer.

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";

End Sub

:

On Sat, 8 Dec 2007 10:49:01 -0800, Tom wrote:

Hi Fred

I copied the code exactly as you typed it and removed the spaces between the
single and double quotes at both locations.

If I keep the code on one line, I get a compile error at the Where statement
telling me it expected an end of statement.

If I break the line at the where, I get a compile error at the first single
quote "Expcted: expression".

What is it telling me??

Thanx

:

On Fri, 7 Dec 2007 16:02:00 -0800, Tom wrote:

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

:

On Fri, 7 Dec 2007 15:06:01 -0800, Tom wrote:

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


It's always best to copy and paste the actual code you used, not, as I
suspect, re-type it here in the message. I have no idea if all of the
errors were all in your code or just in the re-typing.
I can only see this message and you have...

1) A space missing ... tblParts.[Descrip]from ... in front of the
word "from".
It needs to be there. It should be ...tblParts.[Descrip] from ....

2) You added a double quote after the word tblParts.
"Select tblParts.[Descrip]from tblParts " ....
That last quote should NOT be there.

3) You are missing a single quote.
.... where tblParts.[PartNmbr] = "& ...
should be
where tblParts.[PartNmbr] = ' " & (remove the space between the single
and double quote)

So, to re-write your code:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = ' " & me.[cboParts] & " ' ";

(it should all be on one line)
Remove the spaces between the single and double quotes.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


It's telling you that I should have re-checked my post before I sent
it. Sorry.

The semicolon goes inside the last quote ... " ' ;"
"Select .... etc....
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"

"Select tblParts.[Descrip]from tblParts"
|
You didn't leave a space between [Descrip] and from
"Select tblParts.[Descrip]from tblParts"
|
and you added a " after tblParts.

where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";
|
You seem to have added an additional semicolon at the end.

where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"
is all you need.

To re-state the above:
1) you need a space between the words [Descrip] and from
2) Remove the quote after tblParts
3) Remove the final ; (leave the one within the "';")

Which field can't it find?
So does your tblParts actually have a field named Descrip and a field
named PartNmbr?
Are those field names spelled correctly?
Is the second combo box on the form named cboParts?

Let's try narrowing the problem down.
Just as a test, what happens if you change the code to:
Me.cboDescription.RowSource = "Select tblParts.* from tblParts

The above will return all records and all fields in the tblParts
table.

If that works then try (as a test):

Me.cboDescription.RowSource = "Select tblParts.* from tblParts
where tblParts.[PartNmbr] = '" & Me.[cboParts] & "';"

This should return all fields in tblParts whose [PartNmbr] is the
same as the number selected in the cboParts combo box bound column.

Does the above work?

Or try (just as a test):
Me.cboDescription.RowSource = "Select tblParts.[Descrip] from
tblParts where tblParts.[PartNmbr] = 'ABC123';"

Change 'ABC123' to a known actual PartNmbr.

This should return the Desrip field data for the [PartNmbr] that
matches the entered PartNmbr.

Remember now, you stated in a previous message that [PartNmbr] was a
Text datatype. That's what the above code assumes.

Does either one of these work? Which one?

 

[Tom]

OK... I have checked field names and datatypes, spelling and type (text) are
correct.

The second combo box is named cboDescription, the first is cboParts.

Setting "Select tblParts.*from tblParts did not work gave same error msg.
Field was a straight vertical line between single quotes.

The first part of the test did not work. same error.

Setting a specific part number "ABC123" did work. So, how do I code this to
search the table for the right description based on the part number entered??

Almost there.....

That solved one problem....

Now I get "Run Time Error" 2465. Can't find the field referred to in your
espression.

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = '" & Me.[cboParts] & "';"

End Sub

On Mon, 10 Dec 2007 09:24:00 -0800, Tom wrote:

Hello again Fred.

Still getting an "Expected End of Statement" on the "Where". This is a copy
of the code... It's all on one line.... Hope we're getting closer.

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";

End Sub

:

On Sat, 8 Dec 2007 10:49:01 -0800, Tom wrote:

Hi Fred

I copied the code exactly as you typed it and removed the spaces between the
single and double quotes at both locations.

If I keep the code on one line, I get a compile error at the Where statement
telling me it expected an end of statement.

If I break the line at the where, I get a compile error at the first single
quote "Expcted: expression".

What is it telling me??

Thanx

:

On Fri, 7 Dec 2007 16:02:00 -0800, Tom wrote:

Ok Changed my field name to Descrip.

This what I entered in cboPartNmbr "AfterUpdate event:

Private Sub cboPartNmbr_AfterUpdate()
Me.cboDescription.RowSource = "Select tblParts.[Descrip]from tblParts"
where tblParts.[PartNmbr] = "& me.[cboParts] &" ' ";
End Sub

It hangs on the where statement....

the part number field is a text field... What did I not do correctly??

:

On Fri, 7 Dec 2007 15:06:01 -0800, Tom wrote:

I have two combo boxes. one for a part number and one for description. I
would like to limit the choices for the description combo box based on the
value in the part number box.

The table this is based on has two fields. PartNmbr and Desc. I found some
sample code at www.databasedev.co.uk/filter_combo_boxes.html but am having
issues modifying it to suit my purpose.

Thanx for your help

NOTE: Desc is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

One method is to leave the second combo box rowsource blank.
After you change the Desc field name to something else, code the first
Combo AfterUpdate event:

Me.[Combo2].Rowsource = "Select TableName.[Desc] from TableName Where
TableName.[PartNmbr] = " & Me.[ComboName];

The above assumes the [PartNmbr] field is a Number datatype.

If the [PartNmbr] is actually Text datatype, then use:

Where TableName.[PartNmbr] = '" & Me.[ComboName] & "'";
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


It's always best to copy and paste the actual code you used, not, as I
suspect, re-type it here in the message. I have no idea if all of the
errors were all in your code or just in the re-typing.
I can only see this message and you have...

1) A space missing ... tblParts.[Descrip]from ... in front of the
word "from".
It needs to be there. It should be ...tblParts.[Descrip] from ....

2) You added a double quote after the word tblParts.
"Select tblParts.[Descrip]from tblParts " ....
That last quote should NOT be there.

3) You are missing a single quote.
.... where tblParts.[PartNmbr] = "& ...
should be
where tblParts.[PartNmbr] = ' " & (remove the space between the single
and double quote)

So, to re-write your code:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from tblParts
where tblParts.[PartNmbr] = ' " & me.[cboParts] & " ' ";

(it should all be on one line)
Remove the spaces between the single and double quotes.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


It's telling you that I should have re-checked my post before I sent
it. Sorry.

The semicolon goes inside the last quote ... " ' ;"
"Select .... etc....
where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


"Select tblParts.[Descrip]from tblParts"
|
You didn't leave a space between [Descrip] and from
"Select tblParts.[Descrip]from tblParts"
|
and you added a " after tblParts.

where tblParts.[PartNmbr] = '" & me.[cboParts] & "';";
|
You seem to have added an additional semicolon at the end.

where tblParts.[PartNmbr] = '" & me.[cboParts] & "';"
is all you need.

To re-state the above:
1) you need a space between the words [Descrip] and from
2) Remove the quote after tblParts
3) Remove the final ; (leave the one within the "';")

Which field can't it find?
So does your tblParts actually have a field named Descrip and a field
named PartNmbr?
Are those field names spelled correctly?
Is the second combo box on the form named cboParts?

Let's try narrowing the problem down.
Just as a test, what happens if you change the code to:
Me.cboDescription.RowSource = "Select tblParts.* from tblParts

The above will return all records and all fields in the tblParts
table.

If that works then try (as a test):

Me.cboDescription.RowSource = "Select tblParts.* from tblParts
where tblParts.[PartNmbr] = '" & Me.[cboParts] & "';"

This should return all fields in tblParts whose [PartNmbr] is the
same as the number selected in the cboParts combo box bound column.

Does the above work?

Or try (just as a test):
Me.cboDescription.RowSource = "Select tblParts.[Descrip] from
tblParts where tblParts.[PartNmbr] = 'ABC123';"

Change 'ABC123' to a known actual PartNmbr.

This should return the Desrip field data for the [PartNmbr] that
matches the entered PartNmbr.

Remember now, you stated in a previous message that [PartNmbr] was a
Text datatype. That's what the above code assumes.

Does either one of these work? Which one?

 

[fredg]

OK... I have checked field names and datatypes, spelling and type (text) are
correct.

The second combo box is named cboDescription, the first is cboParts.

Setting "Select tblParts.*from tblParts did not work gave same error msg.
Field was a straight vertical line between single quotes.

The first part of the test did not work. same error.

Setting a specific part number "ABC123" did work. So, how do I code this to
search the table for the right description based on the part number entered??

Almost there.....

*** SNIPPED ***

You have something going on there that I can't see.

If you wish, send me a stripped down copy of your database (just
enough data in the table to fill the combo box).
Include data so that I can open the form as well.
I'll look at it and get back to you here in the newsgroup.
Send it to:

jandf at roadrunner dot com

Make sure you write "Access Database from Tom" in the Subject line,
otherwise I won't see it.
Do not ZIP it.

 

[Tom]

It's on the way

*** SNIPPED ***

You have something going on there that I can't see.

If you wish, send me a stripped down copy of your database (just
enough data in the table to fill the combo box).
Include data so that I can open the form as well.
I'll look at it and get back to you here in the newsgroup.
Send it to:

jandf at roadrunner dot com

Make sure you write "Access Database from Tom" in the Subject line,
otherwise I won't see it.
Do not ZIP it.

 

[fredg]

It's on the way

O.K. Problem solved.

The actual name of your combo box is cboPartNmbr not cboParts.

You need to refer to the actual name of the combo box.
Set the cboPartNmbr AfterUpdate event to:

Me.cboDescription.RowSource = "Select tblParts.[Descrip] from
tblParts where tblParts.[PartNmbr] = '" & cboPartNmbr & "';"

The above should be all on one line.
Next....
Display the cbpPartNmbr property sheet.
On the Data Tab, your current Bound Column property is set to 0.
That is incorrect.
Set the Bound Column to 1
Then, on the Format tab, the Column Width property is blank.
Set the Column Width to 1"
Set the Column Count property to 1

Click on the cboDescription combo box.
Set it's Bound Column to 1
Set it's Column Count to 1
Set it's Column Width to 2"

That's all you need do.

I'll hang on to your database for a couple of days. If you have any
more questions, I'll be able to refer to it. After that I'll delete
it. I hope this resolves your difficulty.

 

[Tom]

That did it. Fred, thanx greatly for your help. I'll be able to use this in
several projects I have going.
Take care
Tom