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.

 

[dhookom]

We missed the alias of JO in all of the back and forth.

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

 

[dhookom]

As suggested much earlier, you could try DLookup()

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

 

[strongm]

Instead of

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

Try

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

 

[BradCustom]

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

 

[dhookom]

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

 

[BradCustom]

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!

 

[dhookom]

Try earlier suggestions like DLookup().

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

 

[BradCustom]

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!

 

[Blorf]

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.

http://www.thelegacyreturns.com/

 

[BradCustom]

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!

 

[dhookom]

Brad,
You should do some trouble-shooting. Start by pressing Ctrl+G and entering

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

Then consider creating a query like:

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

 

[strongm]

>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]

 

[BradCustom]

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!

 

[strongm]

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;

 

[BradCustom]

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;

 

[dhookom]

Brad,
Can you reply to my questions/suggestions?


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

 

[BradCustom]

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!!

 

[Andrzejek]

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 &"'")

Did you try:

[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.

 

[BradCustom]

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)

 

[Andrzejek]

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.