Need Help With a Module

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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")
 
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
 
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 -
 
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
 
Great. But I would need to change the key field value - appending it as is
would cause a duplication error...... is that possible?
 
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.
 
Back
Top