Combo box Me!= Memo Not all text.

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi;

Created a Combo Box with an After Update Event:

Me![txtProdDescript] = Me![cbxProdDescript].Column(1)

Both txtProdDescript and cbxProdDescript Data Types are Memos.

After the update only a few lines of [cbxProdDescript].Column(1) are
transferred to txtProdDescript.

Tried setting the txtProdDescript Unicode compression to No in the table.
That didn't work.

Anyone have an answer to this?

Andy
 
I suspect that the Column property may only be returning a text data type,
which is limited to 255 characters.

You may actually have to do a database lookup to get the memo value back.
 
Anyone have an answer to this?
Click to expand...

You cannot include an entire memo field (which might be hundreds of
megabytes in size!) in a combo control. It simply won't let you - it
will truncate it to 255 bytes.

Could you explain why you want to store a Memo field redundantly in a
second table? My guess is that it should not be necessary to do so! If
it is, then you will need to write VBA code in the combo's afterupdate
event to explicitly copy the data from table to table.
 
Douglas/John;

In answer to the question why store redundant information in two tables.

The Combo Box displays a title for a description of the product, when
selected the After Update event:
Me![txtProdDescript] = Me![cbxProdDescript].Column(1)

Populates the full description of the product in [txtProdDescript].

I believe Both of You are correct and a database lookup is neccessary
because the description is being trunicated.

I know how to create a lookup when only one record is in a table:
Example: =DLookUp("[CompanyName]","tblCompany")

What I haven't learned yet is how to LookUp a specific record that is
related to a Combo Box.

Searched MSFT's Access Support, Nothing.

Can You point me in the right direction?

Thank You.

Andy
 
Put your lookup code in the combo box's AfterUpdate event.

Are your product descriptions really in excess of 255 characters? Can you
not survive with shorter descriptions?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Andy said:
Douglas/John;

In answer to the question why store redundant information in two tables.

The Combo Box displays a title for a description of the product, when
selected the After Update event:
Me![txtProdDescript] = Me![cbxProdDescript].Column(1)

Populates the full description of the product in [txtProdDescript].

I believe Both of You are correct and a database lookup is neccessary
because the description is being trunicated.

I know how to create a lookup when only one record is in a table:
Example: =DLookUp("[CompanyName]","tblCompany")

What I haven't learned yet is how to LookUp a specific record that is
related to a Combo Box.

Searched MSFT's Access Support, Nothing.

Can You point me in the right direction?

Thank You.

Andy

John Vinson said:
You cannot include an entire memo field (which might be hundreds of
megabytes in size!) in a combo control. It simply won't let you - it
will truncate it to 255 bytes.

Could you explain why you want to store a Memo field redundantly in a
second table? My guess is that it should not be necessary to do so! If
it is, then you will need to write VBA code in the combo's afterupdate
event to explicitly copy the data from table to table.
Click to expand...
Click to expand...
 
Doug;

Answering Your question re: shorter descriptions.
Pasted the first description in Word. Statistic is: 700 Characters w/Spaces
870. So yes do need longer descriptions.

Took Your suggestion, went to MSFT help for an example:
DLookup("[CompanyName]", "Shippers", "[ShipperID] = " &
Forms!Shippers!ShipperID)

Tried the following:
Me![txtProdDescript] = DLookup("[ProdDescript]", "tblProducts", "[ProdID] =
" & Forms![frmMainForm]![sfrmProducts]!cbxProdDescript)

Except for the sfrm, it matches exactly!

Getting:
Run-time error '3075'
Syntax error (missing operator) in query expression "[ProdID] = Button down
shirt.

Button down shirt is the [ProdID]

Don't see where an operator is needed nor understand why it's returning the
error.

Doug; Your a good man.

When I typed that last sentence got to thinking...

Why am I seeing "Button down shirt" instead of a number?

Opened the tbl. [ProdID] the Primary Key Data Type was Text.
(Had created a set of synchronized Combo Boxes earlier following MSFT
instructions where the Primary Key Data Type HAS to be Text, and simply
copied and pasted one of those tables to use in a different situation.)

Added an AutoNumber Primary Key and everything works!

Thank You.

Doug; Your a good man.

Andy

Douglas J. Steele said:
Put your lookup code in the combo box's AfterUpdate event.

Are your product descriptions really in excess of 255 characters? Can you
not survive with shorter descriptions?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Andy said:
Douglas/John;

In answer to the question why store redundant information in two tables.

The Combo Box displays a title for a description of the product, when
selected the After Update event:
Me![txtProdDescript] = Me![cbxProdDescript].Column(1)

Populates the full description of the product in [txtProdDescript].

I believe Both of You are correct and a database lookup is neccessary
because the description is being trunicated.

I know how to create a lookup when only one record is in a table:
Example: =DLookUp("[CompanyName]","tblCompany")

What I haven't learned yet is how to LookUp a specific record that is
related to a Combo Box.

Searched MSFT's Access Support, Nothing.

Can You point me in the right direction?

Thank You.

Andy

John Vinson said:
Anyone have an answer to this?

You cannot include an entire memo field (which might be hundreds of
megabytes in size!) in a combo control. It simply won't let you - it
will truncate it to 255 bytes.

Could you explain why you want to store a Memo field redundantly in a
second table? My guess is that it should not be necessary to do so! If
it is, then you will need to write VBA code in the combo's afterupdate
event to explicitly copy the data from table to table.
Click to expand...
Click to expand...
Click to expand...
 
Since ProdID is a text field, you need quotes around the value:

Me![txtProdDescript] = DLookup("[ProdDescript]", "tblProducts", "[ProdID] =
"
& Chr$(34) & Forms![frmMainForm]![sfrmProducts]!cbxProdDescript & "Chr$(34))

Chr$(34) is the representation for "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Andy said:
Doug;

Answering Your question re: shorter descriptions.
Pasted the first description in Word. Statistic is: 700 Characters w/Spaces
870. So yes do need longer descriptions.

Took Your suggestion, went to MSFT help for an example:
DLookup("[CompanyName]", "Shippers", "[ShipperID] = " &
Forms!Shippers!ShipperID)

Tried the following:
Me![txtProdDescript] = DLookup("[ProdDescript]", "tblProducts", "[ProdID] =
" & Forms![frmMainForm]![sfrmProducts]!cbxProdDescript)

Except for the sfrm, it matches exactly!

Getting:
Run-time error '3075'
Syntax error (missing operator) in query expression "[ProdID] = Button down
shirt.

Button down shirt is the [ProdID]

Don't see where an operator is needed nor understand why it's returning the
error.

Doug; Your a good man.

When I typed that last sentence got to thinking...

Why am I seeing "Button down shirt" instead of a number?

Opened the tbl. [ProdID] the Primary Key Data Type was Text.
(Had created a set of synchronized Combo Boxes earlier following MSFT
instructions where the Primary Key Data Type HAS to be Text, and simply
copied and pasted one of those tables to use in a different situation.)

Added an AutoNumber Primary Key and everything works!

Thank You.

Doug; Your a good man.

Andy

Douglas J. Steele said:
Put your lookup code in the combo box's AfterUpdate event.

Are your product descriptions really in excess of 255 characters? Can you
not survive with shorter descriptions?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Andy said:
Douglas/John;

In answer to the question why store redundant information in two tables.

The Combo Box displays a title for a description of the product, when
selected the After Update event:
Me![txtProdDescript] = Me![cbxProdDescript].Column(1)

Populates the full description of the product in [txtProdDescript].

I believe Both of You are correct and a database lookup is neccessary
because the description is being trunicated.

I know how to create a lookup when only one record is in a table:
Example: =DLookUp("[CompanyName]","tblCompany")

What I haven't learned yet is how to LookUp a specific record that is
related to a Combo Box.

Searched MSFT's Access Support, Nothing.

Can You point me in the right direction?

Thank You.

Andy

Anyone have an answer to this?

You cannot include an entire memo field (which might be hundreds of
megabytes in size!) in a combo control. It simply won't let you - it
will truncate it to 255 bytes.

Could you explain why you want to store a Memo field redundantly in a
second table? My guess is that it should not be necessary to do so! If
it is, then you will need to write VBA code in the combo's afterupdate
event to explicitly copy the data from table to table.
Click to expand...
Click to expand...
Click to expand...
 
Doug;

You are an MVP.

Me

Douglas J. Steele said:
Since ProdID is a text field, you need quotes around the value:

Me![txtProdDescript] = DLookup("[ProdDescript]", "tblProducts", "[ProdID] =
"
& Chr$(34) & Forms![frmMainForm]![sfrmProducts]!cbxProdDescript & "Chr$(34))

Chr$(34) is the representation for "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Andy said:
Doug;

Answering Your question re: shorter descriptions.
Pasted the first description in Word. Statistic is: 700 Characters w/Spaces
870. So yes do need longer descriptions.

Took Your suggestion, went to MSFT help for an example:
DLookup("[CompanyName]", "Shippers", "[ShipperID] = " &
Forms!Shippers!ShipperID)

Tried the following:
Me![txtProdDescript] = DLookup("[ProdDescript]", "tblProducts",
Click to expand...
"[ProdID]
=
" & Forms![frmMainForm]![sfrmProducts]!cbxProdDescript)

Except for the sfrm, it matches exactly!

Getting:
Run-time error '3075'
Syntax error (missing operator) in query expression "[ProdID] = Button down
shirt.

Button down shirt is the [ProdID]

Don't see where an operator is needed nor understand why it's returning the
error.

Doug; Your a good man.

When I typed that last sentence got to thinking...

Why am I seeing "Button down shirt" instead of a number?

Opened the tbl. [ProdID] the Primary Key Data Type was Text.
(Had created a set of synchronized Combo Boxes earlier following MSFT
instructions where the Primary Key Data Type HAS to be Text, and simply
copied and pasted one of those tables to use in a different situation.)

Added an AutoNumber Primary Key and everything works!

Thank You.

Doug; Your a good man.

Andy

Douglas J. Steele said:
Put your lookup code in the combo box's AfterUpdate event.

Are your product descriptions really in excess of 255 characters? Can you
not survive with shorter descriptions?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas/John;

In answer to the question why store redundant information in two tables.

The Combo Box displays a title for a description of the product, when
selected the After Update event:
Me![txtProdDescript] = Me![cbxProdDescript].Column(1)

Populates the full description of the product in [txtProdDescript].

I believe Both of You are correct and a database lookup is neccessary
because the description is being trunicated.

I know how to create a lookup when only one record is in a table:
Example: =DLookUp("[CompanyName]","tblCompany")

What I haven't learned yet is how to LookUp a specific record that is
related to a Combo Box.

Searched MSFT's Access Support, Nothing.

Can You point me in the right direction?

Thank You.

Andy
Click to expand...
wrote:

Anyone have an answer to this?

You cannot include an entire memo field (which might be hundreds of
megabytes in size!) in a combo control. It simply won't let you - it
will truncate it to 255 bytes.

Could you explain why you want to store a Memo field redundantly
Click to expand...
Click to expand...
in
a
Click to expand...
so!
If
Click to expand...
Click to expand...
 
Back
Top