Get all filtered results over to query

[vbaInet]

GREAT! Glad you got it working.

 

[LFC]

...alright. It was working great and then all of a sudden after i clicked the button i got the message, "System Resource Exceeded". Since it's the same amount of data as before I can't imagine that's the problem. Would you have any idea how to fix this?

 

[vbaInet]

Compile & Repair for now.

 

[LFC]

i still got the error. Any other ideas?

 

[vbaInet]

Do you have a loop anywhere?

 

[LFC]

yes. that's how I'm getting all the ID's.

 

[vbaInet]

The loop is endlessly executing.

 

[LFC]

Do Until IsNull(Me.SearchResults_Warranty_subform.Form.Claim_Number.Value)
        strSQL = strSQL & " OR (LHW_All_Claims_IMP_tbl.[Claim Number]) = '" & Me.SearchResults_Warranty_subform.Form.Claim_Number.Value & "'"
        DoCmd.GoToRecord , , acNext
    Loop

I have been changing stuff all over and cannot figure out what is wrong. Can you see anything?

 

[vbaInet]

It seems Claim_Number will never be Null.

 

[LFC]

I had also tried the code below and it still didn't work. Is there a better way of going through until the last record?

Me.SearchResults_Warranty_subform.SetFocus
DoCmd.GoToRecord , , acLast
strClaimNum = Me.SearchResults_Warranty_subform.Form.Claim_Number.Value
Do Until Me.SearchResults_Warranty_subform.Form.Claim_Number.Value = strClaimNum
        strSQL = strSQL & " OR (LHW_All_Claims_IMP_tbl.[Claim Number]) = '" &    Me.SearchResults_Warranty_subform.Form.Claim_Number.Value & "'"
        DoCmd.GoToRecord , , acNext
Loop

 

[vbaInet]

Before you entered the loop you sent the cursor to the LAST record. Rethink your strategy. Also, look into RecordsetClone.

I don't see why you even need to loop?

 

[LFC]

Sorry, I cut out the part of my code in between. I bring it back up the first record. I'm looping to set the sql code to all the filtered records. I will look into RecordsetClone. The only reason i was looping was because I didn't know any other way to get all the filtered results to show up.

 

[LFC]

Alright. I have figured out my problem. After I do the procedure the query's criteria stays in the query and then after a while the criteria for the field will fill up, so I just have to figure out how to have the criteria auto clear after I get the query exported. It works perfect aside from the criteria staying.

Me.SearchResults_Qnote_subform.SetFocus
    DoCmd.GoToRecord , , acFirst
    strSQL = "SELECT * FROM [QIT_Z3_QNOTE_tbl] WHERE [Q-note #]=" & Me.SearchResults_Qnote_subform.Form.[Q-note #].Value
    Me.SearchResults_Qnote_subform.SetFocus
    DoCmd.GoToRecord , , acNext
    Do Until IsNull(Me.SearchResults_Qnote_subform.Form.[Q-note #].Value)
        If Me.SearchResults_Qnote_subform.Form.[Q-note #].Value <> "" Then
            strSQL = strSQL & " OR (QIT_Z3_QNOTE_tbl.[Q-note #]) = " & Me.SearchResults_Qnote_subform.Form.[Q-note #].Value
            DoCmd.GoToRecord , , acNext
        End If
    Loop
    Set db = CurrentDb
    Set qd = db.QueryDefs("SearchResultsQnoteExport_qry")
    qd.SQL = strSQL
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "SearchResultsQnoteExport_qry", strPath, False

 

[vbaInet]

As you can see, you've changed the sql of the original query so before you change it save the original sql in a temporary variable, change it, export, then set it back to the temp variable.

Regarding the loop, there's obviously some criteria that is limiting the number of records. It seems to me that the query was set via a textbox. So all you do is get the criteria from the textbox.

 

[LFC]

Me.SearchResults_Qnote_subform.SetFocus
    DoCmd.GoToRecord , , acFirst
    strSQL = "SELECT * FROM [QIT_Z3_QNOTE_tbl] WHERE [Q-note #]=" & Me.SearchResults_Qnote_subform.Form.[Q-note #].Value
    Me.SearchResults_Qnote_subform.SetFocus
    DoCmd.GoToRecord , , acNext
    Do Until IsNull(Me.SearchResults_Qnote_subform.Form.[Q-note #].Value)
        If Me.SearchResults_Qnote_subform.Form.[Q-note #].Value <> "" Then
            strSQL = strSQL & " OR (QIT_Z3_QNOTE_tbl.[Q-note #]) = " & Me.SearchResults_Qnote_subform.Form.[Q-note #].Value
            DoCmd.GoToRecord , , acNext
        End If
    Loop
    Set db = CurrentDb
    Set qd = db.CreateQueryDef("SearchResultsQnoteExport_qry", strSQL)
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "SearchResultsQnoteExport_qry", strPath, False
    db.QueryDefs.Delete ("SearchResultsQnoteExport_qry")

I think I have it working. I don't use those query's for anything other than this so I just deleted them. As far as I can tell this is doing the trick....however in about 10 minutes I'll probably have another problem.

 

[vbaInet]

As long as you feel comfortable with your approach.

 

[LFC]

hahaha...yeah i'm sure it's very jerryrigged, but the fact that it works makes me very pleased. Thanks for your assistance, I really couldn't have done it without you.

 

[vbaInet]

So are you happy with the fact that you can see it interrupting your form by moving from one record to another just to get the values?

You can optimise this whole process.

 

[LFC]

haha..I don't have a problem with it right now...I got way too much stuff dumped on me to worry about making people watch records go flying down the page. At some point I'll probably go back and change it, but the fact it's operational is more than enough for me at this point. You'll probably see something about this on the forums again in the future and you'll know exactly what it's about.

 

[vbaInet]

Oki doki. Good luck with the rest of your project.