11 Replies - 2518 Views - Last Post: 10 May 2010 - 08:54 AM Rate Topic: -----

#1 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Access "Find" Function

Posted 07 May 2010 - 09:32 AM

So I have a program in access that runs a duplicate query on a table. If the query comes back with dups, I have to loop throught the table finding these instances and flag them as dups. A table can contain any ammount of records from 100 to 100k so you could see how this could become time consuming. My question is this: is there a way I can set the name of my dup as string and seek it out in the table to go directly to that record? I've read a few things off google that point me towards SQL but I'm curious if VB has anything to offer.

As always, thanks in advance!

Is This A Good Question/Topic? 0
  • +

Replies To: Access "Find" Function

#2 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: Access "Find" Function

Posted 08 May 2010 - 07:30 AM

this is one of the way i found in net

http://www.databasej...cess-Tables.htm


http://office.micros...0345581033.aspx

This post has been edited by thava: 08 May 2010 - 07:32 AM

Was This Post Helpful? 0
  • +
  • -

#3 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Re: Access "Find" Function

Posted 08 May 2010 - 07:42 AM

View Postthava, on 08 May 2010 - 06:30 AM, said:



Thank! I was just coming on to an update actually...I've been experimenting with Seek and FindFirst, Find Next. I am trying to use the find methods for now becuase I can't seem to get the Seek syntax down so here's what I have

Sub DemoDupDeleter2()
    
    Dim rsMn As Recordset
    Dim rsQuery As Recordset
    Dim dupnums As Integer
    Dim MRN As String
    
    Set rsQuery = CurrentDb.OpenRecordset("Main_Demo_Dups")
    Set rsMn = CurrentDb.OpenRecordset("Main_Demo")
    
    rsQuery.MoveFirst
    dupnums = rsQuery.Fields("Numberofdups")
    MRN = rsQuery.Fields("MRN Field")
    
    rsMn.MoveFirst
        Do Until dupnums = 1
            rsMn.FindFirst MRN ''''''''''''''''''''''''stops here''''''''''''
            If rsMn.NoMatch Then MsgBox "close"
            rsMn.Fields("Flag") = "X"
        Loop
        
End Sub



I get an error that says I'm using the operation wrong....thanks for the links, I am going to check those out now
Was This Post Helpful? 0
  • +
  • -

#4 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Access "Find" Function

Posted 08 May 2010 - 03:26 PM

I have been able to get FindFirst to work only with DAO recordset. Try:
Dim rsMn As DAO.Recordset 
Set rsMn = CurrentDb.OpenRecordset("Main_Demo", dbOpenDynaset)
Be sure to set Reference to Microsoft DAO x.x Object Library

The FindFirst method needs criteria (it's like WHERE clause of SQL statement). EX:
rsMn.FindFirst "fieldname = '" & stringValue & "'"
rsMn.FindFirst "fieldname = " & numberValue

Check this link for info on recordsets: http://www.classanyt...recordsets.html

This post has been edited by June7: 08 May 2010 - 03:30 PM

Was This Post Helpful? 1
  • +
  • -

#5 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Re: Access "Find" Function

Posted 10 May 2010 - 06:44 AM

View PostJune7, on 08 May 2010 - 02:26 PM, said:

I have been able to get FindFirst to work only with DAO recordset. Try:
Dim rsMn As DAO.Recordset 
Set rsMn = CurrentDb.OpenRecordset("Main_Demo", dbOpenDynaset)
Be sure to set Reference to Microsoft DAO x.x Object Library

The FindFirst method needs criteria (it's like WHERE clause of SQL statement). EX:
rsMn.FindFirst "fieldname = '" & stringValue & "'"
rsMn.FindFirst "fieldname = " & numberValue

Check this link for info on recordsets: http://www.classanyt...recordsets.html


Thank you very much! I'm definitely making some headway. I've gotten past the original error but now I have a different problem. I get error 3001 'Invalid Argument' at the same line in my code. I imagine, once again, wrong syntax. Would you be so kind as to show me how or point me towards an article that explains the syntax on this.

Also, I am going to have to loop through my recordset more than once to fine my dups, in some cases. If I start with FindFirst then I have to do a second loop of FindNext. I read that I have to preceed FindNext with FindFirst. Is this true or can I start from BOF and go right to FindNext?

Thanks very much!

Sub DemoDupDeleter2()
    
    Dim rsMn As DAO.Recordset
    Dim rsQuery As Recordset
    Dim dupnums As Integer
    Dim MRN As String
    
    Set rsQuery = CurrentDb.OpenRecordset("Main_Demo_Dups")
    Set rsMn = CurrentDb.OpenRecordset("Main_Demo", dbOpenDynaset)
    
    rsQuery.MoveFirst
    dupnums = rsQuery.Fields("Numberofdups")
    MRN = rsQuery.Fields("MRN Field")
    
    rsMn.MoveFirst
        Do Until dupnums = 1
            rsMn.FindFirst (MRN)
           If Not rsMn.NoMatch Then rsMn.Delete
        Loop
        
End Sub


Was This Post Helpful? 0
  • +
  • -

#6 raziel_   User is offline

  • Like a lollipop
  • member icon

Reputation: 469
  • View blog
  • Posts: 4,280
  • Joined: 25-March 09

Re: Access "Find" Function

Posted 10 May 2010 - 06:51 AM

from the site June7 send
here is example:
rs.MoveFirst
rs.FindFirst "pkPeopleID=2"

If rs.NoMatch = True Then
    Debug.Print "Not found"
Else
    Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If


This post has been edited by NoBrain: 10 May 2010 - 06:53 AM

Was This Post Helpful? 0
  • +
  • -

#7 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Re: Access "Find" Function

Posted 10 May 2010 - 07:11 AM

View PostNoBrain, on 10 May 2010 - 05:51 AM, said:

from the site June7 send
here is example:
rs.MoveFirst
rs.FindFirst "pkPeopleID=2"

If rs.NoMatch = True Then
    Debug.Print "Not found"
Else
    Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If



I didn't read down far enough...thank you! This is what I have now and I'm still receiving the same error, 3001.

Sub DemoDupDeleter2()
    
    Dim rsmn As dao.Recordset
    Dim rsQuery As Recordset
    Dim dupnums As Integer
    Dim MRN As String
    
    Set rsQuery = CurrentDb.OpenRecordset("Main_Demo_Dups")
    Set rsmn = CurrentDb.OpenRecordset("Main_Demo", dbOpenDynaset)
    
    rsQuery.MoveFirst
    dupnums = rsQuery.Fields("Numberofdups")
    MRN = rsQuery.Fields("MRN Field")
    
    rsmn.MoveFirst
        Do Until dupnums = 1
            rsmn.FindFirst "MRN"
           If rsmn.NoMatch = False Then rsmn.Delete
        Loop
        
End Sub



Should I try doing this as an ADO vs a DAO? From reading that entire page now, I think I can get a handle on that.
Was This Post Helpful? 0
  • +
  • -

#8 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: Access "Find" Function

Posted 10 May 2010 - 07:39 AM

Sub DemoDupDeleter2()
    
    Dim rsmn As dao.Recordset
    Dim rsQuery As Recordset
    Dim dupnums As Integer
    Dim MRN As String
    
    Set rsQuery = CurrentDb.OpenRecordset("Main_Demo_Dups")
    Set rsmn = CurrentDb.OpenRecordset("Main_Demo", dbOpenDynaset)
    
    rsQuery.MoveFirst
    dupnums = rsQuery.Fields("Numberofdups")
    MRN = rsQuery.Fields("MRN Field")
    
    rsmn.MoveFirst
        Do Until dupnums = 1
            rsmn.FindFirst "[MRN Field] =" & MRN
           If rsmn.NoMatch = False Then rsmn.Delete
        Loop
        
End Sub


check will this solve you

This post has been edited by thava: 10 May 2010 - 07:39 AM

Was This Post Helpful? 1
  • +
  • -

#9 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Re: Access "Find" Function

Posted 10 May 2010 - 07:59 AM

View Postthava, on 10 May 2010 - 06:39 AM, said:

Sub DemoDupDeleter2()
    
    Dim rsmn As dao.Recordset
    Dim rsQuery As Recordset
    Dim dupnums As Integer
    Dim MRN As String
    
    Set rsQuery = CurrentDb.OpenRecordset("Main_Demo_Dups")
    Set rsmn = CurrentDb.OpenRecordset("Main_Demo", dbOpenDynaset)
    
    rsQuery.MoveFirst
    dupnums = rsQuery.Fields("Numberofdups")
    MRN = rsQuery.Fields("MRN Field")
    
    rsmn.MoveFirst
        Do Until dupnums = 1
            rsmn.FindFirst "[MRN Field] =" & MRN
           If rsmn.NoMatch = False Then rsmn.Delete
        Loop
        
End Sub


check will this solve you


I get it..specify the field to search in then the item to search for. Very nice! Thanks!

Now when I run this code, I get a brand new error, '3464' Data type mismatch in criteria or expression. Is it possible that this is because I have it set as a text field in the table I'm working in or should I use integers instead of strings to identify the char sets here?
Was This Post Helpful? 0
  • +
  • -

#10 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: Access "Find" Function

Posted 10 May 2010 - 08:10 AM

if it's a text field
  rsmn.FindFirst "[MRN Field] ='" & MRN & "'"


will solve your problem
Was This Post Helpful? 1
  • +
  • -

#11 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Re: Access "Find" Function

Posted 10 May 2010 - 08:41 AM

View Postthava, on 10 May 2010 - 07:10 AM, said:

if it's a text field
  rsmn.FindFirst "[MRN Field] ='" & MRN & "'"


will solve your problem


AMAZING! Thank you very much. My question is, how do you know that the apostrophy will make the differnce? Obvioulsy you're great at this but how would I figure something like this out in the future without having to ask someone else? What is the value of the "'" that makes a difference in the code?

Thanks very much! Big help as always!
Was This Post Helpful? 0
  • +
  • -

#12 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Access "Find" Function

Posted 10 May 2010 - 08:54 AM

If the field is a text datatype use the apostrophe delimiter. If the field is a date datatype use # as delimiter. No delimiter with number fields.

Re your question about FindFirst preceeding FindNext. Think you may have confused with MoveFirst. I use MoveFirst to make sure am at beginning of recordset for next run through recordset in a loop.

This post has been edited by June7: 10 May 2010 - 09:03 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1