Access Data from a txtBox on a form using SQL

[Magno101]

Hi all,
First let me start out with what I want to happen with this and then
maybe y'all can give me some better insight. I have a database in
Microsoft Access which we need to upsize to SQL. We currently have a
problem with it now. On one of our forms (frmScan, which is an unbound
form with two unbound controls) there is a txtBox contorl (txtScan)
which gets a number from a barcode put into it (usually a 9 character
varchar). I have a list box underneath this control which uses a query
as its rowsource. The trick is, I want that query to filter its data
using the specified information in the txtScan control. In access I
did this eaisly with a module and some VBA. But SQL wont let me work
like this now (or I don't know quite how to work with it now). Any
suggestions?

Thanks,
Marcus

[Albert D. Kallal]

You can try the following:

(first, I shall assume that the two tables involved are linked to tables on
sql server, and those tables can be viewed when you simply click on the
table name in the standard "tables" tab. I am also assuming that we are
talking about linked tables (odbc)....however, the following should also
work if you are in fact used a adp proejct.

What I would do is in the txtScan after update event, simply stuff in the
sql source into the listbox.

So, in the after update event, you go:

dim strSql as string

strSql = "select * from tblProducts where ProductCode = " & me.txtScan & _
" Order by ProductDescription"

me.MyListbox.RowSource = strSql

Of course, if your ProducutCode, (or whatever you filter by) is a string,
then we need:

strSql = "select * from tblProducts where ProductCode = '" & me.txtScan &
"'"
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is the ListBox's RowSource property an SQL string or the name of a
query/table?

Usually I set up the RowSource to use an SQL string w/ a ref to the
control I want to use as a filter. E.g.:

SELECT * FROM table_name WHERE column_name = Form!control_name

The "Form!control_name" pulls the data from the control on the current
form. I believe this format can be used in an .adp or ODBC linked
table.

Required: In the control's AfterUpdate event .Requery the ListBox.

If you want to use a stored procedure (SP) you can put something like
this in the ListBox's RowSource property:

EXEC usp_SP_Name control_name

"control_name" will act as the parameter to the SP "usp_SP_Name." If
that doesn't work, you may have to use a syntax like this:

"EXEC usp_SP_Name " & Form!control_name

Remember to .Requery the ListBox in the TextBox's AfterUpdate event.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQUIlp4echKqOuFEgEQLKvACgqp5r5+PI9eiQydg9o6qJhM 4gOmMAoJQf
qWYf4qQQ6rCmXx4Pc90/yzCF
=XKOa
-----END PGP SIGNATURE-----
Magno101 wrote:

Hi all,
First let me start out with what I want to happen with this and then
maybe y'all can give me some better insight. I have a database in
Microsoft Access which we need to upsize to SQL. We currently have a
problem with it now. On one of our forms (frmScan, which is an unbound
form with two unbound controls) there is a txtBox contorl (txtScan)
which gets a number from a barcode put into it (usually a 9 character
varchar). I have a list box underneath this control which uses a query
as its rowsource. The trick is, I want that query to filter its data
using the specified information in the txtScan control. In access I
did this eaisly with a module and some VBA. But SQL wont let me work
like this now (or I don't know quite how to work with it now). Any
suggestions?

[Rich P]

In some event, like the txtScan AfterUpdate event you can add this code

Private Sub txtScan_AfterUpdate()
lstScan.RowSourceType = "Table/Query"
lstScan.RowSource = Select * yourQuery WHere ScanNum = " & txtScan
Me.Requery
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!