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.

 

[Andrzejek]

This is really confusing.
By looking at your code - you have a text box named [red][tt]txtOPNo[/tt][/red], but you refer to it as [tt]Forms![frm_ChangeEntry]!OpNo[/tt], not as [tt]Forms![frm_ChangeEntry]![red]txtOpNo[/red][/tt], the same goes for combo box [tt]cboOperation / Operation, cboJob / Job[/tt], etc.

You either have all of these 'double' controls on the form, of you should have a whole lot of syntax errors while running your code.

Are all those controls on the same Form named [tt]frm_ChangeEntry[/tt]?


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Ok, so I copied and pasted your entire code, saved it and then closed the form. I opened the Form and selected Job 202550 and then Operation CMV-KIT3XI which is OpNo 60 but the text that was populated into orgChange from Note_Text was from Operation CMV-KIT3XI, OpNo 10.

I don't get why it's doing this because the Row Source for cboOperation uses the Job_Operation (I called it Job_OperationID) which should return the records for the selection made in cboOperation. After all the data is coming from one table dbo.Job_Operation.


Frustrating

 

[BradCustom]

Andy,
You either have all of these 'double' controls on the form, of you should have a whole lot of syntax errors while running your code.

Before I copied and pasted your last suggestion I had changed all the references to comboboxes to be cboNAME. Unfortunately it didn't seem to correct the problem.

 

[Andrzejek]

So what code do you have now and what is the problem?


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Good Morning Andy,
Below is the entire code for both cboJob and cboOperation as well as each cobobox's Row Source.

cboJob

Private Sub cboJob_AfterUpdate()
Me.txtPart.Value = [cboJob].[Column](1)
Me.txtPartRev.Value = [cboJob].[Column](2)
Me.txtPartName.Value = [cboJob].[Column](3)
End Sub

cboJob Row Source

SELECT dbo_Job.Job, dbo_Job.Part_Number, dbo_Job.Rev, dbo_Job.Description, dbo_Job.Status
FROM dbo_Job
WHERE (((dbo_Job.Status)="Active"))
ORDER BY dbo_Job.Job DESC;

cboOperation

Private Sub cboOperation_AfterUpdate()


Dim strWhere As String
Me.txtOPNo.Value = [cboOperation].[Column](2)
Me.txtSetupHrs.Value = [cboOperation].[Column](3)
Me.txtRunRate.Value = [cboOperation].[Column](4)
Me.txtRunType.Value = [cboOperation].[Column](5)

If Len(Forms![frm_ChangeEntry]!cboJob & "") * Len(Forms![frm_ChangeEntry]!cboOperation & "") * Len(Forms![frm_ChangeEntry]!txtOPNo & "") = 0 Then
    Me.txtOrgChange.Value = ""
    Exit Sub
End If

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!cboOperation & "' And OpNo = '" & Forms![frm_ChangeEntry]!txtOPNo & "' And Job = '" & Forms![frm_ChangeEntry]!cboJob & "'"

Debug.Print strWhere

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

End Sub

cboOperation Row Source

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;

This field dbo_Job_Operation.Job_Operation is the unique identifier for each Job Operation.

 

[BradCustom]

Still the same problem, Note_Text is not the correct value.

 

[Andrzejek]

Do all of your controls 'reside' / are placed on the same form named [tt]frm_ChangeEntry[/tt]?


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Yes they are all on frm_ChangeEntry

 

[Andrzejek]

Then your code may look a lot simpler:

Private Sub cboOperation_AfterUpdate()
Dim strWhere As String

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

If Len(Me.cboJob & "") * Len(Me.cboOperation & "") * Len(Me.txtOPNo & "") = 0 Then
    Me.txtOrgChange.Value = ""
    Exit Sub
End If

strWhere = "[WC_Vendor] = '" & Me.cboOperation & _
    "' And OpNo = '" & Me.txtOPNo & _
    "' And Job = '" & Me.cboJob & "'"

Debug.Print strWhere

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

End Sub

And as far as "Note_Text is not the correct value" place some break points in your code, step thru your code and examine variables / data in controls to see what's going on step-by-step


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Unfortunately the output is the same as before.

So here is what I've done.
I added a Debug.Print txtOrgChange so that I could see what text the code is calling.

So, here is the output from your code in the last post.

[WC_Vendor] = 'CMV-KIT3XI' And OpNo = '60' And Job = '202550'

[highlight #FCE94F][b]Note_Text[/b][/highlight]
CNC VERT. MILL
USE FIXTURE AF00394

REFERENCE OPERATION DRAWING PAGE 1
NOTE: USE UNIGAGE TO MEASURE OD OF EACH PIECE.
REFERENCE  DRAWING 35 54004 4121 5

SEMI FINISH FACE TO AOL 7.485 REF. DIM. AND MAINTAIN 1.125 DIM. TO 1.140.
       "REMOVE CLAMPS & ADD BOLT THRU CENTER"
SEMI FINISH 4.0935 DIA. TO 4.123/4.125 DIA. X .373 .375 DEPTH WITH .020 .020 CORNER 
RAD. & CHAMFER .030 X 45 DEG.
****************************************************************************
1ST PIECE ACCEPTANCE:

STP/INT:______________________ DATE____________________
****************************************************************************

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

I then looked at our ERP system and the Note_Text result doesn't match our ERP system. I started a new query on the dbo.Job.Operation and added the fields I've been working with and the first query was the following.

SELECT dbo_Job_Operation.Job_Operation, dbo_Job_Operation.Job, dbo_Job_Operation.WC_Vendor, dbo_Job_Operation.Operation_Service, dbo_Job_Operation.Sequence, dbo_Job_Operation.Note_Text
FROM dbo_Job_Operation
WHERE (((dbo_Job_Operation.Job)="202550") AND ((dbo_Job_Operation.WC_Vendor)="CMV-KIT3XI"));

The output from that query returned 2 records, one for Op 10 and one for Op 60. It's listed below:

[highlight #FCE94F]Job_Operation[/highlight]	[highlight #D3D7CF]Job[/highlight]	[highlight #729FCF]WC_Vendor[/highlight]	[highlight #8AE234]Operation_Service[/highlight]	[highlight #EF2929]Sequence[/highlight]	Note_Text
[highlight #FCE94F]278206[/highlight]	[highlight #D3D7CF]202550[/highlight]	[highlight #729FCF]CMV-KIT3XI[/highlight]	[highlight #8AE234]10[/highlight]	[highlight #EF2929]2[/highlight]	

[highlight #FCE94F][b]Note_Text[/b][/highlight]
"CNC VERT. MILL
USE FIXTURE AF00394

REFERENCE OPERATION DRAWING PAGE 1
NOTE: USE UNIGAGE TO MEASURE OD OF EACH PIECE.
REFERENCE  DRAWING 35 54004 4121 5

SEMI FINISH FACE TO AOL 7.485 REF. DIM. AND MAINTAIN 1.125 DIM. TO 1.140.
       ""REMOVE CLAMPS & ADD BOLT THRU CENTER""
SEMI FINISH 4.0935 DIA. TO 4.123/4.125 DIA. X .373 .375 DEPTH WITH .020 .020 CORNER 
RAD. & CHAMFER .030 X 45 DEG.
****************************************************************************
1ST PIECE ACCEPTANCE:

STP/INT:______________________ DATE____________________
****************************************************************************

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____"


[highlight #FCE94F]278213	202550	CMV-KIT3XI	[highlight #FCE94F]60[/highlight]	9[/highlight]

[b]Note_Text[/b][highlight #FCE94F][/highlight]
"CNC VERT. MILL
HOLD IN MILLING FIXTURE LOCATING BY 4.092 4.094 DIA. BOSS AND ORIENTATED BY .2523 .2533 DIA. HOLE (DATUM ""C"") LOCATED @ X2.2115 Y1.5490.

REFERENCE DRAWING 35 54004 4121 5 SHEET 1 ZONE E2;
FINISH MACHINE .468- .478 DIA. SPOTFACES (5) PLACES  USING A PILOTED COUNTERBORE TOOL DIMENSIONED AS SHOWN TO A FLANGE THICKNESS OF .250+/ .005;

  NOTE: USE PILOTED GO/NO GO GAGE TO INSPECT
 INSPECT ALL SPOTFACES 100% DURING MACHINING PROCESS 
****************************************************************************
1ST PIECE ACCEPTANCE:

STP/INT:______________________ DATE____________________
****************************************************************************
INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____"

I then added the OpNo criteria here is the code

SELECT dbo_Job_Operation.Job_Operation, dbo_Job_Operation.Job, dbo_Job_Operation.WC_Vendor, dbo_Job_Operation.Operation_Service, dbo_Job_Operation.Sequence, dbo_Job_Operation.Note_Text
FROM dbo_Job_Operation
WHERE (((dbo_Job_Operation.Job)="202550") AND ((dbo_Job_Operation.WC_Vendor)="CMV-KIT3XI") AND ((dbo_Job_Operation.Operation_Service)="60"));

The output is listed below:

[highlight #FCE94F]Job_Operation[/highlight]	[highlight #D3D7CF]Job[/highlight]	[highlight #729FCF]WC_Vendor[/highlight]	[highlight #8AE234]Operation_Service[/highlight]	[highlight #EF2929]Sequence[/highlight]	Note_Text
[highlight #FCE94F]278213[/highlight]	[highlight #D3D7CF]202550[/highlight]	[highlight #729FCF]CMV-KIT3XI[/highlight]	[highlight #8AE234]60[/highlight]	[highlight #EF2929]9[/highlight]	

[highlight #FCE94F][b]Note_Text[/b][/highlight]
"CNC VERT. MILL
HOLD IN MILLING FIXTURE LOCATING BY 4.092 4.094 DIA. BOSS AND ORIENTATED BY .2523 .2533 DIA. HOLE (DATUM ""C"") LOCATED @ X2.2115 Y1.5490.

REFERENCE DRAWING 35 54004 4121 5 SHEET 1 ZONE E2;
FINISH MACHINE .468- .478 DIA. SPOTFACES (5) PLACES  USING A PILOTED COUNTERBORE TOOL DIMENSIONED AS SHOWN TO A FLANGE THICKNESS OF .250+/ .005;

  NOTE: USE PILOTED GO/NO GO GAGE TO INSPECT
 INSPECT ALL SPOTFACES 100% DURING MACHINING PROCESS 
****************************************************************************
1ST PIECE ACCEPTANCE:

STP/INT:______________________ DATE____________________
****************************************************************************
INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____"

My first thought is that the code in not seeing the third criteria and is returning 2 records but the debug output doesn't show both OpNo 10 and 60. If you compare all of the output you can see the Note_Text field value for the VBA code matches the out put for my first query where I only use Job and WC_Vendor. I'm not sure why this is happening because on the third output when I add the third criteria it returns the correct record set. Very strange what is happening.

 

[Andrzejek]

How is your Note_Text field defined in your dbo_Job_Operation table?
Test? Memo? Something else?

So if you do this request in a SQL view of a query:

[pre]
SELECT Note_Text
FROM dbo_Job_Operation
WHERE Job = "202550"
AND WC_Vendor = "CMV-KIT3XI"
AND Operation_Service = "60"[/pre]

You expect something like this (below) to (eventually) show up in your txtOrgChange text box?

[pre]
CNC VERT. MILL
HOLD IN MILLING FIXTURE LOCATING BY 4.092 4.094 DIA. BOSS AND ORIENTATED BY .2523 .2533 DIA. HOLE (DATUM ""C"") LOCATED @ X2.2115 Y1.5490.

REFERENCE DRAWING 35 54004 4121 5 SHEET 1 ZONE E2;
FINISH MACHINE .468- .478 DIA. SPOTFACES (5) PLACES USING A PILOTED COUNTERBORE TOOL DIMENSIONED AS SHOWN TO A FLANGE THICKNESS OF .250+/ .005;

NOTE: USE PILOTED GO/NO GO GAGE TO INSPECT
INSPECT ALL SPOTFACES 100% DURING MACHINING PROCESS
****************************************************************************
1ST PIECE ACCEPTANCE:

STP/INT:______________________ DATE____________________
****************************************************************************
INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____

INT______DATE________ACC_____REJ_____SCP_____TOT_____
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

Andy,
How is your Note_Text field defined in your dbo_Job_Operation table?
Note_Text's Data Type is Long Text with a Text Format of Plain Text


So if you do this request in a SQL view of a query:

SELECT Note_Text
FROM dbo_Job_Operation
WHERE Job = "202550"
AND WC_Vendor = "CMV-KIT3XI"
AND Operation_Service = "60"

You expect something like this (below) to (eventually) show up in your txtOrgChange text box?

Yes exactly. Ufortunately I'm getting the result from Op 10 which as you can see is the first record in the set if I don't use OpNo as a criteria.

Thanks for your help!!

 

[strongm]

Possibly a bit late as you seem to have shot off in another direction, but going back to my original suggestion I managed to replicate your "when I select a value in cboOpeation the text that is populated in OrgChange is the same each time" issue of 14 May.

Try changing my initial solution from

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

to

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

 

[Andrzejek]

So the OpNo as a criteria is a must.

You need to investigate the [highlight #FCE94F]value that comes from cboOperation.[Column](2)[/highlight] and why it does not give you the value you expect. So your strWhere will be correct for your DLookup function.

Private Sub cboOperation_AfterUpdate()
Dim strWhere As String

[highlight #FCE94F]Debug.Print Me.cboOperation.[Column](2)
[/highlight]
With Me.cboOperation
    Me.[highlight #8AE234]txtOPNo.Value[/highlight] = [highlight #FCE94F].[Column](2)
[/highlight]    Me.txtSetupHrs.Value = .[Column](3)
    Me.txtRunRate.Value = .[Column](4)
    Me.txtRunType.Value = .[Column](5)
End With

If Len(Me.cboJob & "") * Len(Me.cboOperation & "") * Len([highlight #8AE234]Me.txtOPNo[/highlight] & "") = 0 Then
    Me.txtOrgChange.Value = ""
    Exit Sub
End If

strWhere = "[WC_Vendor] = '" & Me.cboOperation & _
    "' And OpNo = '" & [highlight #8AE234]Me.txtOPNo[/highlight] & _
    "' And Job = '" & Me.cboJob & "'"

Debug.Print strWhere

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

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

WOW!!!!

Thanks strongm and Andy!!!

It actually works as expected!!

Here is the wining code

Private Sub cboOperation_AfterUpdate()
 With Me.cboOperation
    Me.txtOPNo.Value = .[Column](2)
    Me.txtSetupHrs.Value = .[Column](3)
    Me.txtRunRate.Value = .[Column](4)
    Me.txtRunType.Value = .[Column](5)
End With
    
    [cboOperation].Recordset.AbsolutePosition = [cboOperation].ListIndex
    Me.txtOrgChange.Value = [cboOperation].Recordset.Fields(6)
End Sub

Thanks guys for all of your help and patients!! I suppose I could take out the "With" statement and hard code the other Values but why? It works!

 

[Andrzejek]

Great!

"I could take out the "With" statement " - I wouldn't, I would even do this:

Private Sub cboOperation_AfterUpdate()

With Me.cboOperation
    Me.txtOPNo.Value = .[Column](2)
    Me.txtSetupHrs.Value = .[Column](3)
    Me.txtRunRate.Value = .[Column](4)
    Me.txtRunType.Value = .[Column](5)
    
    .Recordset.AbsolutePosition = .ListIndex
    Me.txtOrgChange.Value = .Recordset.Fields(6)
End With

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[strongm]

'Me' is implicit, so you could remove that as well ...

 

[dhookom]

Great stuff Andy. I was having a few adult beverages with a former Access team Softie and I asked him about positioning in the record set and he didn’t mention your solution.

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

 

[Andrzejek]

Yes, 'Me' is implicit (and not needed), but sometimes I like to put it in the code so people will see when they type [tt]Me.[/tt] they get the intellisence with a list of all controls available on the Form (among other stuff). Myself, I type [tt]cbooper[/tt] and hit Ctrl-Space to fill the rest of the name of control (or get the intellisence to choose from), but not everybody knows / remembers Ctrl-Space shortcut.


---- Andy

There is a great need for a sarcasm font.