DLookup Problem

  • Thread starter Thread starter Lisa
  • Start date Start date
L

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
 
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
 
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.
 
Lisa said:
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.
Click to expand...

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
 
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.


Klatuu said:
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


Lisa said:
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
Click to expand...
Click to expand...
 
Lisa said:
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?
Click to expand...

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

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.
 
Lisa said:
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.
Click to expand...

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
 
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 Lake said:
Lisa said:
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.
Click to expand...

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
Click to expand...
 
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 Lake said:
Lisa said:
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.
Click to expand...

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
Click to expand...
 
Lisa said:
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])
Click to expand...

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
 
Back
Top