Invoicing Again

SteveF

Registered User.
Local time
Today, 22:08
Joined
Jul 13, 2008
Messages
191
Thanks to the great help already received I'm much further on with this. True to form though, I'm stumped.

I can now produce invoices with the variable vat rate via a fairly simple form/query/table structure.

What I can't do, and have struggled with for hours, is pick one invoice from the query and print it.

How do I pick a single set of data from a list generated by the query and print it?

Thanks in advance for help. Please excuse dumb questions, this database stuff is a real stretch for me :)
 
You don't pick from a query list, in the reality of how you do day to day tasks.

You can approach this many ways but a common way is to have a starting Form where you can select a Customer from a drop down list.
This list could be from a query and when you choose the customer, then data will populate a subform (invoice).

This really has 101 ways to go about getting to the Selected Customer.

Your Forms and Subforms are joined by Master Child fields. This will Filter the forms record source so only the one Invoice is showing or One Customer is showing.

You may have a Continuous form showing Sales for the day. Each record has a command button that will then open the invoice form for that invoice.
When you setup the Command Button you set the Link Criteria.
Often a wizard will assist with this or alternatively one of the wizards on this forum.

In short, don't restrict the query too much and don't Filter the Form. Let the Link do this. The query will show All Customer, All Invoices (within reason). The link or Master Child fields will do the restricting plus you can add Filters by way of VBA to filter the data as required by clicking buttons.
 
Ok, so forms/sub forms are the way forward? I will start there, thanks
 
alternatively, have a form that gives you a text box in which you can enter a single invoice number

have a query selecting the items corresponding to that field

criteria = forms!searchformname!searchfield


and now you have a query that can certainly be used to generate a single invoice.
 
Well I would have thought the basic Invoice (Main Form - Header Details and Sub Form - Line items Details) would already be made. If not get this done.
It will be a Single Form. Can't subform from a continuous form anyway.
This Invoice will be for All your sales and when you get it working you will be able to click on the record selector, bottom left hand corner and scroll through the 15,000 sales invoices.

Next task will be to decide where you will be when wanting to view an invoice. This is where you will no longer see 15,000 invoices but just the one you clicked on or the 50 for the day etc.

This means you will have many other forms eg Customer Sales History,
Product Sales Data, sales for Given Date or Given Date Range etc etc.
Everywhere you go you will be adding buttons to take you somewhere else and or open another form (maybe Invoice). Enjoy:)

The Queries will be in the back ground and mostly return a lot of data and the Form and or VBA will restrict the data displayed as per your Click or data entry - this may be linked to the query more directly where a field is requesting a user input eg Date etc.

Mostly Customer numbers are not user input but rather User Selected from a list.
 
alternatively, have a form that gives you a text box in which you can enter a single invoice number

have a query selecting the items corresponding to that field

criteria = forms!searchformname!searchfield


and now you have a query that can certainly be used to generate a single invoice.

So a form separate from the invoicing form itself? Then a text box that allows me to select a single invoice from the ones I've created?

Clearly, the answer to this lies in forms so I'm further on already.
 
Well I would have thought the basic Invoice (Main Form - Header Details and Sub Form - Line items Details) would already be made. If not get this done.
It will be a Single Form. Can't subform from a continuous form anyway.
This Invoice will be for All your sales and when you get it working you will be able to click on the record selector, bottom left hand corner and scroll through the 15,000 sales invoices.

Next task will be to decide where you will be when wanting to view an invoice. This is where you will no longer see 15,000 invoices but just the one you clicked on or the 50 for the day etc.

This means you will have many other forms eg Customer Sales History,
Product Sales Data, sales for Given Date or Given Date Range etc etc.
Everywhere you go you will be adding buttons to take you somewhere else and or open another form (maybe Invoice). Enjoy:)

The Queries will be in the back ground and mostly return a lot of data and the Form and or VBA will restrict the data displayed as per your Click or data entry - this may be linked to the query more directly where a field is requesting a user input eg Date etc.

Mostly Customer numbers are not user input but rather User Selected from a list.

The basic structure is there already, and I can print the invoice as I've created it. What I'm trying to do is generate a list of historical invoices and be able to re-print or re-send them.

It's a service rather than a supply invoice, so we are looking at hundreds rather than thousands of invoices.
 
The basic structure is there already, and I can print the invoice as I've created it. What I'm trying to do is generate a list of historical invoices and be able to re-print or re-send them.

It's a service rather than a supply invoice, so we are looking at hundreds rather than thousands of invoices.

Then you should get to your Invoice from a Form displaying Sales for Last Month, Customers purchased Last month or similar. A Continuous Form with command Buttons to open a particular Customer and then display the invoices for that customer (one line details on Continuous form) and then another Button on each record to open the Invoice.

While there maybe a few clicks to perform it will be quite fast and no typing required.
First Form will be Menu driven.

I can attach some example screen shots if you are not sure and are interested.
 
I'm very interested :) there is just so much to learn. I've got the invoice printing nicely now, if I could resolve the search/history bit I would be very happy.

Thanks for help.
 
Here are three shots - some censorship done of sensitive data.

Menu is the Menu shot. We have 20 odd menu pages over different issues in the database. This covers following up on Loans that are current and due for payment - all customers who have a current balance.

Customers show some of the 600 plus records in this record set. You can see the Command Buttons on the Form Header (actually the MainForm) which cover Filters and Order By issues. Order by First Name, Amount Owing etc.
The SubForm is a Continuous Form and Command Buttons are on each record to "Drill" down to this data when clicked.

Customer shows the Selected Customer. The MainForm is run by a query that would show all the customers, (one page at a time) but here only shows one because of the Link criteria in the button on the Customers screen shot.
Here, again, you can see buttons on the Main Form and also on the two Sub Forms.
These produce reports or Forms that provide more data on the Customer and or the "Invoice".
We don't have Invoices, just Statements, but same difference.
 

Attachments

  • Screen Menu.jpg
    Screen Menu.jpg
    82.3 KB · Views: 182
  • Screen Customer.jpg
    Screen Customer.jpg
    102.3 KB · Views: 177
  • Screen Customers.jpg
    Screen Customers.jpg
    105.3 KB · Views: 180
Not all buttons included in the beginning. It grows as your needs grow.
 
I need to spend some time on this, but the search fields look good and I'm sure I can work that kind of thinking into what I'm doing. I started off wanting a simple invoicing utility and am already seeing the possibilities of extending it into more of a data collecting and book keeping application.

If I can get the search/queries bit sorted out I can start looking at VAT returns etc.

Thanks for input.
 
We don't use many search queries and still use a Off the Shelf General Ledger for Company Accounts.

Most Databases would be Inventory / Sales and then you either enter or import Data into your GL.

If your Database has good Sales and Customer Balance reports (it should) then all you would need in your GL system is Daily Sales Amount and Daily Repayments amount. ie your Accounts Payable just has the Balance.
Details are in your Sales / Inventory Database.

We used to use search forms more but now we have developed forms that display most of the data we need and just scroll through this to find what we want and click. Sort/Order and filter buttons get the record source more inline with our target.
 

Users who are viewing this thread

Back
Top Bottom