Using value in List Box to fire a query

[policechiefwiggum]

Hi All,

I have a linked table that pulls a load of data from an SQL server, i then have a query that filters this data and that query is used as the rowsource for a list box.

I then have another linked table that connects to a SharePoint list. This list has a query that takes the ID of the selected item in the listbox, and then selects a related document from the SharePoint list.

What i want to do, is set the result of the 2nd (SharePoint) query as the value of a text box.

The SharePoint query will need to fire and the value of the text box will need to change each time a record is selected in the list box.

I've tried me.text2.controlsource = query1 but this gives me a #Name? error

Any ideas is appreciated.

Thanks
Pete

 

[SkipVought]

hi,

Perform your query in a user defined function, and retun the specific VALUE from your query that is required, as...

Function GetNomen(sPN As String) As String
'SkipVought/2006 Mar 7
'--------------------------------------------------
' Access: DWPROD.FRH_MRP.READ
'--------------------------------------------------
':this function returns nomenclature for a given part number
'--------------------------------------------------
'2011-9-26 Converted to Parameter Query
'--------------------------------------------------

    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection, cmd As ADODB.Command
    
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    sServer = "dwprod"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    sSQL = "SELECT PM.Nomen_201 "
    sSQL = sSQL & "FROM FRH_MRP.PSK02101 PM "
    sSQL = sSQL & "WHERE PM.PARTNO_201 =?"
    

    Debug.Print sSQL
        
   With cmd
        .CommandText = sSQL
        .CommandType = adCmdText
        .Prepared = True
        
        .Parameters.Append .CreateParameter( _
            Name:="PARTNO_201", _
            Type:=adChar, _
            Direction:=adParamInput, _
            Size:=16, _
            Value:=sPN)
        
        .ActiveConnection = cnn
        
        Set rst = .Execute
    End With
                          
    rst.MoveFirst
    
    If Err.Number = 0 Then
        [highlight]GetNomen = rst("NOMEN_201")[/highlight]
    Else
        [highlight]GetNomen = ""[/highlight]
    End If
    
    rst.Close
    cnn.Close
    
    Set cmd = Nothing
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[policechiefwiggum]

OK, maybe not the best solution, but i've just changed the text box to a list box and set my sharepoint query as its rowsource

 

[policechiefwiggum]

Thanks SkipVought looks like we replied at the same time