 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / January 2006Need Help With a Module
I want to loop through a recordset and capture the value in a field. There could be as many as 18 records..... do I declare strValue1, strValue2.. etc., or can I declare a value as I go along..... I have Friday head and am lost. Thanks for your help! What is the purpose of looping through the records? To sum them? Would this work: =DSum("Field1", "Table1")  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >I want to loop through a recordset and capture the value in a field. There > could be as many as 18 records..... do I declare strValue1, strValue2.. [quoted text clipped - 3 lines] > > Thanks for your help! Hi Allen - no not to sum, I want to copy an existing recordset and then copy it to a new one (contract information that usually does not change from contract year to year). Is there a way to copy the entire recordset an assign it a new ID number? That might be more efficient, since the number of records will vary. Thanks - > What is the purpose of looping through the records? > To sum them? [quoted text clipped - 9 lines] > > > > Thanks for your help! Can you make a query that gives you the records you want to duplicate? If you can, do so, and then turn it into an Append query (Append on Query menu). Access asks what table you want to append to (which may actually be the same table.) When you run the query, the records are created for you. If you want to do that programmatically, when you have the query working correctly, switch it to SQL View (View menu.) You now have an Update query statement to copy into your code. The code will be like this: Dim strSql As String strSql = "INSERT INTO ... dbEngine(0)(0).Execute strSql, dbFailOnError SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Hi Allen - no not to sum, I want to copy an existing recordset and then > copy [quoted text clipped - 20 lines] >> > am >> > lost. Great. But I would need to change the key field value - appending it as is would cause a duplication error...... is that possible? > Hi Allen - no not to sum, I want to copy an existing recordset and then copy > it to a new one (contract information that usually does not change from [quoted text clipped - 18 lines] > > > > > > Thanks for your help! In query design view, if you have the AutoNumber field in the grid, make sure the Append To beneath it is blank. Access will automatically assign the new autonumber, as long as you are not trying to append a value here. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Great. But I would need to change the key field value - appending it as > is [quoted text clipped - 24 lines] >> > > am >> > > lost. If you want each value, you're probably best off using an array. If the value you're trying to capture is Text, you can use something like: Dim intLoop As Integer Dim strMyValues() As String Set rsCurr = .... ' open your recordset rsCurr.MoveLast ReDim strMyValues(1 To rsCurr.RecordCount) rsCurr.MoveFirst intLoop = 0 Do While rsCurr.EOF = False intLoop = intLoop + 1 strMyValues(intLoop) = rsCurr!MyValue rsCurr.MoveNext Loop (if it's not text, change the datatype in the declaration) Note that you can simply ReDim the array as you read each element, but that's "expensive" from a processing perspective: Dim intLoop As Integer Dim strMyValues() As String Set rsCurr = .... ' open your recordset ReDim strMyValues(1 to rsCurr.RecordCount) intLoop = 0 Do While rsCurr.EOF = False intLoop = intLoop + 1 ReDim Preserve strMyValues(1 To intLoop) strMyValues(intLoop) = rsCurr!MyValue rsCurr.MoveNext Loop SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) >I want to loop through a recordset and capture the value in a field. There > could be as many as 18 records..... do I declare strValue1, strValue2.. [quoted text clipped - 3 lines] > > Thanks for your help! |
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.