Duplicates in Table/Query

[velcrowe]

I am using a combo box to assign gun numbers on an employee form (frmemployees). I also have a query listing only the gun numbers that have been assigned. When I select a gun from my combo box, I what to evaluate if it is already on the gun assignment query. That way I cannot assign the same gun to two different people. I am using the before_update event to do this. The code that I am using is as follows:

Private Sub intgunNumber_BeforeUpdate(Cancel As Integer)

If DLookup([intgunNumber], "qrygunassignmentcurrent", Me.intgunNumber) = Me.intgunNumber Then
MsgBox "Item # is already listed.", vbOK, "DUPLICATE FOUND"
Cancel = True
End If

"me" represents my frmemployees form. The problem is that it's causing an event even for the numbers that aren't listed on the query. I am trying to get the evaluation correct but am having difficulties. All the fields are numeric fields.

 

[rainman89]

this is a link for dlookup samples

u might consider using a dcount to check if there is more than one using the same gun instead...

its basically the same as a dlookup but u would use

if dcount("*", tablename, "gunnumberassigned= " Me.intgunNumber) >0 then
Msgbox "you have already assigned that gun"
else
endif

 

[velcrowe]

I know that I am doing something wrong because even when I use this code the event is triggered as though every gun i choose is already assigned. That isnt the case.

 

[rainman89]

what event are u putting the code in and what is the structure of your tables?

 

[velcrowe]

The before update event

The intgunnumber field is based on a gun information table so that the gun and it's information is selected from the combo box on the frmEmployees form

The employee table has the intgunnumber field and the frmEmployees is based on this employee table.

I built the qrygunassignment query just to evaluate the guns already chosen.

The tables that has intgunnumber are all indexed for the field

I also tried putting a list box on the form showing the guns already assigned. I was hoping to choose a gun from the combo box and as its evaluated against the list it would trigger the event to prevent the duplicate. However I couldn't code correctly...is this a possibility?

 

[rainman89]

why not just create a query for guns not assigned and allow people to chose from them. that way there is no possibility for them to select a gun that is already chosen.

you said u created the query to show which ones HAVE been chosen. just change the combo box query it so it shows which guns HAVE NOT been chosen.

 

[velcrowe]

That's a great Idea. I will adjust the combo box now.