Attention To The Best Access / VB Programmers In This Forum

[stevegoodwinuk]

Right first of all hi everyone,

i have been to about four forums now and No One has been able to make this work it can be done as i have seen it from the access sample database (Order Entry). I have been informed that this forum has the best of the best in access guru's, this problem has had me stuck for 2 weeks i don't normally asked for help but it has breached my knewledge base and now its time for an expert to have a look at it.

I have had many suggestions but none have worked, below is the link to the Order Entry database so you can see what i am trying to acheive and also another link of my actual database as it stands.

The problem is that i have a form called Customers this is displayed when the database is opened, the form has a subform called QuoteSelector that holds all the quotes if there are no quotes then the user is then able to add a new customer and then create a quote when they click the Create New Quote button.

Then another form called Quotes opens this holds the data form the quote and also has a subform called QuoteBuilder, this form holds all the materials that have been selected for the quote.

Now when i click the Create Invoice button that is on the Customers page i want all the information from these forms to be displayed in my report called Invoice. I have already created the Invoice i am just having trouble in passing the CustomerID and the QuoteID to the Invoice or i think this is what is needed in order to make all the data display.

If you can help this would be great as no other forum has been able to and this one has the best reputation for programmers with access so well i will have to see it, but please take a look at the Order Entry database and see how the Invoice is generated you will notice a box pop up letting the user enter more details i don't want this i just want the invoice to come up and then the user can print it.

Thanks in advance and hope this can be done:

This is the sample database Order Entry

Order Entry Download

This is the Database I NEED to create the invoice for:

Database FOR Edit

 

[antomack]

Just a few things first
1) The invoice you provided in your sample database is not properly defined for your tables, it is still using the tables from the 'Order Entry' database. You will first need to substitute the Order and Order Details tables with your Quote and QuoteDetails tables and make the necessary field changes in the report. You will also need to remove the 'Shipping Methods' table as you don't seem to require it in your database.
2) If you are dealing in quotes then the report you are printing would not strictly be an invoice
3) How do quotes become actual orders?

Anyhow to open the 'Invoice' report you need to provide a unique reference to the quote you want printed, in this case the QuoteID. Since each quote is specific to only one customer you do not need to provide the customerid.

Thus adding code similar to the following behind you 'Print Invoice' button will allow you to open the report for the quote selected in the quote subform, this code is exact same as in the Order Entry db but changed to suit your fields and forms. You will need to update the 'Invoice' report to use the Quote tables or it will not be able to find a corresponding QuoteID.

Private Sub Command49_Click()
On Error GoTo Err_Command49_Click
    ' ensure that a quote exists for the customer
    If Forms![Customers]![Quote Selector].Form.RecordsetClone.RecordCount = 0 Then
        MsgBox "Enter quote information before previewing invoice."
    Else
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.OpenReport "Invoice", acPreview, , "[QuoteID] = Forms![Customers]![Quote Selector].form![QuoteID]"
    End If

Exit_Command49_Click:
    Exit Sub

Err_Command49_Click:
    If Err <> 2501 Then
        MsgBox Err.Description
    End If
    Resume Exit_Command49_Click
End Sub

 

[stevegoodwinuk]

Another Look

right well i worked that code out last night roughly and now im not sure on what i need in the SQL query within the Report (Invoices), the current SQL is:

SELECT Orders.*, [Order Details].OrderDetailID, [Order Details].ProductID, [Order Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount, Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Customers.[Country/Region], Customers.PhoneNumber, Customers.ContactFirstName & " " & Customers.ContactLastName AS [Contact Name], [Shipping Methods].ShippingMethod, Products.ProductName
FROM Products INNER JOIN ((Customers RIGHT JOIN ([Shipping Methods] RIGHT JOIN (Employees RIGHT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) ON [Shipping Methods].ShippingMethodID = Orders.ShippingMethodID) ON Customers.CustomerID = Orders.CustomerID) RIGHT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
ORDER BY Orders.ShipDate;


Now do i just need to change all the Orders and Order Details to Quote and Quote Details, and then delete the Shipping Methods one. I have tryed this but i am not at my pc at the moment im at work but when i get home bout 5:45 so please check back here if you read this post by then as i will upload to the stage i am at now.

I currently have recreated the Invoices report and now the code works but when i click the Print Invoice button it doesn't pass the data. I know this is hard to understand without the database and i will post it when i get in for you to see. The above SQL is clear top me apart from the RIGHT JOIN bits is this binding all the ID's togeather so it can link and display all of the information ?? or am i on the wrong track and how does this need to be edited as when i push the Print Invoice button with your code it doesn't pass the data to the report and just comes up with:

There is no data for this report. Canceling report...

Which is the VB that tells me there is no data, so i think this means that the QuoteID isn't being passed over.
But thanks for all your help antomack