Problem running VBA Insert Into

[Irish lad]

All,


I continue to find the information on this forum fantastic. Most of the time I can work out my issues but I am stumped again (with something no doubt very simple).


In my music database I am trying to copy information over from one track (Track A) to another (roles of individual musicians) on a different album (Track B). I want to use a simple VBA code as follows: look up the track on the other album (which has a unique TrackID) and copy the role/musician information over with the Track B unique TrackID. The code is as follows:


Private Sub cmbCopyRoles_Click()
Dim strSQL As String

strSQL = "INSERT INTO jtblTrackRoles (TrackID, Musician ID, RoleID)" _
& " SELECT " & Me.txtTrackID & "," _
& " MusicianID," _
& " RoleID" _
& " FROM jtblTrackRoles" _
& " WHERE TrackID = " & Me.cmbChooseRoles.Value
Debug.Print strSQL

CurrentDb.Execute strSQL, dbFailOnError

End Sub


The debug information is



INSERT INTO jtblTrackRoles (TrackID, Musician ID, RoleID) SELECT 3290, MusicianID, RoleID FROM jtblTrackRoles WHERE TrackID = 3233


which looks like what I am trying to achieve.


One thought is that there is a TrackRoleID Autonumber in the jtblTrackRoles. Does that matter? I assumed Access would autofill it.


Many thanks for any guidance!

 

[June7]

The autonumber field is not issue. What error do you encounter? I suspect the SQL thinks 3290 is a field, not data. Try alias field:

" SELECT " & Me.txtTrackID & " AS TID," _

 

[Irish lad]

Thanks for the reply. Adding the "AS TrackID" did not make a difference. I still get the Error 3134. It's odd as the debug statement looks like it is trying to do the right thing (copying RoleID and MusicianID but with a different TrackID), at least to my eyes.

 

[June7]

Yes, code looks like what has worked for me. If you want to provide file for analysis, follow instructions at bottom of my post.

Make sure there is a space in front of AS: " AS TID"

 

[Gasman]

Musician ID in the insert statement should be [Musician ID] but you appear to have MusicianID ?

 

[Irish lad]

Thanks Gasman. I was just about to post that this was my error.

In case of interest to other amateurs, posting the results of the debug from the Immediate window as a Query showed the errant space straightaway (and Gasman would have bailed me out anyway!)

 

[June7]

Good catch Gasman, did not see that at all.

Just did a test and find the alias fieldname is not needed after all. All those years of wasted keystrokes …