Strange setup attaching 'Jobs' to 'Invoices' using forms/subforms

[noobSPX]

Ok I'm back.....partly your own fault for being too helpful previously. I'll try to explain this as best I can but feel free to ask for clarification.

Background:
I have JOBS which can be numerous per day.
JOBS table holds: JobNo, JobDate, CCode (Customer Code), JobDetails, JobPrice (lots more but this the gist of it).

I request an Order No from Customer before invoicing, and need to be able to select Jobs. The customer will send an Order No and a list of which Jobs it refers to (they may pick different jobs than I would, and I can only include the Jobs they say on my Invoice... one Invoice per Order No given.

I need to be able to tick certain jobs (I've added a Y/N field to the Jobs table to simply allow me to tick the ones I want). Once I tick the ones I want, I print a list of these for Customer, calling my report 'Order No Request'.

The customer then sends my list back, with an Order No for me to use.....but may drop some of my selections off.....this is ok, I can just untick them before proceeding.

I have a form to create Jobs, but this is just used to add jobs for ANY Customer as I get them.....

I thought I could now have a new form entitled 'Attach Jobs to Order' where I can enter a Customer Code, bringing up all of their current jobs ( which haven't been invoiced)...I can do this by using a Query as my Data Source for the form.

What I'm struggling with:

I can create the form ok, asking the user on open to choose the Customer, then running a query to bring the form up with only Open Jobs for the chosen Customer.....

I can then tick/untick the Y/N field (Attach to Order) as I need to.

Once I've settled which Jobs are to be attached.....is there an easy way to UPDATE all selected records, attaching the given Order No?

I don't want to have to type it into each record individually.

Next Step:

My INVOICES table will be using a Form with a Subform to bring up the Jobs so I can create an Invoice with multiple Jobs.

Problem here is, how do I create an Invoice in the Main Form, so that once I choose which Customer it refers to (I'd hoped a CCode Combo box), then make the Jobs Subform only display records relating to the CCode I choose?

I could just ask the user to Enter the CCode before entering the form, then select my form/subform records that way.....but it would be more time consuming for user...who may have numerous invoices for numerous customers each day.

1 Invoice will have 1 Order No and Multiple Jobs.


I hope I have explained myself ok here.....but any queries....feel free to ask.

Apologies for such a lengthy explanation and thanks for reading.

 

[jdraw]

I think it would be more helpful to readers if you told us WHAT exactly a Job is. Job is a pretty generic word that can mean a variety of things in different contexts.
Also if you could relate the pieces:
--Customer
--Job
--Invoice
in a data model it would help with context.

It seems to me you have "sort of" hinted at what you are trying to do, but have influenced that by HOW you have done some things or foresee their being done.
--

it refers to (I'd hoped a CCode Combo box),

--

then select my form/subform records that way..

Good luck with your project.

 

[noobSPX]

Hi and apologies, very new to all this.

I have a system where I enter 'Jobs' which relate to individual tasks done by staff members for our Customers. These Jobs can be simple tasks, which I hold a description and price for, then these are charged out to Customers via an Invoice.
An Invoice can have multiple Jobs (one invoice line item per Job, totalled at bottom of Invoice).

When I enter a number of Jobs on for a particular day, these can be for a number of Customers and each Customer will have to be invoiced separately.

Where I'm at now:

I created a Tabular Form, with one line per Job, data taken from a select query on Jobs table (I get Jobs only for the Customer I choose).

I have a Y/N field which means I can simply TICK the Jobs I want.

I created an Update Query, which I thought I could use to ask user to input an OrderNo and then simply update the 'ticked' Jobs in my form.

This seemed to go ok, then I got some sort of loop or error which screwed up my table data (only sample stuff so sorted again).

I think I'll continue down the Update Query path as it seems close lol

Thanks for looking, I'll post what happens as I work through it.

 

[jdraw]

You might want to work through this tutorial. It will be helpful for concepts and design of tables.

 

[noobSPX]

Thanks, i seem to have it working now

I created a tabular Form where I could tick the Jobs I wanted to select.
Then I created an Update Query to insert a user-entered OrderNo.
This is called by running a macro from a button, it runs the Update Query, then refreshes the form.

Nice

Thanks all once again, when i get this finished I'm gonna spend some time on tutorials etc.