Invoicing for a job help

[antheana]

Hi there,

NeoPa and mccarthy have helped me with some table design advice previously, but I made a bit of a boo boo. It is with regards to invoicing for a job.

tblJobs
tblInvoices

Now, I have a form called frmJobs that allows me to display everything related to a job e.g.

JobID
JobRef
...
EmployeeID
...
ContractID
ClientContact
...
VenueID
,,,

etc - now the problem I initially had was my query - i had a qryJobs on my frmJobs and a qryInvoices (which extracted job information) as a subform on my frmJobs, but when I tried to add the invoice details e.g. invoice date etc, it would ask me for the JobID etc - but I wanted this information to fill automatically based on the job JUST created on the job form - but couldnt work out how.

SO, me being me, I decided to remove the foreign job info from the invoices table and put the InvoiceID in the jobs table as a lookup and added the remaining invoice info to my qryJobs (which populates the frmJobs)

*are u still with me - even I'm confused!*

Now I have somehow messed up everything - my form has locked and wont allow me to enter any new records let alone check if the invoice bit works, none of my expressions work e.g. calculations/displaying first name and surname together etc.

Is there any way I can correct this? Was it a good idea to put an InvoiceID in the tblJobs? I want to be able to enter the job details as planned, then when the job has finished, enter the invoice number, date, payment terms etc and calculate the totals...

Sorry, I know this is a bit heavy for xmas eve!

Any help would be appreciated and Merry Christmas to you all!

[nico5038]

Hmm, I guess you need first to establish the way your application will have to work.
I can imagine that a Job is exceuted by multiple employees and only one invoice needs to be created.
But you also need to check or one Job could have multiple invoices.

Knowing that you can desing the table structure and let's assume that a Job has one Invoice and that you want to display on the frmJob form the employee(s) and the invoice.
In such a case just use a mainform for the Job and place for the employee(s) and the invoice(s) a subform that's linked to the frmJob's ID.

Getting the idea ?

Nic;o)

[antheana]

Hi there,

Sorry about the delay, I meant to reply sooner. Thanks for your advice. One job can be assigned to one person and one invoice will be sent for that job. I've now expanded things a little now anyway, managed to get the invoicing working (I put the jobID in the Invoice table) and have used this site to work out how to display an invoice for the current record - gotta love the scripts!

More problems are on the way though haha

Thanks to you and everyone's help. I'm learning honest :)

Anthea