allocating the payment against the invoice???

I’ve created a db to generate invoices and receipts for customers. I’ve created three tables, ‘Demographics’, ‘BillableItems’ and ‘Payments’. I’ve also created a form with 2 tab controls, one for demographics and the second tab for billing. In the billing tab I’ve inserted two subforms, one for the billable items and the second for the payments. I’ve got the billable items working as I want making reference to the customer id. However now want to allocate payments against invoices. To do this I’ve created a tick box field called “pay” in the billable items table which can be true or false. When allocating a payment to an invoice the user ticks the box for the relevant invoice. From here I’m not sure what is the best approach? I’d like to create a button “Make Payment” on the 2nd page tab form not in the subforms . Once this is clicked, a form appears with the relevant payment fields from the payment table as well as making reference to the selected invoice (Invoice ID) However I’m new to VBA and this level of access. So I’m not sure how to do this… So after all that I guess my question is how do I get the payments table to make reference to the invoice ID as a way of allocating the payment against the invoice???

re: allocating the payment against the invoice???

This is not the type of question that we (as a general rule) offer help with. There are members who will go through with you and do this level of work for/with you, but this type of question relies on a deep understanding of you specific project. Even were you to share this clearly (You mention three tables yet you refer to customers in your explanation) it would not be something I would expect our members to help with. The scope is too large.

Having said that, we do have members who tend to allow themselves to get involved at such a level and may be along to help (No guarantees here of course). I suspect they will require a better understanding of the structure they're dealing with if they do mind you. These questions are so much harder when you don't even have a clear understanding of what the OP wants.

Two final points :
1. Someone may understand that the answer is more simple than I have realised. If so then good.
2. Your explanation was not given unclearly. You did a fair job. My point was that the scope of the question is complex enough (in my estimation) to require a lot more.

Welcome to Bytes!

re: allocating the payment against the invoice???

My apologies, the first line of my post is misleading. When I first referred to customers “I’ve created a db to generate invoices and receipts for customers”. Unfortunately I don’t have customers per say, I was referring to the database I’ve called customers… The db I’ve created I’m using to learn about database design and a bit of programming. After doing a bit of reading I’ve spent the last few days designing a database on paper trying to learn about normalization, relationships etc… Yesterday I decided to create a database in access to see how my design worked in practice. All went ok until I got to the stage where I wanted to get the invoice ID in the payments table to refer or lookup the invoice ID in the Billable Items table automatically. I’ve been looking for some code for example, that would do something like…

Payments.InvoiceID = BillableItems.InvoiceID

My thinking, once the payment makes reference to the invoice it’s then been allocated???

Again sorry if I made the last post a bit long winded and confusing... Hope this attempt is a better one… I’m more than happy to give more information if anyone needs it…

re: allocating the payment against the invoice???

I hope you do manage to find someone who can help you. You give the impression of someone who would be easy to work with. Unfortunately I know how much time/effort this will take someone (even with a co-operative and intelligent poster such as yourself) and I don't have that available I'm afraid. Apart from which I'm really not that hot on the subject of forms etc (Though I've done some work there of course, I'm no expert).

I'll monitor the thread (subscribed) also, and if anything comes up that I can help with I'll pitch in.

Change of subject:
Are you new to Access? New to database work? You mention you're getting up to speed with some of the concepts and are new to code.

Here's a quick link which many (myself included) have found very helpful (Normalisation and Table structures).

However this particular thread turns out, we can help with smaller, more self-contained issues that may come up. Many of us have a deal of experience in what is available in the standard libraries and the syntax of the code. We'd like you to think we can be a valuable resource.

re: allocating the payment against the invoice???

Thanks NeoPa! The link is a good one...

re: allocating the payment against the invoice???

I don't know if i will have the time to look at this, but if possible you would be well served if you posted your DB online as a zip file. A task of this size would move faster if all parties had access to the same DB to help come up with some concepts and or ideas.

-AJ

re: allocating the payment against the invoice???

I hope so RainKing.

I have done a number of articles myself, and yet I find this is the most helpful of all. Shame really, but there you have it ;)

@AJ. It may help in this situation, but as a general rule not requiring an attached database is beneficial to a thread. That way all is more visible even to those just perusing. It takes much more effort for someone to see if a thread matches their requirements when it contains attachments.

I think you may be right about this one of course.

re: allocating the payment against the invoice???

If it's ok with the admins i'm happy to upload the file or i can post screen grabs...

re: allocating the payment against the invoice???

It's fine to attach a zipped version of the database to the thread. Go ahead.

Consider sanitising it first. This includes removing data if you have large amounts which are not relevant and would only increase the size and transfer times, and anything inside which is private or proprietary which you don't want made available publicly.

Please make sure before sending a sanitised version though, that the problem still exists within it ;)

re: allocating the payment against the invoice???

http://www.k-teic.com/db/Customers.zip
It's been sanitised... very small with no data. i've inserted a description of what i'd like to do on the BillableItem tab of the Customers Form.... thanks for the help....