By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,146 Members | 1,847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,146 IT Pros & Developers. It's quick & easy.

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

P: 44
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
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.
Jul 17 '07 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 10K+
P: 12,427
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.
Jul 17 '07 #2

P: 44
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.
Jul 17 '07 #3

Expert Mod 10K+
P: 12,427
Try taking out the requery.
Jul 17 '07 #4

P: 44
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!
Jul 17 '07 #5

Expert Mod 10K+
P: 12,427
The subform control doesn't have any fields listed in its master/child properties does it?
Jul 17 '07 #6

P: 44
The subform control doesn't have any fields listed in its master/child properties does it?
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!
Jul 17 '07 #7

Expert Mod 10K+
P: 12,427
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.
Jul 18 '07 #8

Expert Mod 10K+
P: 12,427
I'm running out of ideas now. A couple of last things to try before calling in reinforcements.

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM tblPupil WHERE idno=" & Me.lstFindPupil
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.
Jul 18 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.