dlookup not always working?

[mizsydney]

I apologize for this question in advance if it is painfully simple, but I have been clomping around trying to teach myself Access as I set up this DB.

I have two tables, one is a list of book numbers and appraiser assignments, the other is the table where the data is stored. (info from the claim form - taxpayer name, etc.)

info from the claim is entered onto the main form. I have a separate form to work just with the pending claims, where I can enter the dates assigned and appraiser assignments without paging through all the records.

when I enter the parcel ID#, I would like to compare the number against the appraiser list and automatically fill in the appraiser field. (right now, I look it up and enter it manually.)

complicating this a bit is the fact that I will need the ability to occasionally manually override the book assignment (sometimes they may trade claims with someone else, and I need to know who HAS the claim).

I have a non-visible field on my form that Trims the first three numbers of the parcel to compare with the book assignment table. at one point I got the lookup to work (find the correct appraiser) but that was not bound to my Appraiser field, so it wasn't saved to the table and I couldn't overwrite it. it would just appear correctly on the field on the form.

after I started trying to figure out how to add the override ability, it stopped looking up the appraiser.
if I put

=DLookUp("[senior]","[Appraiserlist]","[apn3] = [Appraiserlist]![booknumber] ")

as the control source the field looks up the appraiser and shows it on the screen. when I bind the control source to the appraiser field so it will save the info to the table, it doesn't look up the appraiser no matter where I put the formula - beforeupdate, afterupdate, etc.

(if I can EVER get this right, I will start trying to add the SPLIT books, where they are assigned certain pages instead of the entire book.)

I hope this makes sense. can anyone 'splain this to me in small words, or point me in the right direction? I am completely unfamiliar with code, and while I know where the "bound" "beforeevent" "afterupdate" properties are, I'm not positive how it all works. I get little pieces from reading the other posts, but I just can't put it together.

thank you in advance!

 

[PaulF]

if Numeric
=DLookUp("senior","Appraiserlist","[apn3] = " & Appraiserlist!booknumber)

If String


=DLookUp("senior","Appraiserlist","[apn3] = '" & Appraiserlist!booknumber & "'")

PaulF

 

[mizsydney]

thank you, PaulF! but where exactly do I place that code/formula?

I've figured out how to just show the appraiser, but I need to store it in the table.

any ideas welcome!

thanks again.

 

[mrkshpntf]

mizsydney,

You should enter the DLookup statement in the "afterupdate" property of the field on the form where you enter the data for which you want a value to be returned.

In you case, (if I am not mistaken) it is the 'appraiser' field.

When you place the cursor on the 'afterupdate' property of the field, you should see a small button with "..." (3 dots).

When you click on it, you will see the "choose builder" prompt, select "code builder" from that.

Once you do that, it will open the code window. Now enter the code.

First declare the variables with Dim statements and then type/paste the code

=DLookUp("senior","Appraiserlist","[apn3] = '" & Appraiserlist!booknumber & "'")

for example.
---------------------------------------
Dim strSenior As String

strAgentId = Nz(DLookup("[Senior", "Appraiserlist", "[apn3] = '" & Appraiserlist!booknumber & "'"))
Me.Senior = strSenior
------------------------------------------

You may want to make the s in senior uppercase as a better coding practice.

-- Backup your work before making changes, Just In Case!! --

All the Best!

mrkshpntf.

 

[mizsydney]

thanks so much for your help, mrkshpntf!

I try to remember the caps, but when I'm experimenting trying to get something to work it often seems to slow me down and/or I forget.

I think I must have misunderstood something somewhere:

I tried adding the code to the afterupdate on the appraiser field on my pending claims form. nothing happened (blank/empty field instead of senior appraiser name). I thought well, maybe it's not actually updating that field at this point, so I went to my data entry form and added the code directly to the Appraiser field afterupdate. still nothing happens. OK, these records are already entered so maybe there is no update. I tested it by adding a new record and still the field is empty.

can anyone tell me what I am doing wrong?

 

[mrkshpntf]

mizsydney,

Try my code:
-----------------------------
Dim strSenior As String

strAgentId = Nz(DLookup("[Senior", "Appraiserlist", "[apn3] = '" & Appraiserlist!booknumber & "'"))
Me.Senior = strSenior
----------------------------------

For some inexplicable reason, the DLookup statement doesn't work without the additional bells & whistles.

Also, ensure that the datatype of your fields is set to 'text'.

Hope this helps you.