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 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