List form not linking to detail form

[EHS]

Hi there
I have a form that lists people. Each field has a click event that opens a people detail form using docmd.openform "PeopleDetail", , ,"str(PeopleCode = " & str(Form!PeopleCode). The detail form opens but always displays the first people record. PeopleCode is an AutoNumber, hence the str(). All tables are linked. Can anyone help? I've used this technique in other forms and it works (even in the same database).
Thanks

 

[vbaInet]

Have a look at the following link for proper syntax:

http://baldyweb.com/wherecondition.htm

 

[EHS]

Thanks for that vbaInet. I tried "Str(PeopleCode) = '" & Str(Me.PeopleCode)", but I got the same result. If I don't use str() I get the error message 'Data type mismatch'.

 

[vbaInet]

The correct syntax is clearly explained in the link provided. Note that it doesn't use Str() and it explains what to use to wrap your criteria if it is Text or Date. I would advise you re-read that link.

 

[EHS]

Thanks again. I have read the link properly this time, and used 'DoCmd.OpenForm "People Details", , , "PeopleCode = " & Me.PeopleCode'. I didn't get the Data type mismatch error, but I didn't get the required record either. The record displayed was the (yet again) first record.

 

[vbaInet]

If you open the form on its own, does it display ALL records?

 

[EHS]

If I open the detail form on it's own, it does display all records.

 

[vbaInet]

Let me have a look at your db.

 

[EHS]

View attachment TestPupilCode.accdb

View attachment TestPupilData.accdb

I've produced cut down versions of the databases, with code to pull up the detail form only on the PupilCode field.

Thanks for this

 

[vbaInet]

I really don't want to start downloading two db's. Can you consolidate them.

 

[EHS]

View attachment TestPupilCode.accdb

Sorry about that. I've consolidated them into 1 database.

 

[vbaInet]

Your main problem was you had code in the Open event which was "dirtying" your form. I also made some changes too.

See attached.

 

[EHS]

Thank you very much. That has been a great help

 

[vbaInet]

You're welcome!