Changing Seek with a Null to Findfirst Method

[Dan]

Hi,


The following code uses the seek method to find where the
value in cboCarnumber does not match any records in
tblExtensionInfo.

Set Mydata = MyDB.OpenRecordset("tblExtensionInfo"):
MydataOpen = True
Mydata.Index = "carSearch": Mydata.Seek "=", cbocarNumber,
Null


How would I change this statement from Seek to the
Findfirst method?

Thanks in advance for your help.

 

[Allen Browne]

FindFirst accepts something that looks like the WHERE clause of a SQL
statement, so you can use:

MyData.FindFirst "(Field1 = " & Me.cboCarNumber & ") AND (Field2 Is Null)"


However, the best approah is to only fetch the records you need, and specify
your sort order like this:

strSQL = "SELECT * FROM tblExtensionInfo WHERE ((Field1 = " &
Me.cboCarNumber & ") AND (Field2 Is Null)) ORDER BY Field1, Field2;"
Set Mydata = MyDB.OpenRecordset(strSQL)


Remember to add the quote marks as delimiters around string values in the
SQL statement, or # around date values.

 

[Sandra Daigle]

Hi Dan,

If you are trying to identify the condition where there are no matches for
cbocarNumber I'd probably go with a Dlookup statement. Recordsets are
wonderful but I wouldn't open one if this is the sole purpose for opening
it. Assuming that you need the recordset for something other than this
lookup, here's some aircode:

dim MyData as DAO.recordset
dim MyDB as DAO.database
set myDb=currentdb
set mydata=mydb.openrecordset("Select * from tblExtensionInfo;")
with Mydata
.findfirst "carNumber=" & cboCarNumber
if not .nomatch then
'do what you want when
'there are no matches
else
'do whatever you want if there are
'matches
endif
.close
end with

Several things to note:

- Findfirst will not work with a tabletype recordset. Hence the "Select *. .
..". You could also use the name of a query in place of the SQL

- Instead of "select *" with no criteria, in practice I'd use a where clause
to restrict the data as much as possible.

- if your search field (presumably 'carNumber') is actually text then the
test value must be wrapped in quotes:
..findfirst "carNumber=""" & cboCarNumber & """"

Hope this helps you -

 

[Guest]

Thanks for the help. I originally read this statement as
looking for where there weren't any matches with
cbocarnumber and tblextensiontype. Now I think I was
wrong. Do you believe that the null at the end of the
statement is asking for a specific field to be null?
CboCarnumber has about 9 fields in it. I was wondering
how to tell which fields this statement is talking about.
Thanks again.

Dan

 

[Allen Browne]

Open the form in design view.
Right-click the combo cboCarnumber, and choose Properties.
On the Data tab, which is the Bound Column?
Then look at the RowSource, and it will tell you what field name is in that
column position.

 

[Dan]

Thanks for all your help. I'm going to use your
suggestion and use the strsql string. One question. For
the following line of code, where would I put the ' marks
as delimiters, as all fields are text fields.

strsql = "SELECT * FROM tblOverAllowableBids WHERE
((Loannumber = & Me.cboLoanNumber& ) AND (Loannumber = "
& Me.cboLoanNumber & "));"

Thanks again.

Dan

 

[Allen Browne]

strsql = "SELECT * FROM tblOverAllowableBids WHERE Loannumber = """ &
Me.cboLoanNumber & """;"

 

[Dan]

Sorry about that last post. I had the field names wrong.
I'm using the following code and am getting a data type
error. All fields are text and I'm not sure where to put
the ' marks.
strsql = "SELECT * FROM tblOverAllowableBids WHERE
((LoanNumber = " & Me.cboLoanNumber.Column(0) & ") AND
(ClientNumber = " & Me.cboLoanNumber.Column(1) & "))"

Thanks again

 

[Allen Browne]

If you are still stuck after my last reply (parallel to this one), this
article explans how to build a WHERE clause, and the techique is the same:
http://allenbrowne.com/casu-07.html

 

[Dan]

Thanks for all your help and pointing me to your site. I
really appreciate it.

Dan

 

[Dan]

Hi Sandra,

Thanks for the reply and help.

Dan

-----Original Message-----
Hi Dan,

If you are trying to identify the condition where there are no matches for
cbocarNumber I'd probably go with a Dlookup statement. Recordsets are
wonderful but I wouldn't open one if this is the sole purpose for opening
it. Assuming that you need the recordset for something other than this
lookup, here's some aircode:

dim MyData as DAO.recordset
dim MyDB as DAO.database
set myDb=currentdb
set mydata=mydb.openrecordset("Select * from tblExtensionInfo;")
with Mydata
.findfirst "carNumber=" & cboCarNumber
if not .nomatch then
'do what you want when
'there are no matches
else
'do whatever you want if there are
'matches
endif
.close
end with

Several things to note:

- Findfirst will not work with a tabletype recordset. Hence the "Select *. .
..". You could also use the name of a query in place of the SQL

- Instead of "select *" with no criteria, in practice I'd use a where clause
to restrict the data as much as possible.

- if your search field (presumably 'carNumber') is actually text then the
test value must be wrapped in quotes:
..findfirst "carNumber=""" & cboCarNumber & """"

Hope this helps you -

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi,


The following code uses the seek method to find where the
value in cboCarnumber does not match any records in
tblExtensionInfo.

Set Mydata = MyDB.OpenRecordset("tblExtensionInfo"):
MydataOpen = True
Mydata.Index = "carSearch": Mydata.Seek "=", cbocarNumber,
Null


How would I change this statement from Seek to the
Findfirst method?

Thanks in advance for your help.

.