saving text box to table

[Gaetanm via AccessMonster.com]

I'm having difficulty in figuring out how to make my existing SQL string to
up date my table for the bound text box. The text box is comments
on the activity the employee has done on this pericular job. The job may take
6 months of activities to complete
The SQL string works except for this last pice.

The text box is [txtJob_activity]

The table is [Clock_Table]

The Column is [Activity] the property is set to Memo

The following is the SQL

Private Sub Record_Click()
DoCmd.SetWarnings False
Dim SQLstrg As String

SQLstrg = "UPDATE Clock_Table SET [StopDate] = # " & Me.StopDate & " #WHERE (
([StopDate] Is Null) And ([EmployeeID] = " & Me.[EmployeeID] & "));"

DoCmd.RunSQL SQLstrg
DoCmd.SetWarnings True
Me.cboEmployeeID = Null
Me.StopDate = Null
Me.txtJob_Activity = Null
DoCmd.OpenForm "frmclock_start_table"
DoCmd.SelectObject acForm, "frmclock_start_table"

End Sub

Any suggestions

Gaetanm

 

[kingston via AccessMonster.com]

Is the field [EmployeeID] numeric? If not, you'll need to enclose the value
in quotes.

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "# WHERE ((
[StopDate] Is Null) And ([EmployeeID] = '" & Me.[EmployeeID] & "'));"

Put a debug stop at the RunSQL command to see if the string is properly
formulated. Is there an error of some sort that occurs when you run this?
If so, what is it? If there isn't an error, then check your data.
Everything might be working properly and there may not be a record that meets
the WHERE condition.

I'm having difficulty in figuring out how to make my existing SQL string to
up date my table for the bound text box. The text box is comments
on the activity the employee has done on this pericular job. The job may take
6 months of activities to complete
The SQL string works except for this last pice.

The text box is [txtJob_activity]

The table is [Clock_Table]

The Column is [Activity] the property is set to Memo

The following is the SQL

Private Sub Record_Click()
DoCmd.SetWarnings False
Dim SQLstrg As String

SQLstrg = "UPDATE Clock_Table SET [StopDate] = # " & Me.StopDate & " #WHERE (
([StopDate] Is Null) And ([EmployeeID] = " & Me.[EmployeeID] & "));"

DoCmd.RunSQL SQLstrg
DoCmd.SetWarnings True
Me.cboEmployeeID = Null
Me.StopDate = Null
Me.txtJob_Activity = Null
DoCmd.OpenForm "frmclock_start_table"
DoCmd.SelectObject acForm, "frmclock_start_table"

End Sub

Any suggestions

Gaetanm

 

[Gaetanm via AccessMonster.com]

Is the field [EmployeeID] numeric? If not, you'll need to enclose the value
in quotes.

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "# WHERE ((
[StopDate] Is Null) And ([EmployeeID] = '" & Me.[EmployeeID] & "'));"

Put a debug stop at the RunSQL command to see if the string is properly
formulated. Is there an error of some sort that occurs when you run this?
If so, what is it? If there isn't an error, then check your data.
Everything might be working properly and there may not be a record that meets
the WHERE condition.

I'm having difficulty in figuring out how to make my existing SQL string to
up date my table for the bound text box. The text box is comments

[quoted text clipped - 30 lines]

Gaetanm

Kingston

Thanks for the quick reply

The SQL runs fine the employee is a number.
The question is how can I Incorporated this string to include the textbox.
Gaetanm

 

[kingston via AccessMonster.com]

If you simply want to overwrite the field:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,
[Activity]='" & Me.txtJob_activity & "' WHERE (([StopDate] Is Null) And (
[EmployeeID] = '" & Me.[EmployeeID] & "'));"

If you want to append to the existing data:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,
[Activity]=[Activity] & '; " & Me.txtJob_activity & "' WHERE (([StopDate] Is
Null) And ([EmployeeID] = '" & Me.[EmployeeID] & "'));"

Is the field [EmployeeID] numeric? If not, you'll need to enclose the value
in quotes.

[quoted text clipped - 13 lines]Kingston

Thanks for the quick reply

The SQL runs fine the employee is a number.
The question is how can I Incorporated this string to include the textbox.
Gaetanm

 

[Gaetanm via AccessMonster.com]

If you simply want to overwrite the field:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,
[Activity]='" & Me.txtJob_activity & "' WHERE (([StopDate] Is Null) And (
[EmployeeID] = '" & Me.[EmployeeID] & "'));"

If you want to append to the existing data:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,
[Activity]=[Activity] & '; " & Me.txtJob_activity & "' WHERE (([StopDate] Is
Null) And ([EmployeeID] = '" & Me.[EmployeeID] & "'));"

Is the field [EmployeeID] numeric? If not, you'll need to enclose the value
in quotes.

[quoted text clipped - 8 lines]

The question is how can I Incorporated this string to include the textbox.
Gaetanm

Kingston

The code that I put in gives me a syntax error any Idea?

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,"
[Activity]='" & Me.txtJob_activity & "' WHERE (([StopDate] Is Null) And (
[EmployeeID] = '" & Me.[EmployeeID] & "'));"

Gaetanm

 

[kingston via AccessMonster.com]

Take a look at the string that is generated by putting this command after
setting SQLstrg:

MsgBox SQLstrg

Can you spot anything that looks wrong? Create this as visual query and
compare the syntax. You'd replace Me.Field with something like [Forms]!
[FormName]![TextBoxName] in the Update To: section.

If you simply want to overwrite the field:

[quoted text clipped - 13 lines]
Kingston

The code that I put in gives me a syntax error any Idea?

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,"
[Activity]='" & Me.txtJob_activity & "' WHERE (([StopDate] Is Null) And (
[EmployeeID] = '" & Me.[EmployeeID] & "'));"

Gaetanm

 

[Gaetanm via AccessMonster.com]

Take a look at the string that is generated by putting this command after
setting SQLstrg:

MsgBox SQLstrg

Can you spot anything that looks wrong? Create this as visual query and
compare the syntax. You'd replace Me.Field with something like [Forms]!
[FormName]![TextBoxName] in the Update To: section.
[quoted text clipped - 11 lines]

Gaetanm

Kingston

I put the message box up. but it keeps bringing me back to my VB
with syntax error without the msg box poping up. Here is the modified string
per your suggestion

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,"
[Activity] = '" & [Forms]![frmclock_stop_table]![txtjob_activity] & "' WHERE
(([StopDate] Is Null) And ([EmployeeID] = '" & Me.[EmployeeID] & "'));"

If I put a period between [frmclock_stop_table].[txtjob_activity]
I get " expected end of statement" and [activity] is highlighted

Any thoughts

Gaetanm

 

[kingston via AccessMonster.com]

This should be one long line in your VB window (you have a misplaced " before
[Activity]) and check the control and field names:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,
[Activity]='" & [Forms]![frmclock_stop_table]![txtjob_activity] & "' WHERE ((
[StopDate] Is Null) And ([EmployeeID] = '" & Me.[EmployeeID] & "'));"

Take a look at the string that is generated by putting this command after
setting SQLstrg:

[quoted text clipped - 10 lines]Kingston

I put the message box up. but it keeps bringing me back to my VB
with syntax error without the msg box poping up. Here is the modified string
per your suggestion

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,"
[Activity] = '" & [Forms]![frmclock_stop_table]![txtjob_activity] & "' WHERE
(([StopDate] Is Null) And ([EmployeeID] = '" & Me.[EmployeeID] & "'));"

If I put a period between [frmclock_stop_table].[txtjob_activity]
I get " expected end of statement" and [activity] is highlighted

Any thoughts

Gaetanm

 

[Gaetanm via AccessMonster.com]

This should be one long line in your VB window (you have a misplaced " before
[Activity]) and check the control and field names:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,
[Activity]='" & [Forms]![frmclock_stop_table]![txtjob_activity] & "' WHERE ((
[StopDate] Is Null) And ([EmployeeID] = '" & Me.[EmployeeID] & "'));"
[quoted text clipped - 17 lines]

Gaetanm

Kingston
I have rechecked my contrills and fields and spelling appears to be good
I made sure that " is back in and still syntacs error
I do have one long string for the sql

here is the string

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,"
[Activity]='" & [Forms]![frmClock_Stop_Table]![txtJob_Activity] & "' WHERE ((
[StopDate] Is Null) And ([EmployeeID] = " & Me.[EmployeeID] & "));"

The syntacs error occurs only when I add that activity function

Gaetanm

 

[kingston via AccessMonster.com]

Try this exactly as shown:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & _
Me.StopDate & "#, [Activity]='" & _
[Forms]![frmClock_Stop_Table]![txtJob_Activity] & _
"' WHERE (([StopDate] Is Null) And ([EmployeeID] = " & _
Me.[EmployeeID] & "));"

Your statement puts [Activity] outside of the string so Access tries to
interpret it as a variable, which it is not. Hopefully my lines are short
enough that it doesn't get reformatted.

This should be one long line in your VB window (you have a misplaced " before
[Activity]) and check the control and field names:

[quoted text clipped - 8 lines]
Kingston
I have rechecked my contrills and fields and spelling appears to be good
I made sure that " is back in and still syntacs error
I do have one long string for the sql

here is the string

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "#,"
[Activity]='" & [Forms]![frmClock_Stop_Table]![txtJob_Activity] & "' WHERE ((
[StopDate] Is Null) And ([EmployeeID] = " & Me.[EmployeeID] & "));"

The syntacs error occurs only when I add that activity function

Gaetanm

 

[Gaetanm via AccessMonster.com]

Try this exactly as shown:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & _
Me.StopDate & "#, [Activity]='" & _
[Forms]![frmClock_Stop_Table]![txtJob_Activity] & _
"' WHERE (([StopDate] Is Null) And ([EmployeeID] = " & _
Me.[EmployeeID] & "));"

Your statement puts [Activity] outside of the string so Access tries to
interpret it as a variable, which it is not. Hopefully my lines are short
enough that it doesn't get reformatted.

This should be one long line in your VB window (you have a misplaced " before
[Activity]) and check the control and field names:

[quoted text clipped - 16 lines]

Gaetanm

Kingston

That's the answer thanks a lot for your help Now this project
is now over with ( till the user says "can you .....) Thanks
again

Gaetanm