MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

Great thanks to all the helpful responses I've read! Recently using MS Access after a lengthy (7-10 years) away from the tool. I apologize for posting a question that for all intents & purposes appears to have been answered several times. Unfortunately, I've tried nearly a dozen approaches that seem to work for others on this board with similar (nearly identical in some cases) problems.

I would like to navigate through records on a subform based on the selection from a listbox. Id prefer to put the listbox is on the subform, but I've had the most progress putting it on the Main form.

Mainform data tblMaster (with tblMaster.idno as key)
Subform Data tblPupil (with tblPupil.idno as key and frnKeyMaster as frnKey to Master)

On the frmMaster, I have a listbox whose recordsource is a query of the tblPupil table where tblpupil.frnKeyMaster = frmMaster.idno

On UPdate of that listbox, I have this code:

Private Sub lstFindPupil_AfterUpdate()
Dim strSQL As String
strSQL = ""
strSQL = "SELECT tblPupil.* FROM tblPupil WHERE (((tblPupil.idno)=" & lstFindPupil & "));"
MsgBox strSQL
Me.subfrmPupils.Form.RecordSource = strSQL
Me.subfrmPupils.Form.Requery
End Sub

The subform requeries to a "new record", which is clearly not the desired result. further, the new record does not grab the IDNO from the frmMaster (which works when just adding records to the subfrm conventionally).

Thanks in advance for any new suggestions.

re: MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

1) Is idno formatted as a number? The code implies as much.
2) Is the list box bound on the correct field? i.e. the id field that you are trying to find.

re: MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

Rabbit, thanks for your quick response!

Yes, I went in to confirm that the Listbox is bound by "column 1" which is tblPupil.idno in the SQL that returns the values from the listbox "lstFindPupil".

idno is an Autonumber, which seems to have all the attributes of a Long Integer.

I know this one has got to be simple. I just can't figure what I'm doing wrong.

re: MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

Try taking out the requery.

re: MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

Unfortunately, that doesn't change the response.

One thing is interesting, the change in the recordsource seems to work only for the record that is the first record in the table underlying the subform and the listbox. Whenever I update the listbox to create "tblpupil.idno = 1", the navigation to that record works perfectly! It would be great if I had a 1 record table! LOL!

Thanks for taking a look!

re: MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

The subform control doesn't have any fields listed in its master/child properties does it?

re: MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

It did. I took them out and I'm still getting similar results. Are there rules about when you should and should not update those master/child properties?

thanks again for your willingness to share your knowledge!

re: MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

You use the master/child fields when you want to link fields between a main form and a subform. This means that you can only see records where the field match and the fields get auto-filled for you.

re: MS Access 2003 - Changing a Subform Based on listbox selection in Mainform

I'm running out of ideas now. A couple of last things to try before calling in reinforcements.

Try:
If that doesn't work,

1) Compile your code and see if that brings up anything.
2) Check your references and see if you are missing any.