Help with DLookUp

[jparks2010]

I am trying to work with the DLookUp function and have a similar problem as a previous post http://www.access-programmers.co.uk/forums/showthread.php?p=954468. However my problem is the timing. By using the following code:

DLookUp("[Student ID Num]","[Current Students]","[Current Students]![Student name] = [Student name].Text")

I am unable to get DLookUp to pull the information with any consistency. Sometimes it will, sometimes it wont. Any suggestions on how to add consistency and get this working properly.

 

[pbaldy]

I would use the syntax here:

http://www.mvps.org/access/general/gen0018.htm

You probably should not use the .Text property of the control.

 

[jparks2010]

I tried to leave out the .Text property and it came up saying error. When I reformatted it, no results came up. Is it possible I still have the formatting wrong. I am working with Access 2007.

 

[pbaldy]

Can tell, since I can't see your revised format.

What error do you get leaving off .Text? That is a form control I assume?

 

[jparks2010]

K so here is the formatting following the guidelines with the .Text property

DLookUp("[Student ID Num]","[Current Students]","[Current Students]![Student name] = '[Student name].Text'")

I am not entirely sure what the error was. But at one point it displayed #error! without much explanation. If I find a better explanation I will post it promptly.

 

[pbaldy]

Does this work?

DLookUp("[Student ID Num]","[Current Students]","[Student name] = '" & [Student name] & "'")

 

[jparks2010]

Thats all it took. Thanks. I do have one more question.

How do I make it lookup the value in the Current Students table and then write the value to the Daily Contact Info table. Right now it is not recording the value just displaying it on the form. Can you tell me what property needs to be set for it to do that with DLookUp. Thanks

 

[pbaldy]

Generally you would only store the key value in related tables, so I would expect you to store student number in the contacts table, not the name. You'd get the other with a query. If you really need to, you'd use code, like here:

http://allenbrowne.com/casu-14.html

 

[jparks2010]

hey i am just a little confused as to how that would work for me please explain. What is the syntax used.

 

[pbaldy]

Me.BoundTextboxName = DLookup(...)

 

[jparks2010]

The code I have looks like this

Private Sub Student_ID_AfterUpdate()
    Me.Student_ID = DLookup("[Student ID Num]", "[Current Students]", "[Student name] = '" & [Student name] & "'")
End Sub

However it doesnt update the table itself like I need. Is this what you were refering to or something totally different?!

 

[pbaldy]

Since your criteria is based on the name, I'd expect that code in the after update event of the student name textbox. Presumably nothing is being entered by the user in the student ID, so that event would never fire.

 

[jparks2010]

just tried it. same result. I was wondering if that is still pointing to the form. is there a way to directly point it to the table.

 

[pbaldy]

Well, the textbox for Student_ID should have a control source pointing to that field in the table.

 

[jparks2010]

it does that is why I am unsure why the code doesnt update the field in the destination table rather than leave it blank.

 

[jparks2010]

is there a way to use the expression DLookUp on the table itself rather than the form

 

[pbaldy]

No; as I said, you don't normally store related data like that. Can you post the db?

 

[jparks2010]

I have strict orders not to post it because the information is not public. It is confidential information. So I do not believe posting the DB is something that I can do. Sorry!

 

[pbaldy]

I understand, and you certainly shouldn't post anything with confidential info in it. How about a sample with just the form and some test data?

 

[jparks2010]

here is a link to the sample database. It is simplified for obvious reasons. http://studentweb.stcloudstate.edu/paja0901/testbed.zip.