exporting a query with combox text values into excel file

[hfs]

I a trying to search some product from a search button and two combo boxes text values ,and on serch the vba code is :

Private Sub Command4_Click()
    Dim db                  As DAO.Database
    Dim rs                  As DAO.Recordset
    Dim varRecords          As Variant
    Dim intNumReturned      As Integer
    Dim intNumColumns       As Integer
    Dim intColumn           As Integer
    Dim intRow              As Integer
    Dim strParam            As Integer
    Dim strParam1          As Integer
    Dim Myqdf               As DAO.QueryDef
    Dim ctl                 As String


If IsNull(Me!Combo13) Then
    MsgBox "Please Choose a Name from the drop down box above"
    Exit Sub
End If
 
 
   ct1 = Forms!frmSearchByBrandName.Combo13.Column(1)
   ct2 = Forms!frmSearchByBrandName.Combo15.Column(1)
   Set db = CurrentDb()
   Set Myqdf = db.QueryDefs("qryPrintBrand")
   Myqdf.Parameters("Forms!frmSearchByBrandName!Combo13") = ct1
   Myqdf.Parameters("Forms!frmSearchByBrandName!Combo15") = ct2
   Set rs = Myqdf.OpenRecordset()
 
   varRecords = rs.GetRows(1000)
   intNumReturned = UBound(varRecords, 2) + 1
   intNumColumns = UBound(varRecords, 1) + 1
    
   For intRow = 0 To intNumReturned - 1
      For intColumn = 0 To intNumColumns - 1
         Debug.Print varRecords(intColumn, intRow)
      Next intColumn
   Next intRow


'FileLocation = "C:\Users\hahmed\Desktop\navigation\New folder"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryPrintBrand", "C:\Users\hahmed\Desktop\navigation\New folder\test.xls", True
MsgBox "File Created!" & vbCrLf & vbCrLf & "File location:   " & FileLocation
 
End Sub

and the query(qryPrintBrand) code is

SELECT tblCustomers.CustomerDesc, tblProductInfo.ProductNumber, tblProductInfo.ProductDesc1, tblProductInfo.ProductDesc2, tblBrand.BrandDesc, tblBrand.CustomerID, tblProductInfo.UPCCode, tblProductInfo.Effective, tblProductInfo.DateUsed, tblProductInfo.FilmCoding, tblProductInfo.RetailCoding, tblProductInfo.ProductInfoLocation, tblProductInfo.LabelImage, *
FROM (tblCustomers LEFT JOIN tblBrand ON tblCustomers.CustomerID = tblBrand.CustomerID) LEFT JOIN tblProductInfo ON tblCustomers.CustomerID = tblProductInfo.Customer
WHERE tblCustomers.CustomerDesc= forms!frmSearchByBrandName!Combo13 AND tblBrand.BrandDesc=forms!frmSearchByBrandName!Combo15;

I just dont know,the fuile is created on click of button but with headers only,dtaa is not coming but wheni debug ,in immediate window,data is oming but just not coming in excel file !.
need help with this
Thanks

 

[Cronk]

You need to save the SQL for the query before the Docmd.Transfer

set Myqdf = db.querydefs("qryPrintBrand")
Myqdf.sql="SELECT tblCustomers.CustomerDesc, tblProductInfo.ProductNumber, tblProductInfo.ProductDesc1, tblProductInfo.ProductDesc2, tblBrand.BrandDesc, tblBrand.CustomerID, tblProductInfo.UPCCode, tblProductInfo.Effective, tblProductInfo.DateUsed, tblProductInfo.FilmCoding, tblProductInfo.RetailCoding, tblProductInfo.ProductInfoLocation, tblProductInfo.LabelImage, *
FROM (tblCustomers LEFT JOIN tblBrand ON tblCustomers.CustomerID = tblBrand.CustomerID) LEFT JOIN tblProductInfo ON tblCustomers.CustomerID = tblProductInfo.Customer
WHERE tblCustomers.CustomerDesc= " & chr(34) & forms!frmSearchByBrandName!Combo13  & chr(34) & " AND tblBrand.BrandDesc=" & chr(34) & forms!frmSearchByBrandName!Combo15 & chr(34);
set Myqdf= nothing

I've assumed the values in the combos are text and the chr(34)'s are to insert double quotes around the values.

You can test the saved query is OK by debug.printing the sql string and pasting it into a new query.

 

[hfs]

Thanks for your reply.

But,now i have changed all the code and doing it with ID not with the text values.and now on the querydef it says ,

error :item not listed in this collection

the code i am using is 
 strParam = Combo13.Value
 strParam1 = Combo15.Value
 
 
   Set db = CurrentDb()
   Set Myqdf = db.QueryDefs("qryPrintBrand")
  [COLOR=red] Myqdf.Parameters(Forms!frmSearchByBrandName!Combo13) = strParam (error)[/COLOR]
   Myqdf.Parameters(Forms!frmSearchByBrandName!Combo15) = strParam1
  ' Set rs = Myqdf.OpenRecordset()
 
   Set rs = Myqdf.OpenRecordset()
   varRecords = rs.GetRows(1000)
   intNumReturned = UBound(varRecords, 2) + 1
   intNumColumns = UBound(varRecords, 1) + 1
 
   For intRow = 0 To intNumReturned - 1
      For intColumn = 0 To intNumColumns - 1
         Debug.Print varRecords(intColumn, intRow)
      Next intColumn
   Next intRow

the sql code i am using is

SELECT tblCustomers.CustomerDesc, tblProductInfo.ProductNumber, tblProductInfo.ProductDesc1, tblProductInfo.ProductDesc2, tblBrand.BrandDesc, tblBrand.CustomerID, tblProductInfo.UPCCode, tblProductInfo.Effective, tblProductInfo.DateUsed, tblProductInfo.FilmCoding, tblProductInfo.RetailCoding, tblProductInfo.ProductInfoLocation, tblProductInfo.LabelImage, tblCustomers.CustomerId, tblBrand.BrandId
FROM (tblCustomers LEFT JOIN tblBrand ON tblCustomers.CustomerID = tblBrand.CustomerID) LEFT JOIN tblProductInfo ON tblCustomers.CustomerID = tblProductInfo.Customer
WHERE tblCustomers.CustomerId= forms!frmSearchByBrandName!Combo13 And tblBrand.BrandId=forms!frmSearchByBrandName!Combo15

;

 

[spikepl]

You forgot the quotes around the parameter name. And the parameter value is now supposed to be Long and not a String, right?

 

[hfs]

OHHHHH ,,,Thanksssssssssssssssssssssssssssssssssssssssssssss alot!...i mean i never noticed those quotes!!@@@

Thanks again for your help.Appreciated