Hi,
A few things are standing out at me right away...
1. Your SQL is not passing the value of your id_buf variable in your SQL
string. It should be "INSERT INTO...VALUES(' " & id_buf & " ', (rest of your
SQL here)..."
2. Eliminate the ";" at the end of your path string - example:
"...Program.mdb" not "...Program.mdb;"
3. If you're set on using ADO for this (which is fine), then you need to
execute your action query SQL statement using an ADODB.Command object.
>>> Form1 has recordsource of an inner join sql and updates 2 tables. <<<
If all of these tables have a 1-1 relationship on the SS#, then you should
combine them all into one flat table.
--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, etc - 60% off
"James Hartrich" <(E-Mail Removed)> wrote in message
news:707901c3e6a3$f4a2e600$(E-Mail Removed)...
> my Access database has 5 forms and 5 tables(SS# is PK
> on all). Form1 has recordsource of an inner join sql and
> updates 2 tables. I need form1 to update all 5 tables
> with the SS#, so that i can use forms2-5 to update more
> info on other tables on the same SS#. I have tried
> putting a cmdSave button/event in vba to execute a INSERT
> INTO command. (see code below)
> I need someone to debug this code and to tell me if i
> used the correct sql syntax and if i needed to use
> adoConn or recordset objects or not.
>
> Thanking You,
>
> James Hartrich
>
> Campus Info:
> SS#, 1_20 Expiration, Work Location, Year of Study,
> Academic Recognition, Academic Recognition 2, Academic
> Recognition 3, Family Friend, Conversation Partner,
> Current Campus Address, Family Text, Conversation Text,
> Visa Type
>
>
> Public Sub sql_insert_into_Campus_Info_Table()
>
> Dim id_buf As String, p_buf As String, co_buf As String,
> lab_buf As String, date_buf As String
> Dim sqlStr As String, adoCon As ADODB.Connection, adoCon1
> As ADODB.Connection, conString As String
> Dim msg As String, rs As ADODB.Recordset, rs1 As
> ADODB.Recordset, tst As Boolean, co_variant As Variant
>
> On Error Resume Next
>
> 'set up the connection string
> 'conString = "Provider=MSDASQL"
> conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
> conString = conString & "Data Source="
> conString = conString & "C:\Documents and Settings\James
> Hartrich\Desktop\international access db\Copy of JSH BETA
> 6 International_Program.mdb;"
>
> 'create the Connection object
> Set adoCon = New ADODB.Connection
> adoCon.ConnectionString = conString
>
> 'open the connection
> adoCon.Open
>
> 'test for error
> If Err <> 0 Then
> msg = "an error occured trying to connect to the
> International database:" & vbCrLf
> msg = msg & "Error number: " & Err & vbCrLf
> msg = msg & "Description: " & Err.Description
> MsgBox msg
> End If
> Err.Clear
>
> id_buf = Forms![AppDocs-T]![SS#]
> 'p_buf = Forms!clock_out!password
> 'co_buf = Forms!clock_out!clock_out
> 'lab_buf = Forms!clock_out!lab_id
> 'date_buf = Forms!clock_out!Date
>
> 'SQL query to find user and password in the db*****
> MsgBox (id_buf)
> 'adoCon.BeginTrans
>
> adoCon.Execute ("INSERT INTO [Campus Info] ([Campus Info].
> [SS#], [Campus Info].[Work Location]) VALUES(id_buf,
> NULL")
> MsgBox (id_buf)
>
> 'if error, display message then quit
> If Errn <> 0 Then
> msg = "An error occurred trying to INSERT INTO the
> Campus Info TABLE:" & vbCrLf
> msg = msg & "Error number: " & Err & vbCrLf
> msg = msg & "Description: " & Err.Description
> MsgBox msg
> 'Set rs = Nothing
> adoCon.Close
> Exit Sub
> End If
> 'adoCon.CommitTrans
>
>
> adoCon.Close
>
> End Sub
Similar Threads
