Referencing a form control in an ADP query/SP 1

[RobSindle]

I have developed Access front-ends (forever) for Access backends and also for SQL backends via ODBC. This is my first attempt to work with an ADP. I'm having trouble with the syntax for something that I do often in Access front-ends, i.e., base a form off of a query that references a control box on the form itself. In the Access .MDB the query looks like this:

SELECT Projects.IDNum, Projects.Project, Projects.CCountry
FROM Projects
WHERE (((Projects.Project) Like '*' & [Forms]![frmProjectsList]![txtProject] & '*'));

I upsized a trivial form and query and in the ADP the 'function' that was created looks like this (and doesn't work).

ALTER FUNCTION dbo.qryProjectsList
(@Forms_frmProjectsList_txtProject nvarchar(60))
RETURNS TABLE
AS
RETURN ( SELECT IDNum, Project, CCountry
FROM dbo.Projects
WHERE (Project LIKE N'*' + @Forms_frmProjectsList_txtProject + N'*') )


When I open the form, I get a prompt to enter a parameter value for
Forms_frmProjectsList_txtProject

No matter what I put in the control, I never get a returned recordset.
Any help would be greatly appreciated.
Rob

 

[giovi2002]

Hi, there are two way to link fields of forms:

1). place a procedure as a recordsource in the form. Select the form and goto properties. Within the second tab ('data', forgive me i've got the dutch access installed)
you'll find a field called 'input parameters' .
Example: within your stored procedure you have a parameter declared called startdate, example:
--
create sp_myproc as
@startdate datetime declared
select * from table
--

Within the form you'll refer to the parameter the 'input parameters' field like:
startdate datetime = forms![...]![etc]
multiple parameters will be separated like
startdate datetime = forms![...]![etc], enddate datetime etc

2). 2nd way to go is to use ADO, if you use procedures with parameters you'll use code like, example:
On Error GoTo Err_Aggregeer_Knop_Click
Dim myado As ADODB.Command
Dim rec As Single
Dim StrBeginperiode As Date
Dim StrEindperiode As Date
Dim stDocName As String

Set myado = New ADODB.Command
myado.ActiveConnection = CurrentProject.Connection
myado.CommandType = adCmdStoredProc
myado.CommandText = "AP_Rep_Punctualiteiten" --name of your proc
''myado.Parameters.Refresh
--two string variables filled with form values, used to
--fill the parameters
StrBeginperiode = Forms![Frm_El14_Conv_Punctualiteiten]![Selectie_Begindatum]
StrEindperiode = Forms![Frm_El14_Conv_Punctualiteiten]![Selectie_Einddatum]
''Forms![Frm_El10_Conv_Aantal_Reizigers]![Selectie_Begindatum]
myado.Parameters.Append
--these two statements fill the parameters with the string
--values
myado.CreateParameter("Beginperiode", adDate, adParamInput, 10, StrBeginperiode)
myado.Parameters.Append myado.CreateParameter("Eindperiode", adDate, adParamInput, 10, StrEindperiode)
myado.Execute rec



Exit_Aggregeer_Knop_Click:
Exit Sub

Err_Aggregeer_Knop_Click:
MsgBox Err.Description
Resume Exit_Aggregeer_Knop_Click

3). if you don't use procedures with parameters you can use 'filters'. There's a special option called server filter

 

[RobSindle]

Thanks, I tried option 1, above. The missing piece was the "input parameters" property. I wasn't aware of it.
I put in Forms!frmProjects!txtProject
in it and it worked as expected.
Now, here's the weird part. I tried a new sp from scratch. It is:

ALTER PROCEDURE dbo.spProjectsList
(@txtProject nvarchar(50) = NULL)
AS SELECT IDNum, Project, CCountry
FROM dbo.Projects
WHERE (Project = @txtProject)

And put this as a recordsource behind the form and it TOO works, exactly the same way! The wierd part is that it behaves as if the WHERE CLAUSE was: Project Like %@txtProject%

(i.e., putting
dam
in the text box gives me EVERY record where the letters dam are included in the project field.
Any ideas?
Thanks,
Rob

 

[RobSindle]

Forget the second half of my last post...
I was changing the recordsource after the fact.
When I put in

dam

I only get the exact match (if any) that you would expect.
Rob

 

[paulgenga]

I guess you are facing the same problem I faced.
I think you are simply trying to make a list/combo box show relevant values. If this is the case then try using the .RowSourse property in VB.

Private Sub ControlAfterUpdate()
  me.Combo1.RowSource = _
  "SELECT IDNum, Project, CCountry " & _
  "FROM Projects " & _
  "WHERE Project " & = Me.txtProject

  me.Combo1.requery

End Sub

Hope you get help

 

[RobSindle]

No that's not what I was looking for. I was looking for basing the recordsource for the whole form on a value in a text box or combo box. Its interesting though, that the recordsource for the combo box can reference the form directly (Me.cboComboBoxName), but the code under the recordsource must receive the variable via the "Input Parameters" property.
Thanks for the response.
Rob