We help IT Professionals succeed at work.

Trouble showing main form and subform together.

jfilmore33 asked
Medium Priority
Last Modified: 2008-02-26
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
Watch Question


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.



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.


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


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


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 Developer

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 Developer

(Slightly out of context there - sorry all, I didn't post for a long tiem after writing ;-)


I would have to find a method for time to be entered having knowledge of what the orderID is


"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?""




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.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.