Duplicate Record in Form and Subform with 2 where clause

[andy1968]

I am trying to duplicate a record on a form and change one field based on a user input.


I have based the code on this from Allen Browne:


http://allenbrowne.com/ser-57.html


I am having trouble with the where clause in the SQL. I am not very familiar with SQL.


I get a Error Syntax error (missing operator) in the query expression...



The database is attached.


Here is the code:

Private Sub cmdDuplicate_Click()
 On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    Dim NewArea As String
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        NewArea = InputBox("Enter the Name of the New Area", "Enter New Area")
        With Me.RecordsetClone
            .AddNew
                !Area = "ADA East"
                !Job = Me.Job
                
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            'lngID = !OrderID
            
            'Duplicate the related records: append query.
            If Me.frmDurationCalulationItems.Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [tblDurationAnalysis] ( Job, item,  Area ) " & _
                    "SELECT " & NewArea & " As Area, Job, item " & _
                    "FROM [tblDurationAnalysis] WHERE Area = " & NewArea & " And WHERE Job = " & Me.Job & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub

 

[theDBguy]

Hi. I didn't download your attachment yet. I wanted to clarify something first. What are the data types of your fields in the table? Also, when you INSERT into a table and specify the fields, you'll have to make sure the values you're inserting matches the order of the fields as you listed them. It looks like you listed "Job" first, but then try to insert "Area" in its place.

 

[andy1968]

Data type is short text.


Changed the insert order.

"INSERT INTO [tblDurationAnalysis] ( Area, Job, item ) " & _
                    "SELECT " & NewArea & " As Area, Job, item " & _

Got the same error.


Error 3075 Syntax error (missing operator) in the query expression 'text entered in the Input box'

 

[Mark_]

Please check this link to see if it can shed some light...

https://www.w3schools.com/sql/sql_insert.asp

 

[theDBguy]

Data type is short text.

Changed the insert order.

"INSERT INTO [tblDurationAnalysis] ( Area, Job, item ) " & _
                    "SELECT " & NewArea & " As Area, Job, item " & _

Got the same error.

Error 3075 Syntax error (missing operator) in the query expression 'text entered in the Input box'

Okay, since all of the fields are Texts, you'll have to delimit each variable value with single quotes. For example:

"INSERT INTO tblDurationAnalysis (Area, Job, Item)  " _
& " SELECT '" & NewArea & "', Job, Item " _
& " FROM..." _
& " WHERE Area='" & NewArea & "' AND Job='" & Me.Job & "'"

 

[andy1968]

Thanks Mark. That was revieling but how can I translate that into VBZ?


DBGuy, Still getting an error. Changed the code a little to better match what I am trying to accomplish.


I want to add the Job and Item with the NewArea for the Area value.

strSql = "INSERT INTO [tblDurationAnalysis] ( Area, Job, item ) " & _
                    "SELECT " & NewArea & " As Area, Job, item " & _
                    "FROM [tblDurationAnalysis] " & _
                    "WHERE Area='" & Me.Area & "' AND Job='" & Me.job & "'"

 

[theDBguy]

DBGuy, Still getting an error. Changed the code a little to better match what I am trying to accomplish.

I want to add the Job and Item with the NewArea for the Area value.

strSql = "INSERT INTO [tblDurationAnalysis] ( Area, Job, item ) " & _
                    "SELECT " & NewArea & " As Area, Job, item " & _
                    "FROM [tblDurationAnalysis] " & _
                    "WHERE Area='" & Me.Area & "' AND Job='" & Me.job & "'"

Hi. Looks like you may have missed the first single quote around the first NewArea variable.

 

[andy1968]

Still something isn't right

"SELECT '" & NewArea & "' As Area, Job, item "

Error 3075 Syntax error (missing operator) in the query expression 'Area=ADA West AND Job = '04SF-190"

 

[andy1968]

The error seems to be related to the Where statement, base on the message.

 

[Mark_]

I would debug.print your strSQL to make sure of exactly what you are putting in.

Once you SEE the SQL that is generated, you can make a few changes in the string until you figure out what the actual error is. I am guessing your actual table name is tblDurationAnalysis, so it may have an issue with [tblDurationAnalysis].

If you can't figure out what the cause is, I'd suggest posting the exact contents of StrSQL after you've filled it.

I also think your select may be a bit off. Looks like it should be

"SELECT Area, Job, item " &_
"FROM tblDurationAnalysis " & _
"WHERE Area=[COLOR="Red"]'[/COLOR]" & Me.Area & "[COLOR="Red"]'[/COLOR] AND Job=[COLOR="Red"]'[/COLOR]" & Me.job & "[COLOR="Red"]'[/COLOR]"

P.S. used red on the single quotes so I can actually tell which way they went, not to indicate an error.

 

[theDBguy]

The error message is not showing any single quotes around either criteria. To be sure, add a Debug.Print line and show us the result here.

 

[andy1968]

Thank Mark. That did the trick!

 

[Mark_]

Your most welcome!

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.