Dlookup query

[Griffo83]

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

 

[Joe4]

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]

If you have spaces in fieldnames and tables etc, then you need to surround them with [ & ]
Best not to have them and use CamelCase

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

I would have used

DLookup("MobilePhone", "ActiveContactsExtended", "ContactName ='" & Me.Assigned_To & "'")

You even use _ for the control?

 

[Joe4]

If you have spaces in fieldnames and tables etc, then you need to surround them with [ & ]
Best not to have them and use CamelCase

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

I would have used

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]

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]

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

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.

 

[welshgasman]

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

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?

 

[Griffo83]

Legendary @welshgasman got that to work tanks for your help!