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
Andy/Duane,

Your suggestions worked as expected. I got the correct results each time I selected a different value.

Thank you for your help!
 
Upvote 0 Downvote
Great! Congratulation [thumbsup2]

Side question:
Is there any possibility you can have an apostrophe (a single quote) anywhere in [tt]WC_Vendor[/tt] or [tt]Job[/tt] fields? Or in [tt]Forms![frm_ChangeEntry]!Operation[/tt] or [tt]Forms![frm_ChangeEntry]!Job [/tt]?


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Andy,
Thanks!

Do you mean in the entire record set?
If that is what you mean then yes it is possible but not very likely because the Job field is usually a number but there are times when a sub-component job is created and it will have a value similar to this 202435-1 or 202435-1A. The WC_Vendor comes from another table in the SQL database and that table follows a strict naming convention which doesn't include apostrophes.

Brad
 
Upvote 0 Downvote
I've run into an issue.
I have multiple records that equal the first two criteria so I've had to add a third criteria.
Below is the change I made.
Code: 
Dim strWhere As String

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!Operation & "' [highlight #FCE94F]And OpNo = '" & Forms![frm_ChangeEntry]!OpNo & "'[/highlight] And Job = '" & Forms![frm_ChangeEntry]!Job & "'"

Debug.Print strWhere

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", strWhere)

Here is the output;
Code: 
when job is selected: [WC_Vendor] = 'CMV-KIT3XI' And OpNo = '' And Job = '202550'

when Operation is selected: [WC_Vendor] = 'CMV-KIT3XI' And OpNo = '60' And Job = '202550'

I also tried it this way but got similar results.
Code: 
Dim strWhere As String

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!Operation & "' [highlight #FCE94F]And Job = '" & Forms![frm_ChangeEntry]!Job & "'[/highlight] And OpNo = '" & Forms![frm_ChangeEntry]!OpNo & "'"

Debug.Print strWhere

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", strWhere)

Here are the results;
Code: 
when job is selected: [WC_Vendor] = 'CMV-KIT3XI' And Job = '202550' And OpNo = ''

when Operation is selected: [WC_Vendor] = 'AS-ASSY' And Job = '202550' And OpNo = '60'

What happens is on the first selection of the Operation value the OrgChange field is not populated with the value from Note_Text. On the second selection the OrgChage field is populated however if the Job and Operation selection are equal to another record then the OrgChange field is populated with the value of the first Note_Text field that is equal to the selection.

I'm going to continue trying but any ideas would be appreciated!
 
Upvote 0 Downvote
It would be good to see the entire procedure where you have this code, but I would guess you may just add:

Code: 
...[blue]
If Len(Forms![frm_ChangeEntry]!Operation & "") * Len(Forms![frm_ChangeEntry]!OpNo & "") * Len(Forms![frm_ChangeEntry]!Job & "") = 0 Then
    Me.txtOrgChange.Value = ""
    Exit Sub
End If
[/blue]
Dim strWhere As String

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!Operation & "' And OpNo = '" & Forms![frm_ChangeEntry]!OpNo & "' And Job = '" & Forms![frm_ChangeEntry]!Job & "'"

Debug.Print strWhere

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", strWhere)


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Unfortunately that didn't solve the problem. Here is the complete procedure including your new suggestion.

Code: 
Private Sub cboOperation_AfterUpdate()
If Len(Forms![frm_ChangeEntry]!Operation & "") * Len(Forms![frm_ChangeEntry]!OpNo & "") * Len(Forms![frm_ChangeEntry]!Job & "") = 0 Then
    Me.txtOrgChange.Value = ""
    Exit Sub
End If
Dim strWhere As String

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!Operation & "' And OpNo = '" & Forms![frm_ChangeEntry]!OpNo & "' And Job = '" & Forms![frm_ChangeEntry]!Job & "'"

Debug.Print strWhere

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", strWhere)

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

Thanks for your help!!
 
Upvote 0 Downvote
I typically split up the "Where" controls:

Code: 
Dim strWhere As String
[COLOR=#4E9A06]' Assuming all filtering fields are text[/color]
strWhere =  "1=1 "  [COLOR=#4E9A06]'just start it out with really no filter[/color]
If Not IsNull(Forms![frm_ChangeEntry]!Operation) Then
    strWhere = strWhere & " AND [WC_Vendor] = '" & Forms![frm_ChangeEntry]!Operation & "' " 
End If
If Not IsNull(Forms![frm_ChangeEntry]!Job) Then
    strWhere = strWhere & " AND Job = '" & Forms![frm_ChangeEntry]!Job & "' "
End If
If Not IsNull(Forms![frm_ChangeEntry]!OpNo) Then
    strWhere = strWhere & " AND OpNo = '" & Forms![frm_ChangeEntry]!OpNo & "'"
End If
Debug.Print strWhere

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", strWhere)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
Duane,
Unfortunately your suggestion yields the same result as the previous code.​
Does it matter that I'm populating the OpNo field based on the selection made in the cboOperation?

Thanks for your help!!
 
Upvote 0 Downvote
I don't know if this helps but here is the Row Source for Operation.

Code: 
SELECT dbo_Job_Operation.Job_Operation, dbo_Job_Operation.WC_Vendor, dbo_Job_Operation.Operation_Service, dbo_Job_Operation.Est_Setup_Hrs, dbo_Job_Operation.Run, dbo_Job_Operation.Run_Method, dbo_Job_Operation.Note_Text, 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.Sequence;


The field that I use to popilate OpNo is Operation_Service.
 
Upvote 0 Downvote
Brad,
Please, always reply back with what you see in the debug window. This is in there for a purpose of assisting with troubleshooting.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
In my opinion, this is a business problem.

To populate [tt]txtOrgChange[/tt] first you needed just [tt]WC_Vendor[/tt].
Then you found out that's not enough, so you added [tt]Job[/tt] to your criteria.
Then you found out that's not enough, so you added [tt]OpNo[/tt] to your criteria.

Is that the end? Or you need more criteria?
And - do you ALWAYS need all 3 criteria to determine the value of [tt]txtOrgChange[/tt]? Or is it a combination of the 3 with the possibility of any of them missing?

"that didn't solve the problem" is not an answer.
What did you get and how that differs from what you expected?

---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Duane,
Sorry about that! Here is the output.

I didn't notice this error until I was writing this post.

Here is the output from my first selection.
Code: 
1=1  AND [WC_Vendor] = 'CMV-800VG2'  AND Job = '202550'  AND OpNo = '10'
1=1  AND [WC_Vendor] = 'CMV-800VG2'  AND Job = '202550'  AND OpNo = '20'

The problem I see with this output is the OpNo is wrong for both selections. The correct output is listed below.
Code: 
1=1  AND [WC_Vendor] = 'CMV-800VG2'  AND Job = '202550'  AND OpNo = [highlight #FCE94F]'20'[/highlight]
1=1  AND [WC_Vendor] = 'CMV-800VG2'  AND Job = '202550'  AND OpNo = [highlight #FCE94F]'30'[/highlight]


I did one more selection to see if it repeated, it's listed below.
Code: 
1=1  AND [WC_Vendor] = 'CMV-800VG2'  AND Job = '202550'  AND OpNo = '20'1=1  AND [WC_Vendor] = 'CMV-KIT3XI'  AND Job = '202550'  AND OpNo = '[highlight #FCE94F]30[/highlight]'
1=1  AND [WC_Vendor] = 'CMV-KIT3XI'  AND Job = '202550'  AND OpNo = '[highlight #FCE94F]60[/highlight]'

This is what the output should have been. I selected OpNo 60 first and then OpNo 10
Code: 
[code]1=1  AND [WC_Vendor] = 'CMV-800VG2'  AND Job = '202550'  AND OpNo = '20'1=1  AND [WC_Vendor] = 'CMV-KIT3XI'  AND Job = '202550'  AND OpNo = '[highlight #FCE94F]60[/highlight]'
1=1  AND [WC_Vendor] = 'CMV-KIT3XI'  AND Job = '202550'  AND OpNo = '[highlight #FCE94F]10[/highlight]'



 
Upvote 0 Downvote
This is the output from Andy's suggestion.

First selection output is wrong.
Code: 
[WC_Vendor] = 'CMV-800VG2' And OpNo = '10' And Job = '202550'
[WC_Vendor] = 'CMV-800VG2' And OpNo = '20' And Job = '202550'

First selection output should have been.
Code: 
[WC_Vendor] = 'CMV-800VG2' And OpNo = [highlight #FCE94F]'20'[/highlight] And Job = '202550'
[WC_Vendor] = 'CMV-800VG2' And OpNo = [highlight #FCE94F]'30'[/highlight] And Job = '202550'


Second selection is also wrong.
Code: 
[WC_Vendor] = 'CMV-KIT3XI' And OpNo = '30' And Job = '202550'
[WC_Vendor] = 'CMV-KIT3XI' And OpNo = '60' And Job = '202550'

Second selection output should have been.
Code: 
[WC_Vendor] = 'CMV-KIT3XI' And OpNo = [highlight #FCE94F]'10'[/highlight] And Job = '202550'
[WC_Vendor] = 'CMV-KIT3XI' And OpNo = '60' And Job = '202550'
 
Upvote 0 Downvote
What do you mean by: "output is wrong"?
If you select 10 in OpNo, you get OpNo = '10', your code does make up any numbers.
Looks like you have 3 combo boxes (are they all combo boxes?): cboOperation, OpNo, and Job.
Your code resides only in cboOperation, so if you select (enter?) anything in OpNo ot Job, your code does not execute.

Any why do you show 2 lines from Immediate Window? Any execution of this code should give you just one line.

Could you also answer my post from 20 May 19 17:45 ?


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Andy,
I have 2 comboboxes, cboJob and cboOperation. OpNo is a textbox and is filled based on the selection made in cboOperation. And yes the majority of the code is in the Event Procedure for cboOperation

What I mean by the output is wrong is I selected
CMV-800VG2 OpNo = 20 Job = 202550
CMV-800VG2 OpNo = 30 Job = 202550

But the output showed that I selected;
CMV-800VG2 OpNo = [highlight #FCE94F]10[/highlight] Job = 202550
CMV-800VG2 OpNo = [highlight #FCE94F]20[/highlight] Job = 202550

It makes no sense to me either. I didn't notice it until I started writing the post.
 
Upvote 0 Downvote
I know you have a combo box named [tt]cboOperation[/tt].
But you state that you have another combo box named [tt]cboJob[/tt], but in your code you have [tt]Forms![frm_ChangeEntry]!Job[/tt], not [tt]Forms![frm_ChangeEntry]![highlight #FCE94F]cbo[/highlight]Job[/tt]
So, which is it...?

"OpNo is a textbox and is filled based on the selection made in cboOperation" - by 'is filled' do you mean 'data in OpNo text box is entered by hand'?


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Andy,
Could you also answer my post from 20 May 19 17:45 ?
To populate txtOrgChange first you needed just WC_Vendor.
Then you found out that's not enough, so you added Job to your criteria.
Then you found out that's not enough, so you added OpNo to your criteria.

Is that the end? Or you need more criteria?
And - do you ALWAYS need all 3 criteria to determine the value of txtOrgChange? Or is it a combination of the 3 with the possibility of any of them missing?

"that didn't solve the problem" is not an answer.
What did you get and how that differs from what you expected?

Actually, I always had Job as part of my criteria, in fact that is the first criteria. I may not have mentioned in the beginning because I was focused on limiting the Operation selection by Job number.

What I thought would be a fairly simple project has turned into something that is fairly complex. The original goal was simple. Provide a user with a Form where they could select a Job, once the Job was selected the list of operations would be limited by the user selection. Next when the user selects the Operation return the Note_Text for that Operation. I wanted to capture this selection so that I had a From-To state. Something we don't have in our ERP system. Looking back at the path this has taken it would have been better to use the Job_OperationID because then there would be no doubt as to what record was selected.
 
Upvote 0 Downvote
Andy,
But you state that you have another combo box named cboJob, but in your code you have Forms![frm_ChangeEntry]!Job, not Forms![frm_ChangeEntry]!cboJob
So, which is it...?
Yes I do have a cboJob and as to why my code says Forms![frm_ChangeEntry]!Job this is what was suggested in this thread!


"OpNo is a textbox and is filled based on the selection made in cboOperation" - by 'is filled' do you mean 'data in OpNo text box is entered by hand'?
OpNo is filled/populated by the Event Procedure in cboOperation
Code: 
Private Sub cboOperation_AfterUpdate()

If Len(Forms![frm_ChangeEntry]!Operation & "") * Len(Forms![frm_ChangeEntry]!OpNo & "") * Len(Forms![frm_ChangeEntry]!Job & "") = 0 Then
    Me.txtOrgChange.Value = ""
    Exit Sub
End If
Dim strWhere As String

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!Operation & "' And OpNo = '" & Forms![frm_ChangeEntry]!OpNo & "' And Job = '" & Forms![frm_ChangeEntry]!Job & "'"

Debug.Print strWhere

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", strWhere)

[highlight #FCE94F]Me.txtOPNo.Value = [cboOperation].[Column](2)[/highlight]
Me.txtSetupHrs.Value = [cboOperation].[Column](3)
Me.txtRunRate.Value = [cboOperation].[Column](4)
Me.txtRunType.Value = [cboOperation].[Column](5)
End Sub
 
Upvote 0 Downvote
Try this:

Code: 
Private Sub cboOperation_AfterUpdate()
Dim strWhere As String
[blue]
Me.txtOPNo.Value = [cboOperation].[Column](2)
Me.txtSetupHrs.Value = [cboOperation].[Column](3)
Me.txtRunRate.Value = [cboOperation].[Column](4)
Me.txtRunType.Value = [cboOperation].[Column](5)
[/blue]
If Len(Forms![frm_ChangeEntry]!Operation & "") * Len(Forms![frm_ChangeEntry]!OpNo & "") * Len(Forms![frm_ChangeEntry]!Job & "") = 0 Then
    Me.txtOrgChange.Value = ""
    Exit Sub
End If

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!Operation & "' And OpNo = '" & Forms![frm_ChangeEntry]!OpNo & "' And Job = '" & Forms![frm_ChangeEntry]!Job & "'"

Debug.Print strWhere

Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", strWhere)

End Sub

You are going to go mad if that solves your problem.... :)


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
I tried that but I will try it again.

I thought it could be a timing issue.
 
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
423
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