
Originally Posted by
kennejd
The problem must be in your vba?
Can you send your vba code?
That's what I'm thinking. Bellow is the code. I am not a programmer, so my VBA may be incredibly goofy
, I'm learning this stuff on my own, and not having any programming background is making it very difficult fore me 
Code:
Private Sub Command0_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
On Error GoTo Command15_Click_Error
Set db = Application.CurrentDb
Set qdf2 = db.QueryDefs("qryPurchaseOrder_ORD")
Set qdf1 = db.QueryDefs("qryExportSalesHistory_ORD")
Dim wk1 As Date
Dim wk2 As Date
Dim wk3 As Date
Dim wk4 As Date
Dim wk5 As Date
Dim wk6 As Date
Dim delivery_date As Date
Dim vendor As String
Dim weeks As String
Dim weeks2 As String
delivery_date = Text16.Value
vendor = Combo0.Value
'Determine and set the right dates for the query that will export the sales history.
wk1 = Text2.Value
wk2 = Text4.Value
weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "#)"
If Text12.Value <> "" Then
wk6 = Text12.Value
wk5 = Text10.Value
wk4 = Text8.Value
wk3 = Text6.Value
weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "# Or (tblSales_ORD.Dates) = #" & wk3 & "# Or (tblSales_ORD.Dates) = #" & wk4 _
& "# Or (tblSales_ORD.Dates) = #" & wk5 & "# Or (tblSales_ORD.Dates) = #" & wk6 & "#)"
ElseIf Text10.Value <> "" Then
wk5 = Text10.Value
wk4 = Text8.Value
wk3 = Text6.Value
weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "# Or (tblSales_ORD.Dates) = #" & wk3 & "# Or (tblSales_ORD.Dates) = #" & wk4 _
& "# Or (tblSales_ORD.Dates) = #" & wk5 & "#)"
ElseIf Text8.Value <> "" Then
wk4 = Text8.Value
wk3 = Text6.Value
weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "# Or (tblSales_ORD.Dates) = #" & wk3 & "# Or (tblSales_ORD.Dates) = #" & wk4 & "#)"
ElseIf Text6.Value <> "" Then
wk3 = Text6.Value
weeks = "((tblSales_ORD.Dates) = #" & wk1 & "# Or (tblSales_ORD.Dates) = #" & wk2 & "# Or (tblSales_ORD.Dates) = #" & wk3 & "#)"
End If
If DLookup("[Order Date]", "tblOrders_ORD", "[Completed]=0 And [Vendor]=" & "'" & vendor & "'") <> "" Then
msgChoice = MsgBox("It seems that there is an open order for " & vendor & " already. The delivery date for the order is: " & DLookup("[Order Date]", "tblOrders_ORD", "[Completed]=0 And [Vendor]=" & "'" & vendor & "'") & ". If this order has already been delivered and the cost and quantity recieved has been checked, then " _
& "click YES bellow to mark the order as completed and a new order will be created. Otherwise click NO to edit the existing order. Click CANCEL to exit without any changes", _
vbYesNoCancel, "Read Carefully!!!")
If msgChoice = vbYes Then
DoCmd.RunSQL "UPDATE tblOrders_ORD SET tblOrders_ORD.Completed = 1 " _
& "WHERE (((tblOrders_ORD.Vendor)=""" & vendor & """) AND ((tblOrders_ORD.Completed)=0));"
ElseIf msgChoice = vbCancel Then
Exit Sub
End If
End If
'Insert the order into the [tblOrders_ORD] to be populated with the Quantity later;
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblOrders_ORD ( [Item Code], Item, Vendor, [Order Date] ) " _
& "SELECT tblItems_ORD.[Item Code], tblItems_ORD.Item, tblItems_ORD.Vendor.Value,#" & delivery_date & "#AS Expr1 " _
& "FROM tblItems_ORD " _
& "WHERE (((tblItems_ORD.Vendor.Value)=""" & vendor & """));"
DoCmd.SetWarnings True
'Generate Pivot Table query for the selected weeks to be exported to EXCEL
strSQL = "TRANSFORM Sum(tblSales_ORD.Quantity) AS SumOfQuantity " _
& "SELECT tblItems_ORD.[Item Code], tblItems_ORD.Item " _
& "FROM tblItems_ORD LEFT JOIN tblSales_ORD ON tblItems_ORD.[Item Code] = tblSales_ORD.[Item Code] " _
& "WHERE (" & weeks & ") " _
& "GROUP BY tblItems_ORD.[Item Code], tblItems_ORD.Item " _
& "PIVOT tblSales_ORD.Dates;"
qdf1.SQL = strSQL
DoCmd.DeleteObject acTable, "tblWeeklySalesData_ORD"
1:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportSalesHistory_ORD", "C:\qryExportSalesHistory_ORD.xls", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblWeeklySalesData_ORD", "C:\qryExportSalesHistory_ORD.xls", True
Set db = CurrentDb()
Set tbl = db.TableDefs("tblWeeklySalesData_ORD")
Set idx = tbl.CreateIndex("PrimaryKey")
Set fld = idx.CreateField("Item Code")
idx.Fields.Append fld
idx.Primary = True
tbl.Indexes.Append idx
'Generate the SQL for the Purchase Order query
weeks2 = "SELECT tblOrders_ORD.[Item Code], tblOrders_ORD.Item, tblItems_ORD.Layer, tblWeeklySalesData_ORD.[" & wk1 & "], tblWeeklySalesData_ORD.[" & wk2 & "], " _
& "tblItems_ORD.[In Stock], Raw(3,[tblItems_ORD]![In Stock],[tblWeeklySalesData_ORD]![" & wk1 & "], [tblWeeklySalesData_ORD]![" & wk2 & "]) "
strSQL = weeks2 _
& "AS Raw, tblOrders_ORD.Quantity " _
& "FROM (tblItems_ORD RIGHT JOIN tblOrders_ORD ON tblItems_ORD.[Item Code] = tblOrders_ORD.[Item Code]) LEFT JOIN tblWeeklySalesData_ORD ON " _
& "tblOrders_ORD.[Item Code] = tblWeeklySalesData_ORD.[Item Code] " _
& "WHERE (((tblOrders_ORD.Vendor)=""" & vendor & """) AND ((tblOrders_ORD.Completed)=0)) " _
& "ORDER BY tblOrders_ORD.[Item Code];"
qdf2.SQL = strSQL
DoCmd.OpenQuery ("qryPurchaseOrder_ORD")
Forms(ft).RecordSource = strSQL
DoCmd.OpenForm "ft"
CleanUp:
Set fld = Nothing
Set idx = Nothing
Set tbl = Nothing
Set db = Nothing
MsgBox "Weekly sales data generated succesfuly. Click OK to close this dialog", vbOKOnly, "Success!!!"
On Error GoTo 0
Exit Sub
Command15_Click_Error:
If Err.Number = 94 Then
MsgBox "Please make sure that you have a VENDOR, DELIVERY DATE and at least 2 WEEKS OF SALES selected and try again.", vbOKOnly, "NO VENDOR SELECTED!"
End If
If Err.Number = 7874 Then GoTo 1
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command15_Click of VBA Document Form_frmMain"
End Sub