VBA SQL Troubles

[adam.greer]

Hi Guys

I've been searching for a while and have made several changes to my code but I seem to go from one error to the next, and this last one has got me.

    Dim myRec  As DAO.Recordset
    Dim TrackN As String
    Dim strSQL   As String
   
    
    
    strSQL = "SELECT Count(PlaylistDetails.PLaylistDetailsID) AS NewTrackNum" & _
    "FROM PlaylistDetails" & _
    "WHERE PlaylistDetails.PlaylistID =[Forms]![Playlists]![PlaylistID]"
    
    Set myRec = CurrentDb.OpenRecordset(strSQL)
    
    TrackN = strSQL + 1

    Form_PlaylistsSubForm.TrackNum = TrackN

I receive the 'Run Time Error 3141'
The SELECT statement includes a reserved word or an arguement name that is mispelled or missing.


The SQL is copied from a query that works no problem.

Any ideas guys?

 

[GohDiamond]

The word COUNT(...) is a reserved function in access.

I think we need more information, like: What are you trying to select? and from where?(a query?)

Goh

 

[adam.greer]

Ok I am trying to count all the records in the PlaylistDetails table (these are tracks in a music playlist) where the PlaylistID is the currently selected one on a form.

Once it has counted it will then +1 to that value and enter it into the TrackNum field on my subform.

The end result being when a new track is placed a on a playlist it has the next track number.

An auto number cannot be used because I don't want a table for each playlist. So i am trying to find a solution in code.

Anything else you need to know? I'm pretty stumped here.

 

[JohnInStockie]

Hi Guys

I've been searching for a while and have made several changes to my code but I seem to go from one error to the next, and this last one has got me.

    Dim myRec  As DAO.Recordset
    Dim TrackN As String
    Dim strSQL   As String
   
    
    
    strSQL = "SELECT Count(PlaylistDetails.PLaylistDetailsID) AS NewTrackNum" & _
    "FROM PlaylistDetails" & _
    "WHERE PlaylistDetails.PlaylistID =[Forms]![Playlists]![PlaylistID]"
    
    Set myRec = CurrentDb.OpenRecordset(strSQL)
    
    TrackN = strSQL + 1

    Form_PlaylistsSubForm.TrackNum = TrackN

I receive the 'Run Time Error 3141'
The SELECT statement includes a reserved word or an arguement name that is mispelled or missing.


The SQL is copied from a query that works no problem.

Any ideas guys?

Adam, looking at your SQL it looks like you need to put a couple of spaces in.

e.g.
strSQL = "SELECT Count(PlaylistDetails.PLaylistDetailsID) AS NewTrackNum " & _
"FROM PlaylistDetails " & _
"WHERE PlaylistDetails.PlaylistID =[Forms]![Playlists]![PlaylistID]"

hope this helps

John

 

[pbaldy]

I think John has correctly found the immediate problem (while Count is indeed a reserved word, it is being used correctly here). However, you're going to run into another problem. This line is not what you want:

TrackN = strSQL + 1

First, declare TrackN as either an integer or long, as appropriate (you want a numeric variable, not a string variable). Then change that line to:

TrackN = myRec!NewTrackNum + 1

Edited to add: You may also need to change to this:

"WHERE PlaylistDetails.PlaylistID = " & [Forms]![Playlists]![PlaylistID]

 

[adam.greer]

Hi guys

Thanks for your help so far. I've made a few changes to the code, but now I receive a different error.

Dim myRec  As DAO.Recordset
    Dim TrackN As Long
    Dim strSQL As String
    
    
    strSQL = "SELECT Count(PlaylistDetails.PLaylistDetailsID) AS NewTrackNum " & _
    "FROM PlaylistDetails " & _
    "WHERE PlaylistDetails.PlaylistID = " & [Forms]![Playlists]![PlaylistID]
    
    Set myRec = CurrentDb.OpenRecordset(strSQL)
    
    TrackN = myRec!NewTrackNum + 1
    
    Forms!Playlists.Form!PlaylistsSubform.NewTrackNum = TrackN

On the Line 'Forms!Playlists.Form!PlaylistsSubform.NewTrackNum = TrackN' I receive 'Run-time error 438' Object doesn't support this property or method'

I'm just trying to update the text value of a field on a SubForm. What am I missing this time.

Thanks again

 

[pbaldy]

Doesn't look like a valid reference to the control. Good info here:

http://www.mvps.org/access/forms/frm0031.htm

 

[adam.greer]

Excellent that was exactly when I needed

Thanks alot for the help guys, you've done it again.