Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 7th, 2006, 12:45 PM
Registered User
 
Join Date: Apr 2006
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert Statement

Hi,

I have an MS Access application. I am using Data Access Pages as the front end. I have created an insert statement that should create a NEW record each time the user clicks the save button on the Navigation bar. However, instead of inserting a new record, it just updates the current record. I'd really appreciate the help. The coding is as follows:

<SCRIPT language=vbscript event=onclick for=TrackingQueryNavSave>
<!--


Dim strSrc, strSQL, cat1, cmd1, result1
Dim cboDept, cboDeptHead, txtDeptHeadPhone, txtProjMgr
Dim txtProjMgrPhone, txtProjName, cboProjID, txtProjDesc, txtRptDte
Dim txtPriority, txtStatus, txtPerComp, txtAchieveLev, txtWarranty
Dim txtIssues, txtNextSteps, txtMile1, txtMile2, txtMile3, txtMile4
Dim txtMile5, txtMile6, txtMile7, txtMile8, txtMile9, txtMile10
Dim txtMileDD1, txtMileDD2, txtMileDD3, txtMileDD4, txtMileDD5
Dim txtMileDD6, txtMileDD7, txtMileDD8, txtMileDD9, txtMileDD10
Dim txtMileStat1, txtMileStat2, txtMileStat3, txtMileStat4
Dim txtMileStat5, txtMileStat6, txtMileStat7, txtMileStat8
Dim txtMileStat9, txtMileStat10, txtMileStatDesc1, txtMileStatDesc2
Dim txtMileStatDesc3, txtMileStatDesc4, txtMileStatDesc5
Dim txtMileStatDesc6, txtMileStatDesc7, txtMileStatDesc8
Dim txtMileStatDesc9, txtMileStatDesc10, txtTrackSeq

    set strSrc = CreateObject("ADODB.Connection")
    strSrc.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=\\intraweb\ProjectTracking$\ProjectTracking .mdb"

    cboProjID = Project_ID2.value
    txtTrackSeq = TrackingSeq.value + 1
    cboDept = Department.value
    cboDeptHead = DeptHead.value
    txtDeptHeadPhone = DeptHeadPhone.value
    txtProjMgr = ProjectManager.value
    txtProjMgrPhone = ManagerPhone.value
    txtProjName = ProjectName.value
    txtProjDesc = ProjectDescription.value
    txtRptDte = ReportDate.value
    txtPriority = Priority.value
    txtStatus = cboStatus.value
    txtPerComp = PercentComplete.value
    txtAchieveLev = AchievementLevel.value
    txtWarranty = WarrantyPeriod.value
    txtIssues = Issues.value
    txtNextSteps = NextSteps.value
    txtMile1 = Milestone1.value
    txtMile2 = Milestone2.value
    txtMile3 = Milestone3.value
    txtMile4 = Milestone4.value
    txtMile5 = Milestone5.value
    txtMile6 = Milestone6.value
    txtMile7 = Milestone7.value
    txtMile8 = Milestone8.value
    txtMile9 = Milestone9.value
    txtMile10 = Milestone10.value
    txtMileDD1 = MilestoneDueDate1.value
    txtMileDD2 = MilestoneDueDate2.value
    txtMileDD3 = MilestoneDueDate3.value
    txtMileDD4 = MilestoneDueDate4.value
    txtMileDD5 = MilestoneDueDate5.value
    txtMileDD6 = MilestoneDueDate6.value
    txtMileDD7 = MilestoneDueDate7.value
    txtMileDD8 = MilestoneDueDate8.value
    txtMileDD9 = MilestoneDueDate9.value
    txtMileDD10 = MilestoneDueDate10.value
    txtMileStat1 = MilestoneStatus1.value
    txtMileStat2 = MilestoneStatus2.value
    txtMileStat3 = MilestoneStatus3.value
    txtMileStat4 = MilestoneStatus4.value
    txtMileStat5 = MilestoneStatus5.value
    txtMileStat6 = MilestoneStatus6.value
    txtMileStat7 = MilestoneStatus7.value
    txtMileStat8 = MilestoneStatus8.value
    txtMileStat9 = MilestoneStatus9.value
    txtMileStat10 = MilestoneStatus10.value
    txtMileStatDesc1 = MilestoneStatDesc1.value
    txtMileStatDesc2 = MilestoneStatDesc2.value
    txtMileStatDesc3 = MilestoneStatDesc3.value
    txtMileStatDesc4 = MilestoneStatDesc4.value
    txtMileStatDesc5 = MilestoneStatDesc5.value
    txtMileStatDesc6 = MilestoneStatDesc6.value
    txtMileStatDesc7 = MilestoneStatDesc7.value
    txtMileStatDesc8 = MilestoneStatDesc8.value
    txtMileStatDesc9 = MilestoneStatDesc9.value
    txtMileStatDesc10 = MilestoneStatDesc10.value



strSQL = "INSERT INTO Tracking (Project_ID, Tracking_Seq, " & _
    "Project_Name, Department, " & _
    "Dept_Head, Dept_Head_Phone, Project_Manager, " & _
    "Manager_Phone, Priority, Status, " & _
    "Percent_Complete, Issues, Next_Steps, " & _
    "Report_Date, Achievement_Level, " & _
    "Warranty_Period, Project_Description, Milestone_1, " & _
        "Milestone_2, Milestone_3, " & _
    "Milestone_4, Milestone_5, Milestone_6, Milestone_7, " & _
        "Milestone_8, Milestone_9, " & _
    "Milestone_10, Milestone_Status_1, Milestone_Status_2, " & _
        "Milestone_Status_3, " & _
    "Milestone_Status_4, Milestone_Status_5, " & _
        "Milestone_Status_6, Milestone_Status_7, " & _
    "Milestone_Status_8, Milestone_Status_9, " & _
        "Milestone_Status_10, " & _
    "Milestone_Due_Date_1, Milestone_Due_Date_2, " & _
        "Milestone_Due_Date_3, " & _
    "Milestone_Due_Date_4, Milestone_Due_Date_5, " & _
        "Milestone_Due_Date_6, " & _
    "Milestone_Due_Date_7, Milestone_Due_Date_8, " & _
        "Milestone_Due_Date_9, " & _
    "Milestone_Due_Date_10, Milestone_Stat_Desc_1, " & _
        "Milestone_Stat_Desc_2, " & _
    "Milestone_Stat_Desc_3, Milestone_Stat_Desc_4, " & _
        "Milestone_Stat_Desc_5, " & _
    "Milestone_Stat_Desc_6, Milestone_Stat_Desc_7, " & _
        "Milestone_Stat_Desc_8, " & _
    "Milestone_Stat_Desc_9, Milestone_Stat_Desc_10) " & _
    "VALUES (cboProjID, txtTrackSeq, txtProjName, " & _
    "cboDept, cboDeptHead, txtDeptHeadPhone, " & _
    "txtProjMgr, txtProjMgrPhone.value, txtPriority, " & _
        "txtStatus, txtPerComp, txtIssues, txtNextSteps, " & _
    "txtRptDte, txtAchieveLev, " & _
    "txtWarranty, txtProjDesc, txtMile1, " & _
    "txtMile2, txtMile3, txtMile4, " & _
    "txtMile5, txtMile6, txtMile7, " & _
    "txtMile8, txtMile9, txtMile10, " & _
    "txtMileStat1, txtMileStat2, txtMileSta3, " & _
    "txtMileStat4, txtMileStat5, txtMileStat6, " & _
    "txtMileStat7, txtMileStat8, txtMileStat9, " & _
    "txtMileStat10, txtMileDD1, " & _
    "txtMileDD2, txtMileDD3, " & _
    "txtMileDD4, txtMileDD5, " & _
    "txtMileDD6, txtMileDD7, " & _
    "txtMileDD8, txtMileDD9, " & _
    "txtMileDD10, txtMileStatDesc1, " & _
    "txtMileStatDesc2, txtMileStatDesc3, " & _
    "txtMileStatDesc4, txtMileStatDesc5, " & _
    "txtMileStatDesc6, txtMileStatDesc7, " & _
    "txtMileStatDesc8, txtMileStatDesc9, txtMileStatDesc10);"

    Msgbox cboProjID
    Msgbox txtTrackSeq
    Msgbox cboDept
    Msgbox cboDeptHead
    Msgbox strSQL
    Set cmd1 = CreateObject("ADODB.Command")
    cmd1.Name = "InsertTracking"
    cmd1.CommandText = strSQL
    Set cat1 = CreateObject("ADOX.Catalog")
    Set cat1.ActiveConnection = strSrc

    Err.Clear
    On Error Resume Next
    cat1.Procedures.Append cmd1.Name, cmd1
    If Err.Number = -2147217816 Then
        cat1.Procedures.Delete cmd1.Name
        cat1.Procedures.Append cmd1.Name, cmd1
    ElseIf Err.Number <> 0 Then
        MsgBox Err.Description
    End If

    cmd1.Execute strSQL

       Set cmd1 = Nothing
    Set cat1 = Nothing
    cmd1.Close


-->
</SCRIPT>






Similar Threads
Thread Thread Starter Forum Replies Last Post
Complicated Insert Statement jackie25 SQL Server ASP 1 May 31st, 2006 09:08 AM
Problem With INSERT Statement len1591 ASP.NET 2.0 Basics 3 May 19th, 2006 12:14 PM
Insert into statement not working. alisonchase Access ASP 8 January 16th, 2005 10:52 AM
Problems with my Insert Statement brendan82 Beginning VB 6 1 January 6th, 2004 08:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.