I am in the process of moving my database to SQL Server. The migration only copied 20% of my queries.
I have 3 fields on the form and the 3rd fields combo box depend on the 2nd field
I have used a Query in the combo boxes reverence the form’s field. Eg forms!form1!Field2
Is there any way to pass this information to a procedure?
Thanks
Japie

You didn’t mention what you’re using for a front end, so I’m going to assume an ADP.

Lets say you have a textbox on a form named txtCustomer and you have a ComboBox named cboOrders. You want to fill cboOrders with all the order numbers associated with the customer number you enter in txtCustomer (I’m playing with the SQL Server version of Northwind here). I typed ‘ALFKI’ into txtCustomer as my CustomerID.

First, paste the following CREATE PROC into Query Analyzer and run it. This will give you a parameterized proc in the Northwind db that will receive a customer number and return only the order numbers for that customer.

CREATE PROC up_select_orders_for_customers
    @CustomerID nvarchar(10)
AS
SELECT OrderID
FROM Orders
WHERE CustomerID = @CustomerID

Second, set cboOrders Row Source Type to value list.

Third, paste the following code in cboOrders On Enter event:

Private Sub cboOrders_Enter()

    Dim rst As ADODB.Recordset
    Dim strList As String
    Dim fOK As Boolean

    If Len(Me!cboOrders.RowSource & "") > 0 Then
        GoTo ExitHere
    End If

' ' This is where you would call your generic "test for a vaild
' ' connection" function.
' ' You want to be sure you have an open connection here. I set a
' ' global connection object called "gcnn" at startup and assign
' ' it to the .ActiveConnection property of my command object below.

' fOK = OpenConnection()
' If Not fOK Then GoTo ExitHere

    Set cmd = New ADODB.Command

    With cmd
        .Parameters.Append .CreateParameter( _
                                        "@CustomerID", _
                                        adWChar, _
                                        adParamInput, _
                                        5, _
                                        Me.txtCustomer)
        .CommandText = "up_select_orders_for_customers"
        Set .ActiveConnection = gcnn
    End With

    Set rst = New ADODB.Recordset

    With rst
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmd, Options:=adCmdStoredProc
' Run the following if you want to view the contents of your
' recordset object.
' Debug.Print rst.GetString(adClipString, , ";")
' rst.MoveFirst
        Set .ActiveConnection = Nothing
    End With

    Set cmd = Nothing

    ' Use ADO's GetString to fetch the data
    strList = rst.GetString(adClipString, _
        ColumnDelimeter:=";", RowDelimeter:=";")

    ' Set the RowSource of cboOrders to the data
    Me!cboOrders.RowSource = Left(strList, 2000)
    rst.Close
    Set rst = Nothing
    ExitHere: Exit Sub
    ' Add Error Handler
End Sub

Your cboOrders should now be populated with the following order numbers:

10643
10692
10702
10835
10952
11011

Andy Baron and Mary Chipman's "Microsoft Access 2000 Developer's Guide to SQL Server" is helpful for this sort of thing.

HTH

Bob

Hi
Thanks I will try this. I am using MSAccess.
Japie

Alternatively, once you've done the following

Second, set cboOrders Row Source Type to Table/View/Procedure

Third, in the after update event of txtCustomer, put the following code:



Brian Skelton
Braxis Computer Services Ltd.

Thanks Brian this is what I was looking for.
Japie