Quote to Job

[dinsey90]

Hi,



I need to add a button to my view quote page that will create a new record in a Job table with a new job number & carry over the data, but also put the newly generated job number in to the quote table as below and carry the quote number over to the job table also. I also need the quote form to show the full quote number. Currently it only shows the last 2 digits. I have included images below to show the fields in my tables.




Many thanks
J

[June7]

The Quote_Num must have an input mask set that is not saving the Q 70 characters. Will have to apply the same mask in the textbox.

What is the relationship of this data - 1 job to 1 quote?

Advise no spaces or punctuation/special characters (underscore only exception) in naming convention.

[dinsey90]

Thanks June, I've taken out the spaces and put in underscores instead of spaces and forward slashes. I entered the input mask but it is only putting in the Q but not the leading zeros that i need? I think it may be because i have it as an autonumber?

1 quote if going a head with will have one job number but one job could have multiple quotes assigned to it over time.

Thanks for your time and help
J

[June7]

Can't do InputMask on autonumber field so I don't know how table is displaying with prefix. Can be set in textbox. However, I can't get the leading zero either. Never tried to do InputMask on autonumber field. Autonumber field not supposed to have meaning to users, usually not even displayed.

Sounds like 1-to-many relationship. Conventional arrangement for data entry/edit would be a form/subform.

[dinsey90]

The reason i used an autonumber was because i need a new number generated when a new record/quote is being made, i didnt know how to do this with a text box?

Yes a 1 to many relationship would be right. I need to get the code behind 2 buttons that would sit on the view quotes form. One that would be quote one, and that one generate a new job number and take the data from the quote and add it to the job table. Then a 2nd button that would be ‘assign to existin job number’ which would then allow me to oick an existing job number and add the newly won quote to it?

[June7]

I don't understand this process. Why would a quote be created before job? If job is the 'parent' record, it should be created first. That's what a form/subform enables.

Alternatively, select job from a combobox and if job not already existing, code in combobox NotInList event can facilitate creating new job record.

[dinsey90]

A quote gets created first, then if the client agrees the quote it gets coverted to a job. Quote is the parent. I need a button that would convert a quote to a job, generating a job number, and a 2nd button that would add a quote to an existin job number if necessary.

[June7]

In a 1-to-many relationship, the 1 side is 'parent' or 'lookup' table. You said one job can have multiple quotes.

What do you mean by 'add a quote to an existing job number'? You mean select a job to associate with quote, saving job ID in quote record?

[dinsey90]

Sorry yep thats what id need. If a quote gets accepted u would have an option to create a brand new job record for it or another button that would pull up all current job numbers and allow u to assign it to one of them. Im not sure how to go about it at all to be honest.

[June7]

That's why I suggest a combobox that lists jobs.

[dinsey90]

Thanks June I'll try that, how do i get it to put the data from the quote form in to the job table with a new job number if they select NotinList? thanks

[June7]

Common approach is VBA code in NotInList event. http://www.blueclaw-db.com/access_no...ed_example.htm

Do you want to prevent a job being selected for multiple quotes?

[dinsey90]

Thanks for your help June, i dont think the NotInList will work for this though, i probably haven't explained it very well. Processes outlined below
Client asks us for a quote
There will be 2 types of quotes,
1. a quote for a brand new build
2. a quote for an adjustment or revision on an existing job number
Steps for quote 1:
We go in to the database and generate a new quote with a quote number
When the client receives the quote they will either reject it or proceed with it.
If they proceed I would like to have a button that will then generate a Job number and populate the job register table with the details from the quote.
Steps for quote 2:
We go in to the database and generate a new quote with a quote number
When the client receives the quote they will either reject it or proceed with it.
If they proceed I would like to have a button that will pop up a list of current job numbers and allow the user to select one to allocate it too. it will then add a new quote record under that existing job number.

the reason the NotInList might not work for me is because the drop down list would have to be on the client name, however we may have one client who has 5 jobs open with us. so typing in the client name and being told it is not in the list may not work for us as we may have one client who has several different jobs that would show in the combo box when typed in.

sorry if im being stupid or if i have misunderstood what you meant.

J

[June7]

For option 2, wouldn't the quote be associated with a job at the time the quote is created? Isn't that more of a job order change?

As for the combobox, perhaps cascading comboboxes would be useful. Select client in first combobox and if found, offer associated jobs in second combobox. If not found then the NotInList event could be executed for either.

Pretty much anything can be done with enough code. Have to get data entities and relationships defined first.