DLookup on Form

[Brian]

Please forgive the length of the problem description. I
have been working on this for a while without success and
I want to make sure I give as much detail as I can, since
I know someone in this group will have the answer.

I have a large Customer Information form. One of the main
areas is a ListBox that displays several several items
about a product. The query behind this list box has 9
columns (7 of which I display). The other two are two
long for the ListBox. These two columns are displayed in
TextBoxes when a user clicks on the product they are
interested in. All of this works exactly as it should,
with only one problem. The 2nd of the two text boxes
contains a Description field (which is a memo, in the
table). In the query builder, all of the data is present,
however, in the TextBox, it is cut at 255 characters. As
a TextBox can hold the full size of a memo field, I
believe it is a constraint of the ListBox (for clarity,
the Control Source for the TextBox is set to Me!
[ListBox].Column(8)).

To get around this problem, I have tried to use a DLookup,
directly from the TextBox, using the the bound field from
the ListBox (actual code shown below):

=DLookUp("[Product Menu Description]","Product
Subscriptions","[Product Code] = " & "'" & [Forms]!
[Product Master List].[Product Menu Description].Column(8)
& "'")

I get an error back from Access stating, "The object you
referenced in the Visual Basic procedure as an OLE object
isn't an OLE object."

Here are the questions:
1. Does the ListBox have a constraint of 255 characters?
2. How can I change the DLookup to avoid this error, or is
there another way to access the data and get the full
result?
3. Is there a way to remove the horizontal scroll bar from
the ListBox?

Any help would be greatly appreciated.

Thank you.

 

[Wayne Morgan]

You stated that the Memo was in Column 8, then that you were trying to use the DLookup by
using the Bound Column of the listbox as the search parameter, but then in the code you
supplied you are again using the Memo Field (Column 8) as the search parameter.

 

[Brian]

Sorry for the typo. I meant Column 1. This is the column
that contains the Product Code.

-----Original Message-----
You stated that the Memo was in Column 8, then that you

were trying to use the DLookup by

using the Bound Column of the listbox as the search

parameter, but then in the code you

supplied you are again using the Memo Field (Column 8) as the search parameter.

--
Wayne Morgan
Microsoft Access MVP

Please forgive the length of the problem description. I
have been working on this for a while without success and
I want to make sure I give as much detail as I can, since
I know someone in this group will have the answer.

I have a large Customer Information form. One of the main
areas is a ListBox that displays several several items
about a product. The query behind this list box has 9
columns (7 of which I display). The other two are two
long for the ListBox. These two columns are displayed in
TextBoxes when a user clicks on the product they are
interested in. All of this works exactly as it should,
with only one problem. The 2nd of the two text boxes
contains a Description field (which is a memo, in the
table). In the query builder, all of the data is present,
however, in the TextBox, it is cut at 255 characters. As
a TextBox can hold the full size of a memo field, I
believe it is a constraint of the ListBox (for clarity,
the Control Source for the TextBox is set to Me!
[ListBox].Column(8)).

To get around this problem, I have tried to use a DLookup,
directly from the TextBox, using the the bound field from
the ListBox (actual code shown below):

=DLookUp("[Product Menu Description]","Product
Subscriptions","[Product Code] = " & "'" & [Forms]!
[Product Master List].[Product Menu Description].Column (8)
& "'")

I get an error back from Access stating, "The object you
referenced in the Visual Basic procedure as an OLE object
isn't an OLE object."

Here are the questions:
1. Does the ListBox have a constraint of 255 characters?
2. How can I change the DLookup to avoid this error, or is
there another way to access the data and get the full
result?
3. Is there a way to remove the horizontal scroll bar from
the ListBox?

Any help would be greatly appreciated.

Thank you.

.

 

[Brian]

Just for clarification, this was just a typo in my
message, not in my database. Any ideas on how to fix the
problem?

-----Original Message-----
Sorry for the typo. I meant Column 1. This is the column
that contains the Product Code.

-----Original Message-----
You stated that the Memo was in Column 8, then that you

were trying to use the DLookup by

using the Bound Column of the listbox as the search

parameter, but then in the code you

supplied you are again using the Memo Field (Column 8)

as
the search parameter.

--
Wayne Morgan
Microsoft Access MVP

Please forgive the length of the problem description. I
have been working on this for a while without success and
I want to make sure I give as much detail as I can, since
I know someone in this group will have the answer.

I have a large Customer Information form. One of the main
areas is a ListBox that displays several several items
about a product. The query behind this list box has 9
columns (7 of which I display). The other two are two
long for the ListBox. These two columns are displayed in
TextBoxes when a user clicks on the product they are
interested in. All of this works exactly as it should,
with only one problem. The 2nd of the two text boxes
contains a Description field (which is a memo, in the
table). In the query builder, all of the data is present,
however, in the TextBox, it is cut at 255 characters. As
a TextBox can hold the full size of a memo field, I
believe it is a constraint of the ListBox (for clarity,
the Control Source for the TextBox is set to Me!
[ListBox].Column(8)).

To get around this problem, I have tried to use a DLookup,
directly from the TextBox, using the the bound field from
the ListBox (actual code shown below):

=DLookUp("[Product Menu Description]","Product
Subscriptions","[Product Code] = " & "'" & [Forms]!
[Product Master List].[Product Menu Description].Column (8)
& "'")

I get an error back from Access stating, "The object you
referenced in the Visual Basic procedure as an OLE object
isn't an OLE object."

Here are the questions:
1. Does the ListBox have a constraint of 255 characters?
2. How can I change the DLookup to avoid this error,

or

is


.

.

 

[Kelvin Lu]

Try

[Forms]![Product Master List]![Product Menu Description].Column(0)

Your using the priod instead of the exclamation point after [Product Master
List].

Kelvin

Just for clarification, this was just a typo in my
message, not in my database. Any ideas on how to fix the
problem?

-----Original Message-----
Sorry for the typo. I meant Column 1. This is the column
that contains the Product Code.

-----Original Message-----
You stated that the Memo was in Column 8, then that you

were trying to use the DLookup by

using the Bound Column of the listbox as the search

parameter, but then in the code you

supplied you are again using the Memo Field (Column 8)

as
the search parameter.

--
Wayne Morgan
Microsoft Access MVP


Please forgive the length of the problem description. I
have been working on this for a while without success and
I want to make sure I give as much detail as I can, since
I know someone in this group will have the answer.

I have a large Customer Information form. One of the main
areas is a ListBox that displays several several items
about a product. The query behind this list box has 9
columns (7 of which I display). The other two are two
long for the ListBox. These two columns are displayed in
TextBoxes when a user clicks on the product they are
interested in. All of this works exactly as it should,
with only one problem. The 2nd of the two text boxes
contains a Description field (which is a memo, in the
table). In the query builder, all of the data is present,
however, in the TextBox, it is cut at 255 characters. As
a TextBox can hold the full size of a memo field, I
believe it is a constraint of the ListBox (for clarity,
the Control Source for the TextBox is set to Me!
[ListBox].Column(8)).

To get around this problem, I have tried to use a DLookup,
directly from the TextBox, using the the bound field from
the ListBox (actual code shown below):

=DLookUp("[Product Menu Description]","Product
Subscriptions","[Product Code] = " & "'" & [Forms]!
[Product Master List].[Product Menu Description].Column (8)
& "'")

I get an error back from Access stating, "The object you
referenced in the Visual Basic procedure as an OLE object
isn't an OLE object."

Here are the questions:
1. Does the ListBox have a constraint of 255 characters?
2. How can I change the DLookup to avoid this error,

or

is
there another way to access the data and get the full
result?
3. Is there a way to remove the horizontal scroll bar from
the ListBox?

Any help would be greatly appreciated.

Thank you.


.

.

 

[Wayne Morgan]

The way you have put quotes around the criteria field, it appears to be a text field, is
this correct?

In the DLookup, you have "Product Subscriptions", with the way it wrapped, I can't tell if
there is a space in this name or not. If there is, you may need to also enclose it in
brackets, just as you did the field name.

To get rid of the horizontal scroll bar on the list box, the items in the listbox need to
display within the width of the listbox. To do this, you can shrink the column widths down
if there are some that are wider than they need to be, you can increase the width of the
listbox, and you can hide fields that don't need to show in the listbox, such as the ones
you are displaying in the textbox.