Excel - Lookup Data from Combo box filled from backend

[Hap007]

Hap007
Hi,

I have done a good deal of VB and VBA work in Access and VB, but not sure how to handle these same tasks in Excel.

I have managed to add buttons and controls in Excel and have located the code. Now I need the help.

I want to have a combo (Drop down) control lookup a job number in a remote MS Access table.

Once I have done that lookup, I want the value placed in a certain row.

The rows are for different times of the day, and the column is Job Number.

Any suggestions on getting this done?

Is there a way to double click on a cell and the lookup becomes visible and I can select a value?

I need the same lookup to fill and one of the 24 columns, based on which row the user is currently positioned.

Is this possible in Excel?
If so, any suggestions on how?
Should I use a Control on the datasheet or should I use a Form?

Note: I originally posted this question in the wrong forum.

Thanks,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[SkipVought]

Hi,

Here's an example of a user defined function that taps an ACCESS database.

Use the resultset to populate your combo...

Function GetHrsOfOper(sResource As String, dDateIn As Date)
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    Dim sPrevCC As String, BCC As Boolean
    
    sPath = "\\bhdfwfp426.bh.textron.com\M_Ctr$\1_Supply Chain\FP\Procedures\NewAdmin"
    sDB = "APS UNIVERSE"
    
    Set cnn = New ADODB.Connection

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & sPath & "\" & sDB & ".mdb;"
    
    Set rst = New ADODB.Recordset
    
    sSQL = "SELECT (1-TypeValue)*24 "

    sSQL = sSQL & "FROM Resource_calendar_data "

    sSQL = sSQL & "WHERE Resource Like '" & sResource & "%'"
    sSQL = sSQL & "  AND #" & Format(dDateIn, "mm/dd/yyyy") & "# >=FromDate"
    sSQL = sSQL & "  AND #" & Format(dDateIn, "mm/dd/yyyy") & "# <=ToDate"
        
    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        On Error Resume Next
        .MoveFirst
        If Err.Number = 0 Then
            GetHrsOfOper = rst(0)
        Else
            GetHrsOfOper = 24
        End If
        
        .Close
    End With
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}