Comparing data entry fields with query results

[Scanner]

I've been getting ALOT of help from this site lately and it's been a real life-saver. I know that as soon as I learn enough I'll be giving back to the community. But for now, I have another issue.

I have a database and have set up a data entry form for "trouble tickets" at the helpdesk. It works like a charm.

I also created a "find duplicate records" query that will look for TWO specific fields within a record for duplication, but only if they are BOTH the same.

What I need to do is find a way of comparing the first two fields entered in the data entry page to the query and display if the record is a "potential" duplicate (I'll explain more on "potential" later).

I know that one way would be to use the two fields as a composite key instead of the more common "autonumber" field, but I really can't open that can of worms. Especially since both of the fields involved are in relationships with other tables in the database, and it feeds into the whole "potential" duplicate issue.

The fields involved are something like this...

Depts
Hardware
Software
Services
TicketNo (sequentially numbered ie. 001, 002, etc...)
Issue1
Issue2
Issue3

Here's the rub...

1. All three departments use the same set of ticket numbers and they are restricted to three digits (001-999) so I can't use the ticket number as my primary key (the way I wanted to).

2. All three departments REUSE the ticket numbers as needed. There are four "Software" tickets numbered 0001 in this database. This is where "potential" duplicates come in. Even if there are duplicate records for these two fields, it may be intended for the dept/ticketno combination to be created again.

3. I have no way of changing #1 or #2. I've been shot down so many times the rounds are starting to go through the holes left by previous battles.

Ok, I hope I've given enough info.

Thanks in advance for any help!!!

Scanner

 

[fneily]

Not quite totally clear. Supplying your table structures would probably be helpful. I would like to see if your tables are normalized.

But I think, if I understand what you want to do, you could in the query create a new field which would be a concatenation with the two fields in question. Then on your data entry form, after the first two fields are entered, on something like the AfterUpdate of the second field, through code, concatenate those two fields, then loop through the query results recordset comparing to the concatenated field in the query. If a match is found, send out a msgbox.

Maybe someone else sees your question another way.

 

[MajP]

You may want to look at faq700-6905. It is an easy way to supply your db structure that fneily suggests.

 

[Scanner]

Sorry, it didn't hit me until I read it on the site that "issue1 issue2 issue3" looks like way-bad design. The "issues" are merely the description of the issue, ie...

issue1 = "Widget 3 went bad in the Dematriculation Confibrilator"
issue2 = "The old Snozoccilator Bearing no longer fits in the new RF Frequency Grease dispenser"
issue3 = "When cleaning the Keniflin Valve, Jethro noticed four Terbital Transmutators need re-calibrated"

In all seriousness, I would love to rebuild this database, but I was told in no uncertain terms to live with it as-is.

-------------------------------------------------------

This is the table layout for the associated tables and fields. Please note, there are other tables and other fields within these tables, but they have nothing to do with the issue at hand as they are not involved in any relationship and are not involved in any queries or forms with regard to this question:

tblDepts
DeptID (Autonumber/primary key)
DeptName (DataType=Text, FieldSize=10)

tblTickets
TicketID (Autonumber/primary key)
DeptID (DataType=Number, FieldSize=Long Integer)
PRNumber (DataType=Number, FieldSize=Byte) - This is the three digit number from my earlier post

Now for the Relationship:

tblDepts.DeptID (one)<-->(many) tblTickets.DeptID

--------------------------------------------------------

It might help to explain how there might be more than one instance of data to deal with:

Date TicketID DeptID PRNumber Desc
1/1/04 1 1 (Hardware) 001 Drive won't spin
1/1/04 2 2 (Software) 001 Calculation in totals is incorrect
. . . . .
. . . . .
8/5/06 1396 1 (Hardware) 001 CRT went bad
. . . . .
. . . . .
4/13/08 4002 1 (Hardware) 001 Power supply spike caused circuit board to fry

Like I said before, all departments use the same range of PRNumbers, and the PRNumbers are re-issued over and over again. It's lousy design, but I can't do anything about it.

---------------------------------------------------------

nfeily-I think your suggestion is right on the money. I have already concatenated the two fields in my query. What I don't know how to do is the code which will:

a. Concatenate the two fields on the form
b. Loop through the recordset
c. Return the results if a match is found and give the option to modify an existing entry or create a new one

Maybe it's a case of asking the question in the wrong forum, but I wasn't sure if it should be in Queries, Forms or Tables and Relationships.

 

[PHV]

You may consider the DLookUp function.
Note: in VBA and JetSQL, the concatenation operator is &.

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

 

[fneily]

I'll assume in the QBE design that you're new field looks something like
Fullname:[firstname] & " " & [lastname]
(may or may not have a space.

You could use PHV's suggestion of using Dlookup or write some code like the following exampleI placed it on the BeforeUpdate event of the form so this way no data will be saved until a decision is made)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim holdname As String
holdname = Me![fname] & " " & Me![lname]
Set db = CurrentDb
Set rst = db.OpenRecordset("Fullname_query", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
If holdname = rst![Fullname] Then
MsgBox ("Duplicate found. Reenter.")
Me![fname].SetFocus
Exit Do
Else
rst.MoveNext
End If
Loop
End Sub

So it takes what the user types in, then before the record can be saved, it joins the first two fields together, opens the query, loops through the query to find a match, and if found issues a warning, then sets the focus back to the first control.
You can look up the msgbox function and see how you can change it to ask a question, test for the answer to continue or not.
Also, make sure you have the reference to the DAO library near the top of the library list. In VBA, click Tools - References. Make sure the latest library version of Microsoft DAO is checked and about third or fourth in the list.