copy data from QuoteForm & QuoteSubform to OrderForm & OrderSubfor

[CatchinOn]

I have a situation where our clients will have already had our company
produce a quote for materials.

The quote i sproduced from a main quote form where the client's project
information is entered, then a quote subform where the materials are quoted.

Then at a later date when they want to place an order for the quoted
materials I would like to copy the information from the quote form and quote
subform to our order form and order subform.

It would be great if there is a way to do this by opening the quote form to
review the details then by pressing a "Move to Order Form" button and our
order form and order subform open with the information from the quote form
and quote subform entered in the correct locations.

Any help or tips anyone can provide is greatly apprecieated. I've been
banging my head on this form quite a while now.

 

[tina]

the data you enter in the quote mainform/subform is not stored in the form,
it's stored in a table - unless your forms are not bound to tables, in which
case you lose the data when you close the mainform.

assuming that the quote data *is* being written to tables, you should be
able to open a form and find an existing quote record, then use Append
queries to append first the parent quote record into the order table, then
the related child material record(s) into the order details table.

hth

 

[CatchinOn]

Tina,

Awesome and thank you so much for the reply. I think I can figure it out
from here, but I'll be sure to write back when I get stuck again.

Thanks again,
-H-

 

[CatchinOn]

Okay i have the append queries working correctly, but how do i get the
information i have trasfered to be loaded into the order form and for the
order form to automatically open with the tranfered information loaded so
that my users can finish the order form? I hope this makes since... and
thanks again...

-H-

 

[tina]

yes, it makes sense. you can easily add an OpenForm action to your code,
after the Append queries run. BUT, you need a way to identify the specific
order record you're looking for. is there some unique value in the parent
quote record, that also gets appended into the parent order table? perhaps
each quote has a quote number, that gets included in the order table? once
you identify that unique value, you can use the
WHERE argument of the OpenForm action to return only the order record you
want to see. for example, let's say the parent quote table does have a quote
number field that holds a unique value for each record, Number data type,
AND there is a corresponding quote number field of the same data type in the
parent order table to hold that value when the record is appended. i'll just
call the field QuoteNo. open the order form, as

DoCmd.OpenForm "frmOrders", , , "QuoteNo = " & Me!QuoteNo

read up on the OpenForm action and its' arguments in VBA Help, so you'll
understand how it works.

hth

 

[CatchinOn]

Tina, i do have a unique quote number on the quote form and it gets appended
to the order form's table. I also have the appended information from the
quote form correctly being loaded into a new record on the order form, but i
cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

The append query from my quote subform is getting the correct information,
but i cannot get it to load into the order subform.

Basically i think i need to take the information obtained from two append
quieries and load that informatuion into a newly created record on a form and
a subform,a dn i have all but the subforms linked up right.

I have been running through this all day and don't see my problem. The
subform append query is even getting the quoteID correct when it appends teh
info, so i would think it would link up but ya know. urrrgggggg...

Thanks for your patience and help...
-H-

 

[tina]

but i

cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

is the primary key field of the orders table an Autonumber? if so, you'll
need to retrieve that value *after* you Append the quote record to the
orders table, and assign the value to the foreign key field of the order
materials table in the second Append query. the easiest way to do that is to
look up the record and get the primary key value, and assign it to an
unbound textbox control in the form (you can set the control's Visible
property to False, because the user doesn't need to see it). refer to that
control in the Append quote materials query with the following syntax, as

[Forms]![name of quotes form]![name of textbox control]

the code would be something like

DoCmd.SetWarnings False
DoCmd.OpenQuery "name of query to append quote record to order table"
Me!TextboxName = DLookUp("primary key fieldname", _
"name of orders table", "quote number fieldname = " _
& Me![name of quote number field in quote table])
DoCmd.OpenQuery "name of append quote materials query"
DoCmd.SetWarnings True
DoCmd.OpenForm "name of orders form", , , _
"name of quote number field = " & Me!name of quote number field

and keep in mind, you're not appending records into a *form*, you're
appending records into the orders table and order materials table - and
*displaying* those records in the orders form/order materials subform.

hth

 

[CatchinOn]

Tina,

It sounds like this will work but i am a little confused about where and how
to do the lookup for the primary key value. What form do i do the lookup in,
and in what form do i put the unbound textbox?

but i
cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

is the primary key field of the orders table an Autonumber? if so, you'll
need to retrieve that value *after* you Append the quote record to the
orders table, and assign the value to the foreign key field of the order
materials table in the second Append query. the easiest way to do that is to
look up the record and get the primary key value, and assign it to an
unbound textbox control in the form (you can set the control's Visible
property to False, because the user doesn't need to see it). refer to that
control in the Append quote materials query with the following syntax, as

[Forms]![name of quotes form]![name of textbox control]

the code would be something like

DoCmd.SetWarnings False
DoCmd.OpenQuery "name of query to append quote record to order table"
Me!TextboxName = DLookUp("primary key fieldname", _
"name of orders table", "quote number fieldname = " _
& Me![name of quote number field in quote table])
DoCmd.OpenQuery "name of append quote materials query"
DoCmd.SetWarnings True
DoCmd.OpenForm "name of orders form", , , _
"name of quote number field = " & Me!name of quote number field

and keep in mind, you're not appending records into a *form*, you're
appending records into the orders table and order materials table - and
*displaying* those records in the orders form/order materials subform.

hth

Tina, i do have a unique quote number on the quote form and it gets appended
to the order form's table. I also have the appended information from the
quote form correctly being loaded into a new record on the order form, but i
cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

The append query from my quote subform is getting the correct information,
but i cannot get it to load into the order subform.

Basically i think i need to take the information obtained from two append
quieries and load that informatuion into a newly created record on a form and
a subform,a dn i have all but the subforms linked up right.

I have been running through this all day and don't see my problem. The
subform append query is even getting the quoteID correct when it appends teh
info, so i would think it would link up but ya know. urrrgggggg...

Thanks for your patience and help...
-H-

 

[tina]

since you've started another thread in this ng about the same topic, and
have established a dialog with MVP Allen Browne, suggest you stick with that
thread, rather than trying to plow the same patch of ground with two horses.

Tina,

It sounds like this will work but i am a little confused about where and how
to do the lookup for the primary key value. What form do i do the lookup in,
and in what form do i put the unbound textbox?

but i
cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

is the primary key field of the orders table an Autonumber? if so, you'll
need to retrieve that value *after* you Append the quote record to the
orders table, and assign the value to the foreign key field of the order
materials table in the second Append query. the easiest way to do that is to
look up the record and get the primary key value, and assign it to an
unbound textbox control in the form (you can set the control's Visible
property to False, because the user doesn't need to see it). refer to that
control in the Append quote materials query with the following syntax, as

[Forms]![name of quotes form]![name of textbox control]

the code would be something like

DoCmd.SetWarnings False
DoCmd.OpenQuery "name of query to append quote record to order table"
Me!TextboxName = DLookUp("primary key fieldname", _
"name of orders table", "quote number fieldname = " _
& Me![name of quote number field in quote table])
DoCmd.OpenQuery "name of append quote materials query"
DoCmd.SetWarnings True
DoCmd.OpenForm "name of orders form", , , _
"name of quote number field = " & Me!name of quote number field

and keep in mind, you're not appending records into a *form*, you're
appending records into the orders table and order materials table - and
*displaying* those records in the orders form/order materials subform.

hth

Tina, i do have a unique quote number on the quote form and it gets appended
to the order form's table. I also have the appended information from the
quote form correctly being loaded into a new record on the order form,

but
i

cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

The append query from my quote subform is getting the correct information,
but i cannot get it to load into the order subform.

Basically i think i need to take the information obtained from two append
quieries and load that informatuion into a newly created record on a

form
and

a subform,a dn i have all but the subforms linked up right.

I have been running through this all day and don't see my problem. The
subform append query is even getting the quoteID correct when it

appends
teh

info, so i would think it would link up but ya know. urrrgggggg...

Thanks for your patience and help...
-H-

:

yes, it makes sense. you can easily add an OpenForm action to your code,
after the Append queries run. BUT, you need a way to identify the specific
order record you're looking for. is there some unique value in the parent
quote record, that also gets appended into the parent order table? perhaps
each quote has a quote number, that gets included in the order

table?
once

you identify that unique value, you can use the
WHERE argument of the OpenForm action to return only the order

record
you

want to see. for example, let's say the parent quote table does have

a
quote

number field that holds a unique value for each record, Number data type,
AND there is a corresponding quote number field of the same data

type in
the

parent order table to hold that value when the record is appended.

i'll
just

call the field QuoteNo. open the order form, as

DoCmd.OpenForm "frmOrders", , , "QuoteNo = " & Me!QuoteNo

read up on the OpenForm action and its' arguments in VBA Help, so you'll
understand how it works.

hth


Okay i have the append queries working correctly, but how do i get the
information i have trasfered to be loaded into the order form and

for
the

order form to automatically open with the tranfered information

loaded
so

that my users can finish the order form? I hope this makes

since...
and

thanks again...

-H-

:

the data you enter in the quote mainform/subform is not stored

in
the

form,
it's stored in a table - unless your forms are not bound to

tables,
in

which
case you lose the data when you close the mainform.

assuming that the quote data *is* being written to tables, you should be
able to open a form and find an existing quote record, then use Append
queries to append first the parent quote record into the order table,
then
the related child material record(s) into the order details table.

hth


I have a situation where our clients will have already had our company
produce a quote for materials.

The quote i sproduced from a main quote form where the client's
project
information is entered, then a quote subform where the

materials
are

quoted.

Then at a later date when they want to place an order for the quoted
materials I would like to copy the information from the quote

form
and

quote
subform to our order form and order subform.

It would be great if there is a way to do this by opening the quote
form
to
review the details then by pressing a "Move to Order Form"

button
and

our
order form and order subform open with the information from

the
quote

form
and quote subform entered in the correct locations.

Any help or tips anyone can provide is greatly apprecieated. I've
been
banging my head on this form quite a while now.

 

[CatchinOn]

Tina,

You are AWESOME snd thank you so much for your help through all of my
headaches... but we finally got it working!!!
All i needed to do was add the OrderID to the Quote materials append query
and i guess the rest was set up correctly because after I added it it started
working perfectly.

Thanks again,
-H-

Tina,

It sounds like this will work but i am a little confused about where and how
to do the lookup for the primary key value. What form do i do the lookup in,
and in what form do i put the unbound textbox?

but i
cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

is the primary key field of the orders table an Autonumber? if so, you'll
need to retrieve that value *after* you Append the quote record to the
orders table, and assign the value to the foreign key field of the order
materials table in the second Append query. the easiest way to do that is to
look up the record and get the primary key value, and assign it to an
unbound textbox control in the form (you can set the control's Visible
property to False, because the user doesn't need to see it). refer to that
control in the Append quote materials query with the following syntax, as

[Forms]![name of quotes form]![name of textbox control]

the code would be something like

DoCmd.SetWarnings False
DoCmd.OpenQuery "name of query to append quote record to order table"
Me!TextboxName = DLookUp("primary key fieldname", _
"name of orders table", "quote number fieldname = " _
& Me![name of quote number field in quote table])
DoCmd.OpenQuery "name of append quote materials query"
DoCmd.SetWarnings True
DoCmd.OpenForm "name of orders form", , , _
"name of quote number field = " & Me!name of quote number field

and keep in mind, you're not appending records into a *form*, you're
appending records into the orders table and order materials table - and
*displaying* those records in the orders form/order materials subform.

hth

Tina, i do have a unique quote number on the quote form and it gets appended
to the order form's table. I also have the appended information from the
quote form correctly being loaded into a new record on the order form, but i
cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

The append query from my quote subform is getting the correct information,
but i cannot get it to load into the order subform.

Basically i think i need to take the information obtained from two append
quieries and load that informatuion into a newly created record on a form and
a subform,a dn i have all but the subforms linked up right.

I have been running through this all day and don't see my problem. The
subform append query is even getting the quoteID correct when it appends teh
info, so i would think it would link up but ya know. urrrgggggg...

Thanks for your patience and help...
-H-

:

yes, it makes sense. you can easily add an OpenForm action to your code,
after the Append queries run. BUT, you need a way to identify the specific
order record you're looking for. is there some unique value in the parent
quote record, that also gets appended into the parent order table? perhaps
each quote has a quote number, that gets included in the order table? once
you identify that unique value, you can use the
WHERE argument of the OpenForm action to return only the order record you
want to see. for example, let's say the parent quote table does have a quote
number field that holds a unique value for each record, Number data type,
AND there is a corresponding quote number field of the same data type in the
parent order table to hold that value when the record is appended. i'll just
call the field QuoteNo. open the order form, as

DoCmd.OpenForm "frmOrders", , , "QuoteNo = " & Me!QuoteNo

read up on the OpenForm action and its' arguments in VBA Help, so you'll
understand how it works.

hth


Okay i have the append queries working correctly, but how do i get the
information i have trasfered to be loaded into the order form and for the
order form to automatically open with the tranfered information loaded so
that my users can finish the order form? I hope this makes since... and
thanks again...

-H-

:

the data you enter in the quote mainform/subform is not stored in the
form,
it's stored in a table - unless your forms are not bound to tables, in
which
case you lose the data when you close the mainform.

assuming that the quote data *is* being written to tables, you should be
able to open a form and find an existing quote record, then use Append
queries to append first the parent quote record into the order table,
then
the related child material record(s) into the order details table.

hth


I have a situation where our clients will have already had our company
produce a quote for materials.

The quote i sproduced from a main quote form where the client's
project
information is entered, then a quote subform where the materials are
quoted.

Then at a later date when they want to place an order for the quoted
materials I would like to copy the information from the quote form and
quote
subform to our order form and order subform.

It would be great if there is a way to do this by opening the quote
form
to
review the details then by pressing a "Move to Order Form" button and
our
order form and order subform open with the information from the quote
form
and quote subform entered in the correct locations.

Any help or tips anyone can provide is greatly apprecieated. I've
been
banging my head on this form quite a while now.

 

[CatchinOn]

HEllo agan,

After running some mock orders to make sure it was all working correctly I
noticed a problem (of course). When I add orders through the Quote_Form's
button "Add As Order" that we set up to run the append quieries then open the
new record in the order_form/order_subform it works like a charm.

And also when I go to add a new order simply by opening the
order_form/Order_subform to create a new order without a quote it works like
a charm also.

But if I have more than one quote for the same customer, and I try to "Add
As Order" another quote, everyting goes as we want and the new order is
created with an unique OrderID, but everything listed in order_subform (all
the materials ordered) is also appended to and shows up in all the earler
orders. Every new order I add through the quote_form puts all the materials
just ordered correctly into the new order, but also adds the newly ordered
materials into all the client's existing orders.

This is really wierd becasue if i go back to create a new order again by
opening the order_form/order-subform and create a new order, it all works
correctly, so i know it has something to do with the append quieries, but i
can't seem to work it out.

I would be grateful for any ideas. I'm so close it hurts...

-H-

but i
cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

is the primary key field of the orders table an Autonumber? if so, you'll
need to retrieve that value *after* you Append the quote record to the
orders table, and assign the value to the foreign key field of the order
materials table in the second Append query. the easiest way to do that is to
look up the record and get the primary key value, and assign it to an
unbound textbox control in the form (you can set the control's Visible
property to False, because the user doesn't need to see it). refer to that
control in the Append quote materials query with the following syntax, as

[Forms]![name of quotes form]![name of textbox control]

the code would be something like

DoCmd.SetWarnings False
DoCmd.OpenQuery "name of query to append quote record to order table"
Me!TextboxName = DLookUp("primary key fieldname", _
"name of orders table", "quote number fieldname = " _
& Me![name of quote number field in quote table])
DoCmd.OpenQuery "name of append quote materials query"
DoCmd.SetWarnings True
DoCmd.OpenForm "name of orders form", , , _
"name of quote number field = " & Me!name of quote number field

and keep in mind, you're not appending records into a *form*, you're
appending records into the orders table and order materials table - and
*displaying* those records in the orders form/order materials subform.

hth

Tina, i do have a unique quote number on the quote form and it gets appended
to the order form's table. I also have the appended information from the
quote form correctly being loaded into a new record on the order form, but i
cannot get the append query information from the quote subform to get loaded
into the order subform on the newly created record on the order form.

The append query from my quote subform is getting the correct information,
but i cannot get it to load into the order subform.

Basically i think i need to take the information obtained from two append
quieries and load that informatuion into a newly created record on a form and
a subform,a dn i have all but the subforms linked up right.

I have been running through this all day and don't see my problem. The
subform append query is even getting the quoteID correct when it appends teh
info, so i would think it would link up but ya know. urrrgggggg...

Thanks for your patience and help...
-H-

 

[kamalbhatara786]

dear

i need to your help can you tell me how can write mail regarding food.todaysome people transferred to our project then i told our company regarding provide to us transferred technician of food then they reply to me please first to to us one mail for required food.please help me how i can write thismail.