comments inline.
Annemarie said:
Thanks Tina!
I have been playing with DLookup all morning and have not been
successful yet. I have never used this function... I will post what I
have been trying and hopefully someone can help me problem solve.
I have tried three different methods... (I know Calculation is spelled
wrong..I have to go through my database and fix it everywhere just
haven't had the time yet...)
1. I attempted to put the following code in the control box of an
unbound text box to pull the correct NTG. No luck
= DLookup("[NTG]", "Calcluation Lookup Table TEST", "[num] = " _&
Forms![Add New Subs]![Num])
first, you can't use a line break ( _ ) in an expression in Access, only in
VBA code. so the expression would be
=DLookup("[NTG]", "Calcluation Lookup Table TEST", "[num] = " & [Forms]![Add
New Subs]![Num])
the above goes all on one line in the control's ControlSource property,
regardless of how it is "line-wrapped" here by the newsreader.
second, form [Add New Subs] must be open, so that the value of [Num] can be
retrieved. if this control is actually on form [Add New Subs], you don't
need the full syntax. the expression would be
=DLookup("[NTG]", "Calcluation Lookup Table TEST", "[num] = " & [Num])
again, all on one line regardless of line-wrap here.
third, the above syntax is correct if the [num] field in the table is a
Number data type (including Autonumber). but if the field's data type is
Text, then change the syntax to
=DLookup("[NTG]", "Calcluation Lookup Table TEST", "[num] = '" & [Num] &
"'")
again, see above re line-wrap.
and last, when you put an expression in the ControlSource of a textbox
control, that control is - by definition - an "unbound" control. if the
expression is correct, the value will be retrieved and displayed, BUT it
will *not* be saved to the table underlying the form. you will need to use
VBA or a SetValue macro to save the retrieved value to a field in the form's
underlying table.
2. I attempted to put the following code in the before update event of
a textbox bound to subfunctions.ntg (the field i want the value to
store in). I took the sample code from the help function and adapted
by my fields...
Dim varX As Variant
varX = DLookup("[NTG]", "Calcluation Lookup Table TEST", "[num] = " _
& Forms![Add New Subs]![Num])
okay, here again form [Add New Subs] must be open in order to retrieve the
value of [Num]. and if this code is actually running in form [Add New Subs]
module, then you don't need the full reference. instead, use
varX = DLookup("NTG", "Calcluation Lookup Table TEST", "num = " _
& Me!Num)
see my remarks above re the syntax for a Number data type vs a Text data
type. also, suggest you explicitly declare the data type of your variable
rather than using Variant. if the NTG field in the table is a Text data
type, then declare the variable as a string, as
Dim strX As String
if the field is a Number data type, look at the Field Size property of the
field, in table design view, and declare a variable of that type, as
Dim intX As Integer
or
Dim lngX As Long
or
Dim bytX As Byte
you get the picture.
and finally, you can't stop at assigning the retrieved value to a variable,
of course. next, you need to assign the value of the variable to a bound
control on the form, or directly to a field in the form's underlying table,
as
Me!ControlName = lngX
3. I attempted to put the following code in the record source of a
textbox bound to subfunctions.ntg.
= DLookup("[NTG]", "Calcluation Lookup Table TEST", "[Me]![num] = " _ &
Forms![Add New Subs]![Num])
okay, here i have no idea what you mean. textbox controls do not have a
RecordSource, they have a ControlSource - big difference. and i have no idea
what "subfunctions.ntg" is. however, refer to my comments above about values
in unbound controls being *displayed* in the form, but *not* saved in the
underlying table.
hth
What am I missing? I do not have an experience with DLookup...can
someone help me?
Again, I want to take the "library" value from Calcluation Lookup Table
TEST.NTG and be able to revise and store it in Subfunctions.NTG I
have tried pulling it in as default but no luck.
Thanks,
Annemarie
read up on the DLookup() function in Access Help; it may give you what you
need.
hth
Annemarie said:
I have been trying to pull a value from one table (ex [Lookup
Table].[NTG]) on a form and have that value be a baseline. I would
like this number to store in a field in another table (ex
[Subfunctions].[NTG]) this way the user can change the value if needed
for a specific record and not change the pre-set "library" value.
I have tried using [Lookup Table].[NTG] as the Default, and
[Subfunctions].[NTG] as the control source but the number will not pull
in. The form is based on a query that includes both values, so i have
tried to filter the form and have the value pull into the the
[Subfunctions].[NTG] from either that forms' [Lookup Table].[NTG] or a
previous forms' [Lookup Table].[NTG] ...previous form based on same
query. I am not having any luck.
I have the form filtered based on another field value already. The
reason I have to have the second form is to enter additional data based
on the first form...but there are two different forms asking for
different data that pop up based on the value in a field on the first
form. How do I pull this value from one table and store it in another??
