Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 17th, 2003, 02:06 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access Combo box with sql server

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
  #2 (permalink)  
Old June 17th, 2003, 10:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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
  #3 (permalink)  
Old June 17th, 2003, 11:30 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
Thanks I will try this. I am using MSAccess.
Japie
  #4 (permalink)  
Old June 17th, 2003, 03:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Alternatively, once you've done the following

Quote:
quote: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 Table/View/Procedure

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

Code:
Me.cboOrders.RowSource = "EXEC dbo.up_select_orders_for_customers @CustomerID='" & NZ(me.txtCustomer,"") & "'"


Brian Skelton
Braxis Computer Services Ltd.
  #5 (permalink)  
Old June 18th, 2003, 11:33 AM
Registered User
 
Join Date: Jun 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Brian this is what I was looking for.
Japie


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Access Data from a combo box darkhalf Access VBA 1 February 22nd, 2008 04:28 PM
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
Access combo box does not work with SQL Server jerryk Access 3 July 14th, 2006 03:30 PM
Access 2000 Text not refreshing in combo box mkjo99 Access 1 October 11th, 2004 09:48 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.