By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

INSERT INTO Select based on Calculation

P: 3
Hello,

Fairly new to VB Access. Here's the issue: I have an expense field that is on the main form and an allocation amount on a subform. Code from buttons to update the allocation amount works well. BUT I'm trying to code it so it will not allow you to update the allocation amount for more than the expense amount. I did create a sum field in the subform footer which feeds a totalssum field on the main form and thought having statement look at the two fields and compare was best but for the life of me cannot figure it out.

Here's what I have so far and any and all help is greatly appreciated.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO tblallocation ( CountyID, ExpenseID, AllocationAmt ) SELECT tblREFCountyNames.CountyID, [Forms]![frmtab].[ExpenseID] AS ExpenseID, [Forms]![frmtab].[Expense]/23 AS Expense FROM tblREFCountyNames WHERE (((tblREFCountyNames.CountyID) In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)))"
Sep 25 '08 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello.

Several points:
  • Using temporary tables is troublesome and often is not so needed as it seems from a first look.
  • Calculated data stored in table is redundant in all cases (but those when complicated calculations will cause performance penalty).
  • Could you please explain what actually do you want to achieve (example would be nice).
  • Also, posting table(s) metadata is a good idea. Here is an example of how to post table MetaData :
    Table Name=tblStudent
    Expand|Select|Wrap|Line Numbers
    1. Field; Type; IndexInfo
    2. StudentID; AutoNumber; PK
    3. Family; String; FK
    4. Name; String
    5. University; String; FK
    6. Mark; Numeric
    7. LastAttendance; Date/Time

Regards,
Fish
Sep 26 '08 #2

P: 3
Thanks for the reply Fish.

Let me see if I can articulate this to make any sense.

The database itself is for entering budget and expense information. Two main tables with several others for referring to other data.

First table name: tblExpense

ExpenseID: Autonumber PK
Expense: Currency

I think the contact name, invoice numbers are not really relevant to this question, so I won't list them. If it would make more sense with them, let me know and I can list as well.

Second table name: tblAllocation

ExpenseID: Number PK
CountyID: Number FK
AllocationAmt: Currency

The tblAllocation makes up a subform. The purpose behind it is to breakout the Expense(tblExpense) as needed to the CountyID(tblAllocation).

This is done with an option group, then the user can select how they want the system to "Allocate" the funds evenly amongst the Counties, DOR or DOR & Counties. The rub comes when I want to make sure that the user is not allowed to allocate more funds than are entered in the Expense(tblExpense) field.

I had put a text field in the subform footer =Sum(AllocationAmt) and was trying to compare it to the Expense(tblExpense). If the first was 0, the allocation would take place. If not, a MsgBox would appear with the error and no allocation would be made.

Clear as mud? So thankful it's Friday!
Sep 26 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Hello.

....

Second table name: tblAllocation

ExpenseID: Number PK
CountyID: Number FK
AllocationAmt: Currency
I hope ExpenseID is FK.

This is done with an option group, then the user can select how they want the system to "Allocate" the funds evenly amongst the Counties, DOR or DOR & Counties.
You should explain business logic behind 2nd and 3rd option if it is important in context of your question.

The rub comes when I want to make sure that the user is not allowed to allocate more funds than are entered in the Expense(tblExpense) field.
User input validation procedure strongly depends on what input methods are allowed to user at all. Are the 3 options described above the only ways user can make an input? Is user allowed to make single records in [tblAllocation]?

I had put a text field in the subform footer =Sum(AllocationAmt) and was trying to compare it to the Expense(tblExpense). If the first was 0, the allocation would take place. If not, a MsgBox would appear with the error and no allocation would be made.
Sounds like a solution. ;)
Did you try it? Does it work?

So thankful it's Friday!
Amen.

Regards,
Fish
Sep 27 '08 #4

Post your reply

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