Put Query Field Value into Shell Code

[Musicguy]

I have my music database in Access. My SongFind Query has a field SongFile that lists the full file path name of each song. I have written code to open winamp and cycle through the SongFile column, but I can't figure out how to get that song file data into the shell code. The troublesome line is this:

Shell "C:\Program Files (x86)\winamp\winamp.exe /ADD " & ["SongFile"]

How do I get that field data into the shell code? Is it a syntax problem or what? Without the brackets around "SongFile" Winamp opens, Access runs through all the records in the right place, highlighting each file name as it goes, but instead of loading the song, it loads the word SongFile. Thanks.

 

[dhookom]

Could you share the code that cycles? Is there a record set involved?

Duane
Hook'D on Access
MS Access MVP

 

[Musicguy]

I think I have solved the loading of the field value. Here is the whole code with my fix:

Public Function NewPlay()
    Dim rsTable1 As Recordset
    Set rsTable1 = CurrentDb.OpenRecordset("SongFind")
    Do Until rsTable1.EOF
        'DoCmd.GoToControl "SongFile"
        Shell "C:\Program Files (x86)\winamp\winamp.exe /ADD " & """" & rsTable1!SongFile & """"
            'Dim i As Long, d1 As Date, d2 As Date
            'd1 = Now
            'For i = 0 To 1
            'DoEvents
            'Next i
            'd2 = Now
        
        DoCmd.GoToRecord acDataQuery, "SongFind", acNext, 1
        rsTable1.MoveNext
    Loop
    rsTable1.Close
    Set rsTable1 = Nothing
    SendKeys "^{HOME}"
End Function
[code]

But now I have a new problem.  The whole song table contains hundreds of songs.  I run a query and filter the songs I want to hear to a much smaller number.  When I run the code above from the query it loads the first songs from the whole table, not the query.  How can I get it to load the songs in the query only?

I appreciate the help.  You can see that I do a lot of guessing.

 

[dhookom]

What is the query? I would substitute your query name in place of "SongFind".

What is the purpose of the DoCmd.GoToRecord in the Do Loop?
You should be more explicit:

 Dim rsTable1 As DAO.Recordset

I also try to avoid using SendKeys.

Duane
Hook'D on Access
MS Access MVP

 

[Musicguy]

The Query name is "SongFind" and the field I need data from is "SongFile".

Some of the code are remnants of a previous version of this that cycled through hyperlinks of the SongFile fields and activated each one as it went. The DoCmd.GoToRecord was part of the loop. That code worked fine for 10 years until Windows 7. Now I can't disable the hyperlink warning, and it disrupts the whole process.

I'm sure there is wasted code in there, but my immediate goal is to get it to load the query data, not the whole table. You wrote a line of code in your response. What does it do?

Thanks again.

 

[dhookom]

My line of code replaces your similar line. Your code should only play the records in the query. I don't know how the "winamp.exe /ADD " parameter works.

If you don't know how to debug your code, check the FAQs in this forum.

Duane
Hook'D on Access
MS Access MVP