Dlookup error

[simonI]

i have a form where the user has already enter there student details...

they then move onto the next form where they re-enter the student id and the other details appear in the 3 text boxes below..

the problem im encountering is that the Dlookup only seems to return values for the first record and even if it is on record 30

the code i used is below, can someone tell me where i have gone wrong or is there a better way

Private Sub StudentID_afterupdate() 'searches student table to ensure name is correct
Dim name As Variant
Dim surname As Variant
Dim degree As Variant

name = DLookup("[First Name]", "student table", "[StudentID] = '" & StudentID & "'")
surname = DLookup("[Surname]", "student table", "[StudentID] = '" & StudentID & "'")
degree = DLookup("[Degree Programme]", "student table", "[StudentID] = '" & StudentID & "'")

Text35 = name
Text37 = surname
Text39 = degree

End Sub

cheers once more

si

 

[ReAn]

the problem im encountering is that the Dlookup only seems to return values for the first record and even if it is on record 30

You have more than one record with the same StudentID?

 

[simonI]

no because the studentID is unique...

 

[Bat17]

You are searching on Student ID as a Text field, if it's Numeric remove the wrapping quotes

HTH

peter

 

[simonI]

its Alpha numeric in the form of A123456.... do i still need to remove the wrappings?

 

[ReAn]

its Alpha numeric in the form of A123456.... do i still need to remove the wrappings?

Im still not quite clear on the problem.

Do you have it like this?

Table:

Student ID: First Name: Surname: Degree Programme:
A123456 John Doe Somethin
.
.
.
.
.
A654321 Jane Doe Somethin else

And you use the above query where [Student ID] = "A654321" and the function would return:
A123456 John Doe Somethin

instead of

A654321 Jane Doe Somethin else


Or am i missing something?

 

[Bat17]

the code i used is below, can someone tell me where i have gone wrong or is there a better way

Yep, don't do it

Search this forum for "Normalisation" you should not be storing the same data twice anyway. just join your student table to the one behind the form using a query and it will pull out the details when you need them.

Peter

 

[modest]

better way is to use Recordsets. You're pulling data from the same record of the same table; you don't need to perform 3 separate queries to do this. Lookup DAO.Recordset

Additionally, make sure there are not more than one of the same StudentID; if there are dlookup will stop at the first and you may not get the data you want, or you will have to add another constraint

You may not want to place this in the after_update event

 

[Pat Hartman]

As Bat17 suggested, create a query that joins the two tables. Select the columns you need from each table. Save the query. Then change the RecordSource of the form to the query you just saved, get rid of all the code, and bind the controls for name, surname, and degree to the appropriate fields from the RecordSource. Be sure to set the locked properties for these controls to yes to prevent accidental updating.

Currently your name,surname, and degree controls are unbound. That is why they show the same value for every visible row. They will always show the value for whatever is the current record.

 

[simonI]

i think i did what you said (except i have kept the code as there is a fair bit) but no when the form opens i am getting a runtime messge of

"Cannot add record(s): join key of the table not in the recordset"

argh... please help

thanks again

 

[The Stoat]

Hi,

It would help if you could post a pic of the Table relationships. It sounds like you may have some normalization problems that need dealing with first.


TS

 

[simonI]

thanks for everyones help i think i have got it working!!!

cheers again

Si