Problem running VBA Insert Into (1 Viewer)

Irish lad

Registered User.
Local time
Today, 13:14
Joined
Jun 19, 2018
Messages
21
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

AWF VIP
Local time
Today, 05:14
Joined
Mar 9, 2014
Messages
5,425
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

Registered User.
Local time
Today, 13:14
Joined
Jun 19, 2018
Messages
21
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

AWF VIP
Local time
Today, 05:14
Joined
Mar 9, 2014
Messages
5,425
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

Enthusiastic Amateur
Local time
Today, 13:14
Joined
Sep 21, 2011
Messages
14,052
Musician ID in the insert statement should be [Musician ID] but you appear to have MusicianID ?
 

Irish lad

Registered User.
Local time
Today, 13:14
Joined
Jun 19, 2018
Messages
21
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

AWF VIP
Local time
Today, 05:14
Joined
Mar 9, 2014
Messages
5,425
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 …:eek:
 

Users who are viewing this thread

Top Bottom