how to AND Search in access 2000?

[timhysniu]

Hello guys,

I am trying to search using 2 fields (First and Last name). I have no idea how to execute any SQL (which im sure is silly). So im using this code in a form:

DoCmd.ShowAllRecords
DoCmd.GoToRecord , , acFirst

If strLast <> "" Then
DoCmd.GoToControl "Last Name"
DoCmd.FindRecord strLast, acEntire, , acDown, , acCurrent
First_Name.SetFocus
End If

If strFirst <> "" Then
DoCmd.GoToControl "First Name"
DoCmd.FindRecord strFirst, acEntire, , acDown, , acCurrent
End If

The records are sorted Last (ASC), First (ASC). So i guessed this was the closest bet. The thing is, when the second search is done, it seems like the cursor goes back to the beginning and then searches for the First name with whatever last name is first (incorrect entry of course).

Can someone help me out do a simple AND Search using two search fields (First Name and Last Name)?

Thank You
tim

 

[RuralGuy]

Try something like this instead:

Me.RecordsetClone.FindFirst "[First] & [Last] = '" & [First Name] & [Last Name] & "'"
If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate <" & [First Name] & " " & [Last Name] & ">"
End If

It does require a reference to DAO x.x Object Library (use the latest)

Spaces in *any* name is a lousy idea and will give you unexpected grief someday. I use CamelFontNames!

 

[timhysniu]

Thanks so much, but not quite there yet

Sounds like a good example. I could not make it work though. And thats because I can't find any DAO library in my VBA. Do you have any idea why my access has no DAO library. It does have ADODB but thats about it.

Thanks so much

P.S. Sorry I'm new to this thing. I gotta tell you it already have me enough stress.

Try something like this instead:

Me.RecordsetClone.FindFirst "[First] & [Last] = '" & [First Name] & [Last Name] & "'"
If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate <" & [First Name] & " " & [Last Name] & ">"
End If

It does require a reference to DAO x.x Object Library (use the latest)

Spaces in *any* name is a lousy idea and will give you unexpected grief someday. I use CamelFontNames!

 

[workmad3]

if you are using an ADODB connection, then you could try the following:

Dim db As New ADODB.Connection
Dim rs As ADODB.Recordset

db.ConnectionString = "Connection String here"
db.Open

Set rs = db.execute("SELECT * FROM [table names] WHERE First = '" & [First Name] & "' AND Last = '" & [Last Name] & "'"

then you can get to the values using the standard recordset controls

hope this helps(with the problem and getting SQL to run as well )

 

[RuralGuy]

My DAO Library is called "Microsoft DAO 3.6 Object Library". You have nothing like this in your references?

 

[timhysniu]

DAO Library? where can I find this. How do you use one?

It appears that there is no DAO library in both of my computers (work and home). Or maybe I'm a bit clueless about what DAO really is.

Can you give me an example on how you use a DAO library?

I was thinking maybe it should be on there the same way that ADODB is. So say, instead of using Dim rst As ADODB.Recordset I would use:

Dim rst As DAO.Recordset

Well that one does not work. Anything with DAO doesnt work and it doesn't even show up as a library. Any ideas?


Thanks to all for replying. You guys are quick

tim

My DAO Library is called "Microsoft DAO 3.6 Object Library". You have nothing like this in your references?

 

[RuralGuy]

How are you checking for the references? While looking at a code page go to Tools>References and scroll down to the M's. Is that what you are doing?

 

[Pat Hartman]

The RecordSetClone is DAO so if you want to manipulate it, you will need to install DAO if you don't already have it. You can download it from the Microsoft site or go back to your Office install and do a complete installation.

PS, the code would be more like:

Me.RecordsetClone.FindFirst "[First Name] = '" & Me.First_Name & " AND [Last Name] = '" & Me.Last_Name & "'"
If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate <" & Me.First_Name & " " & Me.Last_Name & ">"
End If

Please note the underscores. No programming language will accept object names that contain embedded spaces or special characters. VBA changes these offending characters to underscores. In the future, avoid using spaces or underscores in your names.

Also, if any of your names actually contain the single quote character ' - you will have an error when searching for a name such as O'Brien. It would be better to use the double quote as the delimiter.