DLookup in Text Box

[Colleenm]

After many hours of frustration & trying to copy examples from web searching, I'm breaking down & asking for help. :banghead:

I am new to Access & have 2 weeks to recreate an application currently in an online internet app called Trackvia. Anyway, I know this is probably very simple.

I have a text field named Collector in a table (DebtorsT). I want to use it's value to look up a number in another table. If in the Control Source, I hard code the value of Collector it works. If I want to reference the Collector field it doesn't. My hard code that is working piece is:

=DLookUp("[Commission]","[ContractorsT]","FirstName = 'Roger'")

 

[static]

I assume 'Roger' is the hard coded value.

Without knowing where the 'Roger' value is coming from* there's not much to say.

Except that DLookup is best avoided. Base your form's recordset on a query and join the table fields to get the values you need.

*edit + Before you tell me that the value comes from the DebtorsT table...
The value needs to be accessible from a control on the/a form.

And btw, welcome to the forum

 

[Colleenm]

Thank you for the prompt response. "Roger" is an example of the value in the Collector field in the Debtors table.

I'll go lookup a youtube video on joining tables & try your solution.

 

[Colleenm]

I watched 2 videos on joining table & am not understanding how that can apply to my form. My form current has information on a debtor. Then I have 3 subforms which track actions, addresses & payments.

When the "Collector" field was a number, the dblookup worked. I'd create a new debtor, use a combobox to select my Collector's name & it's store the number for that collector in the collector field. I could then use a dblookup to get the Collector's commission from the Collector's table. The problem is I have 8300 records coming in from the old application & it's "Collector" field the value is stored as a text value, (ie "Roger").

Is it just totally wrong to do a dblookup w/ a text value?

I'm truly sorry I don't have more time to better learn Access properly before beginning on my project. Our vendor gave us a month's notice of a $300 monthly price increase. I spent 2 weeks looking at other online db's before deciding on Access because we only have 2 users & don't need to access it to be stored w/ an online db.

 

[JHB]

Post your database with some sample data, zip it because you haven't post 10 post yet.

 

[moke123]

When the "Collector" field was a number, the dblookup worked

The problem is I have 8300 records coming in from the old application & it's "Collector" field the value is stored as a text value, (ie "Roger").

What changed?
Just because it happens so often, I would check and make sure that the collector field is not a lookup field in the table.

 

[Colleenm]

What changed? When I first created the combo box to select the Collector, it supplied the Collector field w/ a numeric value. My dblookup worked when creating a new record. Then I noticed when I opened up an existing record that had been imported from the old app, the Collector fields had text values (ie "Roger"). Since there are 8300 records, I changed my combo box to instead fill the Collector field w/ the text value so old & new records would match.

 

[Colleenm]

JHB, I'll create a copy of my db w/ sample date later today. Thank you for your time.

 

[Colleenm]

Here is my sample database... The Debtorf form has the CollCommLookup field w/ the text label "Collector Comm" that is driving me crazy. It's where I'm trying to do the dlookup... My last try was using a query.

 

[Colleenm]

JHB, I've uploaded my sample database.

 

[JHB]

Oh yeah - the typical errors:
You've:

=DLookUp("[Commission]","[qryContractorsCommissionQuery]","FirstName='" & [Forms]![DebtorF]![Collector] & "'")

1. You don't have a query called "qryContractorsCommissionQuery", it is called "ContractorsCommissionQuery"
2. You don't have a control called "Collector" it is called "Combo61".
3. You don't need the "[Forms]![DebtorF]!"
So replace what you've with the below:

=DLookUp("[Commission]","[ContractorsCommissionQuery]","[FirstName]='" & [Combo61] & "'")

You can get the value direct from the table, no need for a query:

=DLookUp("[Commission]","[[B][COLOR=Red]ContractorsT[/COLOR][/B]]","FirstName='" & [Combo61] & "'")

Database attached:

 

[Colleenm]

JHB, you have made my week, my weekend & my month... Thank you! I had tried using the table & then switched to a query out of trying so many things. I need to go read up on the Combo box. My understanding was that it passed it's selection to the "Collector" field. Obviously, my understanding is wrong. Thank you again for your time.

 

[JHB]

JHB, you have made my week, my weekend & my month... Thank you!

You're welcome, good luck with your project!
Only one thing which could help you in the future, check and recheck how things (controls/queries/tables) are spelled.

 

[Amlitnod]

Thank you JHB. post saved me 'more' hours. I got busted on syntax. Stupid single quotes hiding beside doubles... lol