DLookup problem in Form.

[noobSPX]

Ok, now that I'm beginning to enjoy myself, and having moved to MS Access 2010, I've found some things easier....but not all.

I have a Purchases form, with a Combo Box storing a Contact Code (CCode).
I want to display the Contact Name relating to the CCode in an Unbound Text Box (called CompanyName) and have this auto looked up when the value in the Combo box changes.

I worked through all the help and tried to use DLookup in a procedure After Updating the Combo Box. I have added the error I am getting below.

Private Sub Combo15_AfterUpdate ()
strFilter = "CCode = " & Me!CCode
Me!CompanyName = DLookup ("CCompany", "Contacts", strFilter)
End Sub

Returns this error in DeBug: strFilter = "CCode = COS001" (COS001 is a valid CCode)


I know I'm not far off, but something is messing this all up. Any help would be much appreciated as I'm working around this but it's niggling me.

 

[noobSPX]

Screen Shots as per attached, with a couple of variances of the error.

 

[Mile-O]

CCode is a string and not a number. Therefore you need to use the correct delimiter.

strFilter = "CCode = """ & Me!CCode & """"

 

[Mile-O]

Would also suggest wrapping the DLookup with the Nz() function, just in case it returns a Null.

 

[CJ_London]

If you are using a combobox, you don't need any code at all

Assuming your combobox is called CCode and its rowsource is something like

SELECT CCode, CompanyName
FROM tblCompanies
ORDER BY CompanyName

Then in your unbound CompanyName textbox put the following into the controlsource

=CCode.Column(1)

That's it.

Note - for selecting the bound column, the first column is column 1, but when referring to the rowsource, the first column is 0 (don't ask me why, just a quirk of access)

 

[namliam]

If you want to "Drop" the name in why use a lookup?

Simply add the name as a hidden column and fetch it from the combobox something along the lines of
Me.Combobox.columns(1)

The column number is a zero based array, which means that 0 is the first column and 1 is the second.

 

[noobSPX]

CCode is a string and not a number. Therefore you need to use the correct delimiter.

strFilter = "CCode = """ & Me!CCode & """"

You are correct sir, this performs the lookup After Update.

My new problem is i would prefer to have this DLookup happen all the time, on rexord change etc.

Can I incorporate the DLookup into the Control Source of the Unbound Box...something like:

=DLookup ("CCompany", "Contacts", "CCode = """ & Me!CCode & """)

This doesn't work, it comes up blank. I'm assuming my syntax is wayward once again.

 

[noobSPX]

If you are using a combobox, you don't need any code at all

Assuming your combobox is called CCode and its rowsource is something like

SELECT CCode, CompanyName
FROM tblCompanies
ORDER BY CompanyName

Then in your unbound CompanyName textbox put the following into the controlsource

=CCode.Column(1)

That's it.

Note - for selecting the bound column, the first column is column 1, but when referring to the rowsource, the first column is 0 (don't ask me why, just a quirk of access)

This looked exactly what i needed but it changes the syntax to:

=[CCode]. [Column](1)

Then, in my form, it displays #Name? where I'd expect the CompanyName to be.

Columns are CCode and CCompany from table Contacts.

 

[Mile-O]

Listen to the other guys. I answered your question, they have provided a more robust solution.

Effectively, have a query be your RowSource for your combo box.

So, for example, select CCode and CCompany in your query (order CCompany as Ascending) and save the query. In your combo, set the RowSourceType to Table/Query and the RowSource to this new query.

Change the ColumnCount to 2.
Change the ColumnWidths to 0.

Now your CCode and CCompany fields are both in your combobox, but only the code will be visible.

Finally, in another textbox, change its ControlSource to be as the guys are saying.

=YourCombo.Column(1)

Whenever the combo is changed, the textbox will automatically 'populate' the company name.

 

[noobSPX]

Listen to the other guys. I answered your question, they have provided a more robust solution.

Effectively, have a query be your RowSource for your combo box.

So, for example, select CCode and CCompany in your query (order CCompany as Ascending) and save the query. In your combo, set the RowSourceType to Table/Query and the RowSource to this new query.

Change the ColumnCount to 2.
Change the ColumnWidths to 0.

Now your CCode and CCompany fields are both in your combobox, but only the code will be visible.

Finally, in another textbox, change its ControlSource to be as the guys are saying.

=YourCombo.Column(1)

Whenever the combo is changed, the textbox will automatically 'populate' the company name.

T
Agreed, this is PERFECT !!

I simply had to change my ControlSource on the unbound box to:

=[Combo31]. [Column](1)

It is doing exactly what I want and updating or displaying everywhere.

Thanks yet again, this forum is phenomenal !! Cheers all for seriously prompt and well explained solutIons, I'm on a steep learning curve but understanding these type of functions is key.

 

[Mile-O]

Here's one for your learning curve: get out of the habit of using default control names. Combo31 is meaningless. And you may come to the database in future, find a reference to it, and wonder what the hell is going on.

Give your controls names. Even better, give it a prefix and a name. I use cbo for comboboxes, txt for textbox, etc.

So, in this instance, a better, recognisable name for your combo would be cboCCity.

 

[namliam]

Here's one for your learning curve: get out of the habit of using default control names. Combo31 is meaningless. And you may come to the database in future, find a reference to it, and wonder what the hell is going on.

Give your controls names. Even better, give it a prefix and a name. I use cbo for comboboxes, txt for textbox, etc.

So, in this instance, a better, recognisable name for your combo would be cboCCity.

Started typing something very simular, then read your post ... +1