Findfirst with 2 criteria not working

[simonyu]

Hi there,

I try to use Findfirst to search for records.
However it only works when I use Findfirst with 1 criteria but not with 2 criteria.
Did I do something wrong? Here is the code.
Thank you for your help

There are 2 tables, TRIAL_BALANCE and BILL_ITEMS tables.
In TRAIL_BALANCE, there are fields, table_id , category_id, amount and others
In BILL_ITEMS, there are fields, id, Cat_id, amount and others.
My function is similar to as below:

Private Sub btnEdit103_click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TRIAL_BALANCE")

' table_id is in table TRIAL_BALANCE while id is in table BILL_ITEMS
' category_id is in TRIAL_BALANCE while Cat_id is in BILL_ITEMS

' BELOW LINE IS NOT WORKING AND NOT SHOW ANY RESULT
rs.FindFirst "[table_id]='" & Me.id & "' AND [category_id]=" & Me.Cat_id
' PROBLEM ON ABOVE LINE
rs.Close

Set rs = Nothing
Set db = Nothing


End Sub

 

[pbaldy]

What are the data types of those 2 fields? Let's get you started in a better direction, which is to open the recordset on the filtered data, rather than on all records. In other words something like:

Set rs = db.OpenRecordset("SELECT * FROM TRIAL_BALANCE WHERE [table_id]='" & Me.id & "' AND [category_id]=" & Me.Cat_id")

Of course we have to fix that for the correct delimiters.

 

[MajP]

The FindFirst method is intended to find a string in the records, you are trying to build it like a WHERE clause

That makes no sense. Of course it is a where clause just without the word where

A String used to locate the record. It is like the WHERE clause in an SQL statement, but without the word WHERE.

There is nothing wrong with the two criteria as originally written. Now lets see what is really resolves to
debug.print "[table_id]='" & Me.id & "' AND [category_id]=" & Me.Cat_id

But as mentioned if you are not using all the records then filter first in your open recordset method.

 

[Insane_ai]

Here's an example of a Find operation with multiple criteria.

https://access-programmers.co.uk/forums/showthread.php?t=203515

 

[MarkK]

Is this your code? That's it?

Set rs = db.OpenRecordset("TRIAL_BALANCE")

' table_id is in table TRIAL_BALANCE while id is in table BILL_ITEMS
' category_id is in TRIAL_BALANCE while Cat_id is in BILL_ITEMS
  ' BELOW LINE IS NOT WORKING AND NOT SHOW ANY RESULT
       rs.FindFirst "[table_id]='" & Me.id & "' AND [category_id]=" & Me.Cat_id
  ' PROBLEM ON ABOVE LINE
      rs.Close

IF so, what result are you expecting? You have three lines of code, 1) you open the recordset, you 2) run the find first, and you 3) close the recordset. How do you know the FindFirst didn't work?
See what I mean?
Add this line...

   if not rs.nomatch then msgbox "found!"

...and actually test the result.
hth
Mark

 

[simonyu]

Hi pbaldy,
These datatype are Integer.
I tried your suggestion, however the line turn to RED and could not run.

Set rs = db.OpenRecordset("SELECT * FROM TRIAL_BALANCE WHERE [table_id]='" & Me.id & "' AND
[category_id]=" & Me.Cat_id")



MajP, MarKK, I intend to find the record and Edit then Update but I didn't mention.
I do the debug.print but still show no record.

Debug.Print "table_id='" & Me.id & "' AND category_id=" & Me.category_id


I tried to use strCriteria but still shows no record.

strCriteria = "table_id='" & Me.id & "' AND category_id=" & Me.category_id

rs.FindFirst strCriteria



Thank you,
Simonyu

 

[pbaldy]

Try

("SELECT * FROM TRIAL_BALANCE WHERE [table_id]=" & Me.id & " AND
[category_id]=" & Me.Cat_id)

 

[The_Doc_Man]

These datatype are Integer.

strCriteria = "table_id='" & Me.id & "' AND category_id=" & Me.category_id

If BOTH are integer, you have inconsistently delimited them. In the resultant string, Me.ID will be presented between apostrophes but Me.Category_ID will not. If they are both integer data types, they should both be treated the same way. If they really are integers, then I don't think you need the apostrophes for either one.

 

[simonyu]

hi the_doc_man,


I use below line and it works.
rs.FindFirst "table_id=" & Me.id & " AND category_id=" & Me.category_id


Thank you very much,
simonyu

 

[pbaldy]

Did you try opening with the SQL in post 7, adjusted for the different control name? What you have works but brings the entire table over the network, then looks for the needed records. It's more efficient to only bring the desired records over the network.

 

[simonyu]

Hi Paul,


I tried but there was RED line so it could not execute.
Thank you,
simonyu

 

[pbaldy]

Try

Set rs = db.OpenRecordset("SELECT * FROM TRIAL_BALANCE WHERE table_id=" & Me.id & " AND category_id=" & Me.category_id)