Form Field limited to 255 characters

[BradCustom]

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.

 

[BradCustom]

Andy,
The code without the &"'" at the end returns a syntax error. The code as you wrote it returns a blank/null value for the txtOrgChange field.

 

[BradCustom]

Duane,
I tried your suggestion again so that I could give you more accurate feed back. Here are the results

? DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='CMV-760V2'")

Returned Null

SELECT Operation_Service, Note_Text FROM dbo_Job_Operation WHERE Operation_Service like "*CMV*"

Returned a Compile Error

 

[Andrzejek]

[tt]? DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='CMV-760V2'") [/tt]
Returned Null

it is an equivalent to:
[tt]
Select [Note_Text] From dbo_Job_Operation
Where [Operation_Service] = 'CMV-760V2'[/tt]

What do you get when you run the Select statement above? NULL?
What do you expect to get?


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Andy,
When I run the Select statement
Select [Note_Text] From dbo_Job_Operation
Where [Operation_Service] = 'CMV-760V2'

I get a Compile Error.

 

[BradCustom]

Andy,
I just tried this and got the same Compile Error (Expected Case)

Select [Note_Text] From dbo_Job_Operation Where [Job] = '202458' and [Operation_Service] = 'CMV-760V2'

 

[BradCustom]

Everyone,
I think I found one issue.

On the Combobox Operation which is named cboOperation the bound column is 1 which equates to the field WC_Vendor. Here is the Row Source for cboOperation.

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;

 

[BradCustom]

Everyone,

SUCCESS......SUCCESS......SUCCESS......SUCCESS......

The problem was using the wrong field in the DLookup statement. The first statement is listed below.
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='" _
& Forms![frm_ChangeEntry]!Operation & "'")

Because the bound column on Combobox Operation is 1 and that field is WC_Vendor the DLookup should have been the following.
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[WC_Vendor]='" _
& Forms![frm_ChangeEntry]!Operation & "'")

Thank you for everyone's help!!
I really appreciate it!!!

 

[BradCustom]

Everyone,
Ok, so the success has been short lived

The DLookup works in the sense that I don't get the same text on the first selection but it's selecting the wrong Note_text value. So I've tried to change the DLookup to include the value from the Job combobox. Below is my attempt that I haven't been able to get working.

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

Any help would be appreciated!!

 

[Andrzejek]

[tt]
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[WC_Vendor]='" _
& Forms![frm_ChangeEntry]!Operation[blue] & "' And {WhatField?} = '" & [/blue]Forms![frm_ChangeEntry]!Job & "'")
[/tt]
You may be better off if you do this approach:

Dim strWhere As String

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!Operation[blue] & "' And {WhatField?} = '" & [/blue]Forms![frm_ChangeEntry]!Job & "'"

Debug.Prin strWhere

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

This way you may have a fighting chance of getting your DLookup right

---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Andy,
I've been trying to make your suggestion work but so far I haven't had any success.

I'm thinking it's a syntax problem on my part.

I'm thinking the {WhatField?} is the field I'm retrieving the data from so the Forms![frm_ChangeEntry]!Job field is dbo.Job.Operation.Job
Here's what I tried.
Me.txtOrgChange.Value = DLookup("[Note_Text]", "dbo_Job_Operation", "[WC_Vendor]='" _
& Forms![frm_ChangeEntry]!Operation & "' And {Job} ='" & Forms![frm_ChangeEntry]!Job & "'")

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

Thanks for your help!!

 

[Andrzejek]

What do you get from these 2 statements:[tt]
Debug.Print Forms![frm_ChangeEntry]!Operation
Debug.Print Forms![frm_ChangeEntry]!Job [/tt]

Assuming [tt]WC_Vendor[/tt] and [tt]Job[/tt] fields in your table are text, your DLookup is equivalent to:
[tt]
Select Note_Text From dbo_Job_Operation
Where WC_Vendor = 'outcome of first Debug above'
And Job = 'outcome of second Debug above'[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Andy,
Here is what I get from the two statements;
Debug.Print Forms![frm_ChangeEntry]!Operation
GR-OGM1224
Debug.Print Forms![frm_ChangeEntry]!Job
202435

I tried your Select statement but I'm not very good when it comes to VBA syntax so I know what I've done is wrong.
Me.txtOrgChange.Value = Select Note_Text From dbo_Job_Operation
Where Job = Debug.Print Forms![frm_ChangeEntry]!Job
And WC_Vendor = Debug.Print Forms![frm_ChangeEntry]!Operation

Thanks for your help!!

 

[dhookom]

Brad,
You are continually entering SQL statements like "Select Note_Text From dbo_Job_Operation" in your VBA code. These belong in a SQL view of a query. I had stated "Then consider creating a query like:" which is not VBA.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Like Duane says, do it in a SQL view of a query, no VBA needed.

And since your Job is a Number, and your Operation / WC_Vendor is Text, your SQL would be:

[tt]Select Note_Text From dbo_Job_Operation
Where WC_Vendor = 'GR-OGM1224'
And Job = 202435[/tt]

And your DLookup would be:

Dim strWhere As String

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

Debug.Print strWhere

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

So when you run this code, your will see in the Immediate Window:

[tt]WC_Vendor = 'GR-OGM1224' And Job = 202435[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Andy,

Both Job and WC_Vendor are text fields.​

 

[BradCustom]

Andy,
Maybe I'm missing something but your SQL query doesn't make any sense to me. Why would you limit the record set to for WC_Vendor to GR-OGM1224 and for Job to 202435?
The values for Job and WC_Vendor are determined by the user input on the frm_ChangeEntry form.

Thanks for your help!

 

[Andrzejek]

Yes, I know that.
What I am trying to show you here is how you can establish valid and complete DLookup syntax in your code with all & and _ and ' and such.. I always start with hard-coded values in my SQL (or the Where portion of my Select statement) so I can make sure it works correctly, and then I replace my hard-coded values with the selection(s) from my user.

Job looked like a number to me, but since you said it is a text field, then the value should be surrounded by single quotes:

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

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Brad,
A lot of the suggestions both Andy and I have provided are basic troubleshooting. By creating a query with the suggested SQL, you should be able to see if the value returned is reasonable. We often "hard code" values into queries and/or code to see if they work in their simplest form.

Since "Both Job and WC_Vendor are text fields", try change the code to:

Dim strWhere As String

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

Debug.Print strWhere

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

If this doesn't work then you must come back with the value in the debug window and tell us what is wrong with it.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

At least Andy and I are consistent all the way to the detail level [highlight #FCE94F]highlighting text[/highlight] ;-)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BradCustom]

Guys,
Ok I understand, it was confusing why you were limiting the selection. I will try your suggestions to ensure that I get the expected result.

Thank you for your help!!