access database control

[phly9]

my question is posted in a web page below, because i needed to add a picture to it. please follow the link

http://www.phly9.com/tvo/TVOCIS.html

thanks

 

[CookieMonster]

Hello phly9,

There's a number of solutions possible to your query.
Assuming that you are using ADO (ActiveX Data Objects) for your data control,
one of the solutions would be to add another data control to your form which will
control only the listbox, a more neater solution would be to create a data object
programmatically.

The possible solution below lets you search for a number using the normal wildcards,
such as * %, assuming that this ID number is indeed a numeric data type.
All possible results of the search will be displayed in your listbox, where you can
doubleclick on an item to display it as the current record.

First you would have to set a reference in your project to Microsoft's ActiveX Data
Objects Library 2.0 or higher.

To keep it simple add to your Search_Click event:
(If you always want all the numbers in the db to display, leave out the LIKE statement)
************************************************************************

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim intSearchValue as integer

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.ConnectionString= "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source= -----put the full path to the access database here-----"

cn.Open

rs.Open "SELECT ----name of the field with the TVO numbers--- FROM ---name of the table--- " & _
"WHERE ----name of the field with the TVO numbers--- " & _
"LIKE " & intSearchValue, cn

lsttvonumlist.Clear
Set lsttvonumlist.Datasource=rs
lsttvonumlist.DataField= ---name of the field in the db---

************************************************************************

Then add to your lsttvonumlist_DoubleClick event:
(wouldn't use OnClick, because users might navigate through list with mouse)
************************************************************************

Dim intSearchValue as integer

intSearchValue = lsttvonumlist.Value

TVOData.RecordSet.Find "---name of the field in the db---" & intSearchValue

************************************************************************

Like I said, there's numerous ways to do this and you might want to think about
getting into some info about ADO. Also, it could be possible that you have to add
some form refresh code here and there, and some bits and bobs to prevent EOF and BOF
errors. One other very good solution would be the 'createparameter' event of the
ADO Data object, this will allow you to send parameters to ie stored queries in your
Access db. (not to be mixed up with Stored Procedures on SQL server.)
The above code connecting to the database would also be a very pro-ish replacement for
your physical Data control. It certainly gives you loads more freedom in accessing your
data, note that you have to create a connection only ONCE, using the 'activeconnection'
property of the ADO object model, simply set it as in this case to object variable 'cn'

If your search criteria is on a string data type, remember that you have to put this
parameter between ' single quotes, as in "'" & parametervariable & "'"

Good luck and feel free to ask if anything is not working out as it should.

Regards from CookieMonster