comparing records

[javier_83]

I have a form, where i assigning computers to peoples

so 2 persons cant have the same computer

so i need to now, how to make this

if i put a serial number, and someone already have that serial assigned

that a message box appear and tell me that, that serial is already use, and by who

how can i do that??

 

[dkinley]

In the BeforeUpdate() event of the form control of the serial number you could have something like .....

If Not IsNull(DLookup("[SerialNum]", "tblComputer", _
[INDENT][INDENT]"[SerialNum]= '" & Me!txtSerialNum & "'")) Then
 
[/INDENT]MsgBox "There is already a serial number with this value." & _
[INDENT]vbCrLf & "Please enter a different serial number.", vbCritical, "Duplicate Serial Number"
[/INDENT][/INDENT][INDENT]Me.txtSerialNum.Undo
Cancel = True
[/INDENT]End If

-dK

 

[javier_83]

thanks this works perfect

but how can i make that on the messege box, appear also who have that serial?

 

[dkinley]

Probably would have to shove a DLookup in there somewhere ....

Can't recall without testing if DLookup will present information or not ...

Perhaps -

MsgBox "This serial number is in use. The user name: " & _
[INDENT]DLookup("[UserName]", "tblComputer", _
"[SerialNum]= '" & Me!txtSerialNum & "'") & " has the computer."
[/INDENT]

If it will not .. will need to assign a variable -

Dim sUserName As String
 
sUserName = DLookup("[UserName]", "tblComputer", _
"[SerialNum]= '" & Me!txtSerialNum & "'") 
 
MsgBox "This serial number is in use. The user name: " & _
[INDENT]sUserName & " has the computer."
[/INDENT]

I was thinking a total rewrite to simplify but not now unless you could guarentee there was always a user name in the record with each serial number.

Play with both to see which one can work for you.

-dK

Edit: Modify existing MsgBox function in accordance with what we have established - I shortened here for example purposes only.

 

[javier_83]

thnkas let me try it

 

[javier_83]

ok, the second one works, the only problem is that i only record the id of the person and by a combobox i get the name, because all the info of the person its in another table

so how can i get the full name, because i only get the id!!

 

[javier_83]

another problem, this doesnt let me to add any numer, it show me a Null problem, error 94

if i write a duplicated serial its works fine, onlye the issue i explian to you about the id, but if add the correct serial, it shot me that error 94

 

[dkinley]

The null issue is because there is no duplicate and DLookup can't handle null values.

We avoid this by asking the question of DLookup ... IsNull ... which returns a true or false and act on that response. Which is why I edited for clarification to use the second DLookup to modify the MsgBox of the first code example .. because now we know that DLookup will not be null so we can just grab the name.

We will fix the null issue in a moment, the name issue should be handled first because it just came to my attention that you are using a UserNameID field reference for the user.

First, for every serial number in your table of serial numbers - is it guarenteed that the UserNameID field is filled? For example, those serial numbers that aren't assigned to a specific person, does that box still get an assignment of a UserNameID that correlates to "Stock" or "Warehouse"?

-dK

 

[javier_83]

everytime i assing a serial its to a person, so theres always a person assing

 

[dkinley]

Bear with me ... knocking around a few ideas trying to determine the best solution.

-dK

 

[javier_83]

i try this but doesnt work!!

what do you think

Private Sub serial_BeforeUpdate(Cancel As Integer)
Dim Person
Dim nombreperson
If Not IsNull(DLookup("[serial]", "registroasset", "[Serial]= '" & Me!serial & "'")) Then
Person = DLookup("[id_persona]", "registroasset", "[Serial]= '" & Me!serial & "'")
nombreperson = DLookup("[Nombre_personas]", "Personas", "[id_persona]= '" & Person & "'")
MsgBox "Ese serial ya esta asignado a la persona: " & Person & vbCrLf & "Porfavor revisa el Serial nuevamente.", vbCritical, "Serial Duplicado"
Me.serial.Undo
Cancel = True
End If
End Sub

 

[dkinley]

This will work; however, that's 3 DLookups and it might impact the performance of your database if you have tons of records (or growing into tons of records). I would prefer something that is query-based for this sort of dynamic activity. In this particular scenario this is outside of my expertise.

I would be interested in hearing from others about other (and more optimized) methods of achieving this goal.

    Dim iCPUAssignedUserID As Long
    Dim sCPUAssignedUserName As String
 
    iCPUAssignedUserID = Nz(DLookup("[UserID]", "tblComputer", _
        "[SerialNum]= '" & Me!txtSerialNum & "'"), 0)
    If iCPUAssignedUserID <> 0 Then
        sCPUAssignedUserName = DLookup("[FirstName]", "tblUser", _
            "[UserID]= " & iCPUAssignedUserID ) & " " & DLookup("[LastName]", "tblUser", _
            "[UserID]= " & iCPUAssignedUserID )
 
        MsgBox "There is already a serial number with this value." & vbCrLf & _
            "It is assigned to user: " & sCPUAssignedUserName & vbCrLf & _
            "Please enter a different serial number.", vbCritical, "Duplicate Serial Number"
        Me.Undo
        Cancel = True
 
    End If

-dK

Edit: Requesting hearing from others even though I know this particular implementation does not comply with Pat's usual implementation of preventing a record from being saved by utilizing another variable.

 

[javier_83]

i have the null error again

 

[dkinley]

Strange ... check out the attached.

-dK

 

[javier_83]

damn, you did it!!

only one thing

if i only use one name (not first and last)

the code will be like this??


Private Sub txtSerialNum_AfterUpdate()
Dim sCPUAssignedUserID
Dim sCPUAssignedUserName
sCPUAssignedUserID = Nz(DLookup("[UserID]", "tblComputer", _
"[SerialNum]= '" & Me!txtSerialNum & "'"), 0)
If sCPUAssignedUserID <> 0 Then
sCPUAssignedUserName = DLookup("[FirstName]", "tblUser", _
"[UserID]= " & sCPUAssignedUserID) & " "
If sCPUAssignedUserID = Me!cboUserID Then

Else

MsgBox "There is already a serial number with this value." & vbCrLf & _
"It is assigned to user: " & sCPUAssignedUserName & vbCrLf & _
"Please enter a different serial number.", vbCritical, "Duplicate Serial Number"
Me.Undo
Cancel = True
End If
End If
End Sub

 

[dkinley]

Ah, thought you wanted both names ....


Change:

sCPUAssignedUserName = DLookup("[FirstName]", "tblUser", _
"[UserID]= " & sCPUAssignedUserID) & " "

To: (no & " " at the end)

sCPUAssignedUserName = DLookup("[FirstName]", "tblUser", _
"[UserID]= " & sCPUAssignedUserID)

-dK

 

[dkinley]

Just for future reference ... remember the DLookup returns a Null if nothing is found so you have to plan on dealing with that fact (I suspect you were getting again because of not containing the null.)

We use tools like IsNull and Nz to get around this fact, feel free to check these out in Access Help for more info to add these to your bag of tricks.

-dK

 

[javier_83]

it works!!!

Thanks man!! you help me a lot!!!

thank you very much!!

 

[dkinley]

No hay de que.

-dK

 

[javier_83]

do you speak spanish??