SQL View filtered by a value on a form

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

I'm working with a .ADP. So, no queries, views. I'd
like have the rowsource of a combo box be something
like...

SELECT "*" From Orders where CustomerID = forms!customerID

I can't seem to get the syntax right. Any suggestions?

Thanks, Stuart
 
The select statement that you have write is send directly to the SQL Server,
without any modification. Of course, SQL-Server no nothing about
Forms!MyForm!CustomerID. (I presume that you have simple forget to write
the name of your form after the forms!... in your exemple ).

The use of parameters in ADP is somewhat tricky. In your case, write
something like the following in the open event of your form or in the
current event, if you want a different ID each time:

Me!MyCombo.RowSource = _
"select * from dbo.Orders where CustomerID = " & Me!CustomerID

Access will automatically make a requery when you change the rowsource, but
to have a more pleasant feeling for your user when he open the combobox the
first time, it's not a bad idea to force the transfert of all data from the
recordset to the buffer of the combobox by adding the following lines right
after:

Dim bidon as long
bidon = Me.MyCombo.ListCount

Of course, this will have some effect only if the number of lines in the
recordset if greater than the number of displayed lines in the combobox
(usually 8 by default).

S. L.
 
Thank you!
-----Original Message-----
The select statement that you have write is send directly to the SQL Server,
without any modification. Of course, SQL-Server no nothing about
Forms!MyForm!CustomerID. (I presume that you have simple forget to write
the name of your form after the forms!... in your exemple ).

The use of parameters in ADP is somewhat tricky. In your case, write
something like the following in the open event of your form or in the
current event, if you want a different ID each time:

Me!MyCombo.RowSource = _
"select * from dbo.Orders where CustomerID = " & Me!CustomerID

Access will automatically make a requery when you change the rowsource, but
to have a more pleasant feeling for your user when he open the combobox the
first time, it's not a bad idea to force the transfert of all data from the
recordset to the buffer of the combobox by adding the following lines right
after:

Dim bidon as long
bidon = Me.MyCombo.ListCount

Of course, this will have some effect only if the number of lines in the
recordset if greater than the number of displayed lines in the combobox
(usually 8 by default).

S. L.





.
Click to expand...
 
Back
Top