VB Code to detect duplicate entries in form at time of entry 1

[nala1]

I need to know what the VB code would be to compare these things for a possible duplicate entry:file name/vendor name/amount dueI took Visual Basic many moons ago; I wasn't that good and don't remember. Anyone?

 

[randy700]

DLookup might do the trick.

Randy

 

[TheAceMan1]

How are ya nala1 . . .

[blue]randy700[/blue] is on target. Here's an example (assuming you've given three field names) in the forms [blue]Before Update[/blue] event:

[blue]   Dim Cri As String
   
   Cri = "[File Name] = '" & Me![File Name] & "' AND " & _
         "[Vendor Name] = '" & Me![Vendor Name] & "' AND " & _
         "[Amount Due] = " & Me![Amount Due]
   
   If Not IsNull(DLookup("[PrimaryKeyName]", "TableName", Cri)) Then
      MsgBox "Duplicate Record Found!", _
             vbCritical + vbOKOnly, _
             "Duplicate Detected! . . ."
      Cancel = True
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[nala1]

This is sort of working...when I first enter the duplicate record nothing happens and I am able to proceed to the next record. If I go back to the previous record (that was a duplicate) and click anywhere then it tells me Duplicate Record Found! I'd actually like to have this happen during the duplicate entry but let the duplicate entry occur if so desired.

Thanks!

 

[MajP]

Please provide your code. Is it in the before update event of the form?

 

[TheAceMan1]

[blue] ... when I first enter the duplicate record nothing happens and [purple]I am able to proceed to the next record.[/purple][/blue]

The message should pop-up as soon as you attempt to goto another record. Also when you click [blue]OK[/blue] in the messagebox you should return to the record you were editing ... still in edit mode.

It doesn't sound like your using the forms [blue]Before Update[/blue] event! Also, ... [blue]you[/blue] substitute proper names in [purple]purple[/purple]

[blue]   Dim Cri As String
   
   Cri = "[[purple][b]File Name[/b][/purple]] = '" & Me![[purple][b]File Name[/b][/purple]] & "' AND " & _
         "[[purple][b]Vendor Name[/b][/purple]] = '" & Me![[purple][b]Vendor Name[/b][/purple]] & "' AND " & _
         "[[purple][b]Amount Due[/b][/purple]] = " & Me![[purple][b]Amount Due[/b][/purple]]
   
   If Not IsNull(DLookup("[[purple][b]PrimaryKeyName[/b][/purple]]", "[purple][b]TableName[/b][/purple]", Cri)) Then
      MsgBox "Duplicate Record Found!", _
             vbCritical + vbOKOnly, _
             "Duplicate Detected! . . ."
      Cancel = True
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[nala1]

Hey TheAceMan1 - later in the evening I realized that I hadn't placed the code properly so when I did it worked.. I should have updated this post but thank you. NOW - I would like to add the records regardless especially since it's highly conceivable that there could be an exact billing but we need to check these things first that's why the Duplicate Record alert. It'll bring to the person's attention that they need to investigate before continuing. I've seen code out there but you're so good! that I'm hoping you can give me the code real quick! Thank You!

 

[nala1]

TheAceMan1 - ignore the reply up above as I've made that change. I don't know if this next thing is do-able and it's way beyond me! We would like to move anything that has been paid to a new file at the time the payment entry is put through so that we can report on that. So...once the paid amount and date are entered I'd like the information to get moved to another file, one called paid perhaps. Thanks again!

 

[TheAceMan1]

[blue]NOW - I would like to add the records regardless ...[/blue]

Just remove the [blue]Cancel = True[/blue] line.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[nala1]

TheAceMan1 - did that already (see ignore statement above).

I don't know if this next thing is do-able and it's way beyond me! We would like to move anything that has been paid to a new file at the time the payment entry is put through so that we can report on that. So...once the paid amount and date are entered I'd like the information to get moved to another file, one called paid perhaps. Thanks again!

 

[nala1]

TheAceMan1 - I figured all of this out. Thanks again though for all of your help...you've been a great source of help! Thank You.

 

[nala1]

TheAceMan1 - one last request! I have a table with two fields and both fields show up just fine in the form however in my report I can't get one of the fields to show. I can't figure it out - any ideas?

Thank You!

 

[TheAceMan1]

nala1 . . .

Start another post! I'll look out for it ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]