HELP INITIALIZING STRINGS

[Paul7905]

I POSTED THIS ON THE VBA FORUM BUT THERE ARE TOO FEW PEOPLE VISITING THERE AND SO TRYING HERE TOO ! <br><br>I have three strings defined in code that i need to initialize.&nbsp;&nbsp;in these three strings i am building a record key from data in an access table that will be used in another system (MAS90 accounting system).&nbsp;&nbsp;My trouble is that after processing the first record in the table, when processing the next record, the &quot;strings&quot; still contain data from the first record key that was constructed.&nbsp;&nbsp;i think i need to clear these strings out before starting on the next record but i am not a coder and don't know how to do this (can't find the instruction that does this).&nbsp;&nbsp;can someone help ?&nbsp;&nbsp;code i am using is below, <br>Thankyou so mucn ! <br>Paul<br>&nbsp;<br>Public Sub UpdateKey()<br><br>Dim db As DAO.Database<br>Dim rst As DAO.Recordset<br>Dim strSize As String<br>Dim strBoName1 As String<br>Dim strBoName2 As String<br>Dim bytCount As Byte<br>Dim bytPos As Byte<br>Dim strKey As String<br><br>Set db = CurrentDb<br>Set rst = db.OpenRecordset(&quot;SELECT * FROM AllItemsConsolidatedTable&quot<br><br>Do While Not rst.EOF<br><br>'Create Size string from field, eliminating spaces <br><br>&nbsp;&nbsp;&nbsp;&nbsp;For bytCount = 1 To Len(rst!BriefDescription)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Mid(rst!BriefDescription, bytCount, 1) &gt; &quot; &quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strSize = strSize & Mid(rst!BriefDescription, bytCount, 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next bytCount<br><br>'Find where a &quot;'&quot; is and grab the first 8 characters of that<br>&nbsp;<br>&nbsp;&nbsp;&nbsp;bytPos = InStr(rst![ItemName], &quot;'&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;If bytPos &lt;&gt; 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strBoName2 = Mid(rst![ItemName], bytPos, 8)<br>&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strBoName2 = &quot;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br>'Create Botanical Name string without spaces (not working)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;For bytCount = 1 To Len(rst![ItemName])<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Mid(rst![ItemName], bytCount) &lt;&gt; &quot; &quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strBoName1 = strBoName1 & Mid(rst![ItemName], bytCount)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;Next bytCount<br><br>'Grab what is left over of the botanical name (works)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;strBoName1 = Left(strBoName1, (30 - Len(strSize) - Len(strBoName2)))<br><br>'Assign the key string<br><br>&nbsp;&nbsp;&nbsp;&nbsp;strKey = strBoName1 & strBoName2 & strSize<br><br>'Edit the record<br><br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;rst!MAS90ITEMKEY = strKey<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Update<br><br>'move to the next record if there is one<br><br>&nbsp;&nbsp;&nbsp;&nbsp;If Not rst.EOF Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>Loop<br><br>rst.Close<br>Set rst = Nothing<br>Set db = Nothing<br><br>End Sub<br><br>

 

[Chopper]

After the variables are dimensioned (following the dim statements), set each one to the null string:<br><br>strBoName1 = &quot;&quot;<br>strBoName2 = &quot;&quot;<br>strKey = &quot;&quot;<br><br>Really, you shouldn't need to do this because when the variables are declared via the dim statement, they are initialized to the null string.&nbsp;&nbsp;The only way they will retain their values from time to time is if you declare them globally, or as Static variables.&nbsp;&nbsp;Therefore, I wouldn't worry about them retaining their values, but initialize them as I specified to set your mind at ease. <p>-Chopper<br><a href=mailto: > </a><br><a href= > </a><br>

 

[PaulF]

because you dim the variables outside of the Do Loop, and you don't reset them as shown above, they will retain the values you add for each record.&nbsp;&nbsp;Either reset them using =&quot;&quot; for non-numerics or = 0 for numerics, or move the dim statements (except for database and recordset) to inside the Do Loop, which will reset them each time you loop back to the start of the Do statement.<br><br>PaulF

 

[Paul7905]

Thanks for the tips !&nbsp;&nbsp;yes, the strings were keeping data in them each time the do loop was executed, this eventually blew one of the strings (i think for trying to exceed the amount of data the string could hold).&nbsp;&nbsp;this is working now (just need to do some more de-bugging because it is not 100%)&nbsp;&nbsp;again, thanks !!