Syntax error in SQL code 1

[Fozzy9767]

I have posted this to the forms forum, but I am posting here as it has gotten to be a code problem now.

I get this error: "Invalid use of '.', '!', or '()' in query expression
'newagentlist.*
where newagentlist.[agent code] = " & "CBL - Agent 144"'."

On this code:

strSQL = "Insert into t" & vbCrLf & "Select newagentlist.*" & vbCrLf & "where newagentlist.[agent code] = " & """" & AgentName & """" & vbCrLf & "From newagentlist;"

I THINK this has something to do with how I am concatenating my query, but I can't find it. Unfortunately the error is not specific enough to point me in the right direction and the VBA editor in 97 is not as sophisticated as Newer versions. Any help will be appreciated.

Thanks, Ken

 

[Fozzy9767]

Give that man a cigar! That's exactly it.

 

[Eupher]

Okay,

What are the names of the fields that need to be changed, and what are the names of the textboxes on the form that contain the new data?

Ken S.

 

[Fozzy9767]

I actually will do better than that. I got it to work by trying my idea about making copies of the tables involved. I have two that need copies made at the same time. This may not be elegant but it seems to work.

Private Sub Command6_Click()
Dim AgentName As String
Dim NewAgent As String
Dim NewSort As String
Dim strSQL As String
NewAgent = Me.NewAgentName
NewSort = Me.NewSortcode
AgentName = Me.AgentCombo

strSQL = "Insert into A Select * From Newagentlist Where agentcode = '" & AgentName & "';"
DoCmd.RunSQL strSQL
strSQL = "Update A set agentcode = '" & NewAgent & "', Sortcode = '" & NewSort & "';"
DoCmd.RunSQL strSQL
strSQL = "INSERT INTO newagentlist Select * From A Where agentcode = '" & NewAgent & "';"
DoCmd.RunSQL strSQL
strSQL = "Delete * from A;"
DoCmd.RunSQL strSQL

strSQL = "Insert into B Select * From Buyrate Where agentcode = '" & AgentName & "';"
DoCmd.RunSQL strSQL
strSQL = "Update B set agentcode = '" & NewAgent & "';"
DoCmd.RunSQL strSQL
strSQL = "INSERT INTO Buyrate Select * From B Where agentcode = '" & NewAgent & "';"
DoCmd.RunSQL strSQL
strSQL = "Delete * from B;"
DoCmd.RunSQL strSQL
    
End Sub

This is tested code. I still need to add some error handling and some other end user goodies. Dress it up nice. Thanks for all your help and by all means if you have a better idea that will work I'm open to it, but I'll keep this one just in case.
Ken

 

[adalger]

Private Sub Command6_Click()
    Dim rstOld as Recordset, rstNew as Recordset
    Dim fld as Field

    Set rstOld = CurrentDB.OpenRecordset("Newagentlist", dbOpenSnapshot)
    rstOld.FindFirst("agentcode = '" & Me.AgentCombo & "'")
    If rstOld.NoMatch Then
        'Error handling as you deem appropriate
        Exit Sub
    End If

    Set rstNew = Db.OpenRecordset("Newagentlist", dbOpenDynaset, dbAppendOnly)
    rstNew.AddNew
    For Each fld in rstOld.Fields
        rstNew.Fields(fld.Name) = rstOld.Fields(fld.Name)
    Next fld
    rstNew!agentcode = Me.NewAgentName
    rstNew!Sortcode = Me.NewSortcode
    rstNew.Update
    rstNew.Close
    rstOld.Close
'Lather, Rinse, Repeat for rstOld and rstNew set to BuyRate
End Sub

For extra credit, I'd factor out the loop and update code to a function which takes the table name and an array of (Field name, Textbox control name) pairs, copies the record, updates each field with the contents of the appropriate textbox, and returns a boolean indicator of whether the record was successfully added.

 

[Fozzy9767]

That works great. One warning to anyone using this: the set srtNew statement has a typo it should be "= Currentdb"
otherwise you get an undeclared variable, but other than that I like this solution much better than the one I came up with. For one thing my solution kept asking the user to confirm all the inserts and updates.
Have a star.

Ken