V
Vernon
This morning I was given a very good suggestion to use
the "dfirst" function to get a calculation on my form to
link to values in an external table which is linked to my
form through a pull down menu in one of the fields.
However, my VB skills are non-existent, so I would very
much appreciate if someone could help me add the dfirst
line of code into the current code that I have.
Here is what I want done:
Calculate the obligation fees field, called "Obl fees", by
multiplying the Land Use Type, called "LU_Type", by the
Units, called "Units", fields. I have already added in
the following visual basic script to the LU_Type and Units
fields using the code builder in the Events Tab in the
field Before Update:
If Not IsNull(Me.[LU_Type] ) And Not IsNull(Me.[Units])
Then
Me.[Obl Fees] = Me.[LU_Type] * Me[Units]
End If
However, I forgot to address one major complication: the
LU_Type field is a pull down menu linked to a table
called "tbl_LU_Type". tbl_LU_Type includes two columns:
the first includes text listing the the names of the land
use types (ie single family, multifamily, retail) and the
other includes the corresponding monetary fees associated
with each (ie $2400.00, $5650.50)... How can I make the
equation reference only the second column of the
tbl_LU_Type table (called "Fee Per Unit") while the
LU_Type field shows only the name of the land use type
(not the monetary value)?
Anne, the expert respondent gave me this response and line
of code:
The dfirst function would allow you to get the correct
amount from the table based on the LU_Type field.
dfirst("Fee_Per_Unit","tbl_LU_Type","LU_Type=" & Me.
[LU_Type] &"")
This means 'return the fee_per_unit from tbl_lu_type where
the Lu_type in the table = the LU_type from the form.'
Her response makes sense, I just don't have the VB
knowledge of where to place her script into my existing
command.
If you need any clarifications, email (e-mail address removed)
Thanks,
Kendra
the "dfirst" function to get a calculation on my form to
link to values in an external table which is linked to my
form through a pull down menu in one of the fields.
However, my VB skills are non-existent, so I would very
much appreciate if someone could help me add the dfirst
line of code into the current code that I have.
Here is what I want done:
Calculate the obligation fees field, called "Obl fees", by
multiplying the Land Use Type, called "LU_Type", by the
Units, called "Units", fields. I have already added in
the following visual basic script to the LU_Type and Units
fields using the code builder in the Events Tab in the
field Before Update:
If Not IsNull(Me.[LU_Type] ) And Not IsNull(Me.[Units])
Then
Me.[Obl Fees] = Me.[LU_Type] * Me[Units]
End If
However, I forgot to address one major complication: the
LU_Type field is a pull down menu linked to a table
called "tbl_LU_Type". tbl_LU_Type includes two columns:
the first includes text listing the the names of the land
use types (ie single family, multifamily, retail) and the
other includes the corresponding monetary fees associated
with each (ie $2400.00, $5650.50)... How can I make the
equation reference only the second column of the
tbl_LU_Type table (called "Fee Per Unit") while the
LU_Type field shows only the name of the land use type
(not the monetary value)?
Anne, the expert respondent gave me this response and line
of code:
The dfirst function would allow you to get the correct
amount from the table based on the LU_Type field.
dfirst("Fee_Per_Unit","tbl_LU_Type","LU_Type=" & Me.
[LU_Type] &"")
This means 'return the fee_per_unit from tbl_lu_type where
the Lu_type in the table = the LU_type from the form.'
Her response makes sense, I just don't have the VB
knowledge of where to place her script into my existing
command.
If you need any clarifications, email (e-mail address removed)
Thanks,
Kendra