Using INSERT INTO with a text field

[Someone]

Hello

I have the following SQL command:

StrSql = "INSERT INTO tbl_Changes ([Changes],[ChangesID]) " & _
"VALUES ('" & strWord + strField + strWord2 + strField2 & "', " &
Me.ID.Value & ");"

When a user changes a value on the main form, wording, based on the above
values structure, is added into the Changes table. The resultant text would
be 'Changed to VALUE1 from VALUE2'.

This works fine for changing values in a drop-down box, but doesn't work
when the item being changed is a text box. For example, say I had the word
'Text' in a text field and tried to change it to 'Hello'. The results in
the Changes table would be 5 identical rows of 'Changed to Text from Text'.

Is there a way for me to allow this to work with text values?

Thanks
M

 

[Douglas J Steele]

How are you setting strField and strField2? Without seeing that code, it's
pretty difficult to comment...

Also, is the textbox bound or unbound? That makes a significant difference
to the answer.

 

[Someone]

Hi Douglas

Sorry: here's the code in full:

Dim StrSql As String
strField = Me.Status.Value
strField2 = Me.Status.OldValue
strWord = "Changed to "
strWord2 = " from "

StrSql = "INSERT INTO tbl_Changes ([Changes],[ChangesID]) " & _
"VALUES ('" & strWord + strField + strWord2 + strField2 & "', " &
Me.ID.Value & ");"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSql
DoCmd.SetWarnings True
Me.frm_Changes_Subform.Form.Requery

The textbox is bound to tbl_Changes.

Many thanks
M

How are you setting strField and strField2? Without seeing that code, it's
pretty difficult to comment...

Also, is the textbox bound or unbound? That makes a significant difference
to the answer.

 

[Douglas J Steele]

So Status is the name of the textbox that you're changing data in and it's
also the name of the field in the underlying table?

Try changing the name of the textbox to, say, txtStatus, and then changing
the code to

strField = Me.txtStatus.Value
strField2 = Me.txtStatus.OldValue

What event is this code in, by the way? I would expect to have it in the
form's BeforeUpdate event (or possibly its AfterUpdate event)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

Sorry: here's the code in full:

Dim StrSql As String
strField = Me.Status.Value
strField2 = Me.Status.OldValue
strWord = "Changed to "
strWord2 = " from "

StrSql = "INSERT INTO tbl_Changes ([Changes],[ChangesID]) " & _
"VALUES ('" & strWord + strField + strWord2 + strField2 & "', " &
Me.ID.Value & ");"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSql
DoCmd.SetWarnings True
Me.frm_Changes_Subform.Form.Requery

The textbox is bound to tbl_Changes.

Many thanks
M

How are you setting strField and strField2? Without seeing that code, it's
pretty difficult to comment...

Also, is the textbox bound or unbound? That makes a significant difference
to the answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello

I have the following SQL command:

StrSql = "INSERT INTO tbl_Changes ([Changes],[ChangesID]) " & _
"VALUES ('" & strWord + strField + strWord2 + strField2 & "', " &
Me.ID.Value & ");"

When a user changes a value on the main form, wording, based on the above
values structure, is added into the Changes table. The resultant text would
be 'Changed to VALUE1 from VALUE2'.

This works fine for changing values in a drop-down box, but doesn't work
when the item being changed is a text box. For example, say I had the word
'Text' in a text field and tried to change it to 'Hello'. The results in
the Changes table would be 5 identical rows of 'Changed to Text from Text'.

Is there a way for me to allow this to work with text values?

Thanks
M

 

[Someone]

Hi Douglas

This has done the trick. Thank you.

To answer your question, the code was in the OnChange event, because this
was where I had the original VBA code. I've now moved the code to the
AfterUpdate event.

Thanks again for your help - it is most appreciated.

M

So Status is the name of the textbox that you're changing data in and it's
also the name of the field in the underlying table?

Try changing the name of the textbox to, say, txtStatus, and then changing
the code to

strField = Me.txtStatus.Value
strField2 = Me.txtStatus.OldValue

What event is this code in, by the way? I would expect to have it in the
form's BeforeUpdate event (or possibly its AfterUpdate event)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

Sorry: here's the code in full:

Dim StrSql As String
strField = Me.Status.Value
strField2 = Me.Status.OldValue
strWord = "Changed to "
strWord2 = " from "

StrSql = "INSERT INTO tbl_Changes ([Changes],[ChangesID]) " & _
"VALUES ('" & strWord + strField + strWord2 + strField2 & "', " &
Me.ID.Value & ");"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSql
DoCmd.SetWarnings True
Me.frm_Changes_Subform.Form.Requery

The textbox is bound to tbl_Changes.

Many thanks
M

How are you setting strField and strField2? Without seeing that code,
it's
pretty difficult to comment...

Also, is the textbox bound or unbound? That makes a significant
difference
to the answer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello

I have the following SQL command:

StrSql = "INSERT INTO tbl_Changes ([Changes],[ChangesID]) " & _
"VALUES ('" & strWord + strField + strWord2 + strField2 & "', " &
Me.ID.Value & ");"

When a user changes a value on the main form, wording, based on the above
values structure, is added into the Changes table. The resultant text would
be 'Changed to VALUE1 from VALUE2'.

This works fine for changing values in a drop-down box, but doesn't
work
when the item being changed is a text box. For example, say I had the word
'Text' in a text field and tried to change it to 'Hello'. The results in
the Changes table would be 5 identical rows of 'Changed to Text from Text'.

Is there a way for me to allow this to work with text values?

Thanks
M