[jjbfoster]

I have a code that should copy a current record and paste it as a new record in the same table and then open the new record in form view. This code works in an Access database created in version 2007, but so that 2002 users could use the database. Now I am trying to use the modified code to do the same thing, just creating and using in Access 2002.

The error says Run-Time error 3078 - The Microsoft Jet database engine cannot find the input table or query.

However, the table is there because it is the same table.

I do not know visual basic, I only edit simple code sometimes and I do not know what code should be put in "tags". I dont even know what "tags" are, but I am pretty familiar with Access.

Below is the code and the line that is erroring out is: Set answer = CurrentDb.OpenRecordset("CombinedTrialSheetFieldsTable"). query was in the () and while troubleshooting, I changed this to the table name.

I have searched for a solution for two days and have not resolved the issue yet, can anyone help me?

Private Sub CopySaveAsNewRecordFormView_Click()
 Dim LotNumber As String
    Dim query As String
    Dim maxID As Integer
    Dim ins As String
    Dim answer As DAO.Recordset
    'DAO.Recordset

    'Query for getting the highest base item
    query = "SELECT TOP 1 ID, LotNumber FROM CombinedTrialSheetFieldsTable WHERE LotNumber = '"
    
    'Insert statement to create the new row with old data...
    ins = "INSERT INTO CombinedTrialSheetFieldsTable SELECT " _
    & "CombinedTrialSheetFieldsTable.[id], CombinedTrialSheetFieldsTable.[Trial Number], CombinedTrialSheetFieldsTable.[LotNumber], CombinedTrialSheetFieldsTable.[Creation Date], CombinedTrialSheetFieldsTable.[T1],CombinedTrialSheetFieldsTable.[T2],CombinedTrialSheetFieldsTable.[T3],CombinedTrialSheetFieldsTable.[Tension Ratio]," _
    & "CombinedTrialSheetFieldsTable.[Trial Date], CombinedTrialSheetFieldsTable.[Tube Color], CombinedTrialSheetFieldsTable.[Textured Description], CombinedTrialSheetFieldsTable.[Customer], CombinedTrialSheetFieldsTable.[Reference Lot],CombinedTrialSheetFieldsTable.[Unitens],CombinedTrialSheetFieldsTable.[CV%],CombinedTrialSheetFieldsTable.[Corrected Denier],CombinedTrialSheetFieldsTable.[Leesona Shrinkage]," _
    & "CombinedTrialSheetFieldsTable.[Machine #], CombinedTrialSheetFieldsTable.[Input Speed], CombinedTrialSheetFieldsTable.[# Positions], CombinedTrialSheetFieldsTable.[RTS #], CombinedTrialSheetFieldsTable.[Merge 1 Dynafil],CombinedTrialSheetFieldsTable.[Elongation %],CombinedTrialSheetFieldsTable.[Elongation CV %],CombinedTrialSheetFieldsTable.[Tenacity gpd],CombinedTrialSheetFieldsTable.[Breakpoint grams]," _
    & "CombinedTrialSheetFieldsTable.[Merge 1 Elongation %], CombinedTrialSheetFieldsTable.[Merge 1 Producer], CombinedTrialSheetFieldsTable.[Merge 1], CombinedTrialSheetFieldsTable.[Merge 1 Denier], CombinedTrialSheetFieldsTable.[Merge 1 Filament],CombinedTrialSheetFieldsTable.[Tie Down Per Meter],CombinedTrialSheetFieldsTable.[Retention 1 %],CombinedTrialSheetFieldsTable.[Retention 2 %],CombinedTrialSheetFieldsTable.[Torque tps]," _
    & "CombinedTrialSheetFieldsTable.[Merge 1 Yarn Type/Luster], CombinedTrialSheetFieldsTable.[Merge 2 Dynafil], CombinedTrialSheetFieldsTable.[Merge 2 Elongation %], CombinedTrialSheetFieldsTable.[Merge 2 Producer],CombinedTrialSheetFieldsTable.[70 Meter],CombinedTrialSheetFieldsTable.[160 Meters],CombinedTrialSheetFieldsTable.[Crimp Force],CombinedTrialSheetFieldsTable.[Pkg Density g/cc],CombinedTrialSheetFieldsTable.[Pkg Diameter in]," _
    & "CombinedTrialSheetFieldsTable.[Merge 2], CombinedTrialSheetFieldsTable.[Merge 2 Denier], CombinedTrialSheetFieldsTable.[Merge 2 Filament], CombinedTrialSheetFieldsTable.[Merge 2 Yarn Type/Luster],CombinedTrialSheetFieldsTable.[Surge Speed mpm],CombinedTrialSheetFieldsTable.[Actual Ply],CombinedTrialSheetFieldsTable.[Lbs Per Spindle],CombinedTrialSheetFieldsTable.[Lbs Per Spindle Hour],CombinedTrialSheetFieldsTable.[Package Weight]," _
    & "CombinedTrialSheetFieldsTable.[Merge 3 Dynafil], CombinedTrialSheetFieldsTable.[Merge 3 Elongation %], CombinedTrialSheetFieldsTable.[Merge 3 Producer], CombinedTrialSheetFieldsTable.[Merge 3],CombinedTrialSheetFieldsTable.[W3 Lifters],CombinedTrialSheetFieldsTable.[W3 Lifter Setting],CombinedTrialSheetFieldsTable.[Doff Time],CombinedTrialSheetFieldsTable.[Requestor],CombinedTrialSheetFieldsTable.[Number of Pkgs],CombinedTrialSheetFieldsTable.[Packing Standards]," _
    & "CombinedTrialSheetFieldsTable.[Merge 3 Denier], CombinedTrialSheetFieldsTable.[Merge 3 Filament], CombinedTrialSheetFieldsTable.[Merge 3 Yarn Type/Luster], CombinedTrialSheetFieldsTable.[Merge 4 Dynafil],CombinedTrialSheetFieldsTable.[Total Pounds],CombinedTrialSheetFieldsTable.[Comments1],CombinedTrialSheetFieldsTable.[Comments2],CombinedTrialSheetFieldsTable.[Start Time],CombinedTrialSheetFieldsTable.[Build time],CombinedTrialSheetFieldsTable.[Run Time]," _
    & "CombinedTrialSheetFieldsTable.[Merge 4 Elongation %], CombinedTrialSheetFieldsTable.[Merge 4 Producer], CombinedTrialSheetFieldsTable.[Merge 4], CombinedTrialSheetFieldsTable.[Merge 4 Denier],CombinedTrialSheetFieldsTable.[Total Time]," _
    & "CombinedTrialSheetFieldsTable.[Merge 4 Filament], CombinedTrialSheetFieldsTable.[Merge 4 Yarn Type/Luster], CombinedTrialSheetFieldsTable.[Entry Guide Disc]," _
    & "CombinedTrialSheetFieldsTable.[Working Disc], CombinedTrialSheetFieldsTable.[Exit Guide Disc], CombinedTrialSheetFieldsTable.[Twist Direction]," _
    & "CombinedTrialSheetFieldsTable.[Unit Type], CombinedTrialSheetFieldsTable.[Unit Stack], CombinedTrialSheetFieldsTable.[Disc Spacing], CombinedTrialSheetFieldsTable.[Taper]," _
    & "CombinedTrialSheetFieldsTable.[Guide Disc], CombinedTrialSheetFieldsTable.[Stroke Length], CombinedTrialSheetFieldsTable.[Inter Air Jet Size], CombinedTrialSheetFieldsTable.[Inter Air Jet Type]," _
    & "CombinedTrialSheetFieldsTable.[Inter PSI], CombinedTrialSheetFieldsTable.[Output Air Jet Size], CombinedTrialSheetFieldsTable.[Output Jet Type]," _
    & "CombinedTrialSheetFieldsTable.[Output PSI], CombinedTrialSheetFieldsTable.[Throughput Speed], CombinedTrialSheetFieldsTable.[Draw Ratio]," _
    & "CombinedTrialSheetFieldsTable.[D/Y Ratio], CombinedTrialSheetFieldsTable.[2nd Overfeed], CombinedTrialSheetFieldsTable.[3rd Overfeed], CombinedTrialSheetFieldsTable.[4th Overfeed]," _
    & "CombinedTrialSheetFieldsTable.[Cross Angle], CombinedTrialSheetFieldsTable.[Wind Angle], CombinedTrialSheetFieldsTable.[Oil Roller Speed]," _
    & "CombinedTrialSheetFieldsTable.[Primary Heat], CombinedTrialSheetFieldsTable.[T1 Htr Short], CombinedTrialSheetFieldsTable.[T1 Htr Long]," _
    & "CombinedTrialSheetFieldsTable.[T1 Htr Inserts], CombinedTrialSheetFieldsTable.[T2 Secondary Htr], CombinedTrialSheetFieldsTable.[T2 Secondary Htr Tubes], CombinedTrialSheetFieldsTable.[Take Up Program]," _
    & "CombinedTrialSheetFieldsTable.[Cradle Pressure], CombinedTrialSheetFieldsTable.[P1 Constant Main], CombinedTrialSheetFieldsTable.[P2 Down Thr Arm]," _
    & "CombinedTrialSheetFieldsTable.[P3 Down Cradle], CombinedTrialSheetFieldsTable.[P4 Up Cradle], CombinedTrialSheetFieldsTable.[Disc Speed]," _
    & "CombinedTrialSheetFieldsTable.[Traverse Speed], CombinedTrialSheetFieldsTable.[Inches/DS], CombinedTrialSheetFieldsTable.[Oil Type/%], CombinedTrialSheetFieldsTable.[Winding Time in Min]," _
    & "CombinedTrialSheetFieldsTable.[Doff Mode], CombinedTrialSheetFieldsTable.[Pre-Draw] FROM CombinedTrialSheetFieldsTable WHERE ID = "

    ' Get the base item from user
    LotNumber = InputBox("Enter Lot Number")
    
    ' Append the base item and the rest of the sql statement needed to get the top base item
    query = query & LotNumber & "' Order by ID Desc"
    
    ' Get a recordset of the top 1 base items
    Set answer = CurrentDb.OpenRecordset("CombinedTrialSheetFieldsTable")
    
    ' Append the ID of the highest base item to the insert statement...
    ins = ins & answer!id
    
    ' Append the new record from the old record
    CurrentDb.Execute ins
    
    ' Open the Edit Form
    DoCmd.OpenForm "AFK - 1200 Trial Sheet - Copy"
End Sub

This post has been edited by modi123_1: 02 May 2012 - 11:40 AM
Reason for edit:: highlight the lines THEN click the code tags

[modi123_1]

So where did you decide to gank this code from?

PS - don't post questions in the tutorial sections...

Side note the 'code tags' is a formatting option we prefer you to use here. When you paste your code in the box you'll notice, to the far right of the bold button, a button that looks like this "[ CODE]". What this means is you high light your pasted code and click that button. That puts the code tags around the chunk of text so it formats better for everyone to see.

[jjbfoster]

A young man in programming class helped me with the code for the original database and knowing it should do the same thing for this one, I jsut copied it and changed the code that applied specifically to each database.

Thank you for the information, I now know how to use tags.

You mentioned to not post questions in the Tutorial section, am I in the tutorial section now? I thought I was in the forum section. Maybe I need to explore this site a little more.

[modi123_1]

No, you had an errant post in the tutorial section.

[jjbfoster]

The code is finally working to an extent; it adds a new record, but does not pull that record up for editing. the oldest record is displayed in the form for editing. Any ideas?

[modi123_1]

There could be a thousand things wrong - what does your code look like?