Append Query

[coolcatkelso]

Hiya

I have an append query working based on a Table called Quotes

I want to copy the Quotes to Workorders, I managed to get that working and it creates a new WorkorderID etc

But the Main form, holds a subform with the following two forms - Materials & Labour

How could I copy that into Workorders, subform, Materials and labour?

Both forms pretty much have identical fields?

Using access 2007
________
korean girl Webcams

 

[pbaldy]

Did you use one of the methods I pointed you to which would allow you to get the autonumber from the workorders table? That's the tricky part.

 

[coolcatkelso]

Hiya Paul

Yeah I got the Append Query working almost perfectly with the main form (Quotes to Workorders) and it now creates a new workorderID, but I just can't get the Subfrom from Quotes across to the Workorders for Materials
________
IOLITE VAPORIZER - CORDLESS PORTABLE VAPORIZER

 

[coolcatkelso]

Oh yeah, also, Using the append query in Quotes to Workorders, its copying all records from quotes, rather than just one

Do you have msn where we can chat direct?

Mines is duc_ati@hotmail.co.uk
________
CoedCutie

 

[pbaldy]

I don't use any of the IM services. The only people with instant access to me are my boss and my wife.

Your append query should point to the record currently displayed on the quotes form in it's criteria, so it only appends that record. You also need to use methods like the ones I pointed you to that will grab the autonumber from the workorder table, so you can use it to append the subform records. It may help to think not about working with the forms and subforms, but with the underlying tables.

 

[coolcatkelso]

Will you be able to have a look at the DB just now with the working version so far and see what I'm missing exactly?

I do appreciate all the time and help your given me so far
________
Roll Blunts

 

[pbaldy]

No promises, but I'll try. It is coming up on dinner time here.

 

[coolcatkelso]

Ok cheers m8

Again, the only thing I can't seem to get working is the main subform that holds all the materials for that job

Hopefully, we'll get this working lol

The append query I made is called Quotes Query2 lol keeping it simple

Please ignore the mess of all other tables & quotes, I will be sorting it out l8r

Cheers,
CCK
________
Fix ps3

 

[coolcatkelso]

Hiya Paul

Just wondering if you managed to get a chance to have a look at this?

Cheers m8
CCK
________
Rhode island marijuana dispensary

 

[pbaldy]

Well, on one of the other sites I see you've posted the question, someone recommended keeping it all in one table, and you seemed to be liking the idea, so I was waiting for the outcome of that. I thought the same thing frankly.

 

[coolcatkelso]

Hiya Paul

Cheers for the reply

I'm not sure which method would be easier, either of them I'm getting lost and confused with lol

Like I said before, I'm not the best with Access yet but I do keep trying. If you are able to guide me through then I'm happy to sit and learn, or if you would rather do it and explain it
________
VERMONT MEDICAL MARIJUANA DISPENSARY

 

[boblarson]

Well, on one of the other sites I see you've posted the question, someone recommended keeping it all in one table, and you seemed to be liking the idea, so I was waiting for the outcome of that. I thought the same thing frankly.

And actually I had mentioned that in another post by the OP on THIS forum too. Just add a flag to move it to live from a quote.

 

[boblarson]

Here's a very basic sample to show how you can have quotes and work orders and only designated via a flag.

 

[coolcatkelso]

Hiya

Cheers for the sample

However, in my own form, I need the subform to be working to list all the materials for the job

Also, in your sample, I noticed that once I click on the Workorder Convert from the quote, it doesn't store the record in the quote, but sends it to workorders?

I need the quote to Stay in the quotes so we can review quotes by customers, and have a button or something to send the quote to the workorder. But most importantly, I need to have the subform showing the materials needed and obviously the UnitPrice, and Qty.

This seems to be the hardest part for me to try to workout

We managed part of it with the Append Query on Quotes to Workorders, but not the Subforms tho
________
Marijuana vaporizers

 

[boblarson]

However, in my own form, I need the subform to be working to list all the materials for the job

Which would actually be another subform on the subform.

Also, in your sample, I noticed that once I click on the Workorder Convert from the quote, it doesn't store the record in the quote, but sends it to workorders?

That's just the code I used to add the workorder conversion date and then it automatically shows up in the workorders because the sample is limited to having the quotes show up for anything that has the checkbox FALSE. But you can build it anyway you want. If you want to see it then don't limit the view by that criteria.

I need the quote to Stay in the quotes so we can review quotes by customers, and have a button or something to send the quote to the workorder.

You can have a button if you want and set the YES/NO field to Yes. Then it is a workorder.

But most importantly, I need to have the subform showing the materials needed and obviously the UnitPrice, and Qty.

Like I mentioned up a bit, that would be another subform which would could be on both of the parent subforms.

Unfortunately I can't give you all of the answers. I just don't have time. But you do need to try to find some subforms and some samples with subforms on subforms to see how they work.

 

[coolcatkelso]

Hiya Bob

Again, thanks for the time to reply

I'm currently building a sample from scratch with just A Customer, Quotes, Workorders and Materials and see how I go

I'm going to attempt to have just one Material table for Both, I suppose for this to work, I should add in the WorkorderID and QuoteID into the table?

Hopefully, I'll figure it out with just one table taking the materials used
________
LovelyWendie

 

[coolcatkelso]

Ok so now I've created the basic forms

I created the Quotes as an Append Query, seems to transfer to the record OK, So, what do I do now for the subform to go? Or am I still getting confused on this one lol, sorry

I have the Materials table now, which is the same on BOTH Quote & Workorders. So both forms are now reading from the same table
________
BLACK TEEN

 

[coolcatkelso]

ok been looking at all these posts again

Now I can almost understand alittle more as my DB is now simple

ok, I create the Customer, then Create a Quote and the materials needed. A QuoteID is created using autonumber -

Then I run the append Query which will copy the Quote to the Workorder for me, and for me to get the resuilts in the subform, I need to link the NEWLY Appended WorkorderID to the WorkorderID field on the subform.

Just now, If I create the Quote and fill out the form and Subform for materials, and enter the workorderID in the subform, then click the Add to workorder button (Which is the append Query) It copys the quote to workorder, and because I added the workorder number to the Subfrom on the quote, its now showing the correct Materials in the Subform.

So, how do I ask the VBA to get the WorkorderID from the new Workorder form that was appended, and add that to the Subform automattically?

I think my understanding on this is getting better from my point of view, wether its correct to the terms you's guys are on lol I'm not sure?
________
Rolls-Royce Silver Dawn

 

[coolcatkelso]

Anyone help with the AUtonumber thing?

Need to find the autonumber that I just appended?
________
BlackRosse

 

[pbaldy]

You're already 3/4 of the way here:

http://www.access-programmers.co.uk/forums/showthread.php?t=168085