If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Sprinks,
For some reason my code isn't performing this way, on my invoicepayments subform, my invoicetotal is still set up as : = Parent.Form!invoicetotal when I pull up my invoicepayment subform, my invoice total populates with my invoice total from my main form, here is what happens when I enter a payment: Payment# Invoicetotal PayDate Amount Balance Due ----------- ----------- ---------- ------------------ 1 $1000.00 3/1/05 $500.00 $500.00 Payment# Invoicetotal PayDate Amount Balance Due ----------- ----------- ---------- ------------------ 1 $1000.00 3/1/05 $500.00 $300.00 2 $1000.00 4/1/05 $200.00 $300.00 Payment# Invoicetotal PayDate Amount Balance Due ----------- ----------- ---------- ------------------ 1 $1000.00 3/1/05 $500.00 $0.00 2 $1000.00 4/1/05 $200.00 $0.00 3 $1000.00 5/1/05 $300.00 $ 0.00 Here is the code, just like you suggested: Private Function MyNewBalance() As Currency Dim curInvoiceTotal As Currency curInvoiceTotal = Me.Parent.Form![invoicetotal] If Nz([paymentdate]) = 0 Then 'Set to zero to avoid triggering an error in the Dsum call MyNewBalance = 0 Else MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]", "tblinvoicePayments", _ "[PaymentDate] = #" & [paymentdate] & "#") End If End Function Any ideas?? Thanks, Brook "Sprinks" wrote: Brook, I don't understand your question. The code is designed to do precisely what you describe; display the balance due as of a given payment: Example of InvoicePaymentDetails Subform (assuming an InvoiceTotal of $1000): Payment# PayDate Amount Balance Due ----------- ----------- ---------- ------------------ 1 3/1/05 $500.00 $500.00 2 4/1/05 $200.00 $300.00 3 5/1/05 $300.00 $ 0.00 Sprinks "Brook" wrote: Sprinks.... I wanted to let you know that I added the updated code, and I am sorry for not catching the miss spellings on my coding... I corrected the code as you suggested and it is working great, I just have one more question, is it possible to keep my previous balance before I add a new payment? So if the BalanceDue was $500.00 and I add a payment of $50.00, the new balance is $450.00, but then if I add a new payment of $50.00, the newbalancedue is $400.00. So therefore it would act as a running balance after the payments are made... thank you so much...... for all your help... check out my website and you will see the products that I design, import and sell... www dot karmaimports dot net Brook "Sprinks" wrote: You're not troublesome; it's my pleasure. Before we get into the code, which had several errors, I have several comments regarding your tables. - You do not need or want a invoicetotal *field* or a balance due *field* in tblinvoicepayments for the reasons I mentioned earlier about storing calculated fields. These calculated fields are merely displayed in form *controls*, and may be recalculated for a report in a query. - Each table's fields should describe an attribute of the "thing" the table represents. Invoicetotal, Balance Due, InvoiceDate, and InvoiceNumber have nothing to do with a payment, they are either calculated fields or attributes of an Invoice. The only field you need in tblInvoicePayments to relate it to tblInvoices is the latter's primary key (called a foreign key in tblInvoicePayments). This field must be the same type. Assuming that invoiceid is the primary key--invoiceid in tblinvoices and tblinvoicepayments do not match in type; it is a Long Integer in the former and Text in the latter. This general subject is called table normalization--I encourage you to read some about from a good reference. It's very common for new users to duplicate fields this way, and it makes it much more difficult to use the database and program form code when the tables are not normalized. Also note the difference, alluded to earlier, between a FIELD, which has a data type, and is where data is STORED in a table, and a CONTROL on a form or report, which has no datatype, and is merely a container to display a field, static text, calculation, function result, etc. - I don't understand why you have invoiceid, invoicenum, and invoicenumber fields in tblinvoices. They presumably do the same thing. - Company, BillingAddr1, and the remainder of the company fields do not belong in the tblInvoices table; they are attributes of Companys. Analogously to the example above, all you need is a foreign key corresponding to Company's primary key, e.g., CustomerNumber. The common way to do this is with a combo box that permits selection by company name, but *stores* the customer number in the underlying field. - Normally in an invoice details table, there would be a unit price and a quantity, and the extended price would merely be a calculated control on the form. I don't see a Qty field. I strongly urge you to read up on table normalization, and normalize your tables before attempting to go further. In the code, the second and third lines refer to "inviocetotal". this should be "invoicetotal", as it is correctly spelled in the MyNewBalance assignment statement later in the function. Also the table where you are looking up Payment Amount is in the tblinvoicepayments table, not tblInvoices. Some minor comments: using a prefix to identify the type of all variables is very valuable in debugging your code. Bracket delimiters around objects (table and field, for example) are similarly helpful. Also, since the If statement checks for the null condition, the Nz call can be removed from the Dsum call in the Else statement. Making these changes, the resulting code is: Private Function MyNewBalance() As Currency Dim curInvoiceTotal As Currency curInvoiceTotal = Me.Parent.Form![invoicetotal] If Nz([paymentdate]) = 0 Then 'Set to zero to avoid triggering an error in the Dsum call MyNewBalance = 0 Else MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]", "tblinvoicePayments", _ "[PaymentDate] = #" & [paymentdate] & "#") End If End Function In order to update after you've entered the payment amount, place: Me.Recalc in its AfterUpdate event procedure. Hope that will resolve it; but if not, don't hesitate to ask. Sprinks "Brook" wrote: I'm sorry that I am so troublesome with this, but thanks for all your help.. one question that I had is that previously we had passed the invoicetotal to the subform invoicepayments, should I change that back to invoicetotal or keep it as (=Parent.Forms!invoicetotal? the code is working when I enter 1 payment, then exit the form then come back, the balance has changed. however, when I add a new payment, the code doesn't deduct the new payment from the previous balance... Here is what I changed the code to based on my tbl & frm values: Private Function MyNewBalance() As Currency Dim inviocetotal As Currency inviocetotal = Me.Parent.Form!invoicetotal If Nz([paymentdate]) = 0 Then ' Set to zero to avoid if no date has yet been entered to avoid triggering ' an error in the Dsum call MyNewBalance = 0 Else MyNewBalance = invoicetotal - DSum("[PaymentAmount]", "tblinvoices", _ "[PaymentDate] = #" & Nz([paymentdate]) & "#") End If End Function Here are my 3 tables and field names: 3 tables: tblinvoices: Fields & Types invoiceid Long Integer invoicetype Text invoicenum Long Integer invoicenumber Text invoicedate Date/Time Company Text Contact Text BillingAddr1 Text BillingAddr2 Text BillingAddr3 Text BillingCity Text BillingState Text BillingZip Text Phone Text Fax Text ShippingAddr1 Text ShippingAddr2 Text ShippingAddr3 Text ShippingCity Text ShippingState Text ShippingZip Text ShippingCompany Text tblinvoicedetails: Fields invoiceid Long Integer orderid Long Integer serialnumber Text DesignNumber Text DesignName Text Quality Text Size Text SqFt Text PricePerSqFoot Currency TotalPrice Currency shippingcost Currency tblinvoicepayments: Fields: invoiceid Text invoicedate Date/Time invoicenumber Text invoicetotal Currency paymentnumber Long Integer paymentdate Date/Time paymentamount Currency Balance Due Currency |
#22
|
|||
|
|||
Sprinks,
I figured it out... I had my date field set up for default =Date(), so therefore it negated the date function set up in the code... Thank you so much for helping me get this figured out!!! Brook "Sprinks" wrote: Brook, I don't understand your question. The code is designed to do precisely what you describe; display the balance due as of a given payment: Example of InvoicePaymentDetails Subform (assuming an InvoiceTotal of $1000): Payment# PayDate Amount Balance Due ----------- ----------- ---------- ------------------ 1 3/1/05 $500.00 $500.00 2 4/1/05 $200.00 $300.00 3 5/1/05 $300.00 $ 0.00 Sprinks "Brook" wrote: Sprinks.... I wanted to let you know that I added the updated code, and I am sorry for not catching the miss spellings on my coding... I corrected the code as you suggested and it is working great, I just have one more question, is it possible to keep my previous balance before I add a new payment? So if the BalanceDue was $500.00 and I add a payment of $50.00, the new balance is $450.00, but then if I add a new payment of $50.00, the newbalancedue is $400.00. So therefore it would act as a running balance after the payments are made... thank you so much...... for all your help... check out my website and you will see the products that I design, import and sell... www dot karmaimports dot net Brook "Sprinks" wrote: You're not troublesome; it's my pleasure. Before we get into the code, which had several errors, I have several comments regarding your tables. - You do not need or want a invoicetotal *field* or a balance due *field* in tblinvoicepayments for the reasons I mentioned earlier about storing calculated fields. These calculated fields are merely displayed in form *controls*, and may be recalculated for a report in a query. - Each table's fields should describe an attribute of the "thing" the table represents. Invoicetotal, Balance Due, InvoiceDate, and InvoiceNumber have nothing to do with a payment, they are either calculated fields or attributes of an Invoice. The only field you need in tblInvoicePayments to relate it to tblInvoices is the latter's primary key (called a foreign key in tblInvoicePayments). This field must be the same type. Assuming that invoiceid is the primary key--invoiceid in tblinvoices and tblinvoicepayments do not match in type; it is a Long Integer in the former and Text in the latter. This general subject is called table normalization--I encourage you to read some about from a good reference. It's very common for new users to duplicate fields this way, and it makes it much more difficult to use the database and program form code when the tables are not normalized. Also note the difference, alluded to earlier, between a FIELD, which has a data type, and is where data is STORED in a table, and a CONTROL on a form or report, which has no datatype, and is merely a container to display a field, static text, calculation, function result, etc. - I don't understand why you have invoiceid, invoicenum, and invoicenumber fields in tblinvoices. They presumably do the same thing. - Company, BillingAddr1, and the remainder of the company fields do not belong in the tblInvoices table; they are attributes of Companys. Analogously to the example above, all you need is a foreign key corresponding to Company's primary key, e.g., CustomerNumber. The common way to do this is with a combo box that permits selection by company name, but *stores* the customer number in the underlying field. - Normally in an invoice details table, there would be a unit price and a quantity, and the extended price would merely be a calculated control on the form. I don't see a Qty field. I strongly urge you to read up on table normalization, and normalize your tables before attempting to go further. In the code, the second and third lines refer to "inviocetotal". this should be "invoicetotal", as it is correctly spelled in the MyNewBalance assignment statement later in the function. Also the table where you are looking up Payment Amount is in the tblinvoicepayments table, not tblInvoices. Some minor comments: using a prefix to identify the type of all variables is very valuable in debugging your code. Bracket delimiters around objects (table and field, for example) are similarly helpful. Also, since the If statement checks for the null condition, the Nz call can be removed from the Dsum call in the Else statement. Making these changes, the resulting code is: Private Function MyNewBalance() As Currency Dim curInvoiceTotal As Currency curInvoiceTotal = Me.Parent.Form![invoicetotal] If Nz([paymentdate]) = 0 Then 'Set to zero to avoid triggering an error in the Dsum call MyNewBalance = 0 Else MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]", "tblinvoicePayments", _ "[PaymentDate] = #" & [paymentdate] & "#") End If End Function In order to update after you've entered the payment amount, place: Me.Recalc in its AfterUpdate event procedure. Hope that will resolve it; but if not, don't hesitate to ask. Sprinks "Brook" wrote: I'm sorry that I am so troublesome with this, but thanks for all your help.. one question that I had is that previously we had passed the invoicetotal to the subform invoicepayments, should I change that back to invoicetotal or keep it as (=Parent.Forms!invoicetotal? the code is working when I enter 1 payment, then exit the form then come back, the balance has changed. however, when I add a new payment, the code doesn't deduct the new payment from the previous balance... Here is what I changed the code to based on my tbl & frm values: Private Function MyNewBalance() As Currency Dim inviocetotal As Currency inviocetotal = Me.Parent.Form!invoicetotal If Nz([paymentdate]) = 0 Then ' Set to zero to avoid if no date has yet been entered to avoid triggering ' an error in the Dsum call MyNewBalance = 0 Else MyNewBalance = invoicetotal - DSum("[PaymentAmount]", "tblinvoices", _ "[PaymentDate] = #" & Nz([paymentdate]) & "#") End If End Function Here are my 3 tables and field names: 3 tables: tblinvoices: Fields & Types invoiceid Long Integer invoicetype Text invoicenum Long Integer invoicenumber Text invoicedate Date/Time Company Text Contact Text BillingAddr1 Text BillingAddr2 Text BillingAddr3 Text BillingCity Text BillingState Text BillingZip Text Phone Text Fax Text ShippingAddr1 Text ShippingAddr2 Text ShippingAddr3 Text ShippingCity Text ShippingState Text ShippingZip Text ShippingCompany Text tblinvoicedetails: Fields invoiceid Long Integer orderid Long Integer serialnumber Text DesignNumber Text DesignName Text Quality Text Size Text SqFt Text PricePerSqFoot Currency TotalPrice Currency shippingcost Currency tblinvoicepayments: Fields: invoiceid Text invoicedate Date/Time invoicenumber Text invoicetotal Currency paymentnumber Long Integer paymentdate Date/Time paymentamount Currency Balance Due Currency |
#23
|
|||
|
|||
Glad it's working. Good luck.
Sprinks "Brook" wrote: Sprinks, I figured it out... I had my date field set up for default =Date(), so therefore it negated the date function set up in the code... Thank you so much for helping me get this figured out!!! Brook "Sprinks" wrote: Brook, I don't understand your question. The code is designed to do precisely what you describe; display the balance due as of a given payment: Example of InvoicePaymentDetails Subform (assuming an InvoiceTotal of $1000): Payment# PayDate Amount Balance Due ----------- ----------- ---------- ------------------ 1 3/1/05 $500.00 $500.00 2 4/1/05 $200.00 $300.00 3 5/1/05 $300.00 $ 0.00 Sprinks "Brook" wrote: Sprinks.... I wanted to let you know that I added the updated code, and I am sorry for not catching the miss spellings on my coding... I corrected the code as you suggested and it is working great, I just have one more question, is it possible to keep my previous balance before I add a new payment? So if the BalanceDue was $500.00 and I add a payment of $50.00, the new balance is $450.00, but then if I add a new payment of $50.00, the newbalancedue is $400.00. So therefore it would act as a running balance after the payments are made... thank you so much...... for all your help... check out my website and you will see the products that I design, import and sell... www dot karmaimports dot net Brook "Sprinks" wrote: You're not troublesome; it's my pleasure. Before we get into the code, which had several errors, I have several comments regarding your tables. - You do not need or want a invoicetotal *field* or a balance due *field* in tblinvoicepayments for the reasons I mentioned earlier about storing calculated fields. These calculated fields are merely displayed in form *controls*, and may be recalculated for a report in a query. - Each table's fields should describe an attribute of the "thing" the table represents. Invoicetotal, Balance Due, InvoiceDate, and InvoiceNumber have nothing to do with a payment, they are either calculated fields or attributes of an Invoice. The only field you need in tblInvoicePayments to relate it to tblInvoices is the latter's primary key (called a foreign key in tblInvoicePayments). This field must be the same type. Assuming that invoiceid is the primary key--invoiceid in tblinvoices and tblinvoicepayments do not match in type; it is a Long Integer in the former and Text in the latter. This general subject is called table normalization--I encourage you to read some about from a good reference. It's very common for new users to duplicate fields this way, and it makes it much more difficult to use the database and program form code when the tables are not normalized. Also note the difference, alluded to earlier, between a FIELD, which has a data type, and is where data is STORED in a table, and a CONTROL on a form or report, which has no datatype, and is merely a container to display a field, static text, calculation, function result, etc. - I don't understand why you have invoiceid, invoicenum, and invoicenumber fields in tblinvoices. They presumably do the same thing. - Company, BillingAddr1, and the remainder of the company fields do not belong in the tblInvoices table; they are attributes of Companys. Analogously to the example above, all you need is a foreign key corresponding to Company's primary key, e.g., CustomerNumber. The common way to do this is with a combo box that permits selection by company name, but *stores* the customer number in the underlying field. - Normally in an invoice details table, there would be a unit price and a quantity, and the extended price would merely be a calculated control on the form. I don't see a Qty field. I strongly urge you to read up on table normalization, and normalize your tables before attempting to go further. In the code, the second and third lines refer to "inviocetotal". this should be "invoicetotal", as it is correctly spelled in the MyNewBalance assignment statement later in the function. Also the table where you are looking up Payment Amount is in the tblinvoicepayments table, not tblInvoices. Some minor comments: using a prefix to identify the type of all variables is very valuable in debugging your code. Bracket delimiters around objects (table and field, for example) are similarly helpful. Also, since the If statement checks for the null condition, the Nz call can be removed from the Dsum call in the Else statement. Making these changes, the resulting code is: Private Function MyNewBalance() As Currency Dim curInvoiceTotal As Currency curInvoiceTotal = Me.Parent.Form![invoicetotal] If Nz([paymentdate]) = 0 Then 'Set to zero to avoid triggering an error in the Dsum call MyNewBalance = 0 Else MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]", "tblinvoicePayments", _ "[PaymentDate] = #" & [paymentdate] & "#") End If End Function In order to update after you've entered the payment amount, place: Me.Recalc in its AfterUpdate event procedure. Hope that will resolve it; but if not, don't hesitate to ask. Sprinks "Brook" wrote: I'm sorry that I am so troublesome with this, but thanks for all your help.. one question that I had is that previously we had passed the invoicetotal to the subform invoicepayments, should I change that back to invoicetotal or keep it as (=Parent.Forms!invoicetotal? the code is working when I enter 1 payment, then exit the form then come back, the balance has changed. however, when I add a new payment, the code doesn't deduct the new payment from the previous balance... Here is what I changed the code to based on my tbl & frm values: Private Function MyNewBalance() As Currency Dim inviocetotal As Currency inviocetotal = Me.Parent.Form!invoicetotal If Nz([paymentdate]) = 0 Then ' Set to zero to avoid if no date has yet been entered to avoid triggering ' an error in the Dsum call MyNewBalance = 0 Else MyNewBalance = invoicetotal - DSum("[PaymentAmount]", "tblinvoices", _ "[PaymentDate] = #" & Nz([paymentdate]) & "#") End If End Function Here are my 3 tables and field names: 3 tables: tblinvoices: Fields & Types invoiceid Long Integer invoicetype Text invoicenum Long Integer invoicenumber Text invoicedate Date/Time Company Text Contact Text BillingAddr1 Text BillingAddr2 Text BillingAddr3 Text BillingCity Text BillingState Text BillingZip Text Phone Text Fax Text ShippingAddr1 Text ShippingAddr2 Text ShippingAddr3 Text ShippingCity Text ShippingState Text ShippingZip Text ShippingCompany Text tblinvoicedetails: Fields invoiceid Long Integer orderid Long Integer serialnumber Text DesignNumber Text DesignName Text Quality Text Size Text SqFt Text PricePerSqFoot Currency TotalPrice Currency shippingcost Currency tblinvoicepayments: Fields: invoiceid Text invoicedate Date/Time invoicenumber Text invoicetotal Currency paymentnumber Long Integer paymentdate Date/Time paymentamount Currency Balance Due Currency |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
Calculated date field in form to table | sendahook | Using Forms | 6 | March 31st, 2005 02:38 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
copying value from subform field to main form field | VNoelDavis | Using Forms | 0 | January 1st, 2005 09:45 PM |
dlookup | miaplacidus | Using Forms | 9 | August 5th, 2004 09:16 PM |