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.

 

[lameid]

I am almost certain that txtOrgChange Text Format property is set to Plain Text. Make it Rich Text.

 

[BradCustom]

Lameid,

I changed the Form and the TravelerChange Table but unfortunately the Form's combobox is still truncated at 255. Any other ideas what might be causing it? Thanks Brad​

 

[strongm]

>the Form's combobox is still truncated at 255

This is a somewhat undocumented feature of the combobox in Access (and has been for many years; seem to recall it being an issue in Access 2000)- the textbox part of it truncates at 255 characters. Nothing you can do about it. The typical workaround is to use the 255 characters in a dlookup against the source table

 

[lameid]

That makes sense for a combobox since you can't index long text. On the other hand seems silly if it is not the bound column being displayed.

Maybe try that if it is the bound, make some other shorter value bound and then display the long text and see if it works (make column width of bound column zero length).

 

[dhookom]

I don't believe any column in a combo or list box will provide more than 255 characters. Use either DLookup() as strongm suggests or VBA.

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

 

[BradCustom]

The column being displayed is not the bound column. It is one of the fields in the table that this form references.

Use either DLookup() as strongm suggests or VBA.

I am using VBA to populate the Textbox here is the statement;
Private Sub cboOperation_AfterUpdate()
Me.txtOrgChange.Value = [cboOperation].[Column](2)
End Sub

Thanks for your ideas!
Unfortunately, I haven't got it working yet.

 

[dhookom]

Brad,
Your VBA is still relying on the combo box which will not work. You can try something like below although I'm not sure what field you need etc. DLookup() is a lot easier.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String
Set db = CurrentDB
strSQL = "SELECT JO.Note_Text FROM dbo_Job J INNER JOIN dbo_Job_Operation JO ON J.Job = JO.Job " & _
    "WHERE J.Job= """ & [Forms]![frm_ChangeEntry]![cboJob] & """"
Set rs = db.OpenRecordset(strSQL)
Me.txtOrgChange = rs.Fields("Note_Text")
rs.Close
Set rs = Nothing
Set db = Nothing

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

 

[BradCustom]

dhookom,
The field is Job_Operation.Note_Text

I'm populating txtOrgChange which is a text field on Form frm_ChangeEntry
Apparently, the textbox is taking the format of the combobox even though I'm only using the value of the combobox to populate the textbox. I can understand Access carrying the format threw although I don't agree with it but none of us wrote the program.

I'll try your suggestion and let you know how I make out.
Thanks for your help!

 

[dhookom]

Actually you shouldn't need the Job table in the recordset:

strSQL = "SELECT JO.Note_Text FROM dbo_Job_Operation " & _
    "WHERE JO.Job= """ & [Forms]![frm_ChangeEntry]![cboJob] & """"

This assumes Job is a text field. I would expect there might be more than one job operation record for each job so it might require further filtering.

DLookup() is still easier.

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

 

[BradCustom]

dhookom,
Where are you placing this code and what invokes it?
As you know the way I'm doing it now is Event Procedures Afterupdate () on each Combobox

So when a value is selected in Combobox cboJob the following Event Procedure is run.
Private Sub cboJob_AfterUpdate()
Me.txtPart.Value = [cboJob].[Column](1)
Me.txtPartRev.Value = [cboJob].[Column](2)
Me.txtPartName.Value = [cboJob].[Column](3)
End Sub

Additionally the Row Source is;
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;

When the cboOperation combobox value is selected the following Event Procedure is run.
Private Sub cboOperation_AfterUpdate()
Me.txtOrgChange.Value = [cboOperation].[Column](2)
Me.txtSetupHrs.Value = [cboOperation].[Column](3)
Me.txtRunRate.Value = [cboOperation].[Column](4)
Me.txtRunType.Value = [cboOperation].[Column](5)
End Sub

Additionally the Row Source is;
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 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;


Thanks for your help!

 

[dhookom]

The code would only be used for the long text fields and would be in the same event procedures.

It looks like you are attempting to save field values in multiple tables. This is not typically recommended.

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

 

[BradCustom]

dlhookom,
Actually everything is being saved to one table; TravelerChange

 

[dhookom]

Brad,
Isn't everything already saved in the Job_Operation or Job tables? I assume you have a good reason for this.

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

 

[BradCustom]

Duane,
Those are linked SQL Tables and I'm created a From: To:
I want to track the changes made to the SQL Tables. This is going to be a Engineering Change Request Form. The user will select the Traveler they want to change. I'll store that information and then they'll enter their change into other fields in the TravelerChange table. It ends up being one record captured on one Form. This way I have the current state and new state in one record.

The SQL tables are from our ERP software.

Thanks,
Brad

 

[dhookom]

Brad,
Did you try what I suggested? Do you have any answers to my earlier assumptions or questions about the replies?

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

 

[BradCustom]

Duane,
I've tried what you suggested but it doesn't update the txtOrgChange textbox on the Form. Below is what I changed the Event Procedure to.
Private Sub cboOperation_AfterUpdate()
strSQL = "SELECT JO.Note_Text FROM dbo_Job_Operation " & _
"WHERE JO.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

 

[dhookom]

What happened to the other 10 lines of code I suggested that created a recordset?

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

 

[BradCustom]

Duane,

I thought you were replacing all of your previous post, my mistake.​

 

[BradCustom]

Duane,
I added all of your code as shown below;
Private Sub cboOperation_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT JO.Note_Text FROM dbo_Job_Operation " & _
"WHERE JO.Job= """ & [Forms]![frm_ChangeEntry]![cboJob] & """"
Set rs = db.OpenRecordset(strSQL)
Me.txtOrgChange = rs.Fields("Note_Text")
rs.Close
Set rs = Nothing
Set db = Nothing

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

But I get the following error;
Run-time error '3061':
Too few parameters. Expected 2.

If I click debug it highlights this section of the code.
Set rs = db.OpenRecordset(strSQL)

Any ideas why?
Thanks!