473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Calculation of invoice Fees

77 64KB
Hi twinnyfo, I used the method which you described in post 36 to setup my forms with the FrmCountries as the main form, and the Regions and Minesites subforms embedded in the main form. The FrmCountries and Regions subform are in single view while the Minesites form is in continuous view. I am having a problem where the FrmCountries form. For instance, Country A has 5 minesites in Region A1, one minesite in Region A2 and one minesite in Region A3. The FrmCountries form will show Country A has 5 minesites in Region A only and if I click on the record indicator button to advance to the next record, FrmCountry form will go to the next Country and it will show that Country B has one minesite in Region B. So the FrmCountries form is not showing the records where Country A has 1 minesite in Region A2 and Country A has a minesite in Region A3. How can I fix this problem?

In regard to the setup of this main form and subforms, I had placed fields in the Minesites subform in the "Details area" in the Design view.

For the Regions Subform, I had added some labels in the Form Header and I called them "Country", "Region Name" and "Region Memo" in the Design view. I then placed the associated fields for these labels in the Details area. I then embedded the Minesites subform in the Form footer.

For the Country main form, I added labels for the "Country Name" and "Country Comments" in the "Details Area" in the Design view. When I switch to the Form view, I did not see the labels and so I moved them to the Form Footer area. When I switched to the Form view, I can see the labels. Afterwards, I embedded the Regions subform, underneath the labels, in the Form Footer. Is this approach correct?

[z{This thread was split from: Duplicate records created using two subforms in a main form }]
Jul 16 '14 #1

✓ answered by twinnyfo

wirejp,

Good to see you again. How are you calculating your admin fees? On the form? This will determine, ultimately, how you are to write the code.

Assuming AdminFeeInitial is a Yes/No field, on a form, then,

Expand|Select|Wrap|Line Numbers
  1. Me.txtAdminFee = _
  2.     Format( _
  3.         IIf(Me.chkAdminFeeInitial, _
  4.             Me.txtInitialPremium * 0.015, _
  5.             0 )
  6.             ), _
  7.         "Currency" _
  8.         )
Remember, that the calculated admin fee may have additional decimal places of accuracy. If you save the value (or charge it), you will only want to save it as the two decimal place calculated value (in which case, you must round your values to the desired decimal place).

Hope this helps...

41 1969
wirejp
77 64KB
hi twinnyfo, the issue described in post 39 has been corrected, as I deleted and re-embedded the subforms into the main form again. This time, I pulled Region subform sheet further down the page in the Country main form and the Regions Subform's recorder indicator buttons,at the bottom of the subform, were visible. So I am now able to scroll thru and view the records for all of the Country A's minesites in the different regions.

I will wait for your help with the premium spreadsheet calculations, when you have a chance.
Jul 16 '14 #2
twinnyfo
3,653 Expert Mod 2GB
wirejp,

Good catch in post #39 about the record selectors. That was going to be my recommendation. As a side note concerning this, I typically build my embedded foms from the deepest subform first, up to the main form. The purpose is to get the form design and looking exactly how I want it, then embed it in another form.

For your minesites subform, this is easy, as it is a continuous form, and just needs to show the records sequentially (it doesn't matter if it is currently showing all records). If I do create a header for the field labels in my embedded continuous subforms, what I will often do is copy those same labels and put them into the parent form, making sure they line up properly with the fields on the subform (view the image on post #36 for an example). Then, I delete the entire Form header, so that only the detail exists (the header is really unneceesary, other than appearance when viewed by itself). This is not a requirement, but I have found it to be a bit more streamlined of an approach.

Additonally, except for my continuous forms, I typically build my own navigation buttons. MS Access's navigation buttons work fine and also include a useful search field. If you need the search feature you can use it--but I find the nav buttons to be too small for my tastes--but this is preference only. There are times when you may want to add these navigation buttons. There is also some simple code that will disable and enable the nav buttons based on which record you are at:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error GoTo EH:
  3.     Dim rstClone As Recordset
  4.     Set rstClone = Me.RecordsetClone
  5.     If Me.NewRecord Then
  6.         Me.cmdFirst.Enabled = True
  7.         Me.cmdPrevious.Enabled = True
  8.         Me.cmdNext.Enabled = False
  9.         Me.cmdNew.Enabled = True
  10.         Exit Sub
  11.     End If
  12.     Me.cmdNew.Enabled = Me.AllowAdditions
  13.     If rstClone.RecordCount = 0 Then
  14.         Me.cmdFirst.Enabled = False
  15.         Me.cmdPrevious.Enabled = False
  16.         Me.cmdNext.Enabled = False
  17.         Me.cmdLast.Enabled = False
  18.     Else
  19.         Me.cmdFirst.Enabled = True
  20.         Me.cmdLast.Enabled = True
  21.         rstClone.Bookmark = Me.Bookmark
  22.         rstClone.MovePrevious
  23.         Me.cmdPrevious.Enabled = Not (rstClone.BOF)
  24.         rstClone.MoveNext
  25.         rstClone.MoveNext
  26.         Me.cmdNext.Enabled = Not (rstClone.EOF)
  27.         rstClone.MovePrevious
  28.     End If
  29.     rstClone.Close
  30.     Exit Sub
  31. EH:
  32.     MsgBox "There was an error moving to the current record.  " & _
  33.         "Please contact your Database Administrator.", vbExclamation, "Error!"
  34.     Exit Sub
  35. End Sub
The only assumption is having the command buttons listed. I've never had a problem with it. This code will also disable the "New" button if your form does not allow that function.

Let me take a look again at the premium spreadsheet. But I have a quick question, first. When viewing left to right, it appears the premiums and fees are duplicated: once for the first $25M and then again for the excess. The fees (at least the percentages) look similar. Do the brokers merely provide for you the premium for the first $25M and the excess amount and then the taxes and other fees are calculated based on thsoe values? If so, this could be very possible....
Jul 17 '14 #3
wirejp
77 64KB
In post 41,
But I have a quick question, first. When viewing left to right, it appears the premiums and fees are duplicated: once for the first $25M and then again for the excess. The fees (at least the percentages) look similar. Do the brokers merely provide for you the premium for the first $25M and the excess amount and then the taxes and other fees are calculated based on thsoe values? If so, this could be very possible....
, on looking from left to right, the premiums and fees are different amounts (I should have used different amounts in my spreadsheet to illustrate this fact). The answer to your second question is "yes" that the brokers merely provide us with the premium for the first $25M and the excess amount and then the taxes and other fees are calculated based on these values.

As you explained in post 41, I added the navigational buttons to the Regions subform, along with the code which you provided. However, I received a Compile error dialog box stating that "Method or data member is not found" and the "Me.cmdFirst" in line 6 of the code was highlighted in blue, and on clicking on the Compile error dialog box, the code in the first line "Private Sub Form_Current()" was highlighted in yellow.
Jul 17 '14 #4
twinnyfo
3,653 Expert Mod 2GB
Take a look at the spreadsheet I attached. I stripped it of anything that could be anything identifiable, so I left even the Minesites field empty. I have some questions and clarifications to see if I understand how the fee structure works.

1. I just noticed that your Spreadsheet was olso broken out by Geographical Areas, with multiple countries included. Guess what, probably another Table for Areas.... :-/

2. If you look at the "New Table Values" sheet, Column J, there are five cells that are highlighted. On your original spreadsheet (that was posted and removed) in the Properties sheet, these five regions in the initial Premium, had no Admin fee. Since every other entry had the standard 1.5%, was this an oversight or do these five regions not have an admin fee? If htis is an oversight, the Admin fee becomes a very simple calculation.

3. The Local Retention Fee (when used) appears to change depending on whether it is the initial premium or the excess premium--just checking to make sure this is true.

4. Fronting Fees vs. Advancing Fees. It appears, that each minesite pays either a Fronting fee of 5% or an Advancing Fee of 2.5% but never both. If this is always the case, then this makes our table easier to manage, as well. Does this value always stay the same, that is, does hte same minesite always either pay one or the other, but never both?

5. Columns T, X, Y and Z (and S & W). Based on your spreadsheet, the Terrorism levy is based on the Premium + the Taxes levied: (Premium + Taxes) * Terrorism%. I just want to make sure that the Local Retention, Fronting and Advancing Fees are based off the Premium ONLY, and dshould not include the taxes levied. Of course, this will affect the overall invoice.

6. Not sure if this is an error or intentional, but on your original spreadsheet, Property WorkSheet, for your African Regions in Tanzania, the Fronting and Admin Fees are based off the Total Invoiced Premium, rather than the Excess Premium listed in Column Z (which differs in these cases). All others have these fields calculated from the Premium column.

Based on the clarifications on these questions, the "New Table Structure" worksheet shows the potential for what your Table may look like.

As you can see, there would only be as few as five fields that would be updated when calculating the Total invoice. The first four fields would be foreign keys to other tables, which, typically would not change unless you added or deleted a minesite. The Brokers provide the Initial and Excess Premiums, and the taxes and Local Retention LEvies are modified as necessary.

Some notes on this structure:

A. If the Admin Fees and Terrorism Levies are always 1.5% and 2.04%, respectively, which it appears they are, then these values can be set globally. This allows you to change the fees in one place, and all invoices would be corrected. Then, in your table, you just need to indicate if the terrorism levy applies, and the Admin fee field is unnecessary.

B. Likewise for the Fronting/Advancing Fees. This could merely be a Yes/No field which indicates whether there is a Fronting Fee (Yes). If the field is No, this means an Advancing Fee. Again, based on the answers to above.

Thus, your Table will have those fields shown in Black, and most likely those in Blue, but probably not those in Red.

I hope all this makes sense, and gets you moving down the road.
Attached Files
File Type: xlsx Premiums Table.xlsx (22.7 KB, 314 views)
Jul 17 '14 #5
twinnyfo
3,653 Expert Mod 2GB
In response to post #42, by "duplicated" what I meant was that the fee schedule was the same. The only difference I saw between the initial and excess premiums what the Fronting/Advancing Fees, which only appeared in the Excess side of the schedule, correct?
Jul 17 '14 #6
wirejp
77 64KB
In response to post 44, "yes", that is correct.
Jul 17 '14 #7
wirejp
77 64KB
Thank you for creating these spreadsheet tables.

In response to post 43, my answers are:-

1. Yes, the regions are divided into geographical areas. As you have indicated, a new table for Areas is required

2. Those five regions do not have an admin fee.

3. Yes, local retention fee changes depending on if it is the initial primary or if it is the excess premium

4. It depends on the policy type. For instance, the marine policy uses both the 2.5% and the 5% in the calculation (the spreadsheet was posted and deleted was the property and both rates were not used in this example). When both rates are used, the calculation will be fronting fee=( premium amount*2.5%)+(premium amount * 5%).

5.Local retention, fronting and admin fees are based on the Premium only and they do not include the taxes levied.

6. The calculations are correct for the African Regions in Tanzania, the Fronting and Admin Fees are based off the Total Invoiced Premium, rather than the Excess Premium listed in Column Z (which differs in these cases).

Comments to the notes to the Structure:-

A. The terrorism levy fee can vary depending on the policy type. It is 2.00% for the business interruption policy type.
The admin fee is always 1.5%.

B. The Fronting and Advancing fees be either 2.5%, 5% or both of them can be used in the calculation.

Can I update the New Table Values spreadsheet with two columns for the insurance coverage period denoted by Effective Date and Expiry Date (i.e. July 2014 - July 2015)?

Should I populate the spreadsheet template with the data and import it into my database?


Looking ahead, how do I create the premium and admin invoice forms? Do I use the client form as the main form. If so, how do I link the client's contacts table to this form and the insurer's table to this form? Should I create a premium and admin queries to create subforms and then embed them into the client main form?
Jul 17 '14 #8
twinnyfo
3,653 Expert Mod 2GB
Wow, too much going on!

So, based on your discussion, do you think you can update the requirements for the Table Structure worksheet? Let's hold off importing things until we get everything right, first....

2. If the Admin fee does not always apply, but the admin fee is a standard rate, then we need two fields for admin fee, both as Yes/No: AdminFeeInitial and AdminFeeExcess. This will simply indicate whether or not we apply the admin fee to the invoice.

4. The Fronting and Advancing fields should then both be Yes/No fields, indicating whether this policy charges the 5% fronting Fee or the 2.5% Advancing Fee or both.

6. This may be a challenge to get this right at run time. We can certainly bulid logic into your invoice builder that will calculate this. My question, though, is where do we get that calculated premium from, as the values on the original spreadsheet were just values....?

A. If the Terrorism levy is a standrad rate based on policy type, then I stillrecommend setting that value globally.

I will ask you to throw those changes into the spreadsheet so I can take a look at it.

What do the Effective and expiry dates have to do with the spreadsheet, or is this a fee schedule for that period, and those rates may change? It is actually a good idea, so that you have a standard fee schedule, and then update the fee schedule for the next year.

The premium and invoice forms must wait until we get all the innards working here first.

One thing for you to think about in the long term (but in the fore front of that long-term) is a way to keep a "ledger" of charges and payments. We will be able to generate an invoice of how much someone must pay, but we also have to keep track of what you have charged and what has been paid. I am not a banker, but my thought is you will want to be able to look up your clients' account and see exactly what you have charged over time and exactly how much they have paid and what the oustanding balance is.

All of this is going to be a lot of work. This cannot be rushed, even though I know you want to get it done ASAP.
Jul 17 '14 #9
wirejp
77 64KB
To answer your question 6, in post 47, yes the premium amounts in the original spreadsheet were just values. I will check with the insurance broker and see if he can provide me with some feedback.

I have updated the Table Structure worksheet with the new requirements.
Attached Files
File Type: xlsx PremiumsTable.xlsx (23.1 KB, 325 views)
Jul 18 '14 #10
wirejp
77 64KB
hi twinnyfo, I just looked over the calculations in the New Table Values worksheet, and I saw some calculation errors in columns AD and AE, as the data were referencing the wrong cells or using incorrect formula. Kindly see the attached updated PremiumsTable spreadsheet.

I have also included the Effective Date nad Expiry Date in both worksheets.
Attached Files
File Type: xlsx PremiumsTable.xlsx (24.0 KB, 333 views)
Jul 18 '14 #11
wirejp
77 64KB
The insurance broker indicated that the premiums are calculated by applying the agreed insurance markets rates against some form of premium base value, and a forecast of expected mine shipments. The adjustments to the premium calculation are made at year end for the time period on risk.
Jul 18 '14 #12
twinnyfo
3,653 Expert Mod 2GB
Your Post #49 just completely confused me more than anything. Don't the invoices include any of the fees? Your original spreadsheet indicated these were all rolled up into the invoice.

Please explain.
Jul 20 '14 #13
wirejp
77 64KB
Hi twinnyfo, I performed a proper review of the premium spreadsheet and I made some changes (the cells are highlighted). In the worksheet "New Tables Values", I have added "column S", "column AG", "column AI".
Attached Files
File Type: xlsx PremiumsTable.xlsx (26.1 KB, 259 views)
Jul 21 '14 #14
wirejp
77 64KB
hi twinnyfo, In response to your post 51,the answer is "yes". There are two types of invoices:- premium invoice and a fronting & admin fee invoice. The premium invoice include the Premium amount and the "Taxes" amount, while the fronting & admin fee invoice include the admin fees, and fronting/advance fees.
Jul 21 '14 #15
wirejp
77 64KB
I created a Fee Schedule form and I then switched to design view and click on the VBA code option to attempt to add some code to generate a premium invoice total calculation in Initial Premium Invoice field box.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Initial_Premium_Invoice_Click()
  2. Dim InitialPremium As Currency
  3. Dim InitialPremiumInvoice As Currency
  4. Dim Taxes As Double
  5. InitialPremium = CDbl(Me.txtInitialPremium)
  6. InitialPremiumInvoice = CDbl(Me.txtInitialPremiumInvoice)
  7. InitialPremiumInvoice = Nz(InitialPremium + ((InitialPremium) * (Taxes)) / (1 - (Taxes)))
  8. End Sub
When I switch back to the Form view, I do not see a result in the Initial Premium Invoice field box? Can you help me with this problem.

Note, I have been reading up on VBA coding and this is just a start for me. I suspect that since one of the countries has a different method of calculating their invoice premiums and "If then" condition may be required in this process
Aug 18 '14 #16
twinnyfo
3,653 Expert Mod 2GB
wirejp,

I guess I have forgotten about you these past few weeks... My apologies for not getting back to you!

The reason you saw no activity on your form is that you have not set the text box to the value you have calculated. Also, your code can be slimmed down as follows (but you need to make sure you have a text box for the taxes, first):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Initial_Premium_Invoice_Click() 
  2.     Me.txtInitialPremiumInvoice = Me.txtInitialPremium + _
  3.         (Me.txtInitialPremiumInvoice * _
  4.             (Me.txtTaxes / (1 - Me.txtTaxes)))
  5. End Sub
But, you are getting the hang of it.
Aug 18 '14 #17
wirejp
77 64KB
Hi twinnyfo, thank you for responding and your help is always appreciated. I inputted your code and I ensured that the captions for the text boxes for the Taxes, Initial Premiums and Initial Premium Invoices had the "txt" prefix before their name. However, there is still no calculation result in the Initial Premium Invoice field box.
Aug 18 '14 #18
twinnyfo
3,653 Expert Mod 2GB
Do you have a command button named "Initial_Premium_Invoice".

This code will only execute when you have a control with that name and after you click the control.

I'm not sure how you have your form set up.

Also, in your Post #54, yes, you will have to have some sort of If...Then or Select Case statmenet to handle special calculations.

But, let's get your initial code working first, then build off that. Always learn to crawl first, then walk, then run!
Aug 18 '14 #19
wirejp
77 64KB
No, I do not have a command button named "Initial_Premium_Invoice", only a text box named "Initial_Premium_Invoice". Is there a way to populate the Initial Premium invoice field box with a result, without having to click on the command button?

.
Aug 18 '14 #20
twinnyfo
3,653 Expert Mod 2GB
Access is "event driven", meaning that it won't do anything unless there is an event that happens. Based on the type of event, Access will do different things. So if you have a text box, one of the events that could happen is that it is updated (a new value is entered into that text box).

Since you already have a text box called txtInitialPremiumInvoice, just add some VBA to the After Update Property: click on the text box in design view, in the properties list, in the events tab, click on the down arrow in the "After Update" and choose "Event Procedure". Then click the "..."


Add the code I listed for post #55:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtInitialPremiumInvoice_AfterUpdate()  
  2.     Me.txtInitialPremiumInvoice = Me.txtInitialPremium + _ 
  3.         (Me.txtInitialPremium * _ 
  4.             (Me.txtTaxes / (1 - Me.txtTaxes))) 
  5. End Sub
This should update your other text box.
Aug 18 '14 #21
wirejp
77 64KB
hi twinnyfo,

I followed the steps but the event is still not producing a result. I have attached screenshots of design view and the vba code for your review and advice.
Attached Files
File Type: docx after event procedure not firing.docx (319.5 KB, 279 views)
Aug 19 '14 #22
twinnyfo
3,653 Expert Mod 2GB
My mistake. Remember, we want to update the INVOICE after we update the PREMIUM.....

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtInitialPremium_AfterUpdate()   
  2.     Me.txtInitialPremiumInvoice = Me.txtInitialPremium + _  
  3.         (Me.txtInitialPremium * _  
  4.             (Me.txtTaxes / (1 - Me.txtTaxes)))  
  5. End Sub
This should be in the After update property of txtInitialPremium. I typed the wrong code initially.

Just something I noticed on your form. The text boxes should be named txtInitialPremium and txtInitialPremiumInvoice; the field names should be InitialPremium and InitialPremiumInvoice. It is not "wrong," per se, to use the naming convention you have chosen--however, I find it helpful to always intentionally disambiguate the actual field name from the text box which uses that field.

The code above should help out this form.
Aug 19 '14 #23
wirejp
77 64KB
hi twinnyfo, when I click the text box named txtInitialPremium, the event tab is blank. If I click the field name InitialPremium, the event tab contains the after update property. If I follow the steps which you outlined in post# 59and added the VBA code to the after update property of the InitialPremium field name, the event procedure is still not producing a result in the InitialPremiumInvoice field name.
Attached Files
File Type: docx After event procedure not firing.docx (561.4 KB, 357 views)
Aug 19 '14 #24
twinnyfo
3,653 Expert Mod 2GB
OK. let's use your word doc that you just attached.

First pic: Look at the orange box highlighting the label. I know it is a label, because it is not a text box that can be updated when the Form is opened normally. Also, note in the property sheet, on the right, at the top, it says, "Selection type: Label". Just below that, you will see a dropdown box which says, "InitialPremium_Label."

So, let's get the nomenclature properly identified: The control you have selected is a label. The name of that label is InitialPremium_Label. The caption in that label is "txtInitialPremium".

It is odd that you cannot add any events to this label, but that is immaterial here, because we don't need to and we don't want to. Change the caption back to Initial Premium.

Now, select the control just to the right of that label (as in the second pic). That control is a text box. That text box has the name of "InitialPremium". See the drop down at the top of the property sheet. Also, I can see that the text box has a control source of InitialPremium. This is the text box that must be named "txtInitialPremium". Likewise for the labels/text boxes for InitialPremiumInvoice and several others.

The Event procedure should be associated with the text box named txtInitialPremium. If you were to click on the "..." button in the After Update Property, it will take you to your code, but it will create a NEW sub, called "Private Sub InitialPremium_AfterUpdate()" You can either copy the code you already have or rename as described above. But, the key issue to remember is that the code is looking for a control called "txtInitialPremium". If you change hte name of the procedure, you also have to change hte name of the control (and vice versa).

Then, when you open the form, the code should fire when you update the Initial Premium text box.
Aug 19 '14 #25
wirejp
77 64KB
hi twinnyfo, I apologize for these repeated msgs. However, the event is still not firing, after I followed the steps which you have outlined in post #63 (refer to first two screenshots in the attachment). In the third screenshot, If I change the amount in the Premium Invoice text box, I get a debugging error.
Attached Files
File Type: docx after event procedure is not firing.docx (458.7 KB, 242 views)
Aug 19 '14 #26
twinnyfo
3,653 Expert Mod 2GB
I have to ask if this form is attached to any table? I don't know how you can change the Control Source -- which is what you are doing without any errors!

Your controls ought to have a control source from the table you are trying to update, which it appears your controls are not connected to anything, as you keep changing that value.

Example, first pic: So you change the caption on the label, and you now have the text box NAMED correctly, but you have also changed the control source--which is why you received the error (most likely, as you did not provide the error generated).

Do you understand that text boxes have to get and store their data somewhere? That is your table. If there is nothing in the table, the text box will be blank. When the user enters something into the text box, that data is then saved in the table. However, you must tell the text box where to get/store your data. As far as I know, we have not created fields called "txtInitialPremium" (et al). Perhaps you did. However, the error is most likely because you are setting a control source to something that does not exist.

This would be much easier via telephone or somehow being able to share monitors, but this will have to do.
Aug 19 '14 #27
wirejp
77 64KB
The form is linked a table but the table fields for "txtInitialPremium" have values, but the txtInitialPremium invoice fields are blank (I had thought the VBA code could populate the Initial Premium Invoice fields in the table). Would I have to input a formula in the Initial Premium Invoice field name of the Fee Schedule table. In the instance, where a country has a different method of calculating the Initial Premium Invoice, would VBA code have to applied for this case?
Aug 19 '14 #28
twinnyfo
3,653 Expert Mod 2GB
The VBA will calculate the Invoice fields. As mentioned much earlier, you have to determine where and how you are going to save invoice data (whether it is some sort of ledger for each client or another means).

For your invoice calculation, you need to determine what the "standard" invoice calculation method will be--which should apply to as many accounts as possible. So, if only two or three countries have a terrorism levy, you can still "calculate" that fee, but if the percentage is 0%, then that additional fee will be 0. Make sense?

However, I know there are a couple of accounts which are very different from the norm. This is where your VBA will handle those exceptions. Perhaps having a command button to calculate the costs is the "better" way--but it depends on when you plan to create the invoices. It might be best to create invoices one at a time by selecting the client, entering the initial premium and then creating the invoice then and there.

Again, I don't know exactly how you want to set up your project, so there may be advantages to your form being based on the fee schedule table, and it might not. At this point, I am not going to recommend anything else that would probably confuse you too much and introduce too many new concepts for you to learn in order to accomplish it.

For right now, I think we are still trying to get your form to calculate the Invoice properly. This is still our task at hand.

BTW, the moderators have informed me that htis thread has drifted infinitely off-topic, and I am aware of that. However, I think in this case, I hope this thread can stay alive for your sake, as you are learning some of the basics of DB design. Perhaps you won't become an expert overnight, but hopefully you will understand the concepts of DB design a bit better in the end.
Aug 19 '14 #29
wirejp
77 64KB
I have added a formula in the InitialPremiumInvoice in the table. From your post #61, I am still unsure about where to go to name the text box as txtInitialPremium.In order to do this, I select the control box "InitialPremium" and in the Other tab under the Property Sheet, I entered the "txtInitialPremium" name in the Name field. Is this correct? I did a similiar action for the other fields. Once, I entered the after update VBA code, and if I change the amount in the InitialPremium text box, I get a debugging code error.
Attached Files
File Type: docx Doc.docx (536.3 KB, 284 views)
Aug 19 '14 #30
twinnyfo
3,653 Expert Mod 2GB
What is the error that is being generated? I don't know where to start without that info....
Aug 19 '14 #31
wirejp
77 64KB
The error is Compile error: Invalid outside procedure (refer to the screenshots)
Attached Files
File Type: docx Compile error - Invalid outside procedure.docx (315.4 KB, 293 views)
Aug 19 '14 #32
twinnyfo
3,653 Expert Mod 2GB
First screenshot:

First line of the sub should be:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
At this point, I don't know why you are still getting an error on frmFeeSchedule.

After you change the frmCountries, go to Debug, Compile and see if there are any other remnants of errors.
Aug 19 '14 #33
zmbd
5,501 Expert Mod 4TB
WireJP:
It is usually better to place the information within the text of the post and use attachment only when you have to for clarity.
With your error, the title (if any and note if none), the number, and the message is the information.
You can then post the section the code that the compiler stops on.

Please note, Many of us work in "secure" environments; thus, many of us are unable to open attachments, even text files, especially if using third party sites for attachments.

For images, please use the [advanced] editor to manage image attachments to your thread.

thnx
Aug 19 '14 #34
wirejp
77 64KB
Database picked up two errors (see the attached screenshots)
Attached Files
File Type: docx Compile debug errors.docx (355.5 KB, 322 views)
Aug 19 '14 #35
wirejp
77 64KB
hi zmbd, my apologies for not compiling with the rules. I didn't see your post#72 until after I posted just now.
Aug 19 '14 #36
twinnyfo
3,653 Expert Mod 2GB
wirejp,

I suspect you will have many errors with your on current event, that is unless you have all of those command buttons mentioned in the code on your form, and based on what I have seen on your form, they do not exist. You can either add all those command buttons or just comment out all of those lines of code for that form (put an apostrophe ['] at the beginning of each line).

The compile will only find the first error (until you fix it). Then it will show the next.

That will be all for tonight, though.....
Aug 19 '14 #37
wirejp
77 64KB
In post # 36, Debug compiler found an error in the code. In line 24, the error is "Compile error: Else without If"
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboCountry_AfterUpdate()
  2.     ValidateData (True)
  3. End Sub
  4.  
  5. Private Sub txtRegionCode_AfterUpdate()
  6.     ValidateData (False)
  7. End Sub
  8.  
  9. Private Function ValidateData(fCountry As Boolean)
  10.     Dim lngValidate As Long
  11.     Me.Refresh
  12.     lngValidate = Nz(DLookup("[RegionID]", _
  13.         "tblRegions", _
  14.         "[Country] = " & Me.cboCountry & _
  15.         " AND [RegionCode] = '" & Me.txtRegionCode & "'"), 0)
  16.     If Not lngValidate = 0 Then
  17.         MsgBox "You already have a Region Code by " & _
  18.             "that name for this Country!  Try again!", _
  19.             vbCritical, "Duplicate Entry!"
  20.         If fCountry Then
  21.             Me.cboCountry = Null
  22.         Else
  23.         Me.txtRegionCode = ""
  24.         Else
  25.         Me.Refresh
  26.     End If
  27. End Function
In regard to post #61, when I change the "InitialPremium" amount, the database generates the following error message in the Form view: -"Runtime error - '2147352567 (8002009)': You can't assign a value to this object. (followed by the dialog boxs: - End, Debug, Help).
When I click the dialog box called "Debug", the VBA code appears and the following code is highlighted in yellow: -
Expand|Select|Wrap|Line Numbers
  1.  Me.txtInitialPremiumInvoice = Me.txtInitialPremium + _
  2.         (Me.txtInitialPremium * _
  3.             (Me.txtTaxes / (1 - Me.txtTaxes)))
Aug 20 '14 #38
twinnyfo
3,653 Expert Mod 2GB
Lines 23ff of the first block should be:

Expand|Select|Wrap|Line Numbers
  1.         Me.txtRegionCode = "" 
  2.     End If 
  3.     Me.Refresh 
  4. End Function
My mistake in the original code (I'm typing it freehand, so I am not compiling as I go along (good advice for you as you build your code.

For your second error, I have received this error when there is ambiguity between the field behind the form and the name of the text box housing that field. As mentioned earlier, this is why I always try to disambiguate.

Make sure you have a text box named "txtInitialPremiumInvoice" and that this text box has as its control source "InitialPremiumInvoice" which is from the underlying table.
Aug 20 '14 #39
wirejp
77 64KB
Hi twinnyfo,

The issue in post#77 has been corrected.

In regard to post 57, I ran a query to calculate the admin fees based on an admin fee percentage of 1.5% (denoted as "yes/no" variable)

Expand|Select|Wrap|Line Numbers
  1. AdminFee: IIf([tblFeeSchedule]![AdminFeeInitial]=Yes,[tblFeeSchedule]![InitialPremium]*0.015,"$0.00")
The result of the calculation is correct for most of the normal cases, but for the special cases, can VBA code be written for the specific client?

P.S. in regard to the calculation above, I was unable to format the resulting admin fees to a currency type and to 2 decimal places. How is this done?
Oct 6 '14 #40
twinnyfo
3,653 Expert Mod 2GB
wirejp,

Good to see you again. How are you calculating your admin fees? On the form? This will determine, ultimately, how you are to write the code.

Assuming AdminFeeInitial is a Yes/No field, on a form, then,

Expand|Select|Wrap|Line Numbers
  1. Me.txtAdminFee = _
  2.     Format( _
  3.         IIf(Me.chkAdminFeeInitial, _
  4.             Me.txtInitialPremium * 0.015, _
  5.             0 )
  6.             ), _
  7.         "Currency" _
  8.         )
Remember, that the calculated admin fee may have additional decimal places of accuracy. If you save the value (or charge it), you will only want to save it as the two decimal place calculated value (in which case, you must round your values to the desired decimal place).

Hope this helps...
Oct 6 '14 #41
zmbd
5,501 Expert Mod 4TB
A secondary question has been split into its own thread:

"Calculation of invoice Fees - Special cases"
Oct 7 '14 #42

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: qurat | last post by:
Alachisoft TierDeveloper is an O/R mapping and code generation tool that helps software developers do better, more creative and useful work by reducing redundant hand coding. TierDeveloper lets you...
3
by: David B | last post by:
I am creating invoices for an app I am busy with. The transactions for the invoice come from 2 tables which store Sales and Facilities Hire. The current arrangement is that I create a temp...
3
by: NoEd | last post by:
I was talking to a couple guys at one of the hospitals I consult with, and they indicated their 401K is charging them $8 per quarter and .0875% based on the value in their accounts at the end of...
9
by: MX1 | last post by:
Hi, I've got an invoice form that has a bunch of business rules behind it. Basically, I'm trying to figure out how to enter a single number in a field (e.g. $1000) and then have the form do a...
15
by: NomoreSpam4Me | last post by:
Hi there i have a little problem with my invoice. Here it is: i have a main menu with buttons, one of my button is "Create new invoice", when click on it a form pop up so i can enter my...
1
by: Herman Beeksma | last post by:
Hi there! I have two tables: Customer (ID, Name) Invoice (ID, Date, Customer, Amount) and want to select only the *last* invoice for each customer. It's easy to get each customer's last...
4
by: vg-mail | last post by:
Hello all, I have identical design for form and report but I am getting calculation error on form and everything is OK on report. The form and report are build up on SQL statement. The...
4
by: gregincolumbus | last post by:
I am trying to get the financial calculation on this to trigger whenever there is a change to select1. Right now, the user has to click on select2 to trigger the changes. Ideally, a change of...
5
by: Bigdaddrock | last post by:
I have two entries on an Invoice form: PRICE and QUANTITY (as well as the INVOICE NUMBER). In creating a new INVOICE I am pulling in a product's PRICE from a table (though the price may change...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.