Replies

So how do I remedy this? How can I show the payments on the invoice without joining them?

I’m a bit confused. I’m not sure how to do a “pop-up” window for each line in the invoice. I gathered from your post that I should add a field to my Invoice Details table to make an individual number for each record. So I added a field called “LineNo” to the Invoice Details table and made that the primary key. I then made another table called Applied that has the fields: LineNo, InvID, and Applied. Now I’m not sure where to go from there. I have my Payments form that has the fields from the Payment table (StudID, Date, Payment amount, ChequeNo, Notes, and check boxes for cheque or cash. I got rid of the subform that I had there. So do I make another subform?

Im just not sure what to do next. Thanks for your help.

I have a Student table and an Invoice table that just shows the invoice #, Student ID#, and date of the invoice, then there is an InvoiceDetails table that shows each line of the invoice (invoice #, type of fee (e.g. elementary, Jr high, etc.), and the amount of the fee). All these tables are linked to the Student table. Then I have a Payment table that has a payment # automatically assigned, Student ID#, date of payment, payment amount, cheque #, Notes, and check boxes for whether its a cheque or cash. This table is linked to the Student table.

I then want to have it so that when I enter a payment and it goes to the Payment form, it has a button to apply the payment. It then goes to the Apply form and shows the invoice line by line and you enter the amount of the payment you are applying to which line. E.G. If a student pays $10, and has an Elementary fee of $35 and a Local Levy of $5–you can apply $5 to the Local Levy and $5 to the Elementary fee. Then when you have another payment of, lets say, $15–you will see the amount remaining of each line of the invoice, and then be able to apply that next payment.

I hope you can understand what I’m trying to do. Maybe I’m even making it more complicated that I need to. If you can suggest another way to do it, I would appreciate any help.

Thanks,
Jennifer

Well, I tried what you said, but I still can’t get it to work. Here is the code I used:

Private Sub cmdPrintReceipt_Click()
On Error GoTo Err_cmdPrintReceipt_Click

Dim stDocName As String

stDocName = “Payment”

DoCmd.OpenReport stDocName, acPreview, , “PaymentID=” & “PaymentID”

Exit_cmdPrintReceipt_Click:
Exit Sub

Err_cmdPrintReceipt_Click:
MsgBox Err.Description
Resume Exit_cmdPrintReceipt_Click

End Sub

PaymentID is my field name, and the name I gave the control in the Payments form. But it still doesn’t work. If I have to give the control a different name, where do I go to change that? I tried giving it a different name in the Payments form, but it just asks for a parameter of txtPaymentID when I pressed the “Print Receipt” button. So I assume you have to go to some other places to change it, but I’m not sure where.

Jen

I figured it out! I had the wrong data put in the Control Source of the StudID box on the Invoices form. I had [forms]![frmInvoices by Student]![StudID] in the Control Source. So I switched it and put it in the Default Value property. I hope that’s right. And I changed the Control Source to StudID. I do need that code in the Default property, right? If I don’t, it doesn’t work.

Jen

It didn’t work. I even went back and started again. I built the Student main form with a linked form that comes from the Invoices table. I then put a subform in the Invoices linked form to go to the Invoice Details. But I still can’t see the invoices on the screen.

I linked it using InvID in the child and master. Is that where I went wrong? Should I use the StudID for this?

Nope. The Data Entry property on both the Invoice Details form and subform are set on “No”.

Do I have to pull in the Payments table to total up the payments? If not, I can’t seem to get it to total up the payments. I used the following code in the control of Total Payments:

=[Forms]![frmPayments]![Payment]

It just shows a “#Name?” where the total payment amount should be. What am I doing wrong?

Jen

This still doesn’t solve my problem. I still can’t add up the payments. Did I link the tables together incorrectly?

Jen

I have a separate table for payments. I have a Student table, Invoice table, Invoice Detail table, and a payment table. I have joined the Payment table to the Invoice table by way of One-to-many (Invoice table to Payment table). Is this maybe the problem?

Jen

Okay, Charlotte! I finally got it to work! I just put in a “Current Form” before the print preview part of the code.

Jen

I have the report pulling from a query. Is that the problem? Anyway, I’ve attached the database. If you have any other suggestions, I would greatly appreciate it. Thanks, Charlotte!

Ooooops! I can’t get the database file smaller that 346K (it has to be under 100K). Let me know what I should do.

Jen

Boy, this is frustrating. It’s still not working. Is it because I have a Subform in the Invoice form? I have a Form with invInvID in the top section, and invdetInvID in the Subform. But I tried changing it to [invdetInvID] and it just gave me a run time error that it couldn’t find that field. Do you know what I’m doing wrong?

Jen

Well, I changed all of them but I am still having trouble. Here’s the line again.

.Filter = “[InvID]=” & [invInvID] & “”

The first “[InvID]=” is that suppose to be the field name in the report? And the second “[invInvID]” suppose to be the control source of the InvID field in the Invoice form?

Jen