Need help - Calculation in form

[jojo86]

Hi. I am new to this site, and fairly new to Access itself.

I am currently designing a database for work that allows people to store information about sites around Blackburn. There is one main form and 3 subforms inside of it.

On Subform Yield Assessment, I would like the database to calculate the DT_YIELD field by multiplying SITE_AREA_HA * DT_DENSITY.

The thing is, SITE_AREA_HA is not visible in Form View, and it is a dLookup field (gets its data from SITES_AREA_HA field in the Sites Details Subform). Also, the DT_DENSITY figure is determined from the combinations from the cascading combo boxes before it (DT_SOURCE_SUP, DT_LOC_TYPE, DT_SITE_AREA_HA and DT_TEMPLATE).

I have attempted to put the different formulas into the Caption Source as follows (without ""):
1. "=[SITE_AREA_HA]*[DT_DENSITY]" <-- didn't work
2. "=[FRM_YIELD].[SITE_AREA_HA]*[FRM_YIELD].[DT_DENSITY]" <-- didn't work, came with the #Name? error in the field
3. "=[SUBFRM_YIELD].[SITE_AREA_HA]*[SUBFRM_YIELD].[DT_DENSITY]" <-- same error as 2
4. "=[SUBFRM_PRIMARY].[SITE_AREA_HA]*[SUBFRM_YIELD].[DT_DENSITY]" <-- same error as 2
5. "=[FRM_PRIMARY].[SITE_AREA_HA]*[SUBFRM_YIELD].[DT_DENSITY]" <-- same error as 2.... etc etc

I have even tried making a command button, with the following code, but it still doesn't work - and this comes up with "Compile Error: Argument not optional":

"Private Sub CAL_YIELD_Click()
Dim fMultiply As Integer

If SITE_AREA_HA > 0 And DT_DENSITY > 0 Then

fMultiply = SITE_AREA_HA * DT_DENSITY

DT_YIELD = fMultiply
Else

End If
End Sub"

Please help, it is driving me nuts, I just can't seem to make it work, yet it seems so simple. Again, I am fairly new at this. The database is attached - form to open is FRM_PRIMARY.

Thanks.

 

[neileg]

Base your form on a query, not the table. Do the calculation in the query and simple add the calculated field to your form.

Warning: You have spaces and punctuation in some of your object names. At best this causes irritation because you need to remeber to wrap your names in square brackets. At worst it can cause things to fail. This can be unpredictable and diificult to diagnose when it happens.

 

[GaryPanic]

cannot help but as it has been pointed out

stick with field names with no gaps and no figures (or at least at hte start of the name)

DT_YIELD would be better being DTYIELD otherwise you are into having to bracket everything

 

[jojo86]

Thank you for the help It works now, had a little help from someone in person, but they basically said what you did neileg.

I know, I had totally forgotten the 'no spaces' rule for access, but i have redone this database so many times now that i just dont want to mess it up even more. Plus it works now (sorta), and the people it is for are happy with it.

Thank you again!