Extract Essence And Paste Queries!!

[Guest]

I got two forms. Both are based on one query respectively.
Forms = Invoice
Query = Invoice

Forms = Delivery
Query = Delivery

Both forms/queries have the same/identical field

The Form Invoice has a checkbox. When the checkbox = yes, then the whole
recordset (all the field in the specified record in its query) will be
transferred/copied/same to any record or new record in query [delivery]

Any changes in [Invoice] will affect [Delivery] but any changes in
[Delivery] will not affect [Invoice]

For example,

Form = Invoice
Checkbox = yes
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

Automatically,

Form = Delivery
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

In short, how to copy a specified line in a query and paste it on any line
in another query?

Please provide codes that will help me to do this.

Thanks very very very much for those who help me.

Kennykee

 

[Alex Dybenko]

I think you can use Append query to make a copy on invoice record, and then
perhaps refresh (requery) delivery form

 

[Guest]

Thanks Alex, I almost got the solution
The remaining unsoluted solution is

How I am going to run the append query that append only current record while
i am in a form?

I got a checkbox. When the checkbox is checked, the current record including
all the data in subform(with different ID) will be appended to targetted
tables. When the checkbox is unchecked, the current record including all the
data in subform(with different ID) will be deleted from the targetted table.

How to do this?

Is there possible to append query set to run once only for each record other
than using non-repeating ID?

Any solutions?

Thanks in advance.

Kennykee

I think you can use Append query to make a copy on invoice record, and then
perhaps refresh (requery) delivery form

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

I got two forms. Both are based on one query respectively.
Forms = Invoice
Query = Invoice

Forms = Delivery
Query = Delivery

Both forms/queries have the same/identical field

The Form Invoice has a checkbox. When the checkbox = yes, then the whole
recordset (all the field in the specified record in its query) will be
transferred/copied/same to any record or new record in query [delivery]

Any changes in [Invoice] will affect [Delivery] but any changes in
[Delivery] will not affect [Invoice]

For example,

Form = Invoice
Checkbox = yes
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

Automatically,

Form = Delivery
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

In short, how to copy a specified line in a query and paste it on any line
in another query?

Please provide codes that will help me to do this.

Thanks very very very much for those who help me.

Kennykee

 

[Alex Dybenko]

yes, but you need several queries:
2 append queries to copy main record, then subform records
and 1-2 delete queries - to delete main record and if necessary - subform
records of target tables (if you dont have cascade delete)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex, I almost got the solution
The remaining unsoluted solution is

How I am going to run the append query that append only current record
while
i am in a form?

I got a checkbox. When the checkbox is checked, the current record
including
all the data in subform(with different ID) will be appended to targetted
tables. When the checkbox is unchecked, the current record including all
the
data in subform(with different ID) will be deleted from the targetted
table.

How to do this?

Is there possible to append query set to run once only for each record
other
than using non-repeating ID?

Any solutions?

Thanks in advance.

Kennykee

I think you can use Append query to make a copy on invoice record, and
then
perhaps refresh (requery) delivery form

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

I got two forms. Both are based on one query respectively.
Forms = Invoice
Query = Invoice

Forms = Delivery
Query = Delivery

Both forms/queries have the same/identical field

The Form Invoice has a checkbox. When the checkbox = yes, then the
whole
recordset (all the field in the specified record in its query) will be
transferred/copied/same to any record or new record in query [delivery]

Any changes in [Invoice] will affect [Delivery] but any changes in
[Delivery] will not affect [Invoice]

For example,

Form = Invoice
Checkbox = yes
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

Automatically,

Form = Delivery
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

In short, how to copy a specified line in a query and paste it on any
line
in another query?

Please provide codes that will help me to do this.

Thanks very very very much for those who help me.

Kennykee

 

[Guest]

Thanks Alex,

I almost finally get to the end of a success results

But in the append queries, i cant append lookup field. Can the lookup field
possess ID field?

If cannot, what is the alternative method to replace the ID field?

Any solutions?

Thanks in advance

Kennykee

yes, but you need several queries:
2 append queries to copy main record, then subform records
and 1-2 delete queries - to delete main record and if necessary - subform
records of target tables (if you dont have cascade delete)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex, I almost got the solution
The remaining unsoluted solution is

How I am going to run the append query that append only current record
while
i am in a form?

I got a checkbox. When the checkbox is checked, the current record
including
all the data in subform(with different ID) will be appended to targetted
tables. When the checkbox is unchecked, the current record including all
the
data in subform(with different ID) will be deleted from the targetted
table.

How to do this?

Is there possible to append query set to run once only for each record
other
than using non-repeating ID?

Any solutions?

Thanks in advance.

Kennykee

I think you can use Append query to make a copy on invoice record, and
then
perhaps refresh (requery) delivery form

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


I got two forms. Both are based on one query respectively.
Forms = Invoice
Query = Invoice

Forms = Delivery
Query = Delivery

Both forms/queries have the same/identical field

The Form Invoice has a checkbox. When the checkbox = yes, then the
whole
recordset (all the field in the specified record in its query) will be
transferred/copied/same to any record or new record in query [delivery]

Any changes in [Invoice] will affect [Delivery] but any changes in
[Delivery] will not affect [Invoice]

For example,

Form = Invoice
Checkbox = yes
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

Automatically,

Form = Delivery
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

In short, how to copy a specified line in a query and paste it on any
line
in another query?

Please provide codes that will help me to do this.

Thanks very very very much for those who help me.

Kennykee

 

[Alex Dybenko]

well, lookup field should work, just make sure you append identifier of
lookup table, a field which actually bound in combobox (as i understand you)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex,

I almost finally get to the end of a success results

But in the append queries, i cant append lookup field. Can the lookup
field
possess ID field?

If cannot, what is the alternative method to replace the ID field?

Any solutions?

Thanks in advance

Kennykee

yes, but you need several queries:
2 append queries to copy main record, then subform records
and 1-2 delete queries - to delete main record and if necessary - subform
records of target tables (if you dont have cascade delete)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex, I almost got the solution
The remaining unsoluted solution is

How I am going to run the append query that append only current record
while
i am in a form?

I got a checkbox. When the checkbox is checked, the current record
including
all the data in subform(with different ID) will be appended to
targetted
tables. When the checkbox is unchecked, the current record including
all
the
data in subform(with different ID) will be deleted from the targetted
table.

How to do this?

Is there possible to append query set to run once only for each record
other
than using non-repeating ID?

Any solutions?

Thanks in advance.

Kennykee



:

I think you can use Append query to make a copy on invoice record, and
then
perhaps refresh (requery) delivery form

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


I got two forms. Both are based on one query respectively.
Forms = Invoice
Query = Invoice

Forms = Delivery
Query = Delivery

Both forms/queries have the same/identical field

The Form Invoice has a checkbox. When the checkbox = yes, then the
whole
recordset (all the field in the specified record in its query) will
be
transferred/copied/same to any record or new record in query
[delivery]

Any changes in [Invoice] will affect [Delivery] but any changes in
[Delivery] will not affect [Invoice]

For example,

Form = Invoice
Checkbox = yes
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

Automatically,

Form = Delivery
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

In short, how to copy a specified line in a query and paste it on
any
line
in another query?

Please provide codes that will help me to do this.

Thanks very very very much for those who help me.

Kennykee

 

[Guest]

Thanks Alex
Below is the lookup SQL statement
Two Table : "Customer Details" and "IndMain"

Is there anything may cause key violation?

SELECT [ID] AS xyz_ID_xyz, [Customer Name] & ', ' & [Contact Number] & ', ' &
[Address] AS xyz_DispExpr_xyz, [Customer Name], [Contact Number], [Address]
FROM [Customer Details] ORDER BY [Customer Name], [Contact Number],
[Address];

SELECT IndMain.ID AS xyz_ID_xyz, [Customer Name] & ', ' & [Contact Number] &
', ' & [Address] AS xyz_DispExpr_xyz, IndMain.[Customer Name],
IndMain.[Contact Number], IndMain.Address FROM IndMain ORDER BY
IndMain.[Customer Name], IndMain.[Contact Number], IndMain.Address;

Is anything wrong up there or anything may cause key violation?

Thanks in advance.

Kennykee

well, lookup field should work, just make sure you append identifier of
lookup table, a field which actually bound in combobox (as i understand you)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex,

I almost finally get to the end of a success results

But in the append queries, i cant append lookup field. Can the lookup
field
possess ID field?

If cannot, what is the alternative method to replace the ID field?

Any solutions?

Thanks in advance

Kennykee

yes, but you need several queries:
2 append queries to copy main record, then subform records
and 1-2 delete queries - to delete main record and if necessary - subform
records of target tables (if you dont have cascade delete)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Thanks Alex, I almost got the solution
The remaining unsoluted solution is

How I am going to run the append query that append only current record
while
i am in a form?

I got a checkbox. When the checkbox is checked, the current record
including
all the data in subform(with different ID) will be appended to
targetted
tables. When the checkbox is unchecked, the current record including
all
the
data in subform(with different ID) will be deleted from the targetted
table.

How to do this?

Is there possible to append query set to run once only for each record
other
than using non-repeating ID?

Any solutions?

Thanks in advance.

Kennykee



:

I think you can use Append query to make a copy on invoice record, and
then
perhaps refresh (requery) delivery form

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


I got two forms. Both are based on one query respectively.
Forms = Invoice
Query = Invoice

Forms = Delivery
Query = Delivery

Both forms/queries have the same/identical field

The Form Invoice has a checkbox. When the checkbox = yes, then the
whole
recordset (all the field in the specified record in its query) will
be
transferred/copied/same to any record or new record in query
[delivery]

Any changes in [Invoice] will affect [Delivery] but any changes in
[Delivery] will not affect [Invoice]

For example,

Form = Invoice
Checkbox = yes
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

Automatically,

Form = Delivery
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

In short, how to copy a specified line in a query and paste it on
any
line
in another query?

Please provide codes that will help me to do this.

Thanks very very very much for those who help me.

Kennykee

 

[Guest]

Thanks all.
I know why I cant append query on lookup fields, the lookup fields contain
ID which is not duplicable

The table is for archive purpose

So I will try append programmatically. I will post my question to
programming sections.

Thanks.

Kennykee

well, lookup field should work, just make sure you append identifier of
lookup table, a field which actually bound in combobox (as i understand you)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Thanks Alex,

I almost finally get to the end of a success results

But in the append queries, i cant append lookup field. Can the lookup
field
possess ID field?

If cannot, what is the alternative method to replace the ID field?

Any solutions?

Thanks in advance

Kennykee

yes, but you need several queries:
2 append queries to copy main record, then subform records
and 1-2 delete queries - to delete main record and if necessary - subform
records of target tables (if you dont have cascade delete)

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Thanks Alex, I almost got the solution
The remaining unsoluted solution is

How I am going to run the append query that append only current record
while
i am in a form?

I got a checkbox. When the checkbox is checked, the current record
including
all the data in subform(with different ID) will be appended to
targetted
tables. When the checkbox is unchecked, the current record including
all
the
data in subform(with different ID) will be deleted from the targetted
table.

How to do this?

Is there possible to append query set to run once only for each record
other
than using non-repeating ID?

Any solutions?

Thanks in advance.

Kennykee



:

I think you can use Append query to make a copy on invoice record, and
then
perhaps refresh (requery) delivery form

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


I got two forms. Both are based on one query respectively.
Forms = Invoice
Query = Invoice

Forms = Delivery
Query = Delivery

Both forms/queries have the same/identical field

The Form Invoice has a checkbox. When the checkbox = yes, then the
whole
recordset (all the field in the specified record in its query) will
be
transferred/copied/same to any record or new record in query
[delivery]

Any changes in [Invoice] will affect [Delivery] but any changes in
[Delivery] will not affect [Invoice]

For example,

Form = Invoice
Checkbox = yes
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

Automatically,

Form = Delivery
Field [Fruit] = Banana
Field [Quantity] = 10
Field [Price] = $100

In short, how to copy a specified line in a query and paste it on
any
line
in another query?

Please provide codes that will help me to do this.

Thanks very very very much for those who help me.

Kennykee