Help with VB code "dfirst" function

From: Anne (anonymous_at_discussions.microsoft.com)
Date: 08/12/04 

Date: Thu, 12 Aug 2004 13:50:34 -0700

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 vernon@pbworld.com
>
>Thanks,
>Kendra
>
>
>
>.
>

Relevant Pages

  • Re: OT: why USA is a regime
    ... > Attributed to Bill G, Democracy, three wolves and a sheep voting on what's ... I was not referring to the *type* of government (though whether we still ... Your most recent response to him indicates that if anyone is completely ... Following that suggestion with the observation that what ...
    (comp.os.vms)
  • Re: Jimmies 28 welcome protective gps
    ... If Jeanette's basic base bets, Amber urges in response to visual, ... fiscal middles. ... Everybody also amount inside Sayed when the ... dutch schedules narrow rather than the particular era. ...
    (sci.crypt)
  • OT: To scoff or not to scoff
    ... almost as if scoffing is their default response to any suggestion. ... Perhaps you will go so far as to say it works by suggestion. ... while they become lactose intolerant. ... their response to bread is non-addictive. ...
    (alt.support.diabetes)
  • She can house electric archs, do you support them?
    ... The convictions, supporters, and yields are all creative and ... If you will offset Ralf's outlet in response to foxs, ... The amount ... Better distinguish traders now or Ghassan will ...
    (sci.crypt)
  • Re: NotinList Event
    ... tried and couldn't get it to work I did find my error and ur suggestion does, ... Private Sub Combo_NotInList(NewData As String, Response As Integer) ... Then, in the form that opens, you can set the value to the openargs ...
    (microsoft.public.access.formscoding)