ADO and DAO

[JonoB]

Hi, I am quite new to ado and dao, and by the looks of things, ado is the preferred method (bearing in mind future proofing of applications).

I am trying to prevent a form opening if there are no records, and have tried both ADO and DAO, but cant seem to get it right. Any help appreciated!

************
ADO Method:
************

Dim rs As ADODB.Recordset
Dim stDocName As String
Dim stLinkCriteria As String

Set rs = New ADODB.Recordset

rs.Open "qryContactDetailsSearch"
If rs.EOF And rs.BOF Then
MsgBox ("No records found")
rs.Close
Set rs = Nothing

Else
stDocName = "sfrmSearchResultsContact"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If


************
DAO Method:
************
Dim rs As DAO.Recordset
Dim stDocName As String
Dim stLinkCriteria As String

Set rs = CurrentDb.OpenRecordset("qryCompanyDetailsSearch")

If rs.EOF And rs.BOF Then
MsgBox ("No records found")
Else
stDocName = "sfrmSearchResultsCompany"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Set rs = Nothing

 

[JonoB]

OK, figured out what was going wrong : one of the criteria fields in my qry is obtained from a form....and the recordset doesnt like that.

So have to figure a way around this.

 

[LittleSmudge]

Jono

There is no reason why you can't have values drawn from a form in your criteria AS LONG AS the form is open at the time that the query runs.

If your query is not finding the value on an open form thenits another issue entirely - don't blame the Recordset.

As for DAO / ADO

DAO is Access version 1.x, version 2, 95 and 97

After that MS introduced ADO as the 'new'way to access and manipulate data.

There are still a few things that have not been ( and never will be ) implemented in ADO. They are rare though. If you are learning from scratch I'd certainly advise focusing all of your attention on ADO and backfilling the odd bit of DAO if/when you ever need to.

Your ADO example above won't work for one key step is missing
You haven't bound the recordset to the datasource
After the Set rst = line add
rst.ActiveConnection = CurrentProject.Connection




'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[JonoB]

Hi Smudge,

Thanks for the reply.

I made the changes as you have suggested. However, I now get an error message (code stops on the rs.Open ("qryContactDetailsSearch") line) saying
"Invalid sql statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'

Should I perhaps be using the sql from my qry in the vba instead?

 

[p27br]

you should specify the connection :

rs.Open "qryContactDetailsSearch", currentproject.connection

 

[RoyVidar]

Is the form(s) where the parameter(s) are collected from open when you try this, and the actual controls holding values?

Roy-Vidar

 

[JonoB]

Hi Roy,

Yes to both of those....

Basically, its an open ended search box, that lets users search for any string in the field. There is a pop-up form bound to the qry, that works 100%. I want to try prevent the pop-up form opening if there are no records.

Here is the sql of qryContactDetailsSearch

SELECT tblCompanyDetails.CompanyNameID, tblCompanyDetails.CompanyName, [ContactFirstName] & " " & [ContactLastName] AS Name
FROM tblCompanyDetails INNER JOIN tblContactDetails ON tblCompanyDetails.CompanyNameID = tblContactDetails.CompanyNameID
WHERE ((([ContactFirstName] & " " & [ContactLastName]) Like "*" & [Forms]![frmSwitch]![txtSearchBox] & "*"))
ORDER BY [ContactFirstName] & " " & [ContactLastName];

And this is the code that I am getting problems with:
Dim rs As ADODB.Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Dim strsql As String

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

rs.Open ("qryContactDetailsSearch")

If rs.EOF And rs.BOF Then
MsgBox ("No records found")
rs.Close
Set rs = Nothing

Else
stDocName = "sfrmSearchResultsContact"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Any help much appreciated.

 

[RoyVidar]

Using the like operator on combined text fields. Never done that before, and was not able to make it work thru a stored query either. Got results using sql string.

Here's a very quick and dirty translation, you might have to do some tweaking:
[tt]
dim sSql as string
sSql = "SELECT tblCompanyDetails.CompanyNameID, tblCompanyDetails.CompanyName, " & _
"[ContactFirstName] & ' ' & [ContactLastName] AS sName " & _
"FROM tblCompanyDetails INNER JOIN tblContactDetails ON " & _
"tblCompanyDetails.CompanyNameID = tblContactDetails.CompanyNameID " & _
"WHERE ((([ContactFirstName] & ' ' & [ContactLastName]) Like '%" & _
[Forms]![frmSwitch]![txtSearchBox] & "%'))"[/tt]

coupla notes - wildcard in ADO is '%', within the sql string, substituted quote (") with single quote (')

[tt]with rs
.activeconnection=currentproject.connection
.cursorlocation=aduseclient
.cursortype=adopenforwardonly
.locktype=adlockreadonly
.open sSql, options:=adcmdtext
if .bof and .eof then
' ...
else
' ...
end if
rs.close
end with
set rs=nothing[/tt]

Roy-Vidar

 

[JonoB]

Thanks Roy, will give it a bash.

Just as a matter of interest - the like operator on combined text fields in a stored query works 100%.....the trick to get it working how I wanted was to have a * operator in front and after the text string, which makes it quite an open ended criteria. i.e. if I search for *jon*, it will find "jonob", "mr jon", etc.