Special Question for OFER

L

ldiaz

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
 
O

Ofer

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
 
L

ldiaz

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


Ofer said:
Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
 
O

Ofer

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


Ofer said:
Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


Ofer said:
In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


Ofer said:
Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


Ofer said:
In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




Ofer said:
About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


Ofer said:
In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




Ofer said:
About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


Ofer said:
Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




Ofer said:
About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


Ofer said:
Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


Ofer said:
Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


Ofer said:
Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

Sorry, I wasn't next to my computer until now, so I didn't have the mdb you
sent.

Is that the SQL you have in MyQuery

SELECT *
FROM IDEmployeeQuery1
WHERE (((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID])) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
((([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null));

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


Ofer said:
Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

I don't know why this code appear like this, because when I put it, it was
like:

SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);


as you told me create a new query Named: MyQuery I don't know if this
statement is correct, also I used the code that you post here:

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

it working but copying all records in the query but does not work copying
only filtered records from IDEmployeesubform

I want appreciate the special attention given to my issue.


I'm very happy for your help.

Thanks
Ldiaz




Ofer said:
Sorry, I wasn't next to my computer until now, so I didn't have the mdb you
sent.

Is that the SQL you have in MyQuery

SELECT *
FROM IDEmployeeQuery1
WHERE (((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID])) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
((([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null));

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


Ofer said:
Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


:

I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..

I have a Form with two Subforms.

Form: BonusForm
Subform1: EmployeeIDSubform
Subform2: EmployeeBonusSubform

the Subform: EmployeeIDSubform is used to filter Employees Name, what I want
is move the Group of records [Employees] to the EmployeeBonusSubform and
apply some Field more that I have selected in the BonusForm such as Product
and Hrs Worked ,

subform [EmployeeBonusSubform] is linked to BonusForm, the
EmployeeIDSubform is used only to sort and filter employees according their
lines of work.

could you help me please..

I can send my Data Base if required.

this is my E-mail: (e-mail address removed)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

But the problem, that it should, if you copy the record source of the sub
form to a query, it should return the same records that are displayed in the
sub form, and ofcourse append them.

So, I'm not sure what went wrong.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
I don't know why this code appear like this, because when I put it, it was
like:

SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);


as you told me create a new query Named: MyQuery I don't know if this
statement is correct, also I used the code that you post here:

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

it working but copying all records in the query but does not work copying
only filtered records from IDEmployeesubform

I want appreciate the special attention given to my issue.


I'm very happy for your help.

Thanks
Ldiaz




Ofer said:
Sorry, I wasn't next to my computer until now, so I didn't have the mdb you
sent.

Is that the SQL you have in MyQuery

SELECT *
FROM IDEmployeeQuery1
WHERE (((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID])) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
((([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null));

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


:

Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


:

I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?

I might be in the wrong direction, so please direct me
--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,I know you can help me...because I have been looking for an answer on
the past weeks but I never got the correct answer.

This is my big problem..
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

Do you think that this issue has NOT a solution?

if so, I appreciate your time spent to fix this issue.

Thanks,I will try to get the correct code, since I need to release this DB.

Ldiaz
--
Lorenzo Díaz
Cad Technician


Ofer said:
But the problem, that it should, if you copy the record source of the sub
form to a query, it should return the same records that are displayed in the
sub form, and ofcourse append them.

So, I'm not sure what went wrong.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
I don't know why this code appear like this, because when I put it, it was
like:

SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);


as you told me create a new query Named: MyQuery I don't know if this
statement is correct, also I used the code that you post here:

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

it working but copying all records in the query but does not work copying
only filtered records from IDEmployeesubform

I want appreciate the special attention given to my issue.


I'm very happy for your help.

Thanks
Ldiaz




Ofer said:
Sorry, I wasn't next to my computer until now, so I didn't have the mdb you
sent.

Is that the SQL you have in MyQuery

SELECT *
FROM IDEmployeeQuery1
WHERE (((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID])) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
((([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null));

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


:

Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


:

I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo, first thank you for your confidence in my abilities, but I'm sure
there are alot of people in the discussion group that can help you just the
same if not better.

To be sure I understand what you need
You have a SubForm (EmployeeIDSubform) that is used to filter the MainForm
(BonusForm).
Now you want to move records to the table that the second SubForm is based
on (EmployeeBonusSubform), my questions are
1. What is the source of this records?
2. How do you filter this records?
3. When do you want this event to happen?
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

While the form is open and you select a filter, run the query, and tell me if
you get different output then the records displayed in the SubForm

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Do you think that this issue has NOT a solution?

if so, I appreciate your time spent to fix this issue.

Thanks,I will try to get the correct code, since I need to release this DB.

Ldiaz
--
Lorenzo Díaz
Cad Technician


Ofer said:
But the problem, that it should, if you copy the record source of the sub
form to a query, it should return the same records that are displayed in the
sub form, and ofcourse append them.

So, I'm not sure what went wrong.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
I don't know why this code appear like this, because when I put it, it was
like:

SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);


as you told me create a new query Named: MyQuery I don't know if this
statement is correct, also I used the code that you post here:

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

it working but copying all records in the query but does not work copying
only filtered records from IDEmployeesubform

I want appreciate the special attention given to my issue.


I'm very happy for your help.

Thanks
Ldiaz




:

Sorry, I wasn't next to my computer until now, so I didn't have the mdb you
sent.

Is that the SQL you have in MyQuery

SELECT *
FROM IDEmployeeQuery1
WHERE (((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID])) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
((([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null));

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


:

Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


:

I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub



--
Lorenzo Díaz
Cad Technician


"Ofer" wrote:
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

I did that, and in the subform I have three records and on the Query appear 6
,there is something wrong, but I don't understand why..

do you have the same problem...?

Ldiaz
--
Lorenzo Díaz
Cad Technician


Ofer said:
While the form is open and you select a filter, run the query, and tell me if
you get different output then the records displayed in the SubForm

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Do you think that this issue has NOT a solution?

if so, I appreciate your time spent to fix this issue.

Thanks,I will try to get the correct code, since I need to release this DB.

Ldiaz
--
Lorenzo Díaz
Cad Technician


Ofer said:
But the problem, that it should, if you copy the record source of the sub
form to a query, it should return the same records that are displayed in the
sub form, and ofcourse append them.

So, I'm not sure what went wrong.

--
\\// Live Long and Prosper \\//
BS"D


:

I don't know why this code appear like this, because when I put it, it was
like:

SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);


as you told me create a new query Named: MyQuery I don't know if this
statement is correct, also I used the code that you post here:

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

it working but copying all records in the query but does not work copying
only filtered records from IDEmployeesubform

I want appreciate the special attention given to my issue.


I'm very happy for your help.

Thanks
Ldiaz




:

Sorry, I wasn't next to my computer until now, so I didn't have the mdb you
sent.

Is that the SQL you have in MyQuery

SELECT *
FROM IDEmployeeQuery1
WHERE (((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID])) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
((([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null));

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


:

Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


:

I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
second subform"""""""

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])



Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

No, If you still have my Email Address, please post what you have.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
I did that, and in the subform I have three records and on the Query appear 6
,there is something wrong, but I don't understand why..

do you have the same problem...?

Ldiaz
--
Lorenzo Díaz
Cad Technician


Ofer said:
While the form is open and you select a filter, run the query, and tell me if
you get different output then the records displayed in the SubForm

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
Do you think that this issue has NOT a solution?

if so, I appreciate your time spent to fix this issue.

Thanks,I will try to get the correct code, since I need to release this DB.

Ldiaz
--
Lorenzo Díaz
Cad Technician


:

But the problem, that it should, if you copy the record source of the sub
form to a query, it should return the same records that are displayed in the
sub form, and ofcourse append them.

So, I'm not sure what went wrong.

--
\\// Live Long and Prosper \\//
BS"D


:

I don't know why this code appear like this, because when I put it, it was
like:

SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);


as you told me create a new query Named: MyQuery I don't know if this
statement is correct, also I used the code that you post here:

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

it working but copying all records in the query but does not work copying
only filtered records from IDEmployeesubform

I want appreciate the special attention given to my issue.


I'm very happy for your help.

Thanks
Ldiaz




:

Sorry, I wasn't next to my computer until now, so I didn't have the mdb you
sent.

Is that the SQL you have in MyQuery

SELECT *
FROM IDEmployeeQuery1
WHERE (((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID])) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
((([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null));

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


:

Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


:

I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);

I have added a Botton named: Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

"""Here I want to put my code to move records of the first subform to the
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

Hi Ofer.

Here is my E-mail: (e-mail address removed)

also this is my hotmail e-mail:

(e-mail address removed)

You can add me if you want.

Thanks for all Support.
--
Lorenzo Díaz
Cad Technician


Ofer said:
No, If you still have my Email Address, please post what you have.

--
\\// Live Long and Prosper \\//
BS"D


ldiaz said:
I did that, and in the subform I have three records and on the Query appear 6
,there is something wrong, but I don't understand why..

do you have the same problem...?

Ldiaz
--
Lorenzo Díaz
Cad Technician


Ofer said:
While the form is open and you select a filter, run the query, and tell me if
you get different output then the records displayed in the SubForm

--
\\// Live Long and Prosper \\//
BS"D


:

Do you think that this issue has NOT a solution?

if so, I appreciate your time spent to fix this issue.

Thanks,I will try to get the correct code, since I need to release this DB.

Ldiaz
--
Lorenzo Díaz
Cad Technician


:

But the problem, that it should, if you copy the record source of the sub
form to a query, it should return the same records that are displayed in the
sub form, and ofcourse append them.

So, I'm not sure what went wrong.

--
\\// Live Long and Prosper \\//
BS"D


:

I don't know why this code appear like this, because when I put it, it was
like:

SELECT * FROM IDEmployeeQuery1 WHERE ([DepartmentID]=Forms!BonusForm!Combo23
Or Forms!BonusForm!Combo23 Is Null) And ([AreaID]=Forms!BonusForm!Combo25 Or
Forms!BonusForm!Combo25 Is Null) And ([LocationID]=Forms!BonusForm!LocationID
Or Forms!BonusForm!LocationID Is Null);


as you told me create a new query Named: MyQuery I don't know if this
statement is correct, also I used the code that you post here:

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

it working but copying all records in the query but does not work copying
only filtered records from IDEmployeesubform

I want appreciate the special attention given to my issue.


I'm very happy for your help.

Thanks
Ldiaz




:

Sorry, I wasn't next to my computer until now, so I didn't have the mdb you
sent.

Is that the SQL you have in MyQuery

SELECT *
FROM IDEmployeeQuery1
WHERE (((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID])) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.LocationID)=[Forms]![BonusForm]![LocationID]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.AreaID)=[Forms]![BonusForm]![Combo25]) AND
(([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
(((IDEmployeeQuery1.DepartmentID)=[Forms]![BonusForm]![Combo23]) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null)) OR
((([Forms]![BonusForm]![Combo23]) Is Null) AND
(([Forms]![BonusForm]![Combo25]) Is Null) AND
(([Forms]![BonusForm]![LocationID]) Is Null));

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,that Work copying the column but taking the datas from MyQuery, but I
need it work copying only the Employees filtered in the IDEmployeesubform,
I have tested and it move all rown from Query, example in the subform I have
3 Employees and when I press the Button it copy all records.

I think it's almost finished, but just this is pending.


Thank you so much.
--
Lorenzo Díaz
Cad Technician


:

Sorry it took me a long time to come back to you
I hope I understand what you are trying to do, two changes that you need to do
1.
The RecordSource of the IDEmployeesubform, save it as query, call it MyQuery
so we can try it.
You can change the Subform RecordSource to this query

2.
To the insert SQL I changed the Query name, the EmployeeID and the IDBonus,
the two fields that required to create a key

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID, IDBonus) SELECT
EmployeeID , Forms![BonusForm]![IDBonus] As MyId FROM MyQuery"

I hope that what you are looking for, if not then please tell me
Good luck
--
\\// Live Long and Prosper \\//
BS"D


:

I sent you my DataBase already.

Thanks.

--
Lorenzo Díaz
Cad Technician


:

Hi Lorenzo
I sent you an Email, please send your MDB.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,,I have put all codes in my data Base but it does not work yet,could
you check my DataBase I can send it to you, or do you have an example of this
function.

I will appreciate so much your help, since it is last step that my database
need to be released.
Thanks

(e-mail address removed)




:

About removing the messages you can use this

Docmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Docmd.SetWarnings True

About the key violation, check the data you are trying to insert, it could
be that it already exist in the table or atleast some of it, so it prompt you
with the key violation message.

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer.

I pur this code...

DoCmd.RunSQL "INSERT INTO BonusEmployee(EmployeeID)SELECT
IDEmployeeQuery1.Name FROM IDEmployeeQuery1"
Me.[EmployeeBonusSubform].Requery


[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

but when I run the code it show me 3 message saying:
1.You are ppend 6 row(s)
Once click Yes, You can't use the Undo command.are you sure you want to
append the selected rows??

when I press YES appear the 2.

Microsoft office can't append all records in the appened query,and it say
like a Table Violation.

I click Yes then appear the last message:

Microsoft Office can't find the field "I" refered to in your expresion.


can you take a look at it please.

Ldiaz


:

In that case one option will be to use an append query to append all the
records from Query - IDEmployeeQuery1 to Table - BonusEmployee, and then
refresh the Sub form
============================================
Private Sub MoveDatas_Click()
On Error GoTo Err_MoveDatas_Click

Docmd.RunSql "INSERT INTO BonusEmployee( Field1, Field2 )
SELECT IDEmployeeQuery1.Field1, IDEmployeeQuery1.Field2
FROM IDEmployeeQuery1"

Me.[EmployeeBonusSubform].Requery

[BonusEmployeesubform]![EmployeeID] = ([IDEmployeesubform]![EmployeeID])

Exit_MoveDatas_Click:
Exit Sub

Err_MoveDatas_Click:
MsgBox Err.Description
Resume Exit_MoveDatas_Click

End Sub

=============================================
I hope I'm on the right direction

--
\\// Live Long and Prosper \\//
BS"D


:

Hi Ofer,,thank you for answer me...I understand your comments about put the
question to all communty,,I will make it after..

Yes, your are correct, the SubForm (EmployeeIDSubform) is used to filter
Employees, it have a query as source named: IDEmployeeQuery1, the second
subform (EmployeeBonusSubform) has as source a table named: BonusEmployee
where need to be stored all record moved from first subform.
the Problem is because I don' know how move 2 or 3 or more records from a
column to another column,

I filter Employee Using this code in two combobox created in the main form.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

two Subform in a Form, First SubForm is used to filther and group 0
How move 3 or more records from a Subform to another subform 0
How Move trhee o more rows from a subform to another subform 7
Filter Datas in a SubForm using a combobox 1
Help on this please.Combobox for filter 4
Linking subforms - 5
Modifying/Appending records between subforms 2
Subform question 2

Top