DLookup Problem

[Lisa]

I need to do a Dlookup and have looked at all the examples and can't seem to
get mine to work on a form.

Form Name: CC2 Eval Info Form
Table to do Lookup: CustomerLoyalty
Criteria (Field on Form) CB_EmplID = (Field on Table to display) PPL_SFT_ID
Fields Data to Display(concatenate of last name, first name) Field Names
are: LST_NM
FRST_NM

=DLookup("[LST_NM] & ', ' & [FRST_NM]", "CustomerLoyalty", "PPL_SFT_ID = '"
& [CB_EmplID] & "'")

Can anyone see what i am doing wrong?

Thanks.

Lisa

 

[Lisa]

So I got the Dlookup to work by using the following:

=DLookUp("[LST_NM] & ', ' & [FRST_NM]","CustomerLoyalty","PPL_SFT_ID = " &
[CB_EmplID])


But my new problem is that I need this to populate in my table that this
form feeds into... and because i have the DLookup Expression in the control
source I can't use the field as the control source.

Hope this makes sense, thanks for any help you can provide.

Lisa

 

[Klatuu]

Change it from the Control Source to the Default Property. Then it will only
fill in the value for new records. Existing records will not be affected.
The reason you got it to work was because you were using text delimiters on
a numeric field, but you corrected that.

 

[Tom Lake]

So I got the Dlookup to work by using the following:

=DLookUp("[LST_NM] & ', ' & [FRST_NM]","CustomerLoyalty","PPL_SFT_ID = " &
[CB_EmplID])


But my new problem is that I need this to populate in my table that this
form feeds into... and because i have the DLookup Expression in the control
source I can't use the field as the control source.

Hope this makes sense, thanks for any help you can provide.

Instead of using the DLookup as the data source, use the DLookup in the
OnEnter event of the field or OnLoad event of the form something similar.

Tom Lake

 

[Lisa]

Dave,

I originally had it in the default and it worked, but if i changed the
Employee ID it didn't update the field with the new last name and first name.
Is there a way to correct that?

Thanks for your help.

Change it from the Control Source to the Default Property. Then it will only
fill in the value for new records. Existing records will not be affected.
The reason you got it to work was because you were using text delimiters on
a numeric field, but you corrected that.
--
Dave Hargis, Microsoft Access MVP

So I got the Dlookup to work by using the following:

=DLookUp("[LST_NM] & ', ' & [FRST_NM]","CustomerLoyalty","PPL_SFT_ID = " &
[CB_EmplID])


But my new problem is that I need this to populate in my table that this
form feeds into... and because i have the DLookup Expression in the control
source I can't use the field as the control source.

Hope this makes sense, thanks for any help you can provide.

Lisa

 

[Tom Lake]

Dave,

I originally had it in the default and it worked, but if i changed the
Employee ID it didn't update the field with the new last name and first name.
Is there a way to correct that?

Put in in the OnChange event of the EmployeeID control. That way when EmployeeID
changes, so will your field.

Tom Lake

 

[Lisa]

Tom,

If I put it on the employee ID field on Change how does that data get
popuplated in the field that has the control source from the table.

 

[Tom Lake]

Tom,

If I put it on the employee ID field on Change how does that data get
popuplated in the field that has the control source from the table.

You put it in a code module that's activated by the OnChange event.

Example: in the code module associated with the On Change event of the EmployeeID
field:

Private EmployeeID_Change()

Me.[FieldToBeFilledIn]=Dlookup(... ' The rest of the DLookup goes here

End Sub

Change the "FieldToBeFilledIn" to the name of the control on your form you want to be
changed.
Also, complete the DLookup.

Tom Lake

 

[Lisa]

Tom,

I tried this module and got an error (Syntax Error (missing operator) in
query expression"PPL_SFT_ID = "

here is the code:

Private Sub txt_EmplID_Change()
Me.[Campus] = DLookup("[CMPS_NM]", "CustomerLoyalty", "PPL_SFT_ID = " &
[txt_EmplID])
Me.[FLM] = DLookup("[SUP_Concat name]", "CustomerLoyalty", "PPL_SFT_ID =
" & [txt_EmplID])
Me.[SiteLead] = DLookup("[SUP_LAST] & ', ' & [SUP_FIRST]",
"CustomerLoyalty", "Txt_FLM" = "[LST_NM] & ', ' & [FRST_NM]")
End Sub

Hope you can help.

Lisa

Tom,

If I put it on the employee ID field on Change how does that data get
popuplated in the field that has the control source from the table.

You put it in a code module that's activated by the OnChange event.

Example: in the code module associated with the On Change event of the EmployeeID
field:

Private EmployeeID_Change()

Me.[FieldToBeFilledIn]=Dlookup(... ' The rest of the DLookup goes here

End Sub

Change the "FieldToBeFilledIn" to the name of the control on your form you want to be
changed.
Also, complete the DLookup.

Tom Lake

 

[Lisa]

Tom,

I tried this module and got an error (Syntax Error (missing operator) in
query expression"PPL_SFT_ID = "

here is the code:

Private Sub txt_EmplID_Change()
Me.[Campus] = DLookup("[CMPS_NM]", "CustomerLoyalty", "PPL_SFT_ID = " &
[txt_EmplID])
Me.[FLM] = DLookup("[SUP_Concat name]", "CustomerLoyalty", "PPL_SFT_ID =
" & [txt_EmplID])
Me.[SiteLead] = DLookup("[SUP_LAST] & ', ' & [SUP_FIRST]",
"CustomerLoyalty", "Txt_FLM" = "[LST_NM] & ', ' & [FRST_NM]")
End Sub

Hope you can help.

Lisa

So I got the Dlookup to work by using the following:

=DLookUp("[LST_NM] & ', ' & [FRST_NM]","CustomerLoyalty","PPL_SFT_ID = " &
[CB_EmplID])


But my new problem is that I need this to populate in my table that this
form feeds into... and because i have the DLookup Expression in the control
source I can't use the field as the control source.

Hope this makes sense, thanks for any help you can provide.

Instead of using the DLookup as the data source, use the DLookup in the
OnEnter event of the field or OnLoad event of the form something similar.

Tom Lake

 

[Tom Lake]

Tom,

I tried this module and got an error (Syntax Error (missing operator) in
query expression"PPL_SFT_ID = "

here is the code:

Private Sub txt_EmplID_Change()
Me.[Campus] = DLookup("[CMPS_NM]", "CustomerLoyalty", "PPL_SFT_ID = " &
[txt_EmplID])

Since txt_EmpID is a text field, you have to delimit it with apostrophes:

Me.[Campus] = DLookup("[CMPS_NM]", "CustomerLoyalty", "PPL_SFT_ID = '" &
[txt_EmplID] & "'")



Me.[FLM] = DLookup("[SUP_Concat name]", "CustomerLoyalty", "PPL_SFT_ID = '" &
[txt_EmplID] & "'")


Me.[SiteLead] = DLookup("[SUP_LAST] & ', ' & [SUP_FIRST]", "CustomerLoyalty",
"Txt_FLM = '"& [LST_NM] & ", " & [FRST_NM] & "'")


Tom Lake