Correct use of Dlookup?

[dab1477]

I have a subform titled NewPartsSubForm. Within this form, I have a combo box
labled as Rtg_oper_num. I am able to choose from a listing of routings within
the box (I choose 100, 120, 140, 32156 etc from a listing). I want to
populate a Description filed within this same subform based upon the entry I
choose in the Rtg_oper_num field. I have a table call Operations, where I
have the operation number in one column and the operation descripton in an
adjacent column. I am trying to lookup the Rtg_oper_num entry on the subform;
match it to the Operation Number in the Operations table, and return the
Operation Description from the Operations table to the Description field on
the subform. At this point I have the following expression coded for the
Control Source property of the Description Field of the subform;

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=Forms![newpartsubform]![rtg_oper_num]")

Is this the correct use of Dlookup? At this point, my Description Field
shows up blank - no error, nothing. I used expression instead of VBA coding
because I am very elementary on the coding side of thinks, but would be
willing to explore if there is a better option than Dlookup function.

Thanks in advance for the assist.

 

[Douglas J. Steele]

Put the reference to the control on the form outside of the quotes. If it's
numeric, use

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=" &
Forms![newpartsubform]![rtg_oper_num])

If it's text, use

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=""" &
Forms![newpartsubform]![rtg_oper_num] & """")

(that's three double quotes in a row before, and four double quotes in a row
after)

 

[dab1477]

Thanks Douglas. Now that presents a second issue:

If I update the rtg_oper_num field than I would need have the Description
field update. The current expression does not allow this to happen. It DOES
allow the initial description field to populate, just not so on an update.
How would I perform similar expression for rtg_oper_num after update that
would update the Decription field. Now I'm thinking this should be coded as
an After Update Event:

Private Sub Rtg_Oper_Num_AfterUpdate()
If Rtg_Oper_Num > 0 Then
Oper_Desc.Value = DLookup("[Oper_Desc]", "[operations]", "[rtg_oper_num]=" &
Forms![newpartsubform]![Rtg_Oper_Num])
End If
End Sub

When I do this I get a debug error saying I can't assign a value to this
object. I think I need to do a DIM statement? Can you direct me here or how
would I do this as an expression.
You were very helpful to date. Now you have me thinking outside my box!
Thank you.

Put the reference to the control on the form outside of the quotes. If it's
numeric, use

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=" &
Forms![newpartsubform]![rtg_oper_num])

If it's text, use

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=""" &
Forms![newpartsubform]![rtg_oper_num] & """")

(that's three double quotes in a row before, and four double quotes in a row
after)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a subform titled NewPartsSubForm. Within this form, I have a combo
box
labled as Rtg_oper_num. I am able to choose from a listing of routings
within
the box (I choose 100, 120, 140, 32156 etc from a listing). I want to
populate a Description filed within this same subform based upon the entry
I
choose in the Rtg_oper_num field. I have a table call Operations, where I
have the operation number in one column and the operation descripton in an
adjacent column. I am trying to lookup the Rtg_oper_num entry on the
subform;
match it to the Operation Number in the Operations table, and return the
Operation Description from the Operations table to the Description field
on
the subform. At this point I have the following expression coded for the
Control Source property of the Description Field of the subform;

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=Forms![newpartsubform]![rtg_oper_num]")

Is this the correct use of Dlookup? At this point, my Description Field
shows up blank - no error, nothing. I used expression instead of VBA
coding
because I am very elementary on the coding side of thinks, but would be
willing to explore if there is a better option than Dlookup function.

Thanks in advance for the assist.

 

[Douglas J. Steele]

You can't assign a value to it because (presumably) its ControlSource is the
DLookup statement.

Try:

Private Sub Rtg_Oper_Num_AfterUpdate()
If Rtg_Oper_Num > 0 Then
Me.Oper_Desc.Requery
End If
End Sub




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas. Now that presents a second issue:

If I update the rtg_oper_num field than I would need have the Description
field update. The current expression does not allow this to happen. It
DOES
allow the initial description field to populate, just not so on an update.
How would I perform similar expression for rtg_oper_num after update that
would update the Decription field. Now I'm thinking this should be coded
as
an After Update Event:

Private Sub Rtg_Oper_Num_AfterUpdate()
If Rtg_Oper_Num > 0 Then
Oper_Desc.Value = DLookup("[Oper_Desc]", "[operations]", "[rtg_oper_num]="
&
Forms![newpartsubform]![Rtg_Oper_Num])
End If
End Sub

When I do this I get a debug error saying I can't assign a value to this
object. I think I need to do a DIM statement? Can you direct me here or
how
would I do this as an expression.
You were very helpful to date. Now you have me thinking outside my box!
Thank you.

Put the reference to the control on the form outside of the quotes. If
it's
numeric, use

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=" &
Forms![newpartsubform]![rtg_oper_num])

If it's text, use

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=""" &
Forms![newpartsubform]![rtg_oper_num] & """")

(that's three double quotes in a row before, and four double quotes in a
row
after)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a subform titled NewPartsSubForm. Within this form, I have a
combo
box
labled as Rtg_oper_num. I am able to choose from a listing of routings
within
the box (I choose 100, 120, 140, 32156 etc from a listing). I want to
populate a Description filed within this same subform based upon the
entry
I
choose in the Rtg_oper_num field. I have a table call Operations, where
I
have the operation number in one column and the operation descripton in
an
adjacent column. I am trying to lookup the Rtg_oper_num entry on the
subform;
match it to the Operation Number in the Operations table, and return
the
Operation Description from the Operations table to the Description
field
on
the subform. At this point I have the following expression coded for
the
Control Source property of the Description Field of the subform;

=DLookUp("[Oper_Desc]","[operations]","[rtg_oper_num]=Forms![newpartsubform]![rtg_oper_num]")

Is this the correct use of Dlookup? At this point, my Description Field
shows up blank - no error, nothing. I used expression instead of VBA
coding
because I am very elementary on the coding side of thinks, but would be
willing to explore if there is a better option than Dlookup function.

Thanks in advance for the assist.