Trouble showing main form and subform together.

Once I get this together, I'll be in pretty good shape...

I have a Main form called ORDERS and a subform called TIME.

In the ORDERSI have 2 date fields: txtStartDate and txt EndDate. IN the subform I have a date field and a few currency fields. The underlying query of the TIME subform. Has the following:

Between [Forms]![ORDERS]![txtstartdate] and [Forms]![ORDERS]![txtenddate]

This brings up all time spent in a table called time. Here's my main issue. This works fine and all but in only shows me subform results in the following way.

I want the main form in datasheet view and to show fields from the subform as if they were part of the main datasheet, not as a subdatasheet (So the user can see everything without having to hit the plus sign to see the other data
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


This doesn't seem like a doable design in Access.

You could produce a datasheet or continuous form based on some union query, but it's bound to look ungly because the main data and the subdata will have to share the same columns, much like in an excel sheet, but without the possible merging of cells. What's more, the data would  be read-only, naturally.

You could then use the build-in subdatasheet view, with a setting of "subdatasheet expanded" set to Yes, but in your case you can't use it. This is because the subdatasheet can only be linked through the link field mechanism, without any additional conditions based on the main table's row. However, if you redesign your tables to have a simple and straightforward way to link records of the main table and the linked table, this could be a solution.

The tempting idea of a continuous form with a subform fails also as this is not allowed.

So as you see, the answer is probably: can't be done.

jfilmore33Author Commented:
Ok, I thought that would be the outcome. Let's try it this way.

I've got a database consisting 2 tables of tblTIME and tblORDERS. I have a form over tblTIME and a form over tblORDERS.

TIME has fields called txtRate, txtTime, and txtDate. There is also a query on tblTIME that multiplies txtRate and txtTime.

ORDERS has it's own set of fields including 2 fields called txtStartDate and txtEndDate. This is how I want to show what has been entered into TIME.

ORDERS also has a CUSTOMERID and an ORDERID field. THe customerID is associated with a project and the OrderID is associated with each invoice sent to that client.

jfilmore33Author Commented:
It may help if you see a screenshot of the ORDERS form.

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

If the table tblTIME is dependant upon tblORDERS, it should also have an OrderID field, so that the tables can be linked meaningfully. Are you saying that in the current structure, records from tblTime are loosly related to just any record in tblOrders that happen to cover that time range?

It looks to me as if you need something like this:

lngOrderID -- order number, key field
lngCustomerID -- foreign key of table tblCustomers
datJobStart -- date field
datJobEnds -- date field

tblOrdersTimes -- details of the order
lngOrderID -- foreign key of tblOrders, also first key field
intLineSort -- line number, second key field
curRate -- currency
datDate -- date/time field
datTime -- date/time field

Does that make sense?
Good luck
jfilmore33Author Commented:
The issue with that is that the orderID is only estblished when a new invoice is made. But time is entered throughout the month, before the invoice is made.
Leigh PurvisDatabase DeveloperCommented:
Of course - data as you describe it would never be updateable in a form.
So could you not produce this in a report - where you have greater controls over different formatting on each detail record?
Showing/hiding controls altering section heights?

View it in Preview - print it out... whatever.
Leigh PurvisDatabase DeveloperCommented:
(Slightly out of context there - sorry all, I didn't post for a long tiem after writing ;-)
jfilmore33Author Commented:
I would have to find a method for time to be entered having knowledge of what the orderID is
jfilmore33Author Commented:
"Are you saying that in the current structure, records from tblTime are loosly related to just any record in tblOrders that happen to cover that time range?""

jfilmore33Author Commented:

Can you give a example?

Hmm. Then my structure will not work.

You basically accumulate, let's call them "items", before they are ordered. Then, at the end of each month, you retroactively "order" the items, and turn the whole thing into an invoice?

In that case, you need tblOrders (a priori), tblJobs (running) and tblInvoices (a posteriori). But let's forget that for now.

I can imagine the following scenario:

A main form Orders (Invoices, really), with one subform showing all associated items, and another subform or listbox with all items not yet associated to any order. When you write out a new invoice, you can then select those items pertaining and associate them with the invoice under construction.

Once you have a clear link between invoices and items, you can show a datasheet view with expanded subdatasheets. As long as there is no link, I see no way to acheive that.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.