I hope I explain this well enough to get help. I'm trying to insert into 2 table from 1 form. When the user selects frome several list boxes and enters other required information, they will click submit. I use a server variable to execute code that calls 2 sub procedures:
<%
If Request.ServerVariables("REQUEST_METHOD" = "POST" then
'if(Session("svHormNum" <> "" then recNum = Session("svHormNum"
if(Request.Form("lstEstrogen" <> "" then intEstrogen = Request.Form("lstEstrogen"
if(Request.Form("lstProgesterone" <> "" then intProg = Request.Form("lstProgesterone"
if(Request.Form("lstTestosterone" <> "" then intTest = Request.Form("lstTestosterone"
if(Request.Form("lstDHEA" <> "" then intDHEA = Request.Form("lstDHEA"
if(Request.Form("hfPhyNum" <> "" then strPhyNum = Request.Form("hfPhyNum"
if(Request.Form("lstPatient" <> "" then intPatNum = Request.Form("lstPatient"
if(Request.Form("lstForms" <> "" then strType = Request.Form("lstForms"
if(Request.Form("lstSIG" <> "" then strSIG = Request.Form("lstSIG"
if(Request.Form("txtQuantity" <> "" then strQuantity = Request.Form("txtQuantity"
if(Request.Form("txtNumRefills" <> "" then strNumRefills = Request.Form("txtNumRefills"
Call Insert_Hormone(strPhyNum, intPatNum, strType, strSIG, strQuantity, strNumRefills)
if intEstrogen <> "" then
Call Insert_Estrogen(intEstrogen)
End if
if intProg <> "" then
Call Insert_Prog(intProg)
End if
if intTest <> "" then
Call Insert_Test(intTest)
End if
if intDHEA <> "" then
Call Insert_DHEA(intDHEA)
End if
Response.Redirect("ConfirmHormone.asp"
End if
%>

It calls these subs:
Sub Insert_Hormone(strPhyNum, intPatNum, strType, strSIG, strQuantity, strNumRefills)

Set DBConn = Server.CreateObject("ADODB.Connection"
DBConn.Open(Session("m_strDBString")
Set objHormone = Server.CreateObject("ADODB.Command"
objHormone.ActiveConnection = DBConn
strSQL = "INSERT INTO Hormone (Phy_Num, Pat_Num, Type, SIG, Quantity, NumRefill) VALUES ('" & strPhyNum & "'," & intPatNum & ", '" & strType & "', '" & strSIG & "', '" & strQuantity & "', '" & strNumRefills & "')"
objHormone.CommandText = strSQL
objHormone.CommandType = 1
objHormone.Execute
Set objHormone = Nothing
'rescordset to capture the value of the new record
Set rsHormone = Server.CreateObject("ADODB.Recordset"
rsHormone.ActiveConnection = DBConn
rsHormone.Open "SELECT MAX(Hormone_Num) as MaxID FROM Hormone"
Session("svHormNum" = rsHormone("MaxID"
rsHormone.Close
Set rsHormone = Nothing
DBConn.Close
Set DBConn = Nothing
End sub
'-------------------------------------------------------------------------

Sub Insert_Estrogen(intEstrogen)
Set DBConn = Server.CreateObject("ADODB.Connection"
DBConn.Open(Session("m_strDBString")
Set objEstrogen = Server.CreateObject("ADODB.Command"
objEstrogen.ActiveConnection = DBConn
recNum = Session("svHormNum"
strSQL = "INSERT INTO HReciepe (Hormone_Num, HormIng_Num) VALUES (" & recNum & ", " & intEstrogen & ""
objEstrogen.CommandText = strSQL
objEstrogen.CommandType = adCmdText
objEstrogen.Execute
Set objEstrogen = Nothing
DBConn.Close
Set DBConn = Nothing
End Sub

'-------------------------------------------------------------------------

Sub Insert_Prog(intProg)
Set DBConn = Server.CreateObject("ADODB.Connection"
DBConn.Open(Session("m_strDBString")
Set objProg = Server.CreateObject("ADODB.Command"
objProg.ActiveConnection = DBConn
recNum = Session("svHormNum"
strSQL = "INSERT INTO HReciepe (Hormone_Num, HormIng_Num) VALUES (" & recNum & ", " & intProg & ""
objProg.CommandText = strSQL
objProg.CommandType = adCmdText
objProg.Execute
Set objProg = Nothing
DBConn.Close
Set DBConn = Nothing
End Sub
'-------------------------------------------------------------------------

Sub Insert_Test(intTest)
Set DBConn = Server.CreateObject("ADODB.Connection"
DBConn.Open(Session("m_strDBString")
Set objTest = Server.CreateObject("ADODB.Command"
objTest.ActiveConnection = DBConn
recNum = Session("svHormNum"
strSQL = "INSERT INTO HReciepe (Hormone_Num, HormIng_Num) VALUES (" & recNum & ", " & intTest & ""
objTest.CommandText = strSQL
objTest.CommandType = adCmdText
objTest.Execute
Set objTest = Nothing
DBConn.Close
Set DBConn = Nothing
End Sub
'-------------------------------------------------------------------------

Sub Insert_DHEA(intDHEA)
Set DBConn = Server.CreateObject("ADODB.Connection"
DBConn.Open(Session("m_strDBString")
Set objDHEA = Server.CreateObject("ADODB.Command"
objDHEA.ActiveConnection = DBConn
recNum = Session("svHormNum"
strSQL = "INSERT INTO HReciepe (Hormone_Num, HormIng_Num) VALUES (" & recNum & ", " & intDHEA & ""
objDHEA.CommandText = strSQL
objDHEA.CommandType = adCmdText
objDHEA.Execute
Set objDHEA = Nothing
DBConn.Close
Set DBConn = Nothing
End Sub


I get this error when trying to view the page:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/looppharmacy/procedures.asp, line 55

the line is in Insert_Hormone-
objHormone.Execute
I have tried about everything and can't get this to work, any suggestions?
Thanks!

Michael Rudge

What is line 55 of procedures.asp? That is where the error creeps in. Can you provide an uncommented version of procedures that I can cut n paste into an editor? This will allow me to spot the problem much more quickly.

Upon closer review...it would appear that you are missing the single quotes for each element starting in the second sub-insert

thanks saintsguy, I eneded up redoing and simplifying the code. Turned out that 'Type' as a field is restricted/keyword in OLEDB. I wosh MS would standardize across the board. Someitimes I get tired of trying to remeber all the quirks in their version of whatever. Again thanks for your effort<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Michael Rudge

This code reminds me of puberty for some reason.. <img src=../images/dmxzone/forum/icon_smile_shock.gif border=0 align=middle> (minus the insert_estrogen subroutine)