Having trouble setting recordsource for subform from SQL 1

[TeresePamela]

I'm using Access 2000.
My users enter one of four values via a textbox, the entered value is found in the "many" table and the linking field is stored to a variable I call FoundPNR, The main form displays the data from the "many" side of the one-to-many relationship in a listbox. I want the sub-form to display the data for the related record from the "one" side. I'm trying to do this by setting the subform's recordsource to the value of an SQL Statement. I'v tried so many different ways and receive error messages no matter what. Below is a section of my code. Can anyone tell me where I'm going wrong?
XREF_01 is the "one" side table. XREF_03 is the "many" side table.

strSQL = "SELECT XREF_01.PNR, XREF_01.PEC, XREF_01.PPC, XREF_01.TITLE FROM XREF_01 " & _
" WHERE XREF_01.PNR like '" & FoundPNR & "*';"

Forms![FrmMain]![FrmFamily].RecordSource = strSQL
Forms![FrmMain]![FrmFamily].Visible = True


As always any help will be greatly appreciated,
Thanks
Pam

 

[PHV]

receive error messages
Any chance you could post the error messages ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Hard to tell where you go wrong, when there's no errormessage to play with, then the guessing will have to take into consideration all possibilities...

Here are some:
* use the .Form keyword when addressing subform properties
[tt]Forms![FrmMain]![FrmFamily].Form.RecordSource = strSQL[/tt]
* are you using the subform control name (vs the name of the subform if they should differ)
* is the lookup field text? If numeric, remove the single quotes, and you'd probably need to remove the Like operator too

Roy-Vidar

 

[TeresePamela]

Thanks for the speedy response PHV and Roy-Vidar.

It was the lack of the Form Keyword that was causing my problem. I can't tell you how grateful I am for your help. I spent most of the weekend pouring over my three ACCESS books to no avail, tried "thinking it through" and experimenting - no good. The on-line help needed repairing which required a call to IT since we don't have access to the software (don't get me started).

Thanks again - the help here is amazing.

Pam