Sending form data to different table

  • Thread starter Thread starter Alan Holroyd via AccessMonster.com
  • Start date Start date
A

Alan Holroyd via AccessMonster.com

Hi,


I hope I have posted this in the correct place.

I am trying to insert data from a current form into a table i've used the
following code behind a on click event

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim SQL As String
Dim cntID As Integer
cntID = Me.ContactID
SQL = "INSERT INTO Contacted(ContactID) VALUES (cntID)"
DoCmd.RunSQL SQL


Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

I get a dialog box appear requesting that I eneter the value for cntID, I
have tried to set the value for cntID (should equal me.ContactID) using the
code. What have I done wrong can anyone help, please
 
Alan Holroyd via AccessMonster.com said:
Hi,


I hope I have posted this in the correct place.

I am trying to insert data from a current form into a table i've used
the following code behind a on click event

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim SQL As String
Dim cntID As Integer
cntID = Me.ContactID
SQL = "INSERT INTO Contacted(ContactID) VALUES (cntID)"
DoCmd.RunSQL SQL


Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

I get a dialog box appear requesting that I eneter the value for
cntID, I have tried to set the value for cntID (should equal
me.ContactID) using the code. What have I done wrong can anyone help,
please
Click to expand...

You need to build the *value* of cntID into the SQL string, not the name
of the variable as you have it now. try this::

SQL = "INSERT INTO Contacted(ContactID) VALUES (" & cntID & ")"
 
Hi,


I hope I have posted this in the correct place.

I am trying to insert data from a current form into a table i've used the
following code behind a on click event
Click to expand...

Umm...

Why? It sounds like you're creating a "dummy" record with a ContactID
field and nothing else. Such placeholder records are essentially NEVER
either necessary nor a good idea. Nonetheless...
Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim SQL As String
Dim cntID As Integer
cntID = Me.ContactID
SQL = "INSERT INTO Contacted(ContactID) VALUES (cntID)"
Click to expand...

Try changing this to concatenate the value of the form reference into
the Values() position of the SQL string:

SQL = "INSERT INTO Contacted(ContactID) Values (" & Me!ContactID & ")"

but I'm still dubious that you actually would WANT to do this.

John W. Vinson[MVP]
 
This helped and did exactly what I wanted to do and that was move the data
to a different table, however when I expand on this to include more fields
it reverts back to giving me the original issue. my code now looks like
this :-

Dim SQL As String

SQL = "INSERT INTO Contacted(ContactID, FirstName, LastName) Values (" & Me!
ContactID & ", " & Me!FirstName & ", " & Me!LastName & ")"

DoCmd.RunSQL SQL

Where have I gone wrong ?
 
Presumably FirstName and LastName are text, which means the values need to
be enclosed in quotes.

SQL = "INSERT INTO Contacted(ContactID, FirstName, LastName) Values (" &
Me!
ContactID & ", " & Chr$(34) & Me!FirstName & Chr$(34) & ", " & Chr$(34) &
Me!LastName & Chr$(34) & ")"
 
Back
Top