Dlookup query

Griffo83

New Member
Joined
Dec 1, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi All
Long time stalker first time poster ;)

I have just started using MS Access and in my opinion trying to add a very simple Dlookup function into one of the default templates (Task Management).
In the Task Details form after the Assigned To field is populated I need an adjacent field to display the Assigned to persons phone number.

I have tried using a After update function on the Assigned To field as per this video-
I have also tried using a textbox with the control source set to Dlookup. as per this video -

Both examples i have used a variation of - DLookup("Mobile Phone", "Active Contacts Extended", "Contact Name='" & Assigned_To & "'")

Both options do not seem to work, everything I have read says it should please help!!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,466
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Please post the entire VBA code block that is supposed to be assigning this.

What are the exact names of the fields in your table?
What are the exact names of the fields on your form?

Can you post a small sample of the data from your table?
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
973
Office Version
  1. 2007
Platform
  1. Windows
If you have spaces in fieldnames and tables etc, then you need to surround them with [ & ]
Best not to have them and use CamelCase

Code:
DLookup("[Mobile Phone]", "[Active Contacts Extended]", "[Contact Name] ='" & Assigned_To & "'")

I would have used
Code:
DLookup("MobilePhone", "ActiveContactsExtended", "ContactName ='" & Me.Assigned_To & "'")

You even use _ for the control? :(
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,466
Office Version
  1. 365
Platform
  1. Windows
If you have spaces in fieldnames and tables etc, then you need to surround them with [ & ]
Best not to have them and use CamelCase

Code:
DLookup("[Mobile Phone]", "[Active Contacts Extended]", "[Contact Name] ='" & Assigned_To & "'")

I would have used
Code:
DLookup("MobilePhone", "ActiveContactsExtended", "ContactName ='" & Me.Assigned_To & "'")

You even use _ for the control? :(
Good catch!
I forgot about the issue with spaces (probably because I never use them myself).
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
973
Office Version
  1. 2007
Platform
  1. Windows
In fact, do not bother with a Dlookup, just bring in the phone number as an extra column (hidden) in your combo source and refer to that instead.
 

Griffo83

New Member
Joined
Dec 1, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the reply's, ill try and address all your requests and suggestions.

@Joe4 For simplicity I have used one of the Default Templates available (Task Management). I did this so I was starting with something I know works that I can expand on, this is the reason for the spaces in the field names. The only changes I have made are -
1. Added a Short Text column (PhoneNo) to the Task Detail table.
2. I then added this field to the Task Detail form using the available fields tool.
3. In the Assigned To form field I added the After Update Event procedure brackets added after @welshgasman pointed out my spaces error) -
VBA Code:
Private Sub Assigned_To_AfterUpdate()
PhoneNo = DLookup("[Mobile Phone]", "[Active Contacts Extended]", "[Contact Name] ='" & [Assigned To] & "'")
End Sub

It is now returning nothing, no error no data just a blank field.
I should point out that the Active Contacts Extended is a query not sure if this makes a difference??

@welshgasman Id like to hear more about how I could do this. sounds like it might be a simpler option in the long run.
In fact, do not bother with a Dlookup, just bring in the phone number as an extra column (hidden) in your combo source and refer to that instead.

Happy to share the entire file if this is an acceptable thing to do?? But im assuming that anyone running Access would have access to the same template.
 

Attachments

  • Form.PNG
    Form.PNG
    222.4 KB · Views: 8
  • Tasks.PNG
    Tasks.PNG
    170.3 KB · Views: 7
  • Active Contacts.PNG
    Active Contacts.PNG
    174.6 KB · Views: 8

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
973
Office Version
  1. 2007
Platform
  1. Windows
Thanks for the reply's, ill try and address all your requests and suggestions.

@Joe4 For simplicity I have used one of the Default Templates available (Task Management). I did this so I was starting with something I know works that I can expand on, this is the reason for the spaces in the field names. The only changes I have made are -
1. Added a Short Text column (PhoneNo) to the Task Detail table.
2. I then added this field to the Task Detail form using the available fields tool.
3. In the Assigned To form field I added the After Update Event procedure brackets added after @welshgasman pointed out my spaces error) -
VBA Code:
Private Sub Assigned_To_AfterUpdate()
PhoneNo = DLookup("[Mobile Phone]", "[Active Contacts Extended]", "[Contact Name] ='" & [Assigned To] & "'")
End Sub

It is now returning nothing, no error no data just a blank field.
I should point out that the Active Contacts Extended is a query not sure if this makes a difference??

@welshgasman Id like to hear more about how I could do this. sounds like it might be a simpler option in the long run.


Happy to share the entire file if this is an acceptable thing to do?? But im assuming that anyone running Access would have access to the same template.
They might have access to the template, but not your modifications?
Remember a combo normally has a hidden column (the key) and the display column?
So inspect the source for your combo. You appear to be using a string (by the single quotes?)
I would also use Me. to indicate is is the form control ?

If you go into the debug window and set a breakpoint, and step line by line with F8, you can inspect what is actually there, not what you think is there?

If you chose to do it my way, then you would set Me.PhoneNo = Me.Assigned_To.Column(2) in the afterupdate of the combo,
where the first column is the bound key, the second colum is the display column and the third is hidden. As the columns start at zero, the third column is index 2.

Or set it as source for Me.PhoneNo, but that might produce an error when the combo is empty?
 
Solution

Forum statistics

Threads
1,175,824
Messages
5,899,686
Members
434,795
Latest member
tracid1987

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top