Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Form Field limited to 255 characters

Status
Not open for further replies.
BradCustom

BradCustom

IS-IT--Management
Oct 5, 2007
296
US
Hi all,
I'm working on Access 2016 and I'm having a problem showing a Long Text field on my Form. I have three tables they are TravelerChange, dbo_Job and dbo_Job_Operation. The two dbo tables are linked tables from a SQL database. The fields I'm using are TravelerChange.OrgChange (formatted as Long Text), TravelerChange.Operation (formatted as Short Text), dbo_Job_Operation.WC_Vendor (formatted as Short Text), dbo_Job_Operation.Note_Text (formatted as Long Text).

The issue I'm having is to get the dbo_Job_Operation.Note_Text to display more than 255 characters on the Form.

The Form's Record Source is: SELECT TravelerChange.* FROM TravelerChange;

I have a combo box on the form which looks up the WC_Vendor from the dbo_Job_Operations table and stores it's value into TravelerChange.Operation.

Here is the query that I use;
SELECT dbo_Job_Operation.WC_Vendor, dbo_Job_Operation.Operation_Service, dbo_Job_Operation.Note_Text, dbo_Job_Operation.Job_OperationKey, dbo_Job.Job
FROM dbo_Job INNER JOIN dbo_Job_Operation ON dbo_Job.Job = dbo_Job_Operation.Job
WHERE (((dbo_Job.Job)=[Forms]![frm_ChangeEntry]![cboJob]))
ORDER BY dbo_Job_Operation.Job_OperationKey;

I've added the field TravelerChange.OrgChange to the form and populate it with the following Event Procedure;
Private Sub cboOperation_AfterUpdate()
Me.txtOrgChange.Value = [cboOperation].[Column](2)
End Sub

Everything works fine except if the text is more than 255 characters then the result is truncated.

Any thoughts as to why the Form is only displaying 255 characters would be greatly appreciated.
 
Sort by date Sort by votes
We missed the alias of JO in all of the back and forth.

Code: 
strSQL = "SELECT JO.Note_Text FROM dbo_Job_Operation [highlight #FCE94F]JO[/highlight] " & _
"WHERE JO.Job= """ & [Forms]![frm_ChangeEntry]![cboJob] & """"
[COLOR=#4E9A06]' add this next line for troubleshooting[/color]
Debug.Print "strSQL: " & strSQL

Can you confirm Job is a text field?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
As suggested much earlier, you could try DLookup()
Code: 
Private Sub cboOperation_AfterUpdate() 

    Me.txtOrgChange = DLookup("Note_Text","dbo_Job_Operation","Job= """ & [Forms]![frm_ChangeEntry]![cboJob] & """")

    Me.txtSetupHrs.Value = [cboOperation].[Column](3)
    Me.txtRunRate.Value = [cboOperation].[Column](4)
    Me.txtRunType.Value = [cboOperation].[Column](5)
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
Instead of

Code: 
[blue][Private Sub cboOperation_AfterUpdate()
    Me.txtOrgChange.Value = [cboOperation].[Column](2)
End Sub[/blue]

Try

Code: 
[blue]Private Sub cboOperation_AfterUpdate()
    Me.txtOrgChange.Value = [cboOperation].Recordset.Fields(2)
End Sub[/blue]
 
Upvote 0 Downvote
Thank you strongm and Duane!!!

Strongm'a suggestion works perfectly!

Private Sub cboOperation_AfterUpdate()
Me.txtOrgChange.Value = [cboOperation].Recordset.Fields(2)
End Sub


Exactly what I wanted thank you guys very much!!

Brad
 
Upvote 0 Downvote
Hi strongm,
I didn't realize the combo box had a recordset. It seems to work as hoped in my tests.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
Hi guys,
I'm noticing something unexpected with populating the OrgChange text field with the following Event Procedure from cboOperation.

Private Sub cboOperation_AfterUpdate()

Me.txtOrgChange.Value = [cboOperation].Recordset.Fields(2)

Me.txtSetupHrs.Value = [cboOperation].[Column](3)
Me.txtRunRate.Value = [cboOperation].[Column](4)
Me.txtRunType.Value = [cboOperation].[Column](5)
End Sub

What I'm seeing is when I select a value in cboOpeation the text that is populated in OrgChange is the same each time (at least for the 5 times I've tried). If I select the same value in cboOperation again then the correct text is populated into OrgChange. I'm wondering is the first text is saved somewhere or something?

Any ideas on this issue?
Thanks for your help!
 
Upvote 0 Downvote
Here is my first attempt at changing the code to a Dlookup()
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service] =" _
& Forms![frm_ChangeEntry]!Operation)

Of course it doesn't work...lol
I get the following error
Run-time error '3075':

Syntax error (missing operator) in query expression '[Operation_Service] =CMV-760V2'


Any thoughts on this attempt?
Thanks for your help!
 
Upvote 0 Downvote
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='" _
& Forms![frm_ChangeEntry]!Operation &"'")

Looks like your field criteria [Operation_Service] is a text field, so your dlookup needs to have the form value in ' (apostrophes)


There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That DonÆt.

 
Upvote 0 Downvote
Blorf,
Unfortunately your suggestion returns nothing. The text field txtOrgChange isn't populated with the text from Note_Text​

Any ideas as to why?
Thanks!
 
Upvote 0 Downvote
Brad,
You should do some trouble-shooting. Start by pressing Ctrl+G and entering

Code: 
? DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='CMV-760V2'")
Then consider creating a query like:
SQL: 
SELECT Operation_Service, Note_Text FROM dbo_Job_Operation WHERE Operation_Service like "*CMV*"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
>the text that is populated in OrgChange is the same each time

Ah. I'd imagine that's because your combobox is bound.

So, what you need to do is delete the combobox's ControlSource property, and set the RowSource to [tt][blue]SELECT TravelerChange.* FROM TravelerChange[/blue][/tt] or even just [tt][blue]TravelerChange [/blue][/tt]
 
Upvote 0 Downvote
Strongm,
I'm not sure I understand your suggestion.
The table I'm getting Note_Text from is a linked SQL table called dbo_Job_Operation.

I'm not following the logic behind Selecting from the table (TravelerChange) I am trying to populate.

Thanks for your help!
 
Upvote 0 Downvote
Just me not paying full attention to all of your posts. SO is your RowSource for the combobox:

SELECT dbo_Job_Operation.WC_Vendor, dbo_Job_Operation.Operation_Service, dbo_Job_Operation.Note_Text, dbo_Job_Operation.Job_OperationKey, dbo_Job.Job
FROM dbo_Job INNER JOIN dbo_Job_Operation ON dbo_Job.Job = dbo_Job_Operation.Job
WHERE (((dbo_Job.Job)=[Forms]![frm_ChangeEntry]![cboJob]))
ORDER BY dbo_Job_Operation.Job_OperationKey;
 
Upvote 0 Downvote
The RowSource is below:

SELECT dbo_Job_Operation.WC_Vendor, dbo_Job_Operation.Operation_Service, dbo_Job_Operation.Note_Text, dbo_Job_Operation.Est_Setup_Hrs, dbo_Job_Operation.Run, dbo_Job_Operation.Run_Method, dbo_Job_Operation.Job_OperationKey, dbo_Job.Job, dbo_Job_Operation.Sequence
FROM dbo_Job INNER JOIN dbo_Job_Operation ON dbo_Job.Job = dbo_Job_Operation.Job
WHERE (((dbo_Job.Job)=[Forms]![frm_ChangeEntry]![cboJob]))
ORDER BY dbo_Job_Operation.Job_OperationKey;
 
Upvote 0 Downvote
Duane,
I can't seem to get the DLookup to work. Obviously I'm doing something wrong. The closest I got was getting the DLookup to return the value of odb.Job_operation.Operation_Service. I actually don't know why the statement I used returned the Operation_Service value, I expected it to return the value of Note_Text. So I'm at a loss.

Here is the code I used and I did try your suggestions but I got the same results.
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service] =" _
& Forms![frm_ChangeEntry]!Operation)

Oh I almost forgot, Blorf's suggestion returned a blank/null result. Here is that suggestion.
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='" _
& Forms![frm_ChangeEntry]!Operation &"'")

The interesting part is the Me.txtOrgChange.Value = [cboOperation].Recordset.Fields(2) works fine as long as your select the a value in cboOperation twice. I don't know where the first text is coming from but it is the same text every time regardless of the Job of Operation selected. I also made sure on the form that Modal is set to No.

Anyway any ides would be greatly appreciated!!

 
Upvote 0 Downvote
BradCustom said:
Blorf's suggestion returned a blank/null result. Here is that suggestion.
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='" _
& Forms![frm_ChangeEntry]!Operation &"'")
Click to expand...

Did you try:

Code: 
[blue]Debug.Print Forms![frm_ChangeEntry]!Operation [/blue]

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='" _
 & Forms![frm_ChangeEntry]!Operation &"'")

And see what you get from Debug statement before you run your Dlookup...


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Andrzejek,
I tried your suggestion but it returns a blank/null value. I also tried this but received a syntax error.
Debug.Print Forms![frm_ChangeEntry]!Operation

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='" _
& Forms![frm_ChangeEntry]!Operation)
 
Upvote 0 Downvote
So the Debug returned blank/null/syntax error ?
Just trying to see what you have in Forms![frm_ChangeEntry]!Operation


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheresAlwaysAWay
  • Locked
  • Question
Can Access be used to generate and respond to text messages? 1
Replies
6
Views
755
TheresAlwaysAWay
TheresAlwaysAWay
renigar
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
424
renigar
renigar
ameedoo3000
  • Locked
  • Question
format partition by vba code
Replies
6
Views
416
mattKnight
mattKnight
lmcate
  • Locked
  • Question
How to Open ACCESS (MDB) from an HTML Page 1
Replies
2
Views
681
lmcate
lmcate
atadbitslow
  • Locked
  • Question
how to search data and display matches that include the user's input, but not necessarily match
Replies
5
Views
249
atadbitslow
atadbitslow

Part and Inventory Search

Sponsor

Back
Top