Using SQL as replacement to VBA

[Someone]

Hello

I originally had this code:

Me.frm_Changes_Subform.Form.AllowAdditions = True
Me.frm_Changes_Subform.SetFocus
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me.frm_Changes_Subform!Changes = ("Changed to " & Me.Status)
Me.frm_Changes_Subform.Form.AllowAdditions = False
Me.frm_Changes_Subform.Form.Requery
Me.Status.SetFocus

This is what the code does: If the Status field on the mainform was
changed, the Changes subform updated with some text to show what Status the
field was updated to. The Changes table (what the subform relates to) has 4
fields - an ID autonumber, Date, Time and the Changes field itself. The
Date and Time have a default of =Now(). The main table ID has a
relationship with the foreign ID (ie, the subform) with a one-to-many
relationship. Everything works fine, but I didn't like the way the updates
looked a bit clunky and the screen flashed with the SetFocus command.

I decided to give SQL a try instead. The SQL command I created is:

Dim strStatus As String
Dim strWord As String
Dim StrSql As String
strStatus = Me.Status.Value
strWord = "Changed to "

StrSql = "INSERT INTO tbl_Changes ([Changes]) " & _
"VALUES ('" & strWord + strStatus & "');"

DoCmd.RunSQL StrSql

The SQL command works, but it doesn't give the record in the Changes table
the same ID as the mainform ID (which my VBA routine does). I can change
the Status as many times as I like on the mainform and the ID in the Changes
table will always be the same as the ID I'm using on the mainform.

Can someone tell me why this doesn't work with SQL and how I can correct it
(if it is correctable of course)

Thank you
M

 

[Ken Snell \(MVP\)]

You need to include the "ID" field in the SQL statement:

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

 

[Someone]

Hi Ken

I'm very grateful to you - it's working.

Many thanks
M

You need to include the "ID" field in the SQL statement:

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

--

Ken Snell
<MS ACCESS MVP>

Hello

I originally had this code:

Me.frm_Changes_Subform.Form.AllowAdditions = True
Me.frm_Changes_Subform.SetFocus
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me.frm_Changes_Subform!Changes = ("Changed to " & Me.Status)
Me.frm_Changes_Subform.Form.AllowAdditions = False
Me.frm_Changes_Subform.Form.Requery
Me.Status.SetFocus

This is what the code does: If the Status field on the mainform was
changed, the Changes subform updated with some text to show what Status
the field was updated to. The Changes table (what the subform relates
to) has 4 fields - an ID autonumber, Date, Time and the Changes field
itself. The Date and Time have a default of =Now(). The main table ID
has a relationship with the foreign ID (ie, the subform) with a
one-to-many relationship. Everything works fine, but I didn't like the
way the updates looked a bit clunky and the screen flashed with the
SetFocus command.

I decided to give SQL a try instead. The SQL command I created is:

Dim strStatus As String
Dim strWord As String
Dim StrSql As String
strStatus = Me.Status.Value
strWord = "Changed to "

StrSql = "INSERT INTO tbl_Changes ([Changes]) " & _
"VALUES ('" & strWord + strStatus & "');"

DoCmd.RunSQL StrSql

The SQL command works, but it doesn't give the record in the Changes
table the same ID as the mainform ID (which my VBA routine does). I can
change the Status as many times as I like on the mainform and the ID in
the Changes table will always be the same as the ID I'm using on the
mainform.

Can someone tell me why this doesn't work with SQL and how I can correct
it (if it is correctable of course)

Thank you
M