×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Form Field limited to 255 characters

Form Field limited to 255 characters

Form Field limited to 255 characters

(OP)
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.

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

(OP)
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

RE: Form Field limited to 255 characters

>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

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

(OP)
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.

RE: Form Field limited to 255 characters

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.

CODE --> vba

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

RE: Form Field limited to 255 characters

(OP)
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!

RE: Form Field limited to 255 characters

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

CODE --> vba

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

RE: Form Field limited to 255 characters

(OP)
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!

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

(OP)
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

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

(OP)
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

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

(OP)
Duane,
I thought you were replacing all of your previous post, my mistake.

RE: Form Field limited to 255 characters

(OP)
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!

RE: Form Field limited to 255 characters

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

CODE --> vba

strSQL = "SELECT JO.Note_Text FROM dbo_Job_Operation JO " & _
"WHERE JO.Job= """ & [Forms]![frm_ChangeEntry]![cboJob] & """"
' add this next line for troubleshooting
Debug.Print "strSQL: " & strSQL 

Can you confirm Job is a text field?

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

RE: Form Field limited to 255 characters

As suggested much earlier, you could try DLookup()

CODE --> vba

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

RE: Form Field limited to 255 characters

Instead of

CODE

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

Try

CODE

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

RE: Form Field limited to 255 characters

(OP)
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

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

(OP)
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!

RE: Form Field limited to 255 characters

Try earlier suggestions like DLookup().

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

RE: Form Field limited to 255 characters

(OP)
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!

RE: Form Field limited to 255 characters

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/

RE: Form Field limited to 255 characters

(OP)
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!

RE: Form Field limited to 255 characters

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

CODE --> ImmediateWindow

? DLookup("[Note_Text]", "dbo_Job_Operation", "[Operation_Service]='CMV-760V2'") 
Then consider creating a query like:

CODE --> SQL

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

RE: Form Field limited to 255 characters

>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 SELECT TravelerChange.* FROM TravelerChange or even just TravelerChange

RE: Form Field limited to 255 characters

(OP)
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!

RE: Form Field limited to 255 characters

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;

RE: Form Field limited to 255 characters

(OP)
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;

RE: Form Field limited to 255 characters

Brad,
Can you reply to my questions/suggestions?

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

RE: Form Field limited to 255 characters

(OP)
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!!

RE: Form Field limited to 255 characters

Quote (BradCustom )

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:

CODE

Debug.Print Forms![frm_ChangeEntry]!Operation 

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.

RE: Form Field limited to 255 characters

(OP)
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)

RE: Form Field limited to 255 characters

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.

RE: Form Field limited to 255 characters

(OP)
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.

RE: Form Field limited to 255 characters

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

CODE

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

CODE

SELECT Operation_Service, Note_Text FROM dbo_Job_Operation WHERE Operation_Service like "*CMV*" 
Returned a Compile Error

RE: Form Field limited to 255 characters

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

it is an equivalent to:

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


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.

RE: Form Field limited to 255 characters

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


I get a Compile Error.

RE: Form Field limited to 255 characters

(OP)
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'

RE: Form Field limited to 255 characters

(OP)
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;

RE: Form Field limited to 255 characters

(OP)
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!!!

RE: Form Field limited to 255 characters

(OP)
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!!

RE: Form Field limited to 255 characters


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

You may be better off if you do this approach:

CODE

Dim strWhere As String

strWhere = "[WC_Vendor] = '" _
 & Forms![frm_ChangeEntry]!Operation & "' And {WhatField?} = '" & 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 swords

---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
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!!

RE: Form Field limited to 255 characters

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


Assuming WC_Vendor and Job fields in your table are text, your DLookup is equivalent to:

Select Note_Text From dbo_Job_Operation
Where WC_Vendor = 'outcome of first Debug above'
And Job = 'outcome of second Debug above'


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
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!!

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

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:

Select Note_Text From dbo_Job_Operation
Where WC_Vendor = 'GR-OGM1224'
And Job = 202435


And your DLookup would be:

CODE

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:

WC_Vendor = 'GR-OGM1224' And Job = 202435


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
Andy,
Both Job and WC_Vendor are text fields.

RE: Form Field limited to 255 characters

(OP)
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!

RE: Form Field limited to 255 characters

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:

CODE

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


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

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:

CODE --> vba

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) 


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

RE: Form Field limited to 255 characters

At least Andy and I are consistent all the way to the detail level highlighting text winky smile

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

RE: Form Field limited to 255 characters

(OP)
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!!

RE: Form Field limited to 255 characters

(OP)
Andy/Duane,

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

Thank you for your help!

RE: Form Field limited to 255 characters

Great! Congratulation thumbsup2

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


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
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

RE: Form Field limited to 255 characters

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

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 & "' And Job = '" & Forms![frm_ChangeEntry]!Job & "' 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!

RE: Form Field limited to 255 characters

It would be good to see the entire procedure where you have this code, but I would guess you may just add:

CODE

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


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
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!!

RE: Form Field limited to 255 characters

I typically split up the "Where" controls:

CODE --> vba

Dim strWhere As String
' Assuming all filtering fields are text
strWhere =  "1=1 "  'just start it out with really no filter
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

RE: Form Field limited to 255 characters

(OP)
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!!

RE: Form Field limited to 255 characters

(OP)
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.

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

In my opinion, this is a business problem.

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?

---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
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 = '20'
1=1  AND [WC_Vendor] = 'CMV-800VG2'  AND Job = '202550'  AND OpNo = '30' 


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 = '30'
1=1  AND [WC_Vendor] = 'CMV-KIT3XI'  AND Job = '202550'  AND OpNo = '60' 

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 = '60'
1=1  AND [WC_Vendor] = 'CMV-KIT3XI'  AND Job = '202550'  AND OpNo = '10' 



RE: Form Field limited to 255 characters

(OP)
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 = '20' And Job = '202550'
[WC_Vendor] = 'CMV-800VG2' And OpNo = '30' 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 = '10' And Job = '202550'
[WC_Vendor] = 'CMV-KIT3XI' And OpNo = '60' And Job = '202550' 

RE: Form Field limited to 255 characters

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.

RE: Form Field limited to 255 characters

(OP)
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 = 10 Job = 202550
CMV-800VG2 OpNo = 20 Job = 202550


It makes no sense to me either. I didn't notice it until I started writing the post.

RE: Form Field limited to 255 characters

I know you have a combo box named cboOperation.
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...?

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

RE: Form Field limited to 255 characters

(OP)
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.

RE: Form Field limited to 255 characters

(OP)
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)

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 

RE: Form Field limited to 255 characters

Try this:

CODE

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]!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.... smile


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
I tried that but I will try it again.

I thought it could be a timing issue.

RE: Form Field limited to 255 characters

This is really confusing.
By looking at your code - you have a text box named txtOPNo, but you refer to it as Forms![frm_ChangeEntry]!OpNo, not as Forms![frm_ChangeEntry]!txtOpNo, the same goes for combo box cboOperation / Operation, cboJob / Job, 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 frm_ChangeEntry?


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
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

RE: Form Field limited to 255 characters

(OP)
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.

RE: Form Field limited to 255 characters

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


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

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

cboJob

CODE

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

CODE

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

CODE

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

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; 


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

RE: Form Field limited to 255 characters

(OP)
Still the same problem, Note_Text is not the correct value.

RE: Form Field limited to 255 characters

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


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
Yes they are all on frm_ChangeEntry

RE: Form Field limited to 255 characters

Then your code may look a lot simpler:

CODE

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.

RE: Form Field limited to 255 characters

(OP)
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.

CODE

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

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

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")); 

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

CODE

Job_Operation	Job	WC_Vendor	Operation_Service	Sequence	Note_Text
278206	202550	CMV-KIT3XI	10	2	

Note_Text
"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_____"


278213	202550	CMV-KIT3XI	60	9

Note_Text
"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

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:

CODE

Job_Operation	Job	WC_Vendor	Operation_Service	Sequence	Note_Text
278213	202550	CMV-KIT3XI	60	9	

Note_Text
"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.

RE: Form Field limited to 255 characters

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:

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?

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_____
 


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
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!!

RE: Form Field limited to 255 characters

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

CODE

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


to

CODE

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


RE: Form Field limited to 255 characters

So the OpNo as a criteria is a must.

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

CODE

Private Sub cboOperation_AfterUpdate()
Dim strWhere As String

Debug.Print Me.cboOperation.[Column](2)

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 


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

(OP)
WOW!!!!

Thanks strongm and Andy!!!

It actually works as expected!!

Here is the wining code

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!

RE: Form Field limited to 255 characters

Great! smile

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

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)
    
    .Recordset.AbsolutePosition = .ListIndex
    Me.txtOrgChange.Value = .Recordset.Fields(6)
End With

End Sub 


---- Andy

There is a great need for a sarcasm font.

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

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

RE: Form Field limited to 255 characters

Yes, 'Me' is implicit (and not needed), but sometimes I like to put it in the code so people will see when they type Me. they get the intellisence with a list of all controls available on the Form (among other stuff). Myself, I type cbooper 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close