Can you set a subform's record source to anything else beside a query?

[jpg250]

The query for my subfile record source is getting too complicated as it pulls data from a lot of tables. Is there a way I can link the subform to a Collection which I can build by using code rather than by query?

[June7]

Never heard of such a thing. Don't think so. Collection of what?

Since normally a form can do data/entry for only one table, RecordSource should not be so complicated. Why is it 'getting complicated' - why so many tables?

[jpg250]

I was thinking a Collection of a Class I have set up, which contains all the info from several tables for a specific job.
I have an application in which the main form has about 8 tabs, which represent the various stages of a job (enquiry, quote, contract to be done, contract sent, contract accepted, etc.). Each tab is a subform, for which a query pulls data from Job, Client, Address, Town, PostalCode, JobContract, JobSite, and several more tables. This is complicated even more by comboboxes on the main form to select data ranges , Client type, and job type.

Anyhow, it appears from your answer that it can't be done, so I'll have to think of something else. Thank you.

[CJ_London]

If your subform is a datasheet view (or you are happy for it to be so), you can just set the sourceobject of the subform control to the name of your query

e.g. query.myquery

[jpg250]

My query is exactly the problem - one of the queries currently is:
SELECT q_JobContract_ActJob.ContractJob, q_JobContract_ActJob.AJJobClosedDate AS [Closed Date], q_JobContract_ActJob.JobQuoteOptionNo AS Opt, q_JobContract_ActJob.JCTotalPrice AS [Total Price], q_JobContract_ActJob.JCSignedContractHeld AS Signed, q_JobContract_ActJob.AJPlannedEndDate AS [End Date], q_JobContract_ActJob.AJApprovalWCRef AS [WC Ref], q_ClientDtls.FullName AS Name, q_ClientDtls.CLMobile AS Mobile, q_ClientDtls.CLOtherPhone AS Phone, q_ClientDtls.CLEmailAdd AS [Email Address], q_siteAddress.GAStreetAddress AS [Site Address], q_siteAddress.TPTown AS Town, q_ClientDtls.CLType, q_JobContract_ActJob.ClientID, q_JobContract_ActJob.AJobID, q_JobContract_ActJob.AJTypeID, q_JobContract_ActJob.JobStageID, q_JobContract_ActJob.JCScopeOfWork AS [Scope of Work]
FROM q_ClientDtls, q_siteAddress, q_JobContract_ActJob
WHERE (((q_JobContract_ActJob.ClientID)=q_ClientDtls.Cli entID) And ((q_JobContract_ActJob.AJobID)=q_siteAddress.AJobI D) And ((q_JobContract_ActJob.JobStageID)=80) )
ORDER BY q_JobContract_ActJob.AJActualEndDate DESC , q_ClientDtls.FullName;

one of the queries used in this query (q_JobContract_ActJob) is:

SELECT ActJob.AJobID, ActJob.AJTypeID, ActJob.JobStageID, JobContract.AJobID AS ContractJob, JobContract.JobQuoteOptionNo, JobContract.JCTotalPrice, JobContract.JCScopeOfWork, ActJob.ClientID, ActJob.SiteAddressID, ActJob.AJPlannedStartDate, JobContract.JCSignedContractHeld, ActJob.AJPlannedEndDate, ActJob.AJApprovalWCRef, ActJob.AJActualEndDate, ActJob.AJJobClosedDate
FROM JobContract INNER JOIN ActJob ON (JobContract.JobQuoteOptionNo = ActJob.JobQuoteOptionNo) AND (JobContract.AJobID = ActJob.AJobID);




I know for a fact that this series of queries sometimes does not give the correct result, but to debug it is a nightmare. That's why I want to get away from queries.

[orange]

You are bombarding readers with SQL and How you have attempted to do something, which you indicate is not how you want to continue. I suggest you tell us in plain English, business terms --no Access, no SQL and no jargon -- what exactly you are trying to do. There may be options, but we can't determine that until we understand WHAT your business requirements are.

[jpg250]

Ok, sorry you didn't get my ideas. Here it is in plain english:

The application controls construction jobs from enquiry stage through job completion.

When the application starts, the user is presented with a screen called the control centre, which has tabs across the top which represent the different stages a job can have:
dropped, enquiry, being quoted, contract being drawn up, contract sent for approval, contract accepted, job scheduled, job active, and job completed.

When clicking on any tab, a list is shown of all jobs that are currently in that job stage. Also on top of the screen is a series of selections the user can make such as date, job type, client type - which further control which jobs are displayed in the list.

The lists are not input-capable. Next to each job in the list is a button, which either brings up a new screen with further details or an edit screen, depending on which tab was selected.

[June7]

I don't understand why need such complex queries. I presume control centre form is not bound. User enters/selects values and code opens appropriate form for record entry/edit. Form does entry/edit for 1 table.

[jpg250]

No, Control centre form is not bound.
The reason why the queries are complex is that for each list (tab) the job details displayed comes from several tables. The user needs to see all this information to identify which job to select.

That is the only part that's causing me problems: to present all the required information in each tab. I posted the SQL earlier to show how complex the queries are (for me, anyway. Maybe this is quite normal.)

[June7]

Well, can't get away from queries.

If you have queries based on queries based on queries as RowSource for listboxes, I am not surprised you are having difficulty.

Here is example of conventional search utility http://www.allenbrowne.com/ser-62.html

[orange]

jpg250,

I got your ideas but I had no idea where all the SQL and queries fit within a business context. I still don't quite understand why most fields in your query have an alias.

When a post identifies complexity as an evolving issue, the first flag that jumps up is --are the tables normalized? Not that normalization alone simplifies queries, but it's a good starting point for several issues.

**Can you post a copy of your tables and relationships (a screen capture of your relationships window)?

To paraphrase your comments:

You are in the construction business.  You manage construction jobs where each 
job goes through a number of defined stages -namely:

dropped
enquiry
being quoted
contract being drawn up
contract sent for approval
contract accepted
job scheduled
job active
job completed

Currently you have a form that, based on a selected stage, presents a read-only list
 of all jobs that are currently in that job stage. 
The list may be filtered/constrained by selecting parameters such as date, job type,
 client type. 

 Next to each job in the list is a button, which either brings up a new screen with
 further details or an edit screen, depending on which tab was selected.

Questions:
How does data get into the current system? You mentioned lists are not input-capable.
Can a job be dropped at any stage or only certain stages?
Who updates the job stage info?
Where do Customers and Job details get stored in relation to these "job management form(s)"?

[jpg250]

Thanks orange,

I really appreciate your help, but please don't spend too much time on this. I didn't want to get into this much detail, and I can get by with the way it is.


In answer to your questions:

How does data get into the current system? - there are separate forms for this, which the user can get to from either the main form or from other input forms.
My input forms are not bound either - so I have more control over what goes in.

Can a job be dropped at any stage or only certain stages? Can only be dropped before contract accepted.
Who updates the job stage info? The users do - with buttons. Some on tabs, others on input forms.
Where do Customers and Job details get stored in relation to these "job management form(s)"? Not sure what you mean. I have a backend where all the tables are.


this is the diagram:

[June7]

Arghh! Compound keys and possible circular relationships. No wonder you are going nuts! http://www.codeproject.com/Articles/...atabase-Design

[jpg250]

Thanks for your contributions June7. I'm almost sorry I started this post. Do you have any experience with tempoary tables? This was one of the options I considered : to create a temporary table for each of the tabs, load the data into it using code instead of query, bind it to the subform for the tab, and then delete the records again if another tab is selected. I then thought may run into problems doing this because of: 1. performance 2. bloating the database size 3. multiple users using the application at the same time.

(Maybe it's against the rules to ask a differen question at this stage. If so, I'm sorry.)

[June7]

Yes, I use temp tables for some complex rearrangement of data for output to reports. Not for any other purposes.

Multi-user database should be split. Temp tables would be in the frontend. Bloating should not be an issue.