My first attempt at record set - cant get it to work 2

[Egglar]

Hi, im trying to make a record set that searched a field in a table, if the data thats enterd into a text box on a form, it returns a message saying so, ive been reading around a lot and trying different things, this is what i have so far

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strCriteria As String
Dim strValue As String
strCriteria = txtSupplierShortName.Text
strSQL = "SELECT tblSupplier.Shortname FROM tblSupplier"
Set rst = CurrentDb.OpenRecordset(strSQL)
strValue = rst
rst.MoveFirst
Do While Not strCriteria = strValue And rst.EOF
If Not strCriteria = strValue Then
rst.MoveNext
Else
End If
Loop
rst.Close
Set rst = Nothing

The thing that keeps getting the error is the strValue = rst, how do i make strValue be the current record the record set is on?

Is there a better way of doing this, or is my code not correct please let me know.

Thanks, Elliot.

 

[jrbarnett]

Acces has no particular record numbers. To read data from the recordset use

strValue = rst!fieldname

or
strValue = rst.Fields("fieldname&quot

If you want to retrieve a recordset where the data matches your field strValue, set the code as

set rst = db.OpenRecordset ("SELECT tblSupplier.Shortname FROM tblSupplier where fieldname='" & strValue & "'&quot
and it will contain only records that match the value.

John

 

[Egglar]

Thanks for your reply.

My code now looks like this:

strCriteria = txtSupplierShortName.Text
strSQL = "SELECT tblSupplier.Shortname FROM tblSupplier"
set rst = CurrentDb.OpenRecordset ("SELECT tblSupplier.Shortname FROM tblSupplier&quot
rst.MoveFirst
strValue = rst!Shortname
Do While Not strCriteria = strValue And rst.EOF
If Not strCriteria = strValue Then
rst.MoveNext
Else
MsgBox "recordfound"
End If
Loop
rst.Close
Set rst = Nothing

When ever i run it i get a "Type mismatch" error and the debug highlghts

Set rst = CurrentDb.OpenRecordset("SELECT tblSupplier.Shortname FROM tblSupplier&quot


What does this mean as ive been getting this error quite a lot?

 

[Egglar]

I found out i think i should be using Dim rst As DAO.Recordset instead of Dim rst As ADODB.Recordset or any thing else?

Im on Access 2002.

But now DAO.recordset gives me an error "User defined type not defined" and debug highlights DAO.Recordset.

Can any one help me on this?

Thanks in advance.

 

[GHolden]

You are right, ADO doesn't support the CurrentDB.

To get the DAO working you need to add a reference to the Microsoft DAO Object Library. When in a module, go to Tools - References and tick this library.



There are two ways to write error-free programs; only the third one works.

 

[Egglar]

DAO is already ticked, what line should i use to set the rst with when using the dao library? Im currently using this:

Set rst = Db.OpenRecordset("SELECT tblSupplier.Shortname FROM tblSupplier&quot

Thanks, Elliot

 

[GHolden]

Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strCriteria As String
Dim strValue As String

strCriteria = txtSupplierShortName.Text
strSQL = "SELECT tblSupplier.Shortname FROM tblSupplier"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

Should work????



There are two ways to write error-free programs; only the third one works.

 

[Egglar]

Just to confirm:

<img src=

http://www.nrpcommercial.com/images/refs.jpg>

Im still getting &quot;user defined type not defined&quot; error when compileing, and the debugger highlights the rst As DAO.Recordset.

About 30 mins ago when i was trying differnt things, vb/access would hang when i typed dao.recordset. Do i have a problem with my dao library?

 

[GHolden]

It sounds like something is not quite right, because the code looks sound enough.



There are two ways to write error-free programs; only the third one works.

 

[jamescen]

Go to your VBA form and check if Microsoft DAO 3.6 Object Library is selected under Tools, Reference menu.

If you only wanted to check if a name already exists in a record, why not try the DCOUNT Function? Look under Help for more details.

dim intCount as integer
intCount = DCount("[Shortname]","TblSupplier","[Shortname] = '" & strCriteria & "'")

if intCount > 0 then
   msgBox "Record Found"
end if

 

[Egglar]

Thanks guys!

It seems i have an out of date dao library. I searched around on the Microsoft site and coulndt find any thing, so searched google and got the latest verson i think.

Its working now any way. Thanks again for your help.

I will play around with the dcount function now!

Thanks again

 

[stickers]

If you want the ADO syntax, try this (or something similar)

Dim cnCurrent As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnCurrent = CurrentProject.Connection
Set rst = new ADODB.Recordset

rst.Open &quot;Your SQL Here&quot;, cnCurrent, CursorTypeHere, _LockTypeHere

Then your code

THen remember to shut down the connections

rst.Close
cnCurrent.Close
Set rst = Nothing
Set cnCurrent = Nothing

Worth a go anyway...

Sarah