Duplicate Record in Form and Subform with 2 where clause (1 Viewer)

A

andy1968

Registered User.
Local time
Today, 02:04
Joined
May 9, 2018
Messages
131
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:


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
 

Attachments

  • DuplicateRecord.accdb
    780 KB · Views: 380
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 02:04
Joined
Oct 29, 2018
Messages
21,449
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.
 
A

andy1968

Registered User.
Local time
Today, 02:04
Joined
May 9, 2018
Messages
131
Data type is short text.


Changed the insert order.



Code: 
"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'
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 02:04
Joined
Oct 29, 2018
Messages
21,449
andy1968 said:
Data type is short text.

Changed the insert order.

Code: 
"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'
Click to expand...
Okay, since all of the fields are Texts, you'll have to delimit each variable value with single quotes. For example:
Code: 
"INSERT INTO tblDurationAnalysis (Area, Job, Item)  " _
& " SELECT '" & NewArea & "', Job, Item " _
& " FROM..." _
& " WHERE Area='" & NewArea & "' AND Job='" & Me.Job & "'"
 
A

andy1968

Registered User.
Local time
Today, 02:04
Joined
May 9, 2018
Messages
131
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.





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

theDBguy

I’m here to help
Staff member
Local time
Today, 02:04
Joined
Oct 29, 2018
Messages
21,449
andy1968 said:
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.

Code: 
strSql = "INSERT INTO [tblDurationAnalysis] ( Area, Job, item ) " & _
                    "SELECT " & NewArea & " As Area, Job, item " & _
                    "FROM [tblDurationAnalysis] " & _
                    "WHERE Area='" & Me.Area & "' AND Job='" & Me.job & "'"
Click to expand...
Hi. Looks like you may have missed the first single quote around the first NewArea variable.
 
A

andy1968

Registered User.
Local time
Today, 02:04
Joined
May 9, 2018
Messages
131
Still something isn't right


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


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

andy1968

Registered User.
Local time
Today, 02:04
Joined
May 9, 2018
Messages
131
The error seems to be related to the Where statement, base on the message.
 
M

Mark_

Longboard on the internet
Local time
Today, 02:04
Joined
Sep 12, 2017
Messages
2,111
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
Code: 
"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. :cool:
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 02:04
Joined
Oct 29, 2018
Messages
21,449
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.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 02:04
Joined
Oct 29, 2018
Messages
21,449
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
You must log in or register to reply here.

Similar threads

C
Adding a new record and getting ID
Replies
14
Views
1,097
Pat Hartman
P
M
Populate Sub Form with similar to last main form record data
Replies
6
Views
518
MHART08
M
C
Solved Update a record with a recordset
Replies
10
Views
237
cheekybuddha
cheekybuddha
S
I want to check for duplicate date before upadating the table. In the Update Button Click Event I have used the follwoing code forcchecking duplicate
Replies
6
Views
450
Gasman
Gasman
N
Solved How to filter a single form in MS Access with VBA
Replies
12
Views
454
nector
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom