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
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
' ' 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
.Parameters.Append .CreateParameter( _
.CommandText = "up_select_orders_for_customers"
Set .ActiveConnection = gcnn
Set rst = New ADODB.Recordset
.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, , ";")
Set .ActiveConnection = Nothing
Set cmd = Nothing
' Use ADO's GetString to fetch the data
strList = rst.GetString(adClipString, _
' Set the RowSource of cboOrders to the data
Me!cboOrders.RowSource = Left(strList, 2000)
Set rst = Nothing
ExitHere: Exit Sub
' Add Error Handler
Your cboOrders should now be populated with the following order numbers:
Andy Baron and Mary Chipman's "Microsoft Access 2000 Developer's Guide to SQL Server" is helpful for this sort of thing.