If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to use insert statement
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? |
#2
|
|||
|
|||
how to use insert statement
On Mon, 14 Nov 2005 13:56:10 -0800, AHP
wrote: 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. 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] )" 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 & "';" 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 This will go to the New Record on the form, and erase all your (unbound) textboxes; Exit_UpDateSingleRecord_Click: Exit Sub 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? 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] |
#3
|
|||
|
|||
how to use insert statement
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 "AHP" wrote: 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? |
#4
|
|||
|
|||
how to use insert statement
On Tue, 15 Nov 2005 14:58:03 -0800, AHP
wrote: 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 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] |
#5
|
|||
|
|||
how to use insert statement
Thanks! It got me back on track
"John Vinson" wrote: On Tue, 15 Nov 2005 14:58:03 -0800, AHP wrote: 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 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Insert Picture Error | Lynda | Powerpoint | 19 | November 22nd, 2005 09:00 PM |
How can i Insert multiple rows by using Insert statement in MS Acc | Kaushik Saha | Running & Setting Up Queries | 3 | March 15th, 2005 01:51 PM |
insert into statement from a form | Alter1971 | Using Forms | 2 | March 7th, 2005 12:23 AM |
Executing an SQL Insert statement on a button click - Help | nick | New Users | 3 | December 27th, 2004 03:33 AM |
Access 2000 query SQL statement into VBA code | Clint | Running & Setting Up Queries | 1 | June 10th, 2004 01:33 PM |