SQL within VBA ..... the basics

[gibben]

Hi all,
I am just trying to get the basic hang of using sql within the VBA attached to my database ... without a great lot of luck.

This is the basic format I have so far:

Public Sub DoSQL()

Dim SQL As String
SQL = "SELECT Breed.Sub_Species " & _
"FROM Breed"
Dim db As dao.database
Dim rs As dao.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)


End Sub

But every time I call it up I get this:

Compile Error:
User defined type not defined

[db As dao.database] <<-- with this highlighted within the code.

Thanks in advance ...

 

[GHolden]

Hi,

When you're in the code window, go to tools - references and make sure you have a tick next to Microsoft DAO Library.

Hopefully this will fix it...


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

 

[gibben]

Thanks ... that solved the problem with the compile time errors

 

[gibben]

Well I have that part of the problem solved, just having a few problems getting my code to work as it should. I am tryin to create a check for existing records, this is so that I can prevent certain duplicate combinations from being created (starting simple with one hardwired value).

This is the code I have:

Private Function DoSQL()


Dim SQL As String
SQL = &quot;SELECT Master_Location.Name FROM Master_Location WHERE Master_Location.Name ='Invermay'&quot;
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)
If rs.NoMatch Then
MsgBox &quot;No record was found.&quot;
Else: MsgBox &quot;A matching record has been found.&quot;
End If
rs.Close
End Function

However regardless of wether the record exists or not rs.NoMatch is always coming up as &quot;False&quot; so the system is always saying a record has been found.

I am pretty new to VBA so will be guessing the problem is in my coding ......

Thanks

 

[GHolden]

You need to change rs.NoMatch (this is only set after searches) to rs.EOF (this is set if you are at the End Of File) which will only be true when you first open the recordset if no records have been returned.

ie.

If rs.EOF Then
MsgBox &quot;No record was found.&quot;
Else
MsgBox &quot;A matching record has been found.&quot;
End If



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