Help with VB code "dfirst" function


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
 
Ad

Advertisements

A

Anne

Hi again Kendra -
Here's your original code:

If Not IsNull(Me.[LU_Type] ) And Not IsNull(Me.[Units])
Then
Me.[Obl Fees] = Me.[LU_Type] * Me[Units]
End If

but, you said Me.[LU_Type] wont work, becuase you don't
want the type, but rather the amount associated with that
type which I suggested as
dfirst("Fee_Per_Unit","tbl_LU_Type","LU_Type=" & Me.
[LU_Type] &"")

So, you just replace your me.[lu_type] with my suggestion
and so

If Not IsNull(Me.[LU_Type] ) And Not IsNull(Me.[Units])
Then
Me.[Obl Fees] = dfirst
("Fee_Per_Unit", "tbl_LU_Type","LU_Type=" & Me.[LU_Type]
&"") * Me[Units]
End If
(Sorry about the strange line breaks, obvsiously, the
entire me.[obl fees]=.... line should be on one line.

Hope that helps - Anne
-----Original Message-----
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



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top