Create an invoice

[Amelia]

I am trying to create a invoice and within the invoice I want to be able to
have a drop down list of my customers to choose from within the form. I have
tried different things and none of them work. Anyone have an easy solution? I
do have it linked to by query and I get a drop down box with my query, but
when I choose the person it only puts in the person's first name and not the
rest.

I also am trying to use a subform but that is not working either. Any
suggestions would be great!

 

[John W. Vinson]

I am trying to create a invoice and within the invoice I want to be able to
have a drop down list of my customers to choose from within the form. I have
tried different things and none of them work. Anyone have an easy solution? I
do have it linked to by query and I get a drop down box with my query, but
when I choose the person it only puts in the person's first name and not the
rest.

I also am trying to use a subform but that is not working either. Any
suggestions would be great!

An invoice would typically be produced using a Report - the *LAST* step of
your database development process. It really sounds like you're mixing tables,
forms, queries and reports all together in a jumble, hence the difficulty!

Get the Tables designed, properly related, and normalized first.
Then get your data entry Forms developed to populate the tables. This is where
you'll use combo boxes and other data management tools.
Then create a Query linking all the tables together to provide the data needed
for the Report.
Then - *AFTER* you have all this done! - start work on your invoice report.

The invoice table should not contain the customer name (first *or* last, but
rather the unique CustomerID. You'ld *display* the full name in the combo but
store the ID on the form; the invoice report would be based on a query joining
the invoice table to the customer table by CustomerID in order to pull in the
first name, last name, and any other specific information.

You might want to look at some of these resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Mark Andrews]

You could take a look at my CRM template it has forms for creating invoices.
Here's a screenshot:
http://www.rptsoftware.com/includes/file.asp?fn=/products/crmtemplate/images/invoice.jpg
You could just design your form/subforms and report the same way or buy it
and steal the code.

I could also shoot you one designed for Access2007 to look at (email me if
you want it).

If you are just trying to:
1. have a form the user select's the customer (dropdown column 0 is
customerid, column 1 is customer name)
2. user presses a button and a report is generated that looks like an
invoice (for the customer selected)
in the query for #2 have criteria which refers to
Forms!frmYourFormName!ComboYourComboName
for CustomerID and then you can pull in all the other customer information

Hope that made sense,
Mark
RPT Software
http://www.rptsoftware.com

 

[Amelia]

Here is a link to a screen shot of what I have so far:

http://www.co.morrison.mn.us/vertic...ds/{4A928B2C-F20E-410D-A692-857AC9665B64}.PDF

What I want to be able to do is have a drop down where I have customer. I
want to be able to pull in the name and address from the contact table. Right
now I have a drop down list but once I click on the name it will only put in
the first name and that is all. I need a code or something to make it put in
all the info.

Or another thought is to start from the contact form, then have a button
that says "new invoice" and then will prompt me for the customer ID and then
it will put the firstname, lastname, address, city, state, and zip in the new
invoice.
Does this sound feasible?

 

[John W. Vinson]

Here is a link to a screen shot of what I have so far:

http://www.co.morrison.mn.us/vertic...ds/{4A928B2C-F20E-410D-A692-857AC9665B64}.PDF

Password protected. Please don't post your password, but it's probably not
needed that we see this.

What I want to be able to do is have a drop down where I have customer. I
want to be able to pull in the name and address from the contact table. Right
now I have a drop down list but once I click on the name it will only put in
the first name and that is all. I need a code or something to make it put in
all the info.

Or another thought is to start from the contact form, then have a button
that says "new invoice" and then will prompt me for the customer ID and then
it will put the firstname, lastname, address, city, state, and zip in the new
invoice.
Does this sound feasible?

It sounds like you're approaching the whole problem on the wrong basis. Are
you designing everything starting with your Form? then DON'T.

Start with properly normalized tables.

Then create a Form or two or three to update and maintain the data in those
tables.

An Invoice (to be printed or emailed) should be constructed as a Report based
on a query joining your customr table to the invoices table, and perhaps other
tables. The query can pull the customer's contact data from the customer table
and the invoice-specific data from the invoices table, using the currently
selected invoice on the Invoices form as a criterion.

If you're trying to copy a lot of fields from the customer table to the
invoice using a combo box, you're going about it all wrong, in other words!

 

[Amelia]

Sorry about that. Well what I am thinking of doing is just trying to redo the
whole thing. I don't have any crucial data in the database yet since it is
not how we want it completly.

I do have tables set up and have forms set up to update the data within the
tables. (this is a template from Microsoft so most of this was done before
hand). But now I think I will look over creating reports a little more and I
think I now get that I don't have to have an "invoice" form. I should be able
to have buttons or something within the report to pull from the tables or
queries??. This is how I am understanding it. I will go home and sleep on it
tonight and try again tomorrow.

Thanks for the responses!!

 

[John W. Vinson]

I do have tables set up and have forms set up to update the data within the
tables. (this is a template from Microsoft so most of this was done before
hand). But now I think I will look over creating reports a little more and I
think I now get that I don't have to have an "invoice" form. I should be able
to have buttons or something within the report to pull from the tables or
queries??. This is how I am understanding it. I will go home and sleep on it
tonight and try again tomorrow.

A Report, just like a Form, has a "recordsource" property - usually a Query
consisting of one or several joined tables containing the data that you want
to see on the report.

On the report (again, just like a form) you can put Textboxes, Checkboxes, and
other controls to display that data where and how you want to see it. You can
even put Combo Boxes though that's usually not a good idea (use a query
joining to the lookup table and a textbox instead).

You'ld set up the query to retreive the information you want to print; set up
the report to arrange that information on the page; and (usually) open the
report from a command button on a form, or some other appropriate event. The
report will open the query, pull the data from the tables, and either display
it in Preview or print it directly.