Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-11-2018, 04:57 AM   #1
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 110
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
pass a SQL to a subform

Hi, I built a lstTutor in a form and would like to list the selected tutor with its appointment history in sbfApptHistory (subform). I have no idea of creating fields for it with VBA codes and just created a blank subform.

tblTutor = tutor's table for peronsal particulars
tblTAppt = tutor's appointment table
tblTRank = tutor's staff rank

The tutors are added to lstTutor with a condition from fraOption.

I used a SQL below and Me.sbfApptHistory.Form.RecordSource = SQL

SQL = "SELECT tblTAppt.TRef, tblTAppt.UID, tblTAppt.ApptStart_Dt, tblTAppt.ApptStart_Dt, tblTAppt.ApptEnd_Dt, tblTAppt.AppointedBy, tblTAppt.JobPosition, tblTRank.HDesc " & _
"FROM (tblTAppt INNER JOIN tblTutor ON tblTAppt.TRef = tblTutor.TRef) INNER JOIN tblTRank ON tblTAppt.RankCode = tblTRank.ID " & _
"WHERE tblTAppt.TRef = " & lstTutor.Column(0) & " " & _
"ORDER BY tblTAppt.ApptEnd_Dt DESC"

However, I run into an error of "2467", "the expression you entered refers to an object that is closed or doesn't exist".

I don't know why. Welcome any idea on this.

NT100 is offline   Reply With Quote
Old 08-11-2018, 05:36 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,531
Thanks: 53
Thanked 1,869 Times in 1,819 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: pass a SQL to a subform

Here's a link with info on form, subform and subform control that may be helpful.

Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 08-11-2018, 10:17 AM   #3
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 411
Thanks: 6
Thanked 99 Times in 97 Posts
MajP will become famous soon enough
Re: pass a SQL to a subform

Quote:
just created a blank subform
You cannot just create a blank subform control without a source object. You need to build the form or query first. If you want this in datasheet view simply build the query definition and save the query. Use the query as the source object of the subform. Get rid of the where clause
Code:
"SELECT tblTAppt.TRef, tblTAppt.UID, tblTAppt.ApptStart_Dt, tblTAppt.ApptStart_Dt, tblTAppt.ApptEnd_Dt, tblTAppt.AppointedBy, tblTAppt.JobPosition, tblTRank.HDesc FROM (tblTAppt INNER JOIN tblTutor ON tblTAppt.TRef = tblTutor.TRef) INNER JOIN tblTRank ON tblTAppt.RankCode = tblTRank.ID ORDER BY tblTAppt.ApptEnd_Dt DESC"
Now link the subform to the listbox using the link criteria
Master Link Fields: [lstTutor]
Child Link Fields: [TRef]

Now when you make a selection in the listbox the subform will automatically filter.

MajP is online now   Reply With Quote
Old 08-12-2018, 02:24 PM   #4
NT100
Newly Registered User
 
Join Date: Jul 2017
Posts: 110
Thanks: 7
Thanked 0 Times in 0 Posts
NT100 is on a distinguished road
Re: pass a SQL to a subform

Thank you for the suggestions.

I managed to work out the pass of a SQL to a subform with the help of real examples from (Advanced Programming In Access 2013) Filtering Sub-Forms Using VBA https://www.youtube.com/watch?v=M1J1rHXR4JY&t=116s.

Thank you again.

NT100 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Help with SQL pass-through to subform businesshippieRH Modules & VBA 11 10-30-2014 11:41 AM
How to pass value from one Navigation Subform to another subform abzalali Forms 1 03-29-2014 11:35 PM
Can noone do this? Pass a value to a subform??? wjmorgan Forms 1 06-04-2004 02:19 PM
Pass Value to Subform crhodus Modules & VBA 6 04-05-2004 01:07 PM




All times are GMT -8. The time now is 06:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World