Inserting concatenated strings from a form into the appropriate ta

  • Thread starter Thread starter Jerome
  • Start date Start date
J

Jerome

I've concatenated two strings on a form ([ComboDept Type] & "" & [Title])
that generates my "Deviation Number". I am trying to get that value
"Deviation Number" to be inputted into my supporting table in the appropriate
field - Deviation Number, however I don't know how to get this value added to
the table. I've tried the following VBA code but nothing occurs, can someone
assist me:

Private Sub Deviation_Number_AfterUpdate()
Dim Deviation_Number As String
Deviation_Number = "INSERT INTO Issues(Deviation_Number)
VALUES(ME.Deviation_Number)"
DoCmd.RunSQL (Deviation_Number_AfterUpdate)
End Sub
 
This code will run only after a manual (meaning a user directly edits/enters
data) data entry/edit in a control named Deviation_Number. If this control
holds the concatenated value using an expression that does the
concatenation, your code will never run.

But assuming that you're doing some type of manual entry, this code should
work:

Private Sub Deviation_Number_AfterUpdate()
Dim Deviation_Number_String As String
Deviation_Number_String = "INSERT INTO Issues ( Deviation_Number ) " & _
"VALUES( '" & ME.Deviation_Number & "')"
DoCmd.RunSQL Deviation_Number_String
End Sub

If you're not doing any manual entry, you'll need to run this code from a
different event or procedure. If this is the situation, post back with more
details and we'll assist you.
 
Ken,

Thanks for your assistance. Based on what you wrote I realized that it was
dependent on a manual operation/trigger so I modified the code you gave me
slightly, however now I'm getting the following prompts:

"You are about to append 1 row" [Yes/No] so I select Yes, then the following
message appears:

"MS Access set 0 field(s) to null due to a type conversion failure, and
didn't add 0 record(s) to the table due to key violations, 0 record(s) due to
lock violations, and 1 record(s) due to violations rules."

Do you know what is required to correct this?

Your help is greatly appreciated.
--
Thanks
Jerome


Ken Snell (MVP) said:
This code will run only after a manual (meaning a user directly edits/enters
data) data entry/edit in a control named Deviation_Number. If this control
holds the concatenated value using an expression that does the
concatenation, your code will never run.

But assuming that you're doing some type of manual entry, this code should
work:

Private Sub Deviation_Number_AfterUpdate()
Dim Deviation_Number_String As String
Deviation_Number_String = "INSERT INTO Issues ( Deviation_Number ) " & _
"VALUES( '" & ME.Deviation_Number & "')"
DoCmd.RunSQL Deviation_Number_String
End Sub

If you're not doing any manual entry, you'll need to run this code from a
different event or procedure. If this is the situation, post back with more
details and we'll assist you.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jerome said:
I've concatenated two strings on a form ([ComboDept Type] & "" & [Title])
that generates my "Deviation Number". I am trying to get that value
"Deviation Number" to be inputted into my supporting table in the
appropriate
field - Deviation Number, however I don't know how to get this value added
to
the table. I've tried the following VBA code but nothing occurs, can
someone
assist me:

Private Sub Deviation_Number_AfterUpdate()
Dim Deviation_Number As String
Deviation_Number = "INSERT INTO Issues(Deviation_Number)
VALUES(ME.Deviation_Number)"
DoCmd.RunSQL (Deviation_Number_AfterUpdate)
End Sub
Click to expand...
Click to expand...
 
The error message suggests that one or more of your fields in the Issues
table is requiring a value that you are not providing it. My guess -- a
field cannot be NULL, and that field is not in your Insert query.

Without knowing more about the structure of the Issues table and the fields
and their properties, that's the best guess I can provide.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jerome said:
Ken,

Thanks for your assistance. Based on what you wrote I realized that it
was
dependent on a manual operation/trigger so I modified the code you gave me
slightly, however now I'm getting the following prompts:

"You are about to append 1 row" [Yes/No] so I select Yes, then the
following
message appears:

"MS Access set 0 field(s) to null due to a type conversion failure, and
didn't add 0 record(s) to the table due to key violations, 0 record(s) due
to
lock violations, and 1 record(s) due to violations rules."

Do you know what is required to correct this?

Your help is greatly appreciated.
--
Thanks
Jerome


Ken Snell (MVP) said:
This code will run only after a manual (meaning a user directly
edits/enters
data) data entry/edit in a control named Deviation_Number. If this
control
holds the concatenated value using an expression that does the
concatenation, your code will never run.

But assuming that you're doing some type of manual entry, this code
should
work:

Private Sub Deviation_Number_AfterUpdate()
Dim Deviation_Number_String As String
Deviation_Number_String = "INSERT INTO Issues ( Deviation_Number ) "
& _
"VALUES( '" & ME.Deviation_Number & "')"
DoCmd.RunSQL Deviation_Number_String
End Sub

If you're not doing any manual entry, you'll need to run this code from a
different event or procedure. If this is the situation, post back with
more
details and we'll assist you.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Jerome said:
I've concatenated two strings on a form ([ComboDept Type] & "" &
[Title])
that generates my "Deviation Number". I am trying to get that value
"Deviation Number" to be inputted into my supporting table in the
appropriate
field - Deviation Number, however I don't know how to get this value
added
to
the table. I've tried the following VBA code but nothing occurs, can
someone
assist me:

Private Sub Deviation_Number_AfterUpdate()
Dim Deviation_Number As String
Deviation_Number = "INSERT INTO Issues(Deviation_Number)
VALUES(ME.Deviation_Number)"
DoCmd.RunSQL (Deviation_Number_AfterUpdate)
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top