 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / October 2007Data truncated in text box on form
I have researched the various issues as it relates to truncation of memo fields. I read Allen Browne's fine treatise on the subject. However I am experiencing something I can't quite understand or correct. Here goes. I have a form with a text box control on it. There is a query that runs that populates this text box. The table the query runs against has a field formatted as memo and the data looks fine in there. When I run the query on its own the memo fields appear in their entirety. As a note, within this query I do not use the "Group By" sorting. However, when I look at the contents of that field in the text box within the form, it's truncating at 255 characters. I have no formatting options assigned to the text box, or within the query, or the table containing this memo field. I have Can Grow set to yes within the text box. Also, the field within the table that is being updated via this form has a Data Type set to memo as well. And of course the data is truncated in here despite that. I think if we can resolve the truncation in the text box on the form this field should contain all the data. I hope. Can someone please resolve this for me? Thanks Mark After further research it appears its the VBA code that is populating the text box that might be causing the problem. How do I allow the VBA code to allow all characters within a memo field? Here is what I have: Dim DB As Database Dim rs As Recordset Set DB = CurrentDb Set rs = DB.OpenRecordset("tblSoxSummaryData") Forms!frmSoxSummaryData!Cntrl_Desc = cboCntrl_Num.Column(1) Forms!frmSoxSummaryData!Process = cboCntrl_Num.Column(2) Set rs = Nothing Set DB = Nothing End Sub >I have researched the various issues as it relates to truncation of memo >fields. I read Allen Browne's fine treatise on the subject. However I am [quoted text clipped - 19 lines] > > Mark > After further research it appears its the VBA code that is > populating the text box that might be causing the problem. How do [quoted text clipped - 10 lines] > Set DB = Nothing > End Sub The source of the truncation is the combobox. You want to organize the code differently, because as it is, it's not doing what you think it does, or you cut out some important parts of it. Dim DB As Database Dim rs As Recordset Dim strSQL as string Set DB = CurrentDb strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = " _ & cboCntrl_Num Set rs = DB.OpenRecordset(strSQL) Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc Forms!frmSoxSummaryData!Process = Process Set rs = Nothing Set DB = Nothing End Sub >>I have researched the various issues as it relates to truncation >>of memo fields. I read Allen Browne's fine treatise on the [quoted text clipped - 22 lines] >> >> Mark  SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com This is the code in its entirety. Not all fields are memo fields however. Any further coding changes based on this? I am simply populating different text boxes/fields based on the query called in the cboCntrl_Num. I will try your code changes in the meantime. Thanks Mark Private Sub cboCntrl_Num_AfterUpdate() 'assigning form field values based on combo box selection for Control data Dim DB As Database Dim rs As Recordset Set DB = CurrentDb Set rs = DB.OpenRecordset("tblSoxSummaryData") Forms!frmSoxSummaryData!Cntrl_Desc = cboCntrl_Num.Column(1) Forms!frmSoxSummaryData!Process = cboCntrl_Num.Column(2) Forms!frmSoxSummaryData!Sub_Process = cboCntrl_Num.Column(3) Forms!frmSoxSummaryData!PD = cboCntrl_Num.Column(4) Forms!frmSoxSummaryData!Cntrl_Environ = cboCntrl_Num.Column(5) Forms!frmSoxSummaryData!Info_Comm = cboCntrl_Num.Column(6) Forms!frmSoxSummaryData!Monitoring = cboCntrl_Num.Column(7) Forms!frmSoxSummaryData!Risk_Assess = cboCntrl_Num.Column(8) Forms!frmSoxSummaryData!Completeness = cboCntrl_Num.Column(9) Forms!frmSoxSummaryData!Val_Alloc = cboCntrl_Num.Column(10) Forms!frmSoxSummaryData!Ext_Occur = cboCntrl_Num.Column(11) Forms!frmSoxSummaryData!Rights_Obl = cboCntrl_Num.Column(12) Forms!frmSoxSummaryData!Present_Discl = cboCntrl_Num.Column(13) Forms!frmSoxSummaryData!Res_Access = cboCntrl_Num.Column(14) Forms!frmSoxSummaryData!SOD = cboCntrl_Num.Column(15) Forms!frmSoxSummaryData!Safe_Assets = cboCntrl_Num.Column(16) Forms!frmSoxSummaryData!Anti_Fraud = cboCntrl_Num.Column(17) Forms!frmSoxSummaryData!Cntrl_Type = cboCntrl_Num.Column(18) Set rs = Nothing Set DB = Nothing End Sub >> After further research it appears its the VBA code that is >> populating the text box that might be causing the problem. How do [quoted text clipped - 54 lines] >>> >>> Mark > This is the code in its entirety. Not all fields are memo fields > however. Any further coding changes based on this? I am simply [quoted text clipped - 3 lines] > > Mark The code below opens a recordset, then populates all the rows from the combobox, closes the recordset and exits without using the recordset. I'd just use the combo box to indicate the selected record, and then open the recordset already filtered to return the one row. If your combobox columns are in the same order as the fields in the table, you could just change the cboCntrl_Num.Column(n) to refer to the recordset as rs.fields(n) a 30 second cut and paste jobbie, if it's the case. Q > Private Sub cboCntrl_Num_AfterUpdate() > 'assigning form field values based on combo box selection for [quoted text clipped - 10 lines] > Forms!frmSoxSummaryData!Cntrl_Environ = > cboCntrl_Num.Column(5) Forms!frmSoxSummaryData!Info_Comm = > cboCntrl_Num.Column(6) Forms!frmSoxSummaryData!Monitoring = > cboCntrl_Num.Column(7) Forms!frmSoxSummaryData!Risk_Assess = > cboCntrl_Num.Column(8) Forms!frmSoxSummaryData!Completeness = > cboCntrl_Num.Column(9) Forms!frmSoxSummaryData!Val_Alloc = > cboCntrl_Num.Column(10) Forms!frmSoxSummaryData!Ext_Occur = > cboCntrl_Num.Column(11) Forms!frmSoxSummaryData!Rights_Obl = > cboCntrl_Num.Column(12) Forms!frmSoxSummaryData!Present_Discl > = cboCntrl_Num.Column(13) Forms!frmSoxSummaryData!Res_Access > = cboCntrl_Num.Column(14) Forms!frmSoxSummaryData!SOD = > cboCntrl_Num.Column(15) Forms!frmSoxSummaryData!Safe_Assets = > cboCntrl_Num.Column(16) Forms!frmSoxSummaryData!Anti_Fraud = > cboCntrl_Num.Column(17) Forms!frmSoxSummaryData!Cntrl_Type = > cboCntrl_Num.Column(18) > > Set rs = Nothing > Set DB = Nothing > End Sub Bob Q SignaturePosted via a free Usenet account from http://www.teranews.com Bob, thanks for your response. Bear with me as I am not yet quite an ace programmer but I hope to be. Trust me I love the cut and paste jobs but I want to understand as well. Just to confirm, using the code you provided me. Would it look like this basically. My previous code was working alright except of course when it came to processing my memo fields. Your code will work around the 255 char limit I presume? Thank you! Mark would the new code look like this and basically replace what I have: Dim DB As Database Dim rs As Recordset Dim strSQL as string Set DB = CurrentDb strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = " _ & cboCntrl_Num Set rs = DB.OpenRecordset(strSQL) Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc Forms!frmSoxSummaryData!Process = rs!Process Forms!frmSoxSummaryData!Process = rs!PD Forms!frmSoxSummaryData!Process = rs!Cntrl_Environ etc. Set rs = Nothing Set DB = Nothing End Sub >> This is the code in its entirety. Not all fields are memo fields >> however. Any further coding changes based on this? I am simply [quoted text clipped - 64 lines] > > Bob Q > Bob, thanks for your response. Bear with me as I am not yet quite > an ace programmer but I hope to be. Trust me I love the cut and [quoted text clipped - 5 lines] > > Mark yes the code works around the 255 character limit. The code looks good. Good luck Q > would the new code look like this and basically replace what I > have: [quoted text clipped - 84 lines] >> >> Bob Q SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Bob, here is my new code. I am getting the following error however: Run-time error '3061' too few parameters. Expected 2. This is happening on this line of code: Set rs = DB.OpenRecordset(strSQL) This is the code in its entirety: Dim DB As Database Dim rs As Recordset Dim strSQL As String Set DB = CurrentDb strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = " _ & cboCntrl_Num Set rs = DB.OpenRecordset(strSQL) Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc Forms!frmSoxSummaryData!Process = rs!Process Forms!frmSoxSummaryData!Sub_Process = rs!Sub_Process Forms!frmSoxSummaryData!PD = rs!PD Forms!frmSoxSummaryData!Cntrl_Environ = rs!Cntrl_Environ Forms!frmSoxSummaryData!Info_Comm = rs!Info_Comm Forms!frmSoxSummaryData!Monitoring = rs!Monitoring Forms!frmSoxSummaryData!Risk_Assess = rs!Risk_Assess Forms!frmSoxSummaryData!Completeness = rs!Completness Forms!frmSoxSummaryData!Val_Alloc = rs!Val_Alloc Forms!frmSoxSummaryData!Ext_Occur = rs!Ext_Occur Forms!frmSoxSummaryData!Rights_Obl = rs!Rights_Obl Forms!frmSoxSummaryData!Present_Discl = rs!Present_Discl Forms!frmSoxSummaryData!Res_Access = rs!Res_Access Forms!frmSoxSummaryData!SOD = rs!SOD Forms!frmSoxSummaryData!Safe_Assets = rs!Safe_Assets Forms!frmSoxSummaryData!Anti_Fraud = rs!Anti_Fraud Forms!frmSoxSummaryData!Cntrl_Type = rs!Cntrl_Type Set rs = Nothing Set DB = Nothing End Sub Thanks >> Bob, thanks for your response. Bear with me as I am not yet quite >> an ace programmer but I hope to be. Trust me I love the cut and [quoted text clipped - 101 lines] >>> >>> Bob Q > Bob, here is my new code. I am getting the following error > however: Run-time error '3061' too few parameters. Expected 2. > This is happening on this line of code: > Set rs = DB.OpenRecordset(strSQL) A fer possibilities: Is Cntrl_num actually a text field, not a number? If it is, then strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = """ _ & cboCntrl_Num & """" There may be a conflict between adoDb and dao in the type of recordset created in Access 2000, explicitly declare Dim rs As dao.Recordset > This is the code in its entirety: > [quoted text clipped - 137 lines] >>>> >>>> Bob Q SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Bob, sorry for the confusion. Cntrl_num is a text field. I should have stipulated that. I'll try the new changes. Thanks >> Bob, here is my new code. I am getting the following error >> however: Run-time error '3061' too few parameters. Expected 2. [quoted text clipped - 154 lines] >>>>> >>>>> Bob Q We're getting closer. Still getting Run-time error '3061' but this time it says Expected 1 instead of 2. So we made some progress there. I made all the changes you suggested. I appreciate your assistance. Mark >> Bob, here is my new code. I am getting the following error >> however: Run-time error '3061' too few parameters. Expected 2. [quoted text clipped - 154 lines] >>>>> >>>>> Bob Q > We're getting closer. Still getting Run-time error '3061' but this > time it says Expected 1 instead of 2. So we made some progress > there. I made all the changes you suggested. I appreciate your > assistance. > > Mark I really don't know, but let's try a few things to debug this. Temporarily change the strSQL to "SELECT * from tblSoxSummaryData " _ & "WHERE Cntrl_num = 'ABC'" I doubt it will return anything, but see if the error message goes away. If that gives no error message, it would indicate that cboCntrl_Num is returning a Null and not a real value. if it still gives an error message, let's add the supposedly optional parameter to the OpenRecordset method Set rs = DB.OpenRecordset(strSQL,dbForwardOnly) else if it works, change ABC to a real control number and see if that works. hth, Q >>> Bob, here is my new code. I am getting the following error >>> however: Run-time error '3061' too few parameters. Expected 2. [quoted text clipped - 156 lines] >>>>>> >>>>>> Bob Q SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Bob, you just might be a candidate for sainthood. At any rate I managed to make the errors go away. The code is running without error but the memo field (Cntrl_Desc) is still getting truncated. That's the key issue here as I need those memo fields in their entirety. Unless of course I made a change that you don't agree with. Here is the code now. All I want is for the text boxes on my form to populate correctly and then of course update the tblSoxSummaryData table. As an FYI the query below runs as a row source within the cboCntrl_Num combo box. This query shows the memo fields just fine. Let me know if it's easier to just send you the database at this point. SELECT tblSOXControls.FY08CntrlNum, tblSOXControls.FY08CntrlDescr, tblSOXControls.Process, tblSOXControls.SubProcess, tblSOXControls.PD, tblSOXControls.CntrlEnviron, tblSOXControls.InfoComm, tblSOXControls.Monitoring, tblSOXControls.RiskAssess, tblSOXControls.Completeness, tblSOXControls.ValAlloc, tblSOXControls.ExtOccur, tblSOXControls.RightsObl, tblSOXControls.PresentDiscl, tblSOXControls.ResAccess, tblSOXControls.SOD, tblSOXControls.SafeAssets, tblSOXControls.AntiFraud, tblSOXControls.CntrlType FROM tblSOXControls; Here is the new and improved VBA that is still truncating the memo fields. Dim DB As dao.Database Dim rs As dao.Recordset Dim strSQL As String Set DB = CurrentDb strSQL = "SELECT * FROM [tblSoxSummaryData] WHERE [Cntrlnum] ='" _ & Me.cboCntrl_Num & "'" Set rs = DB.OpenRecordset(strSQL) Forms!frmSoxSummaryData!Cntrl_Desc = rs!CntrlDesc Forms!frmSoxSummaryData!Process = rs!Process Forms!frmSoxSummaryData!Sub_Process = rs!SubProcess Forms!frmSoxSummaryData!PD = rs!PD Forms!frmSoxSummaryData!Cntrl_Environ = rs!CntrlEnviron Forms!frmSoxSummaryData!Info_Comm = rs!InfoComm Forms!frmSoxSummaryData!Monitoring = rs!Monitoring Forms!frmSoxSummaryData!Risk_Assess = rs!RiskAssess Forms!frmSoxSummaryData!Completeness = rs!Completeness Forms!frmSoxSummaryData!Val_Alloc = rs!ValAlloc Forms!frmSoxSummaryData!Ext_Occur = rs!ExtOccur Forms!frmSoxSummaryData!Rights_Obl = rs!RightsObl Forms!frmSoxSummaryData!Present_Discl = rs!PresentDiscl Forms!frmSoxSummaryData!Res_Access = rs!ResAccess Forms!frmSoxSummaryData!SOD = rs!SOD Forms!frmSoxSummaryData!Safe_Assets = rs!SafeAssets Forms!frmSoxSummaryData!Anti_Fraud = rs!AntiFraud Forms!frmSoxSummaryData!Cntrl_Type = rs!CntrlType Set rs = Nothing Set DB = Nothing End Sub Thanks! MD >> We're getting closer. Still getting Run-time error '3061' but this >> time it says Expected 1 instead of 2. So we made some progress [quoted text clipped - 183 lines] >>>>>>> >>>>>>> Bob Q > Bob, you just might be a candidate for sainthood. > [quoted text clipped - 10 lines] > fine. Let me know if it's easier to just send you the database at > this point. yeah, bob quintal has an Gmail account. Just change the space between first and last names to a dot and send it there because the Sympatico account doesn't like big files. > SELECT tblSOXControls.FY08CntrlNum, tblSOXControls.FY08CntrlDescr, > tblSOXControls.Process, tblSOXControls.SubProcess, [quoted text clipped - 234 lines] >>>>>>>> >>>>>>>> Bob Q SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Bob, you're off the hook. I substituted the code you suggested with the DLOOKUP command and that solved the problem. Will wonders ever cease? Thanks for being patient and hanging in there on this one. I will file your info away for future reference. Take care. Oh, and I'm pretty certain I will post another issue here in this newsgroup in the near future. Mark >> Bob, you just might be a candidate for sainthood. >> [quoted text clipped - 253 lines] >>>>>>>>> >>>>>>>>> Bob Q |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.