Error if filenumber is same as previous. 1

[sparkbyte]

I am trying to build a check into the input form that checks make sure that the user is not trying to input a filenumber multiple times (more than once) for the same BoxNumber.

I can't seem to find an example via google, so once again I am hoping someone here as an example or can point me in the right direction.



Thanks

John Fuhrman

 

[PHV]

Have a look at the DLookUp function.

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

 

[sparkbyte]

OK, looked at Dlookup which led me to Dcount.

I have tried putting the code into multiple subs but all seem to error with te same thing.

Run-time error '4104':

The multi-part identifier "Me.FileNumber.Value" could not be bound.

If DCount("FileNumber", "tblTrackingTable", "FileNumber=Me.FileNumber.Value AND BoxNumber=Me.BoxNumber.Value") >= 1 Then
            strResult = MsgBox("You have attempted to enter a duplicate" & _
                        "File Number for " & Me.BoxNumber.Value & vbCrLf & _
                       "Please Correct the File Number ...", _
                       vbExclamation + vbOKOnly, _
                       "ERROR!")
                Select Case strResult
                    Case vbOK, vbRetry, vbYes, vbNo
                        Exit Sub
                    Case vbCancel, vbAbort, vbIgnore
                        Exit Sub
                    Case Else
                        Exit Sub
                End Select
End If

Thanks

John Fuhrman

 

[PHV]

If DCount("*", "tblTrackingTable", "FileNumber='" & Me!FileNumber & "' AND BoxNumber='" & Me!BoxNumber & "'") >= 1 Then

BTW, you could create a unique index on (FileNumber,BoxNumber) in tblTrackingTable ...

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

 

[sparkbyte]

That did it.

Placed it in the beforeupdate and after all the other checks in that event.

Thanks!!!!

A Star

If DCount("*", "tblTrackingTable", "FileNumber='" & Me!FileNumber & "' AND BoxNumber='" & Me!BoxNumber & "'") >= 1 Then
    Cancel = True
            strResult = MsgBox("You have attempted to enter a duplicate" & _
                        "File Number for " & Me.BoxNumber.Value & vbCrLf & _
                       "Please Correct the File Number ...", _
                       vbExclamation + vbOKOnly, _
                       "ERROR!")
                Select Case strResult
                    Case vbOK, vbRetry, vbYes, vbNo
                        Exit Sub
                    Case vbCancel, vbAbort, vbIgnore
                        Exit Sub
                    Case Else
                        Exit Sub
                End Select
End If

If you could elaborate on the changes it would be very helpful!!

I see "*" as the first argument changed and the referrences to teh form fields changed from {me.BoxNumber} to me[red]![/red]BoxNumber.

It also looks like I missed the single quotes "'" for the field data.

Thanks

Thanks

John Fuhrman