ADP Parameterized list box query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All:
I am trying to populate a list box with a table-valued function or a stored
procedure. The list box is used to navigate among records that populate the
form, so the same query can be used for the form and the list box thereby,
theoretically, reducing calls to the DB. The records are filtered on two
fields, we'll call them type (GUID) and name (nvarchar), whereby the values
are in two different controls on the form.

The form is using a function, fActiveCompanies, and the imput parameter
propety is completed. The form is able to navigate through the records with
no problem.

I am having difficulty create a SMOOTH method of populating the list box.
currently i am using a stored procedure that is called in VB and populates
the list box with a list of values.

I would prefer to not use a list of values and set the row source type to
Table/View/Stored Procedure.

Please point me in the correct direction for populaitng hte list box not
using a list of values... thank you

Access 2003 - Access Data Projects (ADP) - ADO
SQL Server 2000 (production) SQL Server Express 2005 (devlopment)


my current code for populating the list box is below.

Dim str As String: str = ""
Dim lst As String: lst = ""

Dim rds As ADODB.Recordset
Set rds = New ADODB.Recordset

Forms!frmcompanies!lstCompanies.RowSource = lst
str = "EXEC spActiveCompanies @pIdTypeCompany='" &
Nz(Forms!frmcompanies!cboTblTypeEntity, "%") & "', @pNameLegal='" &
Nz(Forms!frmcompanies!txtNav, "%") & "'"

rds.Open str, CurrentProject.Connection

Do Until rds.EOF

If lst = "" Then
lst = """" & rds(0) & """;""" & rds(1) & """"
Else
lst = lst & ";""" & rds(0) & """;""" & rds(1) & """"
End If

rds.MoveNext
Loop

Forms!frmcompanies!lstCompanies.RowSource = lst
Forms!frmcompanies!lstCompanies.Requery

rds.Close: Set rds = Nothing

Forms!frmcompanies.Requery
 
i mis-read one document, the parameters must be the same as the controls...

field = @ComboBoxControlName
 
i mis-read one document, the parameters must be the same as the controls...

field = @ComboBoxControlName
 
Back
Top