Please, please someone help

[VK]

I posted this before under a different topic heading but I haven't gotten any response. I am really stuck with this. I would very much appreciate any suggestions. Thank you in advance.

Here is what I am trying to do...
I have a form that I am trying to set up like a statement.

These are the fields:

CustID, InvDate, InvNo, DueDate, ProductID, TransDesc, Quantity, Price, Total, Pmts, Current, 30Days, 60Days, 90Days, FinanceChg, BalDue

I need to be able to show the various amounts according to how overdue they are, for example if there is an invoice with a balance of 5 dollars that is past 30 days from the due date, I need the amount to show in the 30 day field, calculate the finance charges for those amounts and finally show the total balance due.

Any suggestions on how best to do this would be very much appreciated.

 

[Accessable]

This all should be done with coding. Are you familiar with coding? If so, what you need to do is use some conditional statements to analyze your data and set the values of your fields depending on the data.

I'll assume you're only showing data for one record at a time (no list boxes/combo boxes etc.) If this is the case, the code should go in the On_Current event for your Form.

For example you might do something like this (only a non-working example):


Private Sub Form_Current()
if me.TimeSinceBilled > 30 then
me.ThirtyDayField = me.txtMoneyDue
me.txtMoneyDue = ""
end if
End Sub

This is extremely incomplete (you probably should track the balance and periods with variables and such), but should give you an idea of how this should be handled.

Hope this helps. Let me know if you need more,

-Mark

 

[VK]

Thanks so much for your response, Mark. I will give your suggestion and try and see how it goes. I'm still pretty new to all of this, especially the coding side.

I'll be back with more questions, I'm sure. Thanks again.

 

[Accessable]

One other point: Lots of the standard things done in coding can also be done using macros. Macros can use conditional statements (if this is true then do this...) as well, and are easier to understand for people who don't code as much. You might try using macros to do what you want to do.

For example:
create a macro that will open a form
add a conditional statement to the macro (if myTextbox = "whatever" then open form)
in the on_click event of a button, select your newly created macro.

This would probably accomplish what you want. Just from a different angle. One last note: There is a 'Conditions' button on the toolbar on top of the create macro page. This is how you see the conditions. Also, you can use the 'Build' button to create a condition if this is foreign to you.

Good luck,
-Mark

 

[David R]

However, a word of warning: according to the literature I have, as of Access 2000 Macros are intended for backward compatability only. This means that in some future version, perhaps 2 or 3 down the road from Acc2002, perhaps less, they will no longer be available.

One excellent way to 'use' code without using code, and teach yourself at the same time, is to convert your macros to VBA. Look up the help file on 'convert macro to vba' to see how to do this. Then examine the code to see the similarities and differences between it and Macros.

Good luck,
David R

 

[VK]

Thank you so much for all your help. It is very much appreciated. I'm so new to all of this and finding help hasn't been easy.

Here's a new question....

I have a form with a subform for entering payments made on invoices. The invoices are referrenced first by customer id then by invoice number.

The subform shows the invoices by number, duedate, invtotal, chkno, pmtamt, datepd, baldue. It is already set up to figure the balance due when a payment is made. How can I carry over that balance to the same invoice number so that the next time the record is pulled up, it shows payment already made, new balance due, etc.? The invoice numbers are indexed with duplicates ok.

Hope I've explained this clearly enough. Thanks again for all the help.

 

[Rich]

How can you have duplicate invoice numbers , aren't they unique?

 

[VK]

Yes, they are. I'm still new to this stuff and I'm just trying to work this out. But I'm thinking that I'm not thinking this out right.

I was thinking that duplicates would have to be ok if an invoice wasn't paid in full so that additional payments could be added to it with each payment listed seperately, see what I mean? If the invoices are referenced by the customer id but still, I wouldn't want someone to put the same invoice number with 2 different customers.....I dunno.....guess I need to think this through some more, huh?

Thanks

 

[Rich]

You need a separate table to record payments against invoices. InvoiceID becomes the foreign key in the payments table.

Customers have a one to many relationship with invoices and invoices have a one to many relationship with payments

 

[VK]

Hi, thanks for all the help. I am a moron!!

I wasn't thinking about this the way I should have been. Only excuse I have is that I've been pretty sick for about two weeks....too much cold medicine. *sigh* I think I've got it figured out the 'right' way now. Thanks again for all the help.