INSERT INTO Select based on Calculation

[GR82BBlondie]

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.

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)))"

[FishVal]

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
  

  [i]Field; Type; IndexInfo[/i]
  StudentID; AutoNumber; PK
  Family; String; FK
  Name; String
  University; String; FK
  Mark; Numeric
  LastAttendance; Date/Time

Regards,
Fish

[GR82BBlondie]

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(tblExpe nse) as needed to the CountyID(tblAll ocation).

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(tblExpe nse) field.

I had put a text field in the subform footer =Sum(Allocation Amt) and was trying to compare it to the Expense(tblExpe nse). 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!

[FishVal]

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(tblExpe nse) 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(Allocation Amt) and was trying to compare it to the Expense(tblExpe nse). 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