starrcruise
Registered User.
- Local time
- Today, 15:01
- Joined
- Mar 4, 2011
- Messages
- 18
I am totally at a loss. I have a query in Access that I want to export the multiple filtered versions based on "LeadID's in another table and exported to separate worksheets (per LeadID) using transfer spreadsheet vba. I am asking it to find the PeopleID (autonumber NOT indexed) from the tblListPeople, then create a recordset listing the name matching up with each PeopleID so it can loop through the names and create a filtered query for each name, then export each to a tab on one excel file. I pretty much copied this code, just added my DB object names.
It creates the first qrydef = strtemp(LeadReportExport)
Then comes up with the error 3022 the changes to the table were not successful because of ....indexed duplicate values,
I have gone to each table, and made sure all foreign fields and main ID fields are not EVEN indexed (which I am not happy about, but the primary ID in each table is auto numbered). Nothing seems to work. Here is code.
It creates the first qrydef = strtemp(LeadReportExport)
Then comes up with the error 3022 the changes to the table were not successful because of ....indexed duplicate values,
I have gone to each table, and made sure all foreign fields and main ID fields are not EVEN indexed (which I am not happy about, but the primary ID in each table is auto numbered). Nothing seems to work. Here is code.
Code:
Case 3 'exports Tasks lists by lead to excel tabbed file
If Me.cboChooseReport = 3 Then
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstLead As DAO.Recordset
Dim strSQL As String, strTemp As String, strLead As String
Const strFileName As String = "Project and Task Tracking Input"
Const strQName As String = "LeadReportExport"
Set dbs = CurrentDb
'create temporary query to export data
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] where 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
'Get list of PeopleID values
strSQL = "SELECT DISTINCT PeopleID FROM tblListPeople;"
Set rstLead = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
'Loop through list of People values and create a query
'for each ID so data can be exported
If rstLead.EOF = False And rstLead.BOF = False Then
rstLead.MoveFirst
Do While rstLead.EOF = False
'set strLead
strLead = DLookup("Name", "tblListPeople", _
"PeopleID=" & rstLead!PeopleID.value)
'set strSQL
strSQL = "SELECT * FROM qrySubrptTaskDetailOpenExport WHERE" & _
"LeadID = " & rstLead!PeopleID.value & ";"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strLead
strTemp = qdf.Name
qdf.sql = strSQL
qdf.Close
Set qdf = Nothing
'send to excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTemp, "U:\Enterprise Risk\Corporate Risk Committee\Project and Task Tracking\" & strFileName & ".xls"
rstLead.MoveNext
Loop
End If
rstLead.Close
Set rstLead = Nothing
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing
End If
Exit_Procedure:
End Select
Exit Sub
Error_Handler:
MsgBox "An error has occurred in this application." _
& "Please contact your technical support and" _
& "tell them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
& Err.Description, _
Buttons:=vbCritical, Title:="CRC Project DB"
Resume Exit_Procedure
End Sub
Last edited by a moderator:
