Dlookup stuck on first record

[Swillsy]

K guys this is driving me mad - the dlookup query is working fine... it will find details for the first record in the form. however it gets stuck on that one whe ni go through the records.
I have different dlookups working at the same time properly and changing as I go through records. however with the lookup of the addresses table it is getting stuck.
Whats wrong with it? I have looked at the code and preperties of the box but can't see where its gonig wrong.
Here is the code:

Private Sub Form_Current()
Forms!Applicantsdata1![Title] = DLookup("Title", "tblStudents_Current", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![town] = DLookup("Address_Line_2", "tblStudentAddresses_Current_All", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![town1] = DLookup("town", "tblStudentAddresses_Current_All", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![Mobile] = DLookup("mobile", "tblStudents_Current", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![knownas] = DLookup("Known_As", "tblStudents_Current", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![Course] = DLookup("Course_Title", "tblStudents_Courses_Current", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![HomePostcode] = DLookup("Postcode", "tblStudentAddresses_Current_All", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![Sex] = DLookup("Sex", "tblStudents_Current", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![DOB] = DLookup("DOB", "tblStudents_Current", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![Nation] = DLookup("NatDesc", "tblStudents_Current", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![Ethnicity] = DLookup("EO", "tblStudents_Current", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")

End Sub

 

[KenHigg]

A typical form will look like the following:

Forms!Applicantsdata1![Title] = DLookup("Title", "tblStudents_Current", "Person_Code ='" & Forms!Applicantsdata1!Person_codelbl &"'")

Where Person_codelbl is a text data type and the following if it is a numeric data type:

Forms!Applicantsdata1![Title] = DLookup("Title", "tblStudents_Current", "Person_Code =" & Forms!Applicantsdata1!Person_codelbl)

 

[DCrake]

The thinking behind you form is all well and good, however there seems to be a lot of requests being made to the same table(s).

A simple little trick is to add a combo box to the form that is hidden. This combo box gets it rowsource from the desired table using the correct criteria

Such As

Me.HiddenCombo.Rowsource = "Select Field1, Field2, Field3 From Table Where PK = " & FormID

Where Field1, Field2, etc are the fields you want to use.

Then use the HiddenCombo.Column property to obtain the desired data items on the form.

This way you are only making one request to the backend for the data instead of one request for each data item.

CodeMaster:

 

[boblarson]

You are loading those in the On Current event? Why not just bind the controls to a query?

 

[Swillsy]

Hi its only on oncurrent event because it was the one which worked for me.

I was struggling to put it as a query.

The problem isn;t with the dlookup statement as its returning the first record correctly. it just won't update.

I will to see if it is because there are too many lookups.

 

[boblarson]

Hi its only on oncurrent event because it was the one which worked for me.

I was struggling to put it as a query.

The problem isn;t with the dlookup statement as its returning the first record correctly. it just won't update..

It doesn't make sense to use DLookups like this. The performance of all of these DLookups will continue to lag and lag (even if you do get the syntax right) as you get more and more records. It is WAY slower than binding a form to a query.

When you say "I was struggling to put it as a query" what do you mean? What does "struggling" mean?

 

[Swillsy]

Hi Bob,

I did at one point put the control source as a query and it displayed the records however it wasn't allowing me to change any of them.

Thanks

Swillsy

 

[boblarson]

Then you have other issues at hand. DLookups are not going to let you update the information either, so what's the difference?

 

[NigelShaw]

hi swillsy,

I can't see why this cannot be a straight forward query? All of your dlookups are collecting data from the same table so why not a query? You could create your query based on the table and set the criteria to the current selection on your form of which is dictating the data request in the first place.

Can you post your form and related objects to give everyone a clear picture?

Regs

Nigel ( your nearby neighbour )

Ps. Are you at the local uni as your code example is very student based?

 

[Swillsy]

Hi Nigel,

sorry been busy enrollnig etc last few days will put up a dummy form and tables shortly.

Yes I jsut started at the uni on monday and my code is very amateur because I havent been taught any vb yet, so I have jsut been picking it up as I go along

 

[Swillsy]

Ok here is a dummy form attached. It is replicating the same problem I have been having with the worknig db.

The form will update the student info,course info but it wont change the addresses.

The tblStudents,tblStudentAddresses,tblStudentCourses are central databases which I cannot change.

 

[Swillsy]

Ok I'm an idiot
as you can see very clearly see the index changes in the Addresses table to Per_Person_Code instead of Person_Code
Thanks for looking anyway

*runs to dark corner*

 

[NigelShaw]

Forms!Applicantsdata1![town] = DLookup("Address_Line_2", "tblStudentAddresses_Current_All", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")
Forms!Applicantsdata1![town1] = DLookup("town", "tblStudentAddresses_Current_All", "Person_Code = (Forms!Applicantsdata1!Person_codelbl)")

Hi Swillsy,

i dont quite follow

as you can see very clearly see the index changes in the Addresses table to Per_Person_Code instead of Person_Code

if all of the data is related, why does the address part have a different indexID? surely it would be better to keep it related in the same table. unless of course the student has more than one address in the same town with the same phone numbers and the same postcode... otherwise, i cant see the point in having the particular address labels stored in a different table.


regs

Nigel

 

[Swillsy]

if all of the data is related, why does the address part have a different indexID? surely it would be better to keep it related in the same table. unless of course the student has more than one address in the same town with the same phone numbers and the same postcode... otherwise, i cant see the point in having the particular address labels stored in a different table.

Hi Nigel I think the admins here changed the index which is why it suddenly stopped working. the students can have 2 addresses on the system which is probably the reason why it has changed.

Swillsy