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

 

[Eupher]

Hi, Fozzy9767,

Let's simplify a little - first, I would dump the vbCrLf characters, they are completely unnecessary. For readability in your code, use the line continuation character (the underscore character). Next, what is [blue]AgentName[/blue] - a variable in your code, or a field in a form? For the moment I'm gonna assume it's a variable, because it looks like it's returning a value in your example. Here's how I would write the SQL string:

strSQL = "INSERT INTO t " _
& "SELECT newagentlist.* " _
& "FROM newagentlist " _
& "WHERE newagentlist![agent code] = '" & AgentName & "';"

Give that a try...

Ken S.

 

[MajP]

or if you do not like the line continuation. Just like Eupher said but you can concatenate also like:

strSQL = "INSERT INTO t "
strSql = strSql & "SELECT newagentlist.* "
strSql = strSql & "FROM newagentlist "
strSql = strSql & "WHERE newagentlist![agent code] = '" & AgentName & "';

 

[Fozzy9767]

I've never had any luck using the _ in code. It never works. I've always had to use the vbcrlf for some reason. I'll give the rewrite a try. I changed it to this:

Dim AgentName As String
Dim AgentSort As String
Dim NewAgent As String
Dim strSQL As String
Dim t As TableDef
Dim b As TableDef

AgentName = Me.AgentCombo.Value
AgentSort = Me.NewSortcode
NewAgent = Me.NewAgentName
strSQL = "Insert into t" & _
"Select newagentlist.*" & _
"where newagentlist.[agent code] = '" & AgentName & "'" & _
"From newagentlist;"
DoCmd.RunSQL strSQL

I now get this error:
Run-time error '3134'
Syntax error in INSERT INTO statement.

It's different at least.

 

[Fozzy9767]

Well, I see that I had ny from in the wrong place, but I'm still getting the 3134 error. I'm trying to use a temp table but I've never done that before. I dimmed tabledefs, is this incorrect?
Ken

 

[Fozzy9767]

Maybe I'm going about this whole thing wrong, there may be an easier way to accomplish what I need. I need to copy a record in a table, then change 2 fields and add the resulting record to the table. I can do it by hand in a datasheet view by copying and paste append. I'm trying to just have a form with a drop down so they can choose what record to change. It's got a combo box for that and two text boxes for the new data for the changes. I thought it would be easy to put the record in a temp table then update the 2 fields and then append it to the original table. Better way?

Thanks, Ken.

 

[Eupher]

Hey, Fozzy,

Check your concatenation, you've omitted some necessary spaces on your line breaks. This...

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

...will concatenate as [tt]"Insert into [red]tSelect newagentlist.*where[/red] newagentlist..."[/tt] etc. and will raise an error.

The line continuation character and vbCrLf are completely different. The line continuation character tells the compiler "this line of code isn't done, include the next one, too" - so you can insert carriage returns to eliminate the need to scroll long lines of text in the VBE, thereby improving the readability of your code and simplifying debugging. The vbCrLf inserts a hard return into your code - but does nothing in terms of making your code easier to read in the VBE.

In this context, declaring a tabledef has no effect - your code never initializes the object or defines any of its properties. By using the literal "t" in your SQL string (that is, not the variable "t") you are causing table "t" to be created, so dimming the tabledef is superfluous.

To answer your final question, for me I think it would be simpler just to open a table recordset, find the record I needed and copy the required data to a new record. Something like this:

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim AgentName As String
Dim MyString1 As String
Dim MyString2 As String

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("newagentlist", dbOpenDynaset)
AgentName = Me!AgentCombo
With Rs
    .FindFirst "[agent code] = '" & AgentName & "'"
    If .NoMatch Then
        MsgBox [green]'some appropriate error msg here[/green]
        Exit Sub
    MyString1 = ![blue]MyField1[/blue]
    MyString2 = ![blue]MyField2[/blue]
    .AddNew
    ![blue]MyField1[/blue] = MyString1
    ![blue]MyField2[/blue] = MyString2
    .Update
    .Close
    End If
End With

Set Rs = Nothing
Set Db = Nothing

Or if the table is really big and you don't want to open the entire recordset, then this:

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim strSQL As String
Dim MyString1 As String
Dim MyString2 As String

AgentName = Me!AgentCombo

Set Db = CurrentDb()
strSQL = "Select * FROM newagentlist WHERE [agent code] = '" & Me!AgentCombo "'"
If Not Rs.EOF Then
    MyString1 = [blue]Rs!MyField1[/blue]
    MyString2 = [blue]Rs!MyField2[/blue]
    Else
        MsgBox [green]'appropriate error msg here[/green]
End If
Rs.Close
strSQL = "INSERT INTO newagentlist ( [blue]MyField1, MyField2[/blue] ) " _
& "VALUES ( '" & MyString1 & "', '" & MyString2 & "' )"

Set Rs = Nothing
Set Db = Nothing

HTH,

Ken S.

 

[Eupher]

Oops, in my last bit of code above I assign a value to the AgentName variable but then never call it. So that line can be deleted...

Ken S.

 

[adalger]

Just a comment: if you have many fields, you may wish to loop through them instead of assigning individually:

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
[COLOR=red] Dim Rs2 as DAO.Recordset[/color]
Dim strSQL As String
[COLOR=red][s]Dim MyString1 As String
Dim MyString2 As String[/s][/color]
[COLOR=red]Dim fld as Field[/color]

[COLOR=red][s]AgentName = Me!AgentCombo[/s][/color]

Set Db = CurrentDb()
strSQL = "Select * FROM newagentlist WHERE [agent code] = '" & Me!AgentCombo "'"
If Not Rs.EOF Then
    [COLOR=red][s]MyString1 = Rs!MyField1
    MyString2 = Rs!MyField2[/s][/color]
    [COLOR=red]Set Rs2 = Db.OpenRecordset("[i]tbl_name[/i]", dbOpenDynaset, dbAppendOnly)
    Rs2.AddNew
    For Each fld in Rs.Fields
        Select Case fld.Name
        Case EditedField1:
            Rs2.Fields(fld.Name) = Me!Field1Source
        Case EditedField2:
            Rs2.Fields(fld.Name) = Me!Field2Source
        Case Else:
            Rs2.Fields(fld.Name) = Rs.Fields(fld.Name)
        End Select
   Next fld[/color]
    Else
        MsgBox 'appropriate error msg here
End If
[COLOR=red]Rs2.Close[/color]
Rs.Close
strSQL = "INSERT INTO newagentlist ( MyField1, MyField2 ) " _
& "VALUES ( '" & MyString1 & "', '" & MyString2 & "' )"

[COLOR=red]Set Rs2 = Nothing[/color]
Set Rs = Nothing
Set Db = Nothing

 

[Fozzy9767]

Ok, I'm trying your first suggestion, but I obviously didn't give you enough info. My table has a bunch of other fields that need to be copied to the new record. I basically want to copy the entire record and then update the two fields. Maybe a for each loop? and go through the fields collection?
Ken

 

[adalger]

I forgot my Rs2.Update after the For Each loop.

 

[Fozzy9767]

Let me simplify. The agent Combo box is bound to the newagentlist table, so there will always be 1 row returned in a query or one row in a recordset. SO far as it goes this works:

Private Sub Command6_Click()
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim AgentName As String
Dim AgentSort As String
Dim NewAgent As String
Dim strSQL As String

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("newagentlist", dbOpenDynaset)
AgentName = Me!AgentCombo
AgentSort = Me.NewSortcode
NewAgent = Me.NewAgentName

With Rs
    .FindFirst "agentcode = '" & AgentName & "'"
    .AddNew
    !Sortcode = AgentSort
    !Agentcode = NewAgent
    .Update
    .CLOSE
End With

Set Rs = Nothing
Set Db = Nothing

except it doesn't copy the DATA from all the fields. It just creates a new record and updates the two fields I am getting from Form input. So I need to create the new record, copy each fields data, then update the two fields. Then am I assuming correctly that the .update sends the changes to the original table?
Ken

 

[Fozzy9767]

Well, I tried this, I thought it should work, but I get a rather generic error.

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim AgentName As String
Dim AgentSort As String
Dim NewAgent As String
Dim strSQL As String

Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("newagentlist", dbOpenDynaset)
AgentName = Me!AgentCombo
AgentSort = Me.NewSortcode
NewAgent = Me.NewAgentName
With Rs
    .FindFirst "agentcode = '" & AgentName & "'"
    !Sortcode = AgentSort
    !Agentcode = NewAgent

strSQL = "Insert into NewAgentList From " & Rs & " Where agentcode = '" & NewAgent & "';"
DoCmd.RunSQL strSQL

    .CLOSE
End With

Set Rs = Nothing
Set Db = Nothing

It just says my function cannot return the name of a function or procedure....no other clues or even an offer to debug.

 

[Eupher]

Okay, I'm a little confused...

AgentCombo is bound to newagentlist. You're selecting an agent name from the combo which becomes the criteria for a query, and the goal of this whole operation is to write the entire corresponding record to... the newagentlist table? By doing this, haven't you simply appended a duplicate record? I must be missing something here...

Ken S.

 

[adalger]

The For Each loop in my post is what would copy the data from all the fields. In your code, you FindFirst, and then you don't do anything with it, and then you AddNew -- which means the FindFirst was useless.

And yes, the .update sends the changes to the original table.

 

[Fozzy9767]

Eupher, yes I want to write a duplicate record, then update two of the fields making it no longer a duplicate. Just trying to avoid having to rekey a bunch of data that is identicle except for two fields.

Adalger, the find first is to get the existing record, I then want to copy that record as new and modify two fields so it is no longer a copy.

I just thought it should be simple. This is what happens when an end user tells you "We used to just have a button that created a new record we could modify so we didn't have to type everything. Besides, we can't see the existing record while we are adding a new one."

Ken

 

[Eupher]

Fozzy,

Forgive me for being dense - I understand what you want to do - but I don't understand which fields you want to modify. Everything we've discussed so far serves simply to copy an existing record and append it to the table. Where does the modification come into the picture?

Ken S.

 

[Fozzy9767]

These are from my form:

AgentSort = Me.NewSortcode
NewAgent = Me.NewAgentName

These hold the new values for the two fields "agentcode" and "sortcode". The combo box is for the user to choose which record they want a copy of. They should be able to choose from the dropdown, enter the modified values in the text boxes, click the button and have the table updated.

I have yet been able to copy a record. If I could get the record copied somewhere so I can update the two foelds then append the new record to the original table I'd be very happy.

I started out trying to insert the results of a query into a temp table, that didn't work. I've since tried a few of your suggestions to no avail. Maybe I'll create a copy of my table structure as a real object, then bounce stuff back and forth.

Ken.

 

[adalger]

Yes, the FindFirst goes to the existing record, but when you AddNew, you discard that bookmark without having copied any of the existing data. That's the purpose of the For Each loop and the second Recordset object in my code.

What you probably want is to open your form to the existing record, then something like this:

Private Sub Form_Load()
    Dim Rs as DAO.Recordset
    Dim Fld as Field

    Set Rs = Me.Recordset.Clone
    Rs.Bookmard = Me.Bookmark

    Me.Recordset.AddNew
        For Each Fld in Rs.Fields
        Me.Recordset.Fields(Fld.Name) = Rs.Fields(Fld.Name)
    Next Fld
End Sub

Private Sub cmdSave_Click()
    Me.Recordset.Update
End Sub

 

[Eupher]

Okay, I think it's coming into focus. User selects the record to copy from the AgentCombo control. But data in 2 of the fields needs to be updated with data entered in 2 textboxes on the form. Have I got that right?