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