I have an MS Access db. I am using data access pages to allow users to create/update records. I have three diffeerent pages. My first page is fine. Everything works great. It's purpose is to define a project. I have a separate page for each department however, and these pages are based on queries. My second page, Tracking, allows the users to enter weekly updates on the status of the project.
What the program needs to do is insert a NEW record each time the tracking data gets updated. However, based on the table design, the program should not create a new Tracking ID, it should retain the current Tracking ID and increment the Tracking Sequence by 1. Both fields make up the primary key for the table. I am trying to do this via VB Script. I am having serious problems. The code I'm using is below:
<SCRIPT language=vbscript event=onclick for=TrackingNavSave>
<!--
Dim dbLocation, strSQL, prmProjID, objConn, cat1
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\intraweb\ProjectTracking$\ProjectTracking.mdb"
strSQL = "INSERT INTO Tracking (Tracking_ID, Tracking_Seq, Project_ID, " & _
"Project_Seq, Project_Name, Department, Dept_Head, Dept_Head_Phone, " & _
"Project_Manager, Manager_Phone, Priority, Status, Percent_Complete, " & _
"Issues, Next_Steps, Report_Week, Achievement_Level, User_Name, " & _
"Warranty_Period, Project_Description, Milestone_1, Milestone_2, " & _
"Milestone_3, Milestone_4, Milestone_5, Milestone_Status_1, " & _
"Milestone_Status_2, Milestone_Status_3, Milestone_Status_4, " & _
"Milestone_Status_5, Milestone_Due_Date_1, Milestone_Due_Date_2, " & _
"Milestone_Due_Date_3, Milestone_Due_Date_4, Milestone_Due_Date_5) " & _
"VALUES (Tracking_ID.value, Tracking_Seq.value, Project_ID.value, " & _
"Project_Seq.value, Project_Name.value, Department.value, Dept_Head.value, " & _
"Dept_Head_Phone.value, Project_Manager.value, Manager_Phone.value, " & _
"Priority.value, Status.value, Percent_Complete.value, Issues.value, " & _
"Next_Steps.value, Report_Week.value, Achievement_Level.value, " & _
"User_Name.value, Warranty_Period.value, Project_Description.value, " & _
"Milestone_1.value, Milestone_2.value, Milestone_3.value, Milestone_4.value, " & _
"Milestone_5.value, Milestone_Status_1.value, Milestone_Status_2.value, " & _"Milestone_Status_3.value, Milestone_Status_4.value, Milestone_Status_5.value, " & _
"Milestone_Due_Date_1.value, Milestone_Due_Date_2.value, " & _
"Milestone_Due_Date_3.value, Milestone_Due_Date_4.value, Milestone_Due_Date_5.value)"
Set objConn = CreateObject ("ADODB.Command")
objConn.CommandText = strSQL
Set cat1 = CreateObject("ADOX.Catalog")
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\intraweb\ProjectTracking$\ProjectTracking.mdb"
Set prmProjID = objConn.CreateParameter(Project_ID, 19, 1, 4)
objConn.Parameters.Append prmProjID
Tracking_Seq.value = Tracking_Seq.value + 1
objConn.Execute strSQL
Set objConn = Nothing
-->
</SCRIPT>
Please help. I'm having trouble with defining the parameters for the query.
