Help!
I need to assign a recordset to an MS Access Project form. The recordset is based on a SQL function that seeks an input parameter to return one record.
The objective is to return a record from SQL Srv 2000 that has a company name (key field) that is equivalent to a value selected from a combo box on an Access Project form.
My problem is that the function is not filtering the record set so I'm getting all records returned rather than the one record that has the desired company name.
The combo box is named "COY". The function is named fnCompanyListing. The parameter is @companyname
****************
SQL Server code:
SELECT TOP 100 PERCENT Company, Counter, Region, HQ_Address1, HQ_Address2, HQ_Address3, HQ_Address4, Broker, Bound, Status, LastUpdatedBy, LastUpdate, Producer, Carrier, Client, HQ_Telephone, HQ_Fax FROM dbo.tb_Company_Main WHERE (Company = @companyname)
ORDER BY Company
************
MS Access form code to call function and assign recordset:
Dim rst As ADODB.Recordset, str As String
Set rst = New ADODB.Recordset
str = [COY] 'combo box on form
rst.Open "select * from dbo.fnCompanyListing('" & str & "')", CurrentProject.Connection
Me.Refresh
*******
Please advise where I've gone wrong
Thanks
I need to assign a recordset to an MS Access Project form. The recordset is based on a SQL function that seeks an input parameter to return one record.
The objective is to return a record from SQL Srv 2000 that has a company name (key field) that is equivalent to a value selected from a combo box on an Access Project form.
My problem is that the function is not filtering the record set so I'm getting all records returned rather than the one record that has the desired company name.
The combo box is named "COY". The function is named fnCompanyListing. The parameter is @companyname
****************
SQL Server code:
SELECT TOP 100 PERCENT Company, Counter, Region, HQ_Address1, HQ_Address2, HQ_Address3, HQ_Address4, Broker, Bound, Status, LastUpdatedBy, LastUpdate, Producer, Carrier, Client, HQ_Telephone, HQ_Fax FROM dbo.tb_Company_Main WHERE (Company = @companyname)
ORDER BY Company
************
MS Access form code to call function and assign recordset:
Dim rst As ADODB.Recordset, str As String
Set rst = New ADODB.Recordset
str = [COY] 'combo box on form
rst.Open "select * from dbo.fnCompanyListing('" & str & "')", CurrentProject.Connection
Me.Refresh
*******
Please advise where I've gone wrong
Thanks