Invoice System - Table Relationship Help Required

[grupy]

Im looking to help my cousin with his invoices as he's currently doing them on paper by replacing it with a database version. Unfortunately, being a newbie to programming its taken a lot longer than it would for most people.

I have created the tables, as per my attachment, and am having a crazy time creating an invoice.

I created a form (frmInvoice) and it basically takes the users input and basically gathers data selected by the user, via table-creating queries and stores the data into temp tables.

I have the appropriate data, as he wants to bill his clients (Funeral Homes) every two weeks and I can pull the appropriate data required but cant for whatever reason figure out how to populate or relate that into the Invoice table (tblInvoice) and then create a report from this data.


I would appreciate any insight into my dilemma. Im stuck at the last part of the system and would love to finish this.

If you can either help my through my attachment or help me via providing a sample invoice system with proper table relationships, pls do reply.

 

[PNGBill]

You have a sample system. Have you looked at Northwind which should come with your Access?

You also have Database Templates.
Open Access in your Program Folder and select the wizard. You should then see a number of Databases ready to be created. Select Order Entry and follow the Prompts.

 

[gfultz]

I concur with PNGBill. Might as well use one of those as the base and customize it. That way you know you have a foundation that works.

 

[vbaInet]

I concur with PNGBill. Might as well use one of those as the base and customize it. That way you know you have a foundation that works.

Sample databases provided in Access are mainly there for practice and to get yourself acquianted with the application. Although, due to the complexities of some concepts applied in some of them, you're better off getting a book and starting from scratch. In actual fact, a sample database wouldn't teach you database relationships.

 

[G37Sam]

A thing to note, have the "select company" in frminvoice as combo box just in case you want to update later on

What kind of invoice are you trying to make? Will you have many items per invoice?

Get one of his actual invoices and try to replicate it in a report

 

[PNGBill]

Here is a Screen shot of the Basic Database Order Entry.

While it may not be the Bees Knees for all it does show how the respective Tables relate to each other.

 

[grupy]

I appreciate the replies everyone - so thank you.

PNGBill: I wasnt aware of the Northwind DB else I would have structured my cousins program around this one, which would have saved me a lot of time and headaches.

I did programming before during my studies and really dont care to get back into it as my current position doesnt require it. So, I will definitely use the foundation from Northwind and structure it to meet my needs.

Again, a big thank you to all that took the time to reply.

 

[dfenton]

I would never build a production system on to of a database created from one of the Access templates or using the sample databases. Both the templates and the samples that come with Access are filled with WORST PRACTICES in so many ways that it would take less work to start from scratch than it would to correct the deficiencies.

I really have never understood why MS thinks they are doing their product a favor by continuing to distribute such substandard sample databases.

 

[boblarson]

Both the templates and the samples that come with Access are filled with WORST PRACTICES in so many ways that it would take less work to start from scratch than it would to correct the deficiencies.

Of that I have to agree. Perhaps one day they (MS) will get the picture, but I'm not that hopefull based on what I've heard the Access Team say directly.

 

[vbaInet]

I would never build a production system on to of a database created from one of the Access templates or using the sample databases. Both the templates and the samples that come with Access are filled with WORST PRACTICES in so many ways that it would take less work to start from scratch than it would to correct the deficiencies.

I really have never understood why MS thinks they are doing their product a favor by continuing to distribute such substandard sample databases.

Exactly the point I was hammering David. I hope the OP would have second thoughts.

 

[vbaInet]

Of that I have to agree. Perhaps one day they (MS) will get the picture, but I'm not that hopefull based on what I've heard the Access Team say directly.

I hear, or maybe they've already done, that the new sample DBs will use macros completely for automation, no vba code whatsoever.

 

[boblarson]

I hear, or maybe they've already done, that the new sample DBs will use macros completely for automation, no vba code whatsoever.

That is true as far as I know. There are no samples currently allowed with code. However, the MVP's are vigorously lobbying against that and they may relent a bit on that one. But it is hard to say.

 

[PNGBill]

I can't argue with the "Guns" you Guys have but a sample database can give you something to study and you can edit them to improve and change.

I recently used the Asset template database and while most of it is changed it did give me a great starting point.

Change the table, field, query and form names to be better and build your own forms but at least you had a base to start from.

When you look at a new database build with less experienced eyes you sometimes don't even know where to start.

 

[vbaInet]

That is true as far as I know. There are no samples currently allowed with code. However, the MVP's are vigorously lobbying against that and they may relent a bit on that one. But it is hard to say.

At the moment you don't need to "TRUST" before it can be run. This means that all macros will bypass the Trust Settings.

When you look at a new database build with less experienced eyes you sometimes don't even know where to start.

The problem is people tend to jump the gun rather than learning the basics then build upon that knowledge. Books are a good resource for learning and beginners fail to understand that despite Access being "free", it is a powerful tool that needs to be fully understood before getting your hands dirty. If Access was sold as a separate application (which I think they intend doing in the future), then I think people would reconsider their learning approach.

 

[boblarson]

At the moment you don't need to "TRUST" before it can be run. This means that all macros will bypass the Trust Settings.

Not ALL macros. Only SPECIFIC macros. There are still a list of macros considered dangerous and that is why they do not show up in the macro list until you click the SHOW ALL ACTIONS button.

 

[vbaInet]

Not ALL macros. Only SPECIFIC macros. There are still a list of macros considered dangerous and that is why they do not show up in the macro list until you click the SHOW ALL ACTIONS button.

I don't use them anyway so they can remain hidden

 

[boblarson]

I don't use them anyway so they can remain hidden

However, anyone wanting to use the new Web enabled capabilities of Access 2010 will need to use macros for that part as there is no ability to use VBA in web enabled forms/reports.

 

[vbaInet]

However, anyone wanting to use the new Web enabled capabilities of Access 2010 will need to use macros for that part as there is no ability to use VBA in web enabled forms/reports.

I will play around with the features but I wouldn't deploy it. It's been a while since I used macros.

 

[PNGBill]

These posts are a great insight into some issues but has the OP been scared away from the original simple invoicing system?
I know my Postage Stamp knowledge won't get me far with this issue but I use Word and don't know a fraction of it's capabilities and the same with Excel.

Our Database improves weekly (not weakly) yet if all of the features we now have were a prerequisite of the original, we would not have used a database as we could not have learn't enough to build it.
(No idea what we would have used)