• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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
0
jfilmore33
Asked:
jfilmore33
  • 6
  • 3
  • 2
1 Solution
 
harfangCommented:
Hi,

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.

Cheers!
(°v°)
0
 
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.

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

http://www.dsnainc.com/images/DBasePic.jpg

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
harfangCommented:
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:

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

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
(°v°)
0
 
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.
0
 
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
(Slightly out of context there - sorry all, I didn't post for a long tiem after writing ;-)
0
 
jfilmore33Author Commented:
I would have to find a method for time to be entered having knowledge of what the orderID is
0
 
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?""

YES
0
 
jfilmore33Author Commented:
harfang,

Can you give a example?
0
 
harfangCommented:
> YES

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.

Cheers!
(°v°)
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now