DLookup on Form

  • Thread starter Thread starter Brian
  • Start date Start date
B

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.
 
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.
 
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
Click to expand...
were trying to use the DLookup by
using the Bound Column of the listbox as the search
Click to expand...
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


Brian said:
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.
Click to expand...


.
Click to expand...
 
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
Click to expand...
were trying to use the DLookup by
using the Bound Column of the listbox as the search
Click to expand...
parameter, but then in the code you
supplied you are again using the Memo Field (Column 8)
Click to expand...
as
the search parameter.
--
Wayne Morgan
Microsoft Access MVP


Brian said:
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,
Click to expand...
Click to expand...
or
is


.
Click to expand...
.
Click to expand...
 
Try

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

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

Kelvin

Brian said:
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
Click to expand...
were trying to use the DLookup by
using the Bound Column of the listbox as the search
Click to expand...
parameter, but then in the code you
supplied you are again using the Memo Field (Column 8)
Click to expand...
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,
Click to expand...
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.


.
Click to expand...
.
Click to expand...
Click to expand...
 
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.
 
Back
Top