Setting row source using SQL

[policechiefwiggum]

Hello,

I've been looking on google for a few days to try and figure out how to do this, and i've now confused myself!

I have a combobox which users will select a product.
They then need to launch an external application and an XML file associated with the product.

I have the combobox rowsource correctly set up and I have a buton working to launch the application and notepad, the location of the XML files on the NAS drive is stored in the database.

The problem i'm having is getting the XML location out of the database and using it to open the the correct XML file.

I have this code attached to the onChange of my combobox

Dim XMLLoc As String
Dim strSQL As String
Dim InsVar As String

InsVar = Me.Combo45.Value

strSQL = "SELECT XMLURL FROM TblMaint WHERE Insurer = '" & Insvar & "'"

DoCmd.RunSQL strSQL

'Me.TxtBxXML.RowSource =
'Me.TxtBxXML.Requery
MsgBox (Me.TxtBxXML.RowSource)

TxtBxXML was a textbox but is now a listbox, my plan is to populate the listbox with 1 value (the XML location) and put that in the string used to open the notepad.

using this code

Dim stAppName As String
    Dim stTxtName As String
    Dim XMLLoc As String
    If Me.TxtBxXML.Value > "" Then
    XMLLoc = Me.TxtBxXML.Value
    stAppName = "Explorer \\gd-nas01\Software\Polaris\Polaris Web Service Test Client\"
    stTxtName = "notepad.exe " & XMLLoc
    Call Shell(stAppName, 1)
    Call Shell(stTxtName, 1)
    MsgBox (XMLLoc)
    Else
    stAppName = "Explorer \\gd-nas01\Software\Polaris\Polaris Web Service Test Client\"
    Call Shell(stAppName, 1)
    End If

Many Thanks
Pete

 

[policechiefwiggum]

I should probably add the above is using Access 2010 - i have previously done this but on Access 2003 using an ADODB connection which 2010 doesnt seem to support

 

[dhookom]

I'm confused. I don't see how your posts relate to the subject "Setting row source using SQL". DoCmd.RunSQL is for action queries, not select queries.

If the strSQL returns only a single field and record, why use a list box? You could use DLookup() to get the value and possibly display it in a locked and disabled text box.

Duane
Hook'D on Access
MS Access MVP