Find existing record with multiple criteria

TheWedgie

Registered User.
Local time
Tomorrow, 07:00
Joined
Mar 15, 2009
Messages
19
Alrighty, what I'd like to achieve is to have when a user enters a surname (field "Family Name") and first name (field "Given Name"), as an After Update event (I think?), Access checks the existing records in the table (which changes for each event*, but can just use me.recordset I think) for the same two values, and if there is one (or more) pops up a MsgBox.

I have NFI where to start, tried DLookup but got confused!


*not my design, I'm retrofitting an existing DB
 
I believe you can still use DLookup to see if a value exists.

If DLookup("[PersonID]","tblPersons", "[LastName] ='" & txtLastName & "' AND [FirstName]='" & txtFirstName & "'") > 0 then
msgbox "It Exists"
End if

You can also use recordset.

dim db as DAO.Database
dim rs as DAO.Recordset
dim rsSQL as String

Set db = CurrentDB

rsSQL = "SELECT tblPersons.PersonID, tblPersons.FirstName, tblPersons.LastName FROM tblPersons WHERE (((tblPersons.FirstName)='" & txtFirstName & "') AND ((tblPersons.LastName)='" & txtLastName & "'));"

Set rs = db.OpenRecordSet(rsSQL, dbOpenDynaSet)

If Not rs.EOF and rs.BOF Then
msgbox "It Exists"
End If

Set rs = nothing
Set db = nothing
 
I would run a querry that the search criteria include the actual field names on the form. then requerry the records.

For example:
SELECT * from TABLE
where FIELD1= [forms]![NameofForm]![FieldName]
 
Thanks DevastatioN, will try that out and let you know :)
 
I would say that's not really a good design then. You shouldn't have to have different tables for different events, etc.

And you'd be 100% correct. I have no idea why the guy before me did it like that, but until I get the time to redo it from the ground up, I'm getting the critical features and GUI working.

(As background, it's a medical triage system used for event medical services, so for some reason he's got it creating a new table for every event!)
 
I believe you can still use DLookup to see if a value exists.

If DLookup("[PersonID]","tblPersons", "[LastName] ='" & txtLastName & "' AND [FirstName]='" & txtFirstName & "'") > 0 then
msgbox "It Exists"
End if

You can also use recordset.

dim db as DAO.Database
dim rs as DAO.Recordset
dim rsSQL as String

Set db = CurrentDB

rsSQL = "SELECT tblPersons.PersonID, tblPersons.FirstName, tblPersons.LastName FROM tblPersons WHERE (((tblPersons.FirstName)='" & txtFirstName & "') AND ((tblPersons.LastName)='" & txtLastName & "'));"

Set rs = db.OpenRecordSet(rsSQL, dbOpenDynaSet)

If Not rs.EOF and rs.BOF Then
msgbox "It Exists"
End If

Set rs = nothing
Set db = nothing


Couldn't get either of these to work - first one didn't make sense as I want to find any records where both first name and surname are the same as what was just entered - there is no PersonID field (or anything resembling it).
The second one didn't like not having tblPersons (and everything I tried to replace it with didn't work).
 
Ok I will explain a bit more clearly what each one does.

The DLookup looks up the primary key (PersonID in my example, just because this is the easiest field to pick if something exists or not), from the table where you want to lookup the duplicates (tblPersons in my example, this is the table that contains your name), the last part is the filter, it says only return results where the LastName in the table is exactly the same as the LastName in your textbox, AND results where the FirstName in the table is exactly the same as the FirstName in your textbox.

If this DLookup returns something greater than 0, it means that there must be at least one record in the table that has the exact first name and exact last name.

The recordset is very similar:

I am SELECTING the primary key, first name, and last name, FROM the table that stores the information you wish to look for duplicates, WHERE the LastName in the table is exactly the same as the LastName in your textbox, AND results where the FirstName in the table is exactly the same as the FirstName in your textbox.

The basic idea is the same, I'm looking to see if any records exist by looking up the primary key, filtering it by the FirstName and LastName in the table.
 
Ahh, that makes a lot more sense - the Primary Key thing was getting me confused. At work today so will try it out tonight, thanks!
 
Woo - it works, thanks DevatstioN!

Here's what I'm currently using:
If DLookup("[Job Number]", Me.RecordSource, "[Family Name] ='" & Me.Family_Name.Value & "' AND [Given Name]='" & Me.Given_Name.Value & "'") > 0 Then
msgbox "It Exists"
End if

Any ideas how I can get it to return the highest [Job Number] that matches, and also ignore the job number of the one currently being inputted?
 
To get the one not currently being entered, try something like:

"[Family Name] ='" & Me.Family_Name.Value & "' AND [Given Name]='" & Me.Given_Name.Value & "' AND Not [Job Number] =" & Me.Job_Number.Value

This should automatically look for the highest job number, but if not there may be a way to use the "ORDER BY" prompt, I have never done this with DLookup, however you could look at ordering/sorting a DLookup
 
Thanks, looks like I can do an OrderBy with ELookup, will have a crack later tonight.
 
Excellent, that works well DevastatioN.
I haven't bothered with OrderBy, as I've used the DLookup results to set off another Search/Filter function that was already implemented, and that'll do for now.

Thanks again for your help.
 

Users who are viewing this thread

Back
Top Bottom