Update table from unbound field in form

[MyMel]

Hi, I have a form call Project which generates a control_number for each
project created. On this form I have a button that opens another form for
employee assignment to the project. This form displays the current project
control_number you are assigning to, it has a subform that display the list
of employees to be assigned and a checkbox for assignment selection. When
the check box value is true it adds the assigned employees to a temp table.
I want the project control number on the form to be be added to the
assignment. How do I update the table with the value from the form . I have
tried numerous suggestion from this web site but I am unable to succefully
apply what I have read to my problem. Please help. Thank you in advance.

strSQL = UPDATE [ActiveEmp_Temp] SET (ActiveEmp_Temp.Control_Number =
Forms![EmpAssignment_Log]![Control_Number])

 

[J_Goddard via AccessMonster.com]

Hi -

Were you getting any kind of error message? The strSQL = ... as you posted
it wont compile in VBA.
Try writing the SQL this way:

strSQL = "UPDATE [ActiveEmp_Temp] SET (ActiveEmp_Temp.Control_Number = " &
Forms![EmpAssignment_Log]![Control_Number] & ")"

currentdb.execute strsql

I can't see why this would not work, though the [EmpAssignment_Log] form must
be open when you do this.

John

Hi, I have a form call Project which generates a control_number for each
project created. On this form I have a button that opens another form for
employee assignment to the project. This form displays the current project
control_number you are assigning to, it has a subform that display the list
of employees to be assigned and a checkbox for assignment selection. When
the check box value is true it adds the assigned employees to a temp table.
I want the project control number on the form to be be added to the
assignment. How do I update the table with the value from the form . I have
tried numerous suggestion from this web site but I am unable to succefully
apply what I have read to my problem. Please help. Thank you in advance.

strSQL = UPDATE [ActiveEmp_Temp] SET (ActiveEmp_Temp.Control_Number =
Forms![EmpAssignment_Log]![Control_Number])

 

[MyMel]

Hi J,

Thank you for responding. This is what finally worked for me.

DoCmd.RunSQL "UPDATE [ActiveEmp_Temp] " & _
"SET [ActiveEmp_Temp].Control_Number =
Forms![EmpAssignment_Log]![Control_Number]" & _
"WHERE IsNull([ActiveEmp_Temp].Control_Number);"

Happy Thanksgiving!!!

Hi -

Were you getting any kind of error message? The strSQL = ... as you posted
it wont compile in VBA.
Try writing the SQL this way:

strSQL = "UPDATE [ActiveEmp_Temp] SET (ActiveEmp_Temp.Control_Number = " &
Forms![EmpAssignment_Log]![Control_Number] & ")"

currentdb.execute strsql

I can't see why this would not work, though the [EmpAssignment_Log] form must
be open when you do this.

John

Hi, I have a form call Project which generates a control_number for each
project created. On this form I have a button that opens another form for
employee assignment to the project. This form displays the current project
control_number you are assigning to, it has a subform that display the list
of employees to be assigned and a checkbox for assignment selection. When
the check box value is true it adds the assigned employees to a temp table.
I want the project control number on the form to be be added to the
assignment. How do I update the table with the value from the form . I have
tried numerous suggestion from this web site but I am unable to succefully
apply what I have read to my problem. Please help. Thank you in advance.

strSQL = UPDATE [ActiveEmp_Temp] SET (ActiveEmp_Temp.Control_Number =
Forms![EmpAssignment_Log]![Control_Number])

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca




.