[RESOLVED] Search problem

I have a database in access connected though ADO.

I want to retrieve data from the table that contains many fields using a command button once inputed a unique field (id) and get rest of fields onto remaining textboxes.

Thanks in advance

VB Code:

Dim sSQL As String
sSQL = "SELECT field1, field2, field3 etc FROM tablename "
sSQL = sSQL & "WHERE idfieldname = '" & txtId.Text & "' "
Set rs = New ADODB.Recordset
rs.Open sSQL, AdoConnectionObjName
Text1.Text = rs.Fields.Item("field1").Value
Text2.Text = rs.Fields.Item("field2").Value
Text3.Text = rs.Fields.Item("field3").Value
etc
rs.Close
Set rs = Nothing

Where rs is the name of your recordset object and AdoConnectionObjName is the name of your connection object.

Thank for the reply hack,

What if the id it's not unique because have different dates?. If i have a button to scroll through the different dated id's and display rest of info on the textboxes.

Thanks in advance

Either select based on id and date to get a unique record, or select on based on id and get all records for that id.

"WHERE idfieldname = '" & txtId.Text & "' And DateFieldName = #" & txtDate.Text & "#"

Thank for the reply,

Now i´m having a problem, any value i input in the id txtbox returns the first row of the database.

Al42,

What i want it´s to retrive several records with the same id but different dates only.

Thanks in advance.

cant you use what hack gave you to select the data then just use a for loop to display all records matching it?
Im not sure on the code

rs.Open sSQL, AdoConnectionObjName
for i = 1 to rs.recordcount
'Text1.Text = rs.Fields.Item("field1").Value
'Text2.Text = rs.Fields.Item("field2").Value
'Text3.Text = rs.Fields.Item("field3").Value
'etc
rs.movenext
next
rs.Close

if your returning several records then you might want to consider grid-like controls such as flexgrid or listview. Otherwise, using textboxes, you will have to provide control for navigating through the records. Often this is accomplished with data bound ado control but most would advise against using databound controls.

Thanks all for your help.....Resolved