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]

@jmar93
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]

@jmar93
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]

@jmar93
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")

[jmar93]

Change made no difference.

[dsatino]

Could have. You can't add a number to a Null. Before the change, you weren't moving any data to rsDest.Fields("TotalSF") so it would be null. Now, there should be data in the TotalSF field of your schedule table, but if there is an empty value in one of the records, you'll get the same error.

There are lots of ways to deal with that via code, but you're probably better off setting the default value in the table to 0 so you don't have to worry about it.

[jmar93]

There was a null value in the source table, once I removed it everything works great. Thank you so much for all of your help, it was huge.

thanks again,
Jeff

[jmar93]

Hi,

One last question, what is the easiest way to replace the 10000 in the following line:

Do Until x > 10000

with a number that you enter at run-time?

thanks,
Jeff

[jmar93]

Forget it, figured it out.

thanks,
Jeff

[NeoPa]

Jeff,

Please note all the times people have had to tidy up after you adding the code tags that are required when posting code. Please ensure this never happens again, or we will issue an official site warning which could lead to a suspension of your account on further transgressions.

-Administrator.