Add records to a recordset

[jmar93]

I am using Access 2007 and am trying to add records from a table to a recordset. I understand how to add all of the records from the table, but need to figure out how to only add records until a criteria is meet. Each record contains a footage field which contains the footage of an order. I need to add records to the recordset until the total of these footage fields reaches a predetermined total. Example: say the predetermined total is 1,000 feet, I need the recordset to stop adding records when the records already added total 1,000feet. Does anybody have any idea how I would do this?

thanks

[dsatino]

I am using Access 2007 and am trying to add records from a table to a recordset. I understand how to add all of the records from the table, but need to figure out how to only add records until a criteria is meet. Each record contains a footage field which contains the footage of an order. I need to add records to the recordset until the total of these footage fields reaches a predetermined total. Example: say the predetermined total is 1,000 feet, I need the recordset to stop adding records when the records already added total 1,000feet. Does anybody have any idea how I would do this?

thanks

Use a Do Until...Loop statement with a variable to capture the feet as each record is added. This is a basic example

Public Sub Example()
Dim rstSource as recordset
Dim rst Destination as recordset
Dim x as double

Set rstSource=YourSource
Set rstSource=YourDesination
x=0
With rstSource
  .movefirst
  Do Until x>1000
      With rstDestination
          .add
          !Yourfields=rstSource!fields
          .update
          x=x+!Fields(yourfootagefield)
      End with
      .movenext
  Loop
End with
End Sub

[TheSmileyCoder]

Are you adding these one at a time, or do you want to simply select 1000(ft) at a time?
If you allready have some code, please post that, and remember to use the [code][/code] tags around your code.

[jmar93]

What I am trying to do is use a recordset to generate a report that lists the oldest orders in a table up to the point were their cumulative footage's total a predetermined amount, say 1,000feet.

Example:

order #1 250 feet
order #2 200 feet
order #3 550 feet

for a total of 1,000 feet.

thanks,

[NeoPa]

A recordset is simply a way of viewing existing data and is basically driven by some SQL (even a simple table can be considered to reflect SELECT * FROM [Table]). Do you mean recordset? Or are you really referring to a table (where data may be stored)?

I see no way to do it for the former, but dsatino has provided a way to handle it for the latter.

[jmar93]

OK, I guess I don't understand recordsets at all. I think I understand dsatino's
"do until loop", but after the records have been added to the cutoff point how do I place them in a table so I can use them to generate a report?

thanks,
Jeff

[NeoPa]

If I were doing it I wouldn't move them to a separate table at all. I'd set up a flag field in the existing table that you would set/reset in the code as you processed through the loop. The report would simply be built on a query of the existing table filtered by the flag.

[dsatino]

OK, I guess I don't understand recordsets at all. I think I understand dsatino's
"do until loop", but after the records have been added to the cutoff point how do I place them in a table so I can use them to generate a report?

thanks,
Jeff

The do...loop i gave you is actually placing those results in whatever table you are using as YourDestination . You definitely have to modify this code to suit your specific DB, but the basic idea is there. Don't forget to clear the table first though.

NeoPa's suggestion of the filter flag will work as well and give the added benefit of using much less space.

[jmar93]

I still can't get it to work, I have included the code I have tried. I have tried it 2 different ways and both return a "type mismatch" error. Any ideas?

Private Sub Command5_Click()
Dim rsSource As Recordset
Dim rsDest As Recordset
Dim x As Long

Set rsSource = "Schedule"
Set rsDest = "Table2"
x = 0
With rsSource
    .MoveFirst
    Do Until x > 10000
        With rsDest
            .AddNew
            !YourFields = rsSource!Fields
            .Update
            x = x + !Fields(TotalSF)
        End With
        .MoveNext
    Loop
End With
End Sub




Private Sub Command4_Click()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim rsSource As New ADODB.Recordset
Dim rsDest As New ADODB.Recordset
Dim mySql As String
Dim x As Long
mySql = "SELECT Schedule.[Customer], Schedule.[MAS500number], Schedule.[TotalSF] FROM Schedule ORDER BY Schedule.[MAS500number]"
rsSource.ActiveConnection = cnn1
rsDest.ActiveConnection = cnn1
rsSource.Open mySql
rsDest.Open "Table2", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
x = 0
With rsSource
    .MoveFirst
    Do Until x > 10000
        With rsDest
            .AddNew
            !YourFields!Fields = rsSource!Fields
            .Update
            x = x + !Fields(TotalSF)
        End With
        .MoveNext
    Loop
End With

End Sub

"The SQL statement is on a single line in the actual code."

[dsatino]

your type mismatch is in setting your recordset variables, you're setting them as text. The syntax you used would have been fine if the variable was a string, but with object variables you generally need a method or object reference. The example I gave you was very basic with some of the details left out. Hopefully this will get you there:

Public Sub GetData()
Dim rsSource as recordset
Dim rsDest as recordset
Dim x as double

Set rsSource = currentdb.openrecordset("Schedule")
Set rsDest = currentdb.openrecordset("Table2")
x = 0
With rsSource
    .MoveFirst
     Do Until x > 10000
         With rsDest
             .AddNew
             !YourFields = rsSource!Fields
             .Update
         x = x + !Fields("TotalSF")
        End With
.MoveNext
Loop
End With
End Sub

This example assumes that both tables are in the CurrentDB and you have tables named 'Schedule' and 'Table2'.

You'll still need to modify this to fit your specific needs though. !YourFields is not a method or valid reference, is was just something for me to put there to tell you this is where you add in your fields. TotalSF looks to be your field name, but you need to enclose it in quotes.

[jmar93]

I modified the code and now I get the following error:

Compile error:
Type-declaration character does not match declared data type, and it highlights "Fields" behind "rsSource" in the following line:

rsDest!Fields!("Customer") = rsSource!Fields!("Customer")






Private Sub Command5_Click()
Dim rsSource As Recordset
Dim rsDest As Recordset
Dim x As Double

Set rsSource = CurrentDb.OpenRecordset("Schedule")
Set rsDest = CurrentDb.OpenRecordset("Table2")
x = 0
With rsSource
    .MoveFirst
    Do Until x > 10000
        With rsDest
            .AddNew
            rsDest!Fields!("Customer") = rsSource!Fields!("Customer")
            .Update
            x = x + rsDest!Fields!("TotalSF")
        End With
    .MoveNext
    Loop
End With

End Sub

Jeff

[dsatino]

The syntax is wrong.You're combining two different ways to reference fields.

You can do it like this:

rsDest.Fields("Customer") = rsSource.Fields("Customer")

or

rsDest!Customer = rsSource!Customer

*************** *************** *************** *************** *************

rsDest.Fields("TotalSF")

or

rsDest!TotalSF

[dsatino]

I modified the code and now I get the following error:

Compile error:
Type-declaration character does not match declared data type, and it highlights "Fields" behind "rsSource" in the following line:

rsDest!Fields!("Customer") = rsSource!Fields!("Customer")






Private Sub Command5_Click()
Dim rsSource As Recordset
Dim rsDest As Recordset
Dim x As Double

Set rsSource = CurrentDb.OpenRecordset("Schedule")
Set rsDest = CurrentDb.OpenRecordset("Table2")
x = 0
With rsSource
    .MoveFirst
    Do Until x > 10000
        With rsDest
            .AddNew
            rsDest!Fields!("Customer") = rsSource!Fields!("Customer")
            .Update
            x = x + rsDest!Fields!("TotalSF")
        End With
    .MoveNext
    Loop
End With

End Sub

Jeff

Just noticed the line 1, your compiler thinks you're trying to declare something but it can't figure out what. Delete that line.

[jmar93]

I don't know how line got pasted in my post, it's not in the code. I've made the changes and now I get a "run-time error '94'", "invalid use of null" on the following line:

x = x + rsDest.Fields("TotalSF")


Private Sub Command5_Click()
Dim rsSource As Recordset
Dim rsDest As Recordset
Dim x As Long

Set rsSource = CurrentDb.OpenRecordset("Schedule")
Set rsDest = CurrentDb.OpenRecordset("Table2")
x = 0
With rsSource
    .MoveFirst
    Do Until x > 10000
        With rsDest
            .AddNew
            rsDest.Fields("Customer") = rsSource.Fields("Customer")
            .Update
            x = x + rsDest.Fields("TotalSF")
        End With
    .MoveNext
    Loop
End With

thanks,
Jeff

[dsatino]

change x = x + rsDest.Fields("TotalSF")

to

x = x + rsSource.Fields("TotalSF")