Finding Records in 2007 2

[perrymans]

Must be something different, because I am getting a data type mismatch error on my findfirst.

    With Me.RecordsetClone
        .FindFirst "[MemberNr] = " & Str(Nz(Me![txtMemberNr_Search], 0))
        If Not .NoMatch Then Me.Bookmark = .Bookmark
    End With

Oddly, it works with the MemberID (the primary key) but not MemberNr.

Thanks. Sean.

 

[TheAceMan1]

Howdy perrymans . . .

Wrong function used here!

My impression is that [blue]MemberNr[/blue] is a string. If thats true what you need is:

[blue].FindFirst "[MemberNr] = [red][b]'[/b][/red]" & [red][b]C[/b][/red]Str(Nz(Me![txtMemberNr_Search], 0) & "[red][b]'[/b][/red]"[purple])[/purple][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[perrymans]

Thanks, that does work.

However, it is painfully slow. It is an ODBC connection to an online MySQL database. Filtering actually happens pretty quick.

Would it be better to filter for the record?

Or is there a faster way to do the find?

Thanks. Sean.

 

[TheAceMan1]

perrymans . . .

I've never used MySQL, but logic saids to perform as few calculations as possible in the criteria ... perferably none! With that, try the following:

[blue]   Dim Cri As String
   
   Cri = "[MemberNr] = " & Str(Nz(Me![txtMemberNr_Search], 0))
   
   With Me.RecordsetClone
      .FindFirst Cri
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With[/blue]

If its still slow the go with the filtering.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[perrymans]

Still too slow. I'll just give up and filter.

So now, which one is better?

DoCmd.RunCommand acCmdFind

OR

Me.Filter = "MemberNr = '" & Me.txtMemberNr_Search & "'"
Me.FilterOn = True

Thanks. Sean.

 

[TheAceMan1]

perrymans . . .

As I said ... I don't have or have used MYSQL.

You'll just have to do some testing to see which one is the better performer!

Whish I could help more.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

I'd create an index on MemberNr

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[perrymans]

Thanks guys.

PHV, I didn't even think to heck for an index, and there wasn't one there!

Of course, that still didn't help any, but at least a found what would have been a performance issue later on.

AceMan,

I was asking which was better from a standpoint of Access and using the most recent and relevent procedures.

MySQL and Access find seem to be just too slow together. but filtering is lightning fast so I will just stick with that.

Thanks. Sean.

 

[TheAceMan1]

perrymans . . .

Its only logical to use the latest ... as you can only expect legacy to eventually fall off the charts.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]