Forms Question

[azwildcat4ever]

I have a form where I have a drop-down menu which when updated , updates another value on the form (Gross Profit) when updated changes

So the form looks something like this

Revenue [ ]
Cost [ ] Drop Drop Menu with choice (0.14, 0.045)

Gross Profit [ ] Updated once you Update Cost

the cost table has the following fields
ConsultantCost_ID -> Primary Key
ConsultantCost

However, the cost field is using ConsultantCost_ID instead of ConsultantCost. How do I make it use ConsultantCost.. This is how the VB code looks like

Private Sub Command90_Click()
Forms![frmPlacements]![Gross_Profit_Accrued] = Forms![frmPlacements]![Revenue_Accrued] * Forms![frmPlacements]![ConsultantCost_ID]
    Forms![frmPlacements].Requery
End Sub

If I change ConsultantCost_ID to ConsultantCost it does not calculate, so essentially its looking at the ConsultantCost_ID value to calculate GrossProfit.

Please let me know .. Also included is a screen shot

 

[pbaldy]

Looks like you need to refer to the second column (the column reference is zero based):

Forms![frmPlacements]![ConsultantCost_ID].Column(1)

 

[azwildcat4ever]

Not working..anything else i can try. Thanks

this is what i got
Forms![frmPlacements]![Gross_Profit_Accrued] = Forms![frmPlacements]![Gross_Profit_Accrued] - (Forms![frmPlacements]![Revenue_Accrued] * Forms![frmPlacements]![ConsultantCost_ID].Column(1))


Basically the "Cost" will be determinde by the following calculation

14%
4.5 %

EXAMPLE : FOR A 14% Calculation:
Revenue $1,000
Cost is 14% of 1,0000 = $140
GP is $860 (GP is calculated automatically thru a similar code.

 

[pbaldy]

When you say "not working", are you getting an error or simply the wrong result? Can you post a sample db?

 

[azwildcat4ever]

Its not doing anything. I dont think this forum will allow us to post the dbs

Here is the code to ConsultantCost_ID

Its not picking up the value for it for some reason

Private Sub Combo89_AfterUpdate()

If Not ((Forms![frmPlacements]![Cost]) = "") Then
        
        'Update gross profit accrued
        Forms![frmPlacements]![Cost] = Forms![frmPlacements]![Revenue_Accrued] * Forms![frmPlacements]![ConsultantCost_ID].Column(1)
        Forms![frmPlacements]![Gross_Profit_Accrued] = Forms![frmPlacements]![Revenue_Accrued] - (Forms![frmPlacements]![Cost])
       

End Sub

So essentialy I am updating two fields. Gross Profit is usually automaticaly done by puting in a Cost or either a ConsultantPercentage . Therefore Consultant Percentage when update changes Cost and Cost effect the Gross Profit. Makes sense? let me know so i may explain u further.
thanks.
salman

 

[pbaldy]

The forum does allow db's to be posted, they just need to be zipped and under a certain size (I think 100k here).

 

[azwildcat4ever]

possible

Hey Paul,
I know whats the problem its rounding the numbers to 0.

FrmPlacements has reference to ConsultantCost_ID however no reference to ConsultantCost

 

[azwildcat4ever]

can u think any properties or additions to form..ConsultantCost is identified as a double in the ConstultCost table ...it is picking up the ConsultantCost_ID which are bascially two right now..two entries..1 and 2

So my ConsultantCost table looks like this

ConsultantCost_ID ConsultantCost
1 0.14
2 0.045

 

[pbaldy]

I thought that the combo box had the cost in the second column? This would be a lot easier with a sample db.

 

[azwildcat4ever]

how do u make the sample db? i am new to access
sorry

 

[pbaldy]

If the existing db is small enough (and doesn't contain sensitive data), you can just compact it, zip it and attach it. As noted earlier, I think the zipped file needs to be under 100k. If necessary, you can just create a new, empty db, and import the relevant objects into it, then do the same thing (compact & zip). In this case, it looks like the relevant objects are the form, the ConsultantCost table and tblPlacements.

 

[azwildcat4ever]

Ok i think i figured it out..I deleted the ConsultantCost_ID from tblPlacements
and then i placed ConsultantCost in the tblPlacement.
on the drop down, I have ConsultantCost looking up tblConsultantCost..


now the problem is the dropdown says 1 and 2 which are offcourse ConsultantCost_ID however its calculating using the values for which are
0.14 (CC_ID=1) and 0.045 (CC_ID=2)

how do i get it to show something else like a Percentage or something