Forms- Subforms trouble

[Mounds]

So I now have 2 perfect queries. One pulls all the airfare for the order and sums it, the other does the same for other expenses. I can now get the totals calculated correctly, but when I try to put it on my main form it gives me trouble. Do I need to make another subform for totals?

[Robeen]

I'm sorry Mounds - I've lost track of where you are right now.

What you should have is:
1. A Query that gives you everything you need [including the AirfareTotal & the OtherExpenses] for your Main Form.
2. Your Main Table that will provide you with the Details [for your SubForm] of all your transactions.

Is that what you have?

[Mounds]

Its fine, I just am surprised how much I'm struggling with this really. I've been away from this too long and its quite frustrating just because I used to do just fine at all of this. Anyways, I uploaded it again so that you can see exactly what I have.
But entries get entered in the subform. I have 2 queries "airfare" and "jobexpenses." These select only the items in that category, for the active order, and sum the entries to give me a total. That much is working. However, whenever I try to get that value to be shown on my main form and saved in my main table i get errors. It may be some sort of circle/ loop function and thats why it is giving me an error. Not sure if I need to add some sort of on update/ before update, or ad another sub forum and have the queries results in there. Job ID 5 has useful data so I've been using that to try and check the numbers.

[Robeen]

Mounds,

I believe you are making this more complicated than it is.

First - I think you should keep your data entry form separate.

Second - your Main Form is still based on a Table - and not a Query.

In an earlier post I suggested:

One way to show all those Totals for a job is to write a query that uses a Group By clause.

Create a Query in Query Design View.
Drag all your fields into the query.
Under Job - type in a Job#.
Under Expense - Sort Ascending [this will put all similar expenses together].

If you run the query now - you will see all expenses for that Job# in order.

Go back to Design View and click on the Totals button on top. This will 'Group' all your fields in the query.

Under Cost -> where it says 'Group By' - click the down arrow and select 'Sum'.

Now - when you run your query, you will see one record for each type of Expense [Airfare, Food . . .] - and the total amount for each Expense.

If you follow this method and then create a 'Main' Form based on the Query described above - then you won't have to bother with doing any coding to get sub-totals etc onto your Form. The sub-totals will all have been done in the query and will be available as fields to put on your Form.

Your Sub Form could be based on the Table that has all the spending details.

When you go from Job to Job on your Main Form - you will see all the Detail for each Job on your Sub Form.

Right now - with the way you have designed your Main Form - in order to get totals into your Main Form - you will have to write VBA code to get the JobID off your Form, plug it into a SQL Statement that does your sub-totals for the Job, and then extract the sub-totals and put them into your text boxes . . .

[Mounds]

Okay I'll try and redo the forms. It felt like I was making harder then it needed to be. Thanks.

[Robeen]

Let me know if you need help/advice as you go along.

Try & keep it simple.

What you're trying to do is pretty straightforward so you should not have too much trouble.

If you feel you're not getting the help you need from me - post your problem again. Other very experienced people will look at it and you may make better progress!!

[Mounds]

Got it done. Thanks Robeen for all your help.

[Robeen]

Happy that I could help!