how to use insert statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to update fields in a table with data selected on a form from
a combo box. The code compiles without error but, it does not update the
table with the information. Below is the code I am using to attempt to update
the table.


Private Sub UpDateSingleRecord_Click()



On Error GoTo Err_UpDateSingleRecord_Click

Dim InstSmt As String
Dim SelSmt As String


InstSmt = "INSERT INTO Student_Info (LAST_NAME) VALUES(
Forms![Student_info1]![LAST_NAME] )"
'SelSmt = "SELECT [Forms]![Student_info1]![LAST_NAME] FROM
[Forms]![Student_info1] WHERE LAST_NAME = '" & LName & "';"

DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

A connection to the table is estabilished OnClick on the combo box. This
connection is closed before Sub Onclick ends. Do I need to establish another
connection N the save record sub?
 
I am attempting to update fields in a table with data selected on a form from
a combo box. The code compiles without error but, it does not update the
table with the information. Below is the code I am using to attempt to update
the table.
Click to expand...

I presume you realize that normally a bound Form requires no code and
no query execution at all to do this... and that you're intentionally
using an unbound Form, in order to update the table separately? I'll
assume so... though it's a MUCH harder thing to do than using Access'
builtin tools.

Let's analyze your code...
Private Sub UpDateSingleRecord_Click()



On Error GoTo Err_UpDateSingleRecord_Click

Dim InstSmt As String
Dim SelSmt As String


InstSmt = "INSERT INTO Student_Info (LAST_NAME) VALUES(
Forms![Student_info1]![LAST_NAME] )"
Click to expand...

This defines a text string variable named InstSmt which will insert
the text string in the LAST_NAME textbox into the LAST_NAME field in
Student_Info...
'SelSmt = "SELECT [Forms]![Student_info1]![LAST_NAME] FROM
[Forms]![Student_info1] WHERE LAST_NAME = '" & LName & "';"
Click to expand...

and this is a comment, which does nothing (good thing because it's not
going to do anything useful, since a Form is not a Table and you can't
select from it);
DoCmd.GoToRecord , , acNewRec
Click to expand...

This will go to the New Record on the form, and erase all your
(unbound) textboxes;
Exit_UpDateSingleRecord_Click:
Exit Sub
Click to expand...

and then you exit.
Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub

A connection to the table is estabilished OnClick on the combo box. This
connection is closed before Sub Onclick ends. Do I need to establish another
connection N the save record sub?
Click to expand...

No connection is established anywhere. Your code does not do anything
to update any tables.

If you REALLY want to do it the hard way, you'll need some more code
after creating the InstSmt variable:

Dim db As DAO.Database ' define a Database object
Dim qd As DAO.Querydef ' define a Querydef object to run your query
Set db = CurrentDb ' open a link to the database
Set qd = db.CreateQuerydef("", InstSmt) ' create an unnamed query from
' your SQL
qd.Execute dbFailOnError ' run the query
Set qd = Nothing ' clean up after yourself

Or... you could do the same using ADO if you prefer; or you could just
use a bound form and let Access do the work for you.

John W. Vinson[MVP]
 
I open a connection to the db when I make a selection form the combo box

Private Sub Combo26_Click()

Dim myString As String
Dim conn1 As ADODB.Connection
Dim recd1 As ADODB.Recordset
Dim num1 As Integer
Set conn1 = New ADODB.Connection
conn1.Provider = "Microsoft.Jet.OLEDB.4.0"
conn1.Open "C:\Documents and Settings\alonzo.pugh\My
Documents\TrainingDATAb\AFNOSCC2Dtraining.mdb"


FName = ""
LName = ""

num1 = InStr(1, Combo26.Text, Chr(32))
FName = Left(Combo26.Text, (num1 - 1))
LName = Mid(Combo26.Text, (num1 + 1))


Set recd1 = New ADODB.Recordset
myString = "SELECT* FROM DET1PERSONNEL WHERE FIRST_NAME = '" & FName & "'
AND LAST_NAME = '" & LName & "';"
recd1.Open myString, conn1, adOpenKeyset, adLockOptimistic

The code that displays the data selected from the combo box, in the text box
on the form is here

recd1.Close
Set recd1 = Nothing
conn1.Close
Set conn1 = Nothing

End Sub


This code is for the command button to save the selected data to one of the
tables the forms can add a record to.

Private Sub UpDateSingleRecord_Click()



On Error GoTo Err_UpDateSingleRecord_Click

Dim InstSmt As String
Dim SelSmt As String


InstSmt = "INSERT INTO Student_Info (LAST_NAME) VALUES(
Forms![Student_info1]![LAST_NAME] )"
'SelSmt = "SELECT [Forms]![Student_info1]![LAST_NAME] FROM
[Forms]![Student_info1] WHERE LAST_NAME = '" & LName & "';"

DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click

End Sub
 
This code is for the command button to save the selected data to one of the
tables the forms can add a record to.

Private Sub UpDateSingleRecord_Click()



On Error GoTo Err_UpDateSingleRecord_Click

Dim InstSmt As String
Dim SelSmt As String


InstSmt = "INSERT INTO Student_Info (LAST_NAME) VALUES(
Forms![Student_info1]![LAST_NAME] )"
'SelSmt = "SELECT [Forms]![Student_info1]![LAST_NAME] FROM
[Forms]![Student_info1] WHERE LAST_NAME = '" & LName & "';"

DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click
Click to expand...

Well, you're creating a SQL string - but you're not executing it.

DoCmd.RunSQL InstSmt will do the job; or you can use the Querydef
Execute method which I suggested. If you want the query to run, you
must run it - not just define its SQL!

John W. Vinson[MVP]
 
Thanks! It got me back on track

John Vinson said:
This code is for the command button to save the selected data to one of the
tables the forms can add a record to.

Private Sub UpDateSingleRecord_Click()



On Error GoTo Err_UpDateSingleRecord_Click

Dim InstSmt As String
Dim SelSmt As String


InstSmt = "INSERT INTO Student_Info (LAST_NAME) VALUES(
Forms![Student_info1]![LAST_NAME] )"
'SelSmt = "SELECT [Forms]![Student_info1]![LAST_NAME] FROM
[Forms]![Student_info1] WHERE LAST_NAME = '" & LName & "';"

DoCmd.GoToRecord , , acNewRec



Exit_UpDateSingleRecord_Click:
Exit Sub

Err_UpDateSingleRecord_Click:

MsgBox Err.Description
Resume Exit_UpDateSingleRecord_Click
Click to expand...

Well, you're creating a SQL string - but you're not executing it.

DoCmd.RunSQL InstSmt will do the job; or you can use the Querydef
Execute method which I suggested. If you want the query to run, you
must run it - not just define its SQL!

John W. Vinson[MVP]
Click to expand...
 
Back
Top