I am trying to set up a dtabase for someone who invoices by the month. Order details for each client need to have the order detail date on them. Most order details are repeated each month.

I have set something up where there is a 1 to many relationship for: clients > orders > order details

This is the tricky part for me:

1- When the user goes to the main form she should see the order for the current month and any associated order details.
2- Also, when the user creates a new order record the new record should display the next calender month automatically, or the current calendar month if this is a new customer without any existing orders.

I know it's a lot of information, but I'm sure this is a pretty common scenario. How hard can it be to set this up?

Thanks for your help,

Mark

The orders should be queried for a specific month, not all months.

The billing month and year might be specified on your form with combo boxes. Then those values used as the criteria on your orders subform.

(Please see first post for background)

Thanks for the reply, but . . . . . .

I am trying to avoid using seperating the date into month and year. I would prefer to 'pull' this information from a regular date field, thereby not limiting further report and form uses for these dates.

If the OrderDetails sub-form is taken from an underlying query, can i use an SQL Select Where statement to filter the OrderDetails date field for year and month? If so can anyone take a stab at the syntax?

Or perhaps i would be better of using VBA and event statements? What event would trigger the filtering of the OrderDetails? AfterUpdate or BeforeUpdate? As you can tell I'm a little confused . . . .

If there was a book out there that dealt extensively with date issues, i'm sure there would be a market for it (or is there one on the market already?)

Dazed and confused,

Mark