Call up Module Function

[EraserveAP]

ah I see now, I see you have the append query running the Module itself so when the button is click it does it all.

Yes, I tried to stay true to the original function you posted.

 

[coolcatkelso]

Cool m8 cheers

ok, Just copied the module to my DB and all the querys you done.

I went to the Quotes page I have, created the button to execute, edited the code behind to accept the bound field - WorkorderID instead of the txtworkorder

I click on Exxcute, and I get a Variable not define - WorkorderID?

The only thing in Quotes that holds a WorkorderID field is the subform for Quote materials
________
Expert insurance

 

[coolcatkelso]

again lol its ok, I found it

I had forgot to add the WorkorderID to the Quotes form in design view

Got it working now

However, still have same problem about showing the Subform (Quote Materials) into the Workorder, Workorder Materials?
________
Blonde goddess

 

[coolcatkelso]

ok got it going, I just had to click refresh to show the subform info

Only problem I got now, is when the Quote has been sent to the workorder, and the new workorderID has been created. And I want to do another Quote as a workorder, I get the SAME WorkorderID.. Always seems to be workorderID 64 now, and I haven't changed any of your coding
________
MERCEDES-BENZ O530 CITARO

 

[coolcatkelso]

Can you manage to add this code into the Module somewhere

INSERT INTO [Workorder Materials] ( QuoteID, MaterialID, Quantity, UnitPrice, WorkorderID )
SELECT [Quote Material].QuoteID, [Quote Material].MaterialID, [Quote Material].Quantity, [Quote Material].UnitPrice, [Quote Material].WorkorderID
FROM [Quote Material]
WHERE ((([Quote Material].QuoteID)=[Forms]![Quotes]![QuoteID]));


AND

INSERT INTO [Workorder Labour] ( QuoteID, EmployeeID, BillableHours, BillingRate, Comments, WorkorderID )
SELECT [Quotes Labour].QuoteID, [Quotes Labour].EmployeeID, [Quotes Labour].BillableHours, [Quotes Labour].BillingRate, [Quotes Labour].Comments, [Quotes Labour].WorkorderID
FROM [Quotes Labour]
WHERE ((([Quotes Labour].QuoteID)=[Forms]![Quotes]![QuoteID]));

I think this would allow the EXECUTE button to add everything over to the workorder on a single click
________
Pre-paid legal services forums

 

[coolcatkelso]

Also, is there a way the code could be fixed, so that it won't add the workorder if the button has been click previously. For example, if the customer double clicked the Execute button, it would create 2 identical workorders?

I would imagine somewhere there would be an IF workorderID then blah blah lol

I may also ask for help with a sum command
________
E CIGARETTE SHOP

 

[EraserveAP]

Can you manage to add this code into the Module somewhere
....
I think this would allow the EXECUTE button to add everything over to the workorder on a single click

I will look at it when I get to work again tomorrow.

 

[EraserveAP]

Also, is there a way the code could be fixed, so that it won't add the workorder if the button has been click previously. For example, if the customer double clicked the Execute button, it would create 2 identical workorders?

I would imagine somewhere there would be an IF workorderID then blah blah lol

Certainly.

I may also ask for help with a sum command

We shall see.

 

[EraserveAP]

I have added your two appends and a brief timed disable Button code to the new version. Essentially the button is disabled after it is clicked, then after a short period in a do loop it re-enables itself thus preventing un-intentional dblClicks. It may not be the slickest code but it seems functional for your purpose.

Take a look at the Module and the Button and see if that will work for you.

 

[coolcatkelso]

WOW 3 weeks off atempts on this and you got it pinned in 2 days lol Your a genius

Seems to be working perfectly.

That button will do the trick nicely. I think I will add in a check field in the quotes tho, so once a Workorder has been created by the quote, a check could be placed, letting the customer know that the workorder already has been created?

Do you think the code for that would be difficult? Like, click button, select YES in YEs/No box
________
Expert Insurance

 

[coolcatkelso]

Hmm after creating a few Quotes with the new module I run into a small problem

It appears that when in the Quote form, I have to Click on Create Workorder button before I start to get the WorkorderID. Then I have to enter the Materials in sub. I exit and goto workorders, and its created the workorder and new id but no materials. I have to go back to Quotes and Create the Workorder AGAIN, then exit, refresh and the Materials are now in the Workorder, but its created another workorder with the Next ID but no sub

I'll keep playing about a little and see if I can figure out why
________
First vaporizer

 

[EraserveAP]

Hmm after creating a few Quotes with the new module I run into a small problem

It appears that when in the Quote form, I have to Click on Create Workorder button before I start to get the WorkorderID.

Yes, because when you append to the WorkOrder table it creates the new WorkorderID at that time. You would have to move the code that grabs the new WorkorderID up before the second and third Appends to be able to have the new WorkorderID for use with them. I did not make the Second two appends with the ability to use the WorkorderID as a variable.
That was an oversight on my part. However you should have the information you need to make needed adjustments.

 

[coolcatkelso]

***Update***

I found a quock fix to the problem just now. I created another button next to Create Workorder and called it Update to Workorder.

I did it as a Macro, and had it add in the Appends for Materials & Labour and had a refresh too.

appears to be working now..

I now, fill out the Basic Quote details, then click Create Workorder, Fillout the Materials & Labour needed, and then click Update to Workorder, and it works perfectly on that.

I did notice that with the changes you made to the Module with the new Insert INTO, it didn't work for the new part. I created the workorder, but still didn't carry over the Subforms
________
Fetish Porn

 

[EraserveAP]

I created the workorder, but still didn't carry over the Subforms

You will need to insert code to carry over the details to the subforms. In the ShowIdentity function you can re-use some of the code for this purpose or not.

If you do not want to adapt a new function you should be able to use the LstID variable prety much anywhere the WorkOrderID is needed prior to clearing it, such as in a Select query to grab any needed details from the record in which it resides.

You could have the following kick off as soon as the WorkOrderID is created.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Some_ Variable As String
‘You would change the String to whatever you need, Like Long for WorkOrderID and String for a name ect…
‘The only way I know of to use a variable in other forms is to make it Global in a module as I made LstID
‘So instead of Dim Some_Variable in the current Procedure you would use Global Some_Variable in a module
‘Note that in using Global Variables you need to stay aware of their values and useage in your code.

Set rs = db.OpenRecordset("SELECT WorkOrders.* FROM WorkOrders WHERE (((WorkOrders.WorkorderID)=" & LstID & "));")

‘You can return a field value with this:
‘You can add more Variables to capture more field values as needed
If Not IsNull(rs.Fields("FieldName ")) Then
'Set our Variable equal to the desired Field
Some_ Variable = rs.Fields("FieldName")
Else
Some_Variable = vbNullString ’or 0 or whatever you need it to in line with its data type
End If

 

[coolcatkelso]

Brill cheers m8, can't thank you enough for helping with this one, Your a star

the only last problem I have now with the DB, Is I want to create a field on Customers, to show the total unpaid balance on all workorders for that customer.

But because the field is a calculated field, I cant seem to get a Sum around it, or a Dsum

Could you have a look if you have time?

The form in question would be Workorders by Customer Subform (this is where all the calcs are done)
________
Buy e cigs

 

[EraserveAP]

Brill cheers m8, can't thank you enough for helping with this one, Your a star

I am glad I was able to help.

But because the field is a calculated field, I cant seem to get a Sum around it, or a Dsum

A calculated field calculated on the fly? For example adding and subtracting individual transactions based on a customer?
If so my suggestion would be to make an update to a new field or perhaps a new table and field that includes a customers Record number and Balance. Essentially each new transaction would update the balance. From this you would not then have to calculate sums on a field calculated on the fly, you just pull a customer and grab his or her balance from a table. To me that would be easier on the database if not easier all around.

To make this work, if you do not have something like it already, you would have to pull each customer and their transactions into a temporary table. From there you could then use a make table query and sum the customers and their transactions to make the new balance table. Once you have the balance table you no longer need the Temp table and can dispose of it.
After that you just need to make some code add to the balance on purchases and subtract from it on payments.
This may not be the best solution but it should work.


If the above is all way off base then please clarify your intent.

 

[coolcatkelso]

Hiya m8

The calculation is already done is general. I just need to be able to show the sum on the customer page

The current AmountDue that is being calculated per workorder is control source -
=Forms![Workorders by Customer]![Workorders by Customer Subform].Form![Workorder Total]-nz([Total Payments])

I created a new TotalDue in the customer table, and want to run a Sum command for all workorders by that customer

Have no idea where to start.

Again, its currently adding up the total amountdue per workorder, and as you can see in the DB it the main form displays all the workorders on the amountdue individually per customer, so I thought it be a simple ie. (=sum Forms![Workorders by Customer]![Workorders by Customer Subform].Form![Workorder Total]-nz([Total Payments])

but no, I just get #Error

Hopefully I've explained better this time
________
Uhwh Warehouse

 

[EraserveAP]

Hiya m8

The calculation is already done is general. I just need to be able to show the sum on the customer page

The current AmountDue that is being calculated per workorder is control source -
=Forms![Workorders by Customer]![Workorders by Customer Subform].Form![Workorder Total]-nz([Total Payments])

I created a new TotalDue in the customer table, and want to run a Sum command for all workorders by that customer

Have no idea where to start.

Again, its currently adding up the total amountdue per workorder, and as you can see in the DB it the main form displays all the workorders on the amountdue individually per customer, so I thought it be a simple ie. (=sum Forms![Workorders by Customer]![Workorders by Customer Subform].Form![Workorder Total]-nz([Total Payments])

but no, I just get #Error

Hopefully I've explained better this time

As I understand it you are not able to Sum a calculated field like yours in the way you want. http://support.microsoft.com/kb/207763
If this is in error then please let a more experienced member correct me.


This gives you a few choices:

You can repeat the calculations for the calculated fields and then Sum them all in one. You should be able to accomplish this in much the manner as you have the previous ones.

You can do it through VBA using a couple SQL queries and a couple Variables and simply set the value of a textbox or whatever you want to use.

You can do the work in a Query however this does not really fit with your forms or what you seem to want.